Drop Stored

Procedure

Drops or removes one or more stored procedures from the current database.

Syntax

DROP PROCEDURE procedure-name

Example

The following statement drops the stored procedure myproc from the dictionary:

DROP PROCEDURE myproc

Create Stored Procedure

The CREATE PROCEDURE statement creates a new stored procedure. Stored Procedures consist of statements you precompile and save in a dictionary.

Syntax

CREATE PROCEDURE procedure-name ( parameter [ , parameter ]... )

[ RETURNS data-type 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

| pamameter-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' ]

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

transaction-statement ::= commit-statement

| rollback-statement

| release-statement

commit-statement ::= see COMMIT statement

rollback-statement ::= see ROLLBACK WORK statement

release-statement ::= see RELEASE 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 must begin with ':' (that is, with a colon). Likewise, in a procedure, the name of a parameter must begin with ':' (that is, with a colon), both in the definition and use of the variable or parameter.

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

BEGIN

        INSERT INTO Enrolls

        VALUES(:Stud_id, :Class_id, 0.0);

END;

CALL Enrollstudent(1023456781, 146)

SELECT * FROM Enrolls WHERE Student_id = 1023456781

This select statement will display the row that was added by calling the procedure and passing arguments as:

"Student_ID", "Class_ID", "Grade"

1023456781, 146, 0

The following example procedure is called by the trigger CheckCourseLimit. The 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 before updating the Enrolls table.

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 Succesful.';

END IF;

END;

CALL Checkmax(101)

An example for using the OUT parameter when creating stored procedures. Calling this procedure will return the value of the TINYINTCOL that satisfies the WHERE clause into the variable :outval.

CREATE PROCEDURE PROCOUT

(out :outval TINYINT)

AS BEGIN

SELECT TinyIntCol into :outval

FROM TABLE

WHERE INTEGERCOL = 1;

END;

An example for using the INOUT parameter when creating stored procedures. Calling this procedure will require an INPUT parameter :IOVAL and return the value of the output in the variable :IOVAL. The procedure will set the value of this variable based on the input and the IF condition.

CREATE PROCEDURE PROC_IO_DATE

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