NexusDB Manual V3 > SQL Reference > SQL Statements > Schema Statements
Define an SQL-invoked procedure.
Syntax
<user-defined procedure> ::=
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
|
© Nexus Database Systems Pty Ltd.