CREATE PROCEDURE

The CREATE PROCEDURE statement creates a new stored procedure. Stored procedures are SQL statements that are pre-defined and saved in the database dictionary.

Syntax

CREATE PROCEDURE procedure-name 
( [ parameter [, parameter ]... ] ) 
[ RETURNS ( result [ , result ]... ) ] see Remarks 
[ WITH DEFAULT HANDLER | WITH EXECUTE AS `MASTER' | 
WITH DEFAULT HANDLER , EXECUTE AS `MASTER' | WITH 
EXECUTE AS `MASTER', DEFAULT HANDLER] 
as-or-semicolon 
proc-stmt 

procedure-name ::= user-defined-name 
parameter ::= parameter-type-name data-type [ DEFAULT proc-expr | = proc-
expr ] 
| SQLSTATE 

parameter-type-name ::= parameter-name 
| parameter-type parameter-name 
| parameter-name parameter-type 

parameter-type ::= IN | OUT | INOUT | IN_OUT 
parameter-name ::= :user-defined-name 
proc-expr ::= same as normal expression but does not allow IF expression, 
or ODBC-style scalar functions 
result ::= user-defined-name data-type  
as-or-semicolon ::= AS | ; 
proc-stmt ::=	 [ label-name : ] BEGIN [ATOMIC] [ proc-stmt [ ; proc-
stmt ]... ] END [ label-name ] 
| CALL procedure-name ( proc-expr [ , proc-expr ]... ) 
| CLOSE cursor-name 
| DECLARE cursor-name CURSORFOR select-statement [ FOR UPDATE | 
FOR READ ONLY ] 
| DECLARE variable-name data-type [ DEFAULT proc-expr | = proc-
expr ] 
| DELETE WHERE CURRENT OF cursor-name 
| delete-statement 
| FETCH [ fetch-orientation [ FROM ] ]cursor-name [ INTO variable-
name [ , variable-name ] ] 
| IF proc-search-condition THEN proc-stmt [ ; proc-stmt ]... [ ELSE 
proc-stmt [ ; proc-stmt ]... ] END IF 
| IF proc-search-condition proc-stmt [ ELSE proc-stmt ] 
| insert-statement 
| LEAVE label-name 
| [ label-name : ] LOOP proc-stmt [ ; proc-stmt ]... END LOOP 
[ label-name ] 
| OPEN cursor-name 
| PRINT proc-expr [ , 'string' ] 
- applies only to Windows-based platforms 
| RETURN [ proc-expr ] 
| transaction-statement 
| select-statement-with-into 
| select-statement 
| SET variable-name = proc-expr 
| SIGNAL [ ABORT ] sqlstate-value 
| START TRANSACTION 
| update-statement 
| UPDATE SET column-name = proc-expr [ , column-name = proc-expr 
]... WHERE CURRENT OF cursor-name 
| [ label-name : ] WHILE proc-search-condition DO [ proc-stmt [ ; 
proc-stmt ] ]... END WHILE [ label-name ] 
| [ label-name : ] WHILE proc-search-condition proc-stmt  
| alter-table-statement  
| create-index-statement  
| create-table-statement  
| create-view-statement  
| drop-index-statement  
| drop-table-statement  
| drop-view-statement  
| grant-statement  
| revoke-statement  
| set-statement  

transaction-statement ::= commit-statement 
| rollback-statement 
| release-statement 
commit-statement ::= see COMMIT statement 
rollback-statement ::= see ROLLBACK statement 
release-statement ::= see RELEASE SAVEPOINT statement 
create-table-statement ::= see CREATE TABLE statement 
alter-table-statement ::= see ALTER TABLE statement 
drop-table-statement ::= see DROP TABLE statement 
create-index-statement ::= see CREATE INDEX statement 
drop-index-statement ::= see DROP INDEX statement 
create-view-statement ::= see CREATE VIEW statement 
drop-view-statement ::= see DROP VIEW statement 
grant-statement ::= see GRANT statement 
revoke-statement ::= see REVOKE statement 
set-statement ::= see SET DECIMALSEPARATORCOMMA statement 
label-name ::= user-defined-name 
cursor-name ::= user-defined-name 
variable-name ::= user-defined-name 

proc-search-condition ::= same as normal search-condition, but does not 
allow any expression that includes a subquery. 
fetch-orientation ::= | NEXT 
sqlstate-value ::= 'string'

Remarks

To execute stored procedures, use the CALL or EXECUTE statement.

Note that, in a procedure, the name of a variable and the name of a parameter must begin with a colon (:), both in the definition and use of the variable or parameter.

The RETURNS clause is required if the stored procedure returns a result set or a scalar value.

The WITH DEFAULT HANDLER clause, when present, causes the procedure to continue execution when an error occurs. The default behavior (without this clause) is to abort the procedure with SQLSTATE set to the error state generated by the statement.

The use of a StmtLabel at the beginning (and optionally at the end) of an IF statement is an extension to ANSI SQL 3.

The PRINT statement applies only to Windows-based platforms. It is ignored on other operating system platforms.

In SQL Editor, the only way to test a stored procedure by using variable parameters is to call the stored procedure from another stored procedure. This technique is shown in the example for pdate (CREATE PROCEDURE pdate();).

You may use variables as SELECT items only within stored procedures. This technique is shown in the example for varsub1 (CREATE PROCEDURE varsub1 ();).

You cannot use the CREATE DATABASE or the DROP DATABASE statement in a stored procedure.

Trusted and Non-Trusted Stored Procedures

A trusted stored procedure includes the WITH EXECUTE AS `MASTER' clause. See Trusted and Non-trusted Objects .

Memory Caching

By default, the database engine creates a memory cache in which to store multiple stored procedures for the duration of the SQL session. Once a stored procedure is executed, its compiled version is then retained in the memory cache. Typically, caching results in improved performance for each subsequent call to a cached procedure. The cache provides no performance improvement the first time that a stored procedure is executed since the procedure has not yet been loaded into memory.

Two SET statements apply to the memory cache:

SET CACHED_PROCEDURES - specifies the number of procedures to cache. The default is 50.

SET PROCEDURES_CACHE - specifies the amount of memory for the cache. The default is 5 MB.

Note that excessive memory swapping, or thrashing, could occur depending on the cache settings and the SQL being executed by your application. Thrashing can cause a decrease in performance.

Caching Exclusions

A stored procedure is not cached, regardless of the cache setting(s), for any of the following:

The stored procedure references a local or a global temporary table. A local temporary table has a name that begins with the pound sign (#). A global temporary table has a name that begins with two pound signs (##). See CREATE (temporary) TABLE .

The stored procedure contains any data definition language (DDL) statements. See Data Definition Statements .

The stored procedure contains an EXEC[UTE] statement used to execute a character string, or an expression that returns a character string. For example: EXEC ('SELECT Student_ID FROM ' + :myinputvar).

Data Type Restrictions

The following data types cannot be passed as parameters or declared as variables in a stored procedure or trigger:

Table 3-2 Data Types Prohibited in Stored Procedures and Triggers
BFLOAT4	BFLOAT8
MONEY	NUMERIC
NUMERICSA	NUMERICSTS

Limits

The following limitations must be observed when creating stored procedures:


Attribute	Limit
Number of columns allowed in a trigger or stored procedure	300
Number of arguments in a parameter list for a stored procedure	300
Size of a stored procedure	64 KB

Examples

The following example creates stored procedure Enrollstudent, which inserts a record into the Enrolls table, given the Student ID and the Class ID.

CREATE PROCEDURE Enrollstudent(in :Stud_id integer, in 
:Class_Id integer, IN :GPA REAL); 
BEGIN 
INSERT INTO Enrolls VALUES(:Stud_id, :Class_id, 
:GPA); 
END;

Use the following statement to call the stored procedure.

CALL Enrollstudent(1023456781, 146, 3.2)

Use the following statement to retrieve the newly inserted record.

SELECT * FROM Enrolls WHERE Student_id = 1023456781

The CALL and SELECT statements, respectively, call the procedure by passing arguments, then display the row that was added.

The following procedure reads the Class table, using the classId parameter passed in by the caller and validates that the course enrollment is not already at its limit.

CREATE PROCEDURE Checkmax(in :classid integer); 
Begin 
DECLARE :numenrolled integer; 
DECLARE :maxenrolled integer; 
SELECT COUNT(*) INTO :numenrolled FROM Enrolls WHERE 
class_ID = :classid; 
SELECT Max_size INTO :maxenrolled FROM Class WHERE id 
= :classid; 
IF (:numenrolled > :maxenrolled) THEN 
PRINT `Enrollment Failed. Number of students 
enrolled reached maximum allowed for this 
class' ; 
ELSE 
PRINT `Enrollment Possible. Number of students 
enrolled has not reached maximum allowed for 
this class'; 
END IF; 

END; 
CALL Checkmax(101)

Note that COUNT(expression) counts all non-NULL values for an expression across a predicate. COUNT(*) counts all values, including NULL values.

The following is an example of using the OUT parameter when creating stored procedures. Calling this procedure returns the number of students into the variable :outval that satisfies the WHERE clause.

CREATE PROCEDURE PROCOUT (out :outval INTEGER)  
AS BEGIN 
SELECT COUNT(*) INTO :outval FROM Enrolls WHERE Class_Id 
= 101; 
END;

The following is an example of using the INOUT parameter when creating stored procedures. Calling this procedure requires an INPUT parameter :IOVAL and returns the value of the output in the variable :IOVAL. The procedure sets the value of this variable based on the input and the IF condition.

CREATE PROCEDURE PROCIODATE (inOUT :IOVAL DATE) 
AS BEGIN  
IF :IOVAL = '1982-03-03' 
THEN  
SET :IOVAL ='1982-05-05'; 
ELSE  
SET :IOVAL = '1982-03-03'; 
END IF; 

END;

You cannot call the above procedure using a literal value (as in call prociodate('1982-03-03')), because it requires an output parameter. You must first bind the parameter using ODBC calls, or you can test the procedure by creating another procedure to call it, as shown here:

CREATE PROCEDURE pdate(); 
BEGIN 
   DECLARE :a DATE; 
   CALL prociodate(:a); 
   PRINT :a; 

END 
CALL pdate

The following example illustrates using the RETURNS clause in a procedure. This sample returns all of the data from the Class table where the Start Date is equal to the date passed in on the CALL statement.

CREATE PROCEDURE DATERETURNPROC(IN :PDATE DATE)  
RETURNS( 
ID INTEGER, 
Name CHAR(7), 
Section CHAR(3), 
Max_Size USMALLINT, 
Start_Date DATE, 
Start_Time TIME, 
Finish_Time TIME, 
Building_Name CHAR(25), 
Room_Number UINTEGER, 
Faculty_ID UBIGINT 
); 
BEGIN  
SELECT ID, Name, Section, Max_Size, Start_Date, 
Start_Time, Finish_Time, Building_Name, Room_
Number, Faculty_ID FROM CLASS WHERE START_DATE 
= :PDATE; 
END; 

CALL DATERETURNPROC('2001-06-05')

Note that the user-defined names in the RETURNS clause do not have to be named identically to the column names that appear in the selection list, as this example shows.

The following procedure returns data from the Class table where the Start Date is equal to the date passed in on the CALL statement.

CREATE PROCEDURE DateReturnProc (IN :pdate date, IN 
:flag) AS 
BEGIN 
IF (:flag = 1) THEN 
		SELECT ID, Name, Room_Number FROM Class 	 
		WHERE start_date = :pdate;  
ELSE 
		SELECT Name FROM Class WHERE start_date = 	
		:pdate; 	 
END IF; 
END;  
CALL DateReturnProc('2001-06-05', 1)

The following example shows the use of the WHERE CURRENT OF clause, which applies to positioned deletes.

CREATE PROCEDURE MyProc(IN :CourseName CHAR(7)) AS 
BEGIN 
    DECLARE c1 CURSOR FOR SELECT name FROM course  
        WHERE name = :CourseName FOR UPDATE; 
    OPEN c1; 
    FETCH NEXT FROM c1 INTO :CourseName; 
    DELETE WHERE CURRENT OF c1; 
    CLOSE c1; 
END; 

CALL MyProc('HIS 305')

(Note that if you use a SELECT inside of a WHERE clause of a DELETE, it is a searched DELETE not a positioned DELETE.)

The following example shows the use of a variable (:i) as a SELECT item. The example assumes that table1 does not already exist. All records in the person table with an ID greater than 950000000 are selected, then inserted into col2 of table1. Col1 contains the value 0, 1, 2, 3, or 4 as defined by the WHILE loop.

CREATE TABLE table1 (col1 CHAR(10), col2 BIGINT); 
CREATE PROCEDURE varsub1 (); 
BEGIN 
DECLARE :i INT; 
SET :i = 0; 
WHILE :i < 5 DO 
BEGIN 
  INSERT INTO table1 (col1, col2) SELECT :i , A.ID 
FROM PERSON A WHERE A.ID > 950000000; 
  SET :i = :i + 1; 
END; 
END WHILE; 

END 
CALL varsub1 
SELECT * FROM table1 
	-- returns 110 rows

The following is an example of using ATOMIC, which groups a set of statements so that either all succeed or all fail. ATOMIC can be used only within the body of a stored procedure or trigger.

The first procedure does not specify ATOMIC, the second does.

CREATE TABLE t1 (c1 INTEGER) 
CREATE UNIQUE INDEX t1i1 ON t1 (c1) 
CREATE PROCEDURE p1 (); 
BEGIN 
INSERT INTO t1 VALUES (1); 
INSERT INTO t1 VALUES (1); 
END; 

CREATE PROCEDURE p2 (); 
BEGIN ATOMIC  
INSERT INTO t1 VALUES (2); 
INSERT INTO t1 VALUES (2); 
END; 

CALL p1() 
CALL p2() 
SELECT * FROM t1

Both procedures return an error because they attempt to insert duplicate values into a unique index.

The result is that t1 contains only one record because the first INSERT statement in procedure p1 succeeds even though the second fails. Likewise, the first INSERT statement in procedure p2 succeeds but the second fails. However, since ATOMIC is in procedure p2, all of the work done inside procedure p2 is rolled back when the error is encountered.

This example uses a stored procedure to create two tables and insert one row of default values into each. It then turns on security and grants permissions to user user1.

CREATE PROCEDURE p1 (); 
BEGIN 
   CREATE TABLE t1 (c1 INT DEFAULT 10, c2 INT DEFAULT 
100); 
   CREATE TABLE t2 (c1 INT DEFAULT 1 , c2 INT DEFAULT 2); 
   INSERT INTO t1 DEFAULT VALUES; 
   INSERT INTO t2 DEFAULT VALUES; 
	SET SECURITY = larry; 
END; 
CALL p1 
SELECT * FROM t1 
	-- returns 10, 100 
SELECT * FROM t2 
	-- returns 1, 2

This example uses a stored procedure to revoke privileges from user user1, drop the two tables created in Example A, and turn off database security.

CREATE PROCEDURE p3 (); 
BEGIN 
   REVOKE ALL ON t1 FROM user1; 
   REVOKE ALL ON t2 FROM user1;    
   DROP TABLE t1; 
   DROP TABLE t2; 
   SET SECURITY = NULL;  
END; 
CALL p3 
SELECT * FROM t1 
	-- returns an error, table not found  
SELECT * FROM t2 
	-- returns an error, table not found

The following example shows how to loop through a cursor.

CREATE TABLE atable (c1 INT, c2 INT) 
 INSERT INTO atable VALUES (1,1) 
 INSERT INTO atable VALUES (1,2) 
 INSERT INTO atable VALUES (2,2) 
 INSERT INTO atable VALUES (2,3) 
 INSERT INTO atable VALUES (3,3) 
 INSERT INTO atable VALUES (3,4) 

CREATE PROCEDURE pp (); 
 BEGIN 
   DECLARE :i INTEGER; 
   DECLARE c1Bulk CURSOR FOR SELECT c1 FROM atable ORDER 
BY c1 FOR UPDATE; 
   OPEN c1Bulk; 
     BulkLinesLoop: 
     LOOP  
       FETCH NEXT FROM c1Bulk INTO :i; 
        IF SQLSTATE = '02000' THEN 
          LEAVE BulkLinesLoop; 
        END IF;  
       UPDATE SET c1 = 10 WHERE CURRENT OF c1Bulk; 
     END LOOP; 
     CLOSE c1Bulk; 
 END 
  
CALL pp 
	--Succeeds 
SELECT * FROM atable 
	--Returns 6 rows

This example creates a trusted stored procedure named "InParam." User Master then grants User1 EXECUTE and ALTER permissions to InParam. This example assumes that table t99 exists and contains two columns of type INTEGER.

CREATE PROCEDURE InParam(IN :inparam1 INTEGER, IN 
:inparam2 INTEGER) 
WITH DEFAULT HANDLER, EXECUTE AS 'Master' AS 
BEGIN 
INSERT INTO t99 VALUES(:inparam1 , :inparam2); 
END; 
GRANT ALL ON PROCEDURE InParam TO User1

Using Stored Procedures

As an example, CALL foo(a, b, c) executes the stored procedure "foo" with parameters a, b, and c. Any of the parameters may be a dynamic parameter (`?'), which is necessary for retrieving the values of output and inout parameters. For example: {CALL foo (?, ?, `TX')}. The curly braces are optional in your source code.

This is how stored procedures work in the current version of Pervasive PSQL.

Triggers (CREATE TRIGGER, DROP TRIGGER) are supported as a form of stored procedure. This support includes tracking dependencies that the trigger has on tables, and procedures, in the database. You cannot use CREATE PROCEDURE or CREATE TRIGGER in the body of a stored procedure or a trigger.

CONTAINS, NOT CONTAINS, BEGINS WITH are not supported.

LOOP: post conditional loops are not supported (REPEAT...UNTIL).

ELSEIF: The conditional format uses IF ... THEN ... ELSE. There is no ELSEIF support.

General Stored Procedure Engine Limitations

You should be aware of the following limitations before using stored procedures.

There is no qualifier support in CREATE PROCEDURE or CREATE TRIGGER.

Maximum length of a stored procedure variable name is 128 characters.

See Table 1-1, Identifier Restrictions by Identifier Type, in Advanced Operations Guide for the maximum length of a stored procedure name.

Only partial syntactical validation occurs at CREATE PROCEDURE or CREATE TRIGGER time. Column names are not validated until run time.

There is currently no support for using subqueries everywhere expressions are used. For example, set :arg = SELECT MIN(sal) FROM emp is not supported. However, you could rewrite this query as SELECT min(sal) INTO :arg FROM emp.

Only the default error handler is supported.

Limits to SQL Variables and Parameters

Variable names must be preceded with a colon (:). This allows the stored procedure parser to differentiate between variables and column names.

Variable names are case insensitive.

No session variables are supported. Variables are local to the procedure.

Limits to Cursors

Positioned UPDATE does not accept a table name.

Global cursors are not supported.

Limits when using Long Data

When you pass long data as arguments to an imbedded procedure, (that is, a procedure calling another procedure), the data is truncated to 65500 bytes.

Long data arguments to and from procedures are limited to a total of 2 MB.

Internally long data may be copied between cursors with no limit on data length. If a long data column is fetched from one statement and inserted into another, no limit is imposed. If, however, more than one destination is required for a single long data variable, only the first destination table receives multiple calls to PutData. The remaining columns are truncated to the first 65500 bytes. This is a limitation of the ODBC GetData mechanism.