CREATE PROCEDURE

The CREATE PROCEDURE statement creates a new stored procedure. Stored procedures are SQL statements that are predefined 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 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

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

rollback-statement ::= see ROLLBACK

release-statement ::= see RELEASE SAVEPOINT

create-table-statement ::= see CREATE TABLE

alter-table-statement ::= see ALTER TABLE

drop-table-statement ::= see DROP TABLE

create-index-statement ::= see CREATE INDEX

drop-index-statement ::= see DROP INDEX

create-view-statement ::= see CREATE VIEW

drop-view-statement ::= see DROP VIEW

grant-statement ::= see GRANT

revoke-statement ::= see REVOKE

set-statement ::= see SET DECIMALSEPARATORCOMMA

label-name ::= user-defined-name

cursor-name ::= user-defined-name

variable-name ::= user-defined-name

proc-search-condition ::= same as 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 RETURNS 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 21 Data Types Prohibited in Stored Procedures and Triggers
BFLOAT4	BFLOAT8
MONEY	NUMERICSA
NUMERICSLB	NUMERICSLS
NUMERICSTB	NUMERICSTS

See Examples for how PSQL data types that do not have a direct ODBC equivalent can be correctly mapped to be used by a procedure.

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

This section provides a number of examples of CREATE PROCEDURE.

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.

============

This example shows how to assign a default value to a parameter.

CREATE PROCEDURE ReportTitle1 (:rpttitle1 VARCHAR(20) = 'Finance Department');

BEGIN

PRINT :rpttitle1;

END;

CALL ReportTitle1

CREATE PROCEDURE ReportTitle2 (:rpttitle2 VARCHAR(20) DEFAULT 'Finance Department');

BEGIN

PRINT :rpttitle2;

END;

CALL ReportTitle2

These procedures use the default value specified (Finance Department) if a parameter is not provided with the CALL.

============

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 nonnull 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(

DateProc_ID INTEGER,

DateProc_Name CHAR(7),

DateProc_Section CHAR(3),

DateProc_Max_Size USMALLINT,

DateProc_Start_Date DATE,

DateProc_Start_Time TIME,

DateProc_Finish_Time TIME,

DateProc_Building_Name CHAR(25),

DateProc_Room_Number UINTEGER,

DateProc_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('1995-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

INSERT INTO table1 (col1, col2) SELECT :i , A.ID FROM PERSON A WHERE A.ID > 950000000;

SET :i = :i + 1;

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);

END;

CREATE PROCEDURE p2 ();

BEGIN ATOMIC

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 privileges to 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 u1pword;

GRANT ALL ON * TO user1;

END;

CALL p1

SELECT * FROM t1

-- returns 10, 100

SELECT * FROM t2

-- returns 1, 2

Note When you use the GRANT LOGIN statement in a stored procedure, you must separate the user name and password with a space character rather than a colon character. The colon character is reserved to identify local variables in a stored procedure.

============

This example uses a stored procedure to revoke privileges from 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 on 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

Master and User1 can now call this procedure (for example, CALL InParam(2,4)).

============

This example shows how PSQL data types that do not have a direct ODBC equivalent can be correctly mapped to be used by a procedure. The data types NUMERICSA and NUMERICSTS are the ones without direct equivalents so they are mapped to NUMERIC instead.

CREATE TABLE test1 (id identity, amount1 numeric(5,2), amount2 numericsa(5,2), amount3 numericsts(5,2))

CREATE PROCEDURE ptest2 (IN :numval1 numeric(5,2), IN :numval2 numeric(5,2), IN :numval3 numeric(5,2))

BEGIN

Insert into test1 values(0, :numval1, :numval2, :numval3);

END;

CALL ptest2(100.10, 200.20, 300.30)

SELECT * FROM test1

The procedure correctly formats all the amount values according to the PSQL data types defined in the CREATE TABLE statement, despite the fact that they are all passed to the procedure as NUMERIC. See also PSQL Transactional and Relational Data Types for the mappings of data types.

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 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, 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 an UPDATE statement with set :arg = SELECT MIN(sal) FROM emp is not supported. However, you could rewrite the subquery 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 embedded 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.