NexusDB

Define an SQL-invoked procedure.

Syntax

Usage

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

Notes

       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

       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

       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