Define an SQL-invoked procedure.

 

Syntax

 

<user-defined procedure> ::=

 

 

 

CREATE PROCEDURE [ schema-name. ] procedure-name

<SQL parameter declaration list>

<routine characteristics>

[ DESCRIPTION <character string literal> ]

[ AS ] <routine body>

 

 

Usage

 

The CREATE PROCEDURE statement creates a user-defined procedure that is stored in the database.

 

 

Notes

 

The schema and procedure names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The procedure is created in the database specified by schema-name. The current database is implicit if a schema name is not specified.



 



 


Procedure parameters can be specified with a parameter mode of IN, OUT or INOUT. In is implicit if a parameter mode is not specified.




 


SQL parameter names shall conform to the rules for identifiers in NexusDB SQL.




 



 


LANGUAGE SQL is implicit if not specified. LANGUAGE CLR defines a CLR routine.




 


NOT DETERMINISTIC is implicit if neither DETERMINISTIC nor NOT DETERMINISTIC is specified.




 


If an SQL-data access indication is not specified, then CONTAINS SQL is implicit for SQL routines and NO SQL is implicit for CLR routines. The procedure cannot be called by another routine with a lower SQL-data access level.




 


The null-call clause shall not be specified in a procedure definition.




 

DESCRIPTION is a free text attribute used to store a comment in the procedure descriptor.



 



 


SQL procedures shall specify a SQL routine body, and CLR procedures shall specify an external body reference.


An SQL procedure can return a cursor by specifying a SELECT statement as the last statement in the routine body.    



 

 

Examples

 

1)

The following example creates a procedure that inserts a new row in the courses table with values passed through four parameters:



 


CREATE PROCEDURE addCourse


(




// Parameter declarations with implicit IN mode


p_courseID SMALLINT,


p_courseName CHAR(20),


p_department CHAR(20),


p_numCredits TINYINT




)


MODIFIES SQL DATA -- We need write-access to update the courses table


// We prefer to use a compound statement in the routine body, even with a single statement


BEGIN




INSERT INTO courses


VALUES ( p_courseID, p_courseName, p_department, p_numCredits );




END



 

2)

The following example creates a procedure that raises all teacher salaries by a given percent and uses an OUT parameter to signal whether or not the transaction succeeded:



 


CREATE PROCEDURE raiseSalaries


(




IN percent FLOAT,


OUT done BOOLEAN




)


/*


LANGUAGE SQL is implicit


NOT DETERMINISTIC is implicit


*/


MODIFIES SQL DATA -- We need write-access to update the teachers table


// Multiple statements in the routine body must appear inside a compound statement


BEGIN




DECLARE rate FLOAT;


SET rate = 1 + ( percent / 100 );


START TRANSACTION;


TRY




UPDATE teachers SET salary = ROUND( salary * rate );


COMMIT;


SET done = TRUE;




CATCH ( TRUE )




ROLLBACK;


SET done = FALSE;




END;




END



 

3) The following example creates a procedure that returns a cursor derived from the students table:

 


CREATE PROCEDURE studentNames


( IN isFullName BOOLEAN )


READS SQL DATA -- We need read-access to execute the SELECT statement


BEGIN




IF isFullName THEN




SELECT lastName, firstName FROM students;




ELSE




SELECT firstName FROM students;




END IF;




END



 

 


 

Conformance

 

SQL:2003 standard

 

 

NexusDB extensions

-

-

 

-

-

Core SQL

Feature B128 "Routine language SQL"

 

DESCRIPTION

AS clause before the routine body