Pervasive logo

Prev SQL Engine Reference Next

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 ]... ) ] 
[ WITH 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 CURSOR FOR 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 
| 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 ] 
| 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 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.

Statements are delimited with a semi-colon inside stored procedures and triggers.

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 Data Manager, 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 ();) .

Data Type Restrictions

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

 
Table 3-1 Data Types Prohibited in Stored Procedures and Triggers
BFLOAT4
BFLOAT8
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 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; 
   GRANT LOGIN TO user1:user1; 
   GRANT SELECT ON t1 TO user1; 
   GRANT INSERT ON t2 TO user1; 
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

Cursor loop example

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

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.SQL.

General Stored Procedure Engine Limitations

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

Limits to SQL Variables and Parameters

Limits to Cursors

Limits when using Long Data

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.

See Also

DROP PROCEDURE

Prev
CREATE INDEX		 Contents
Up
Check for Revisions 		Next
CREATE TABLE