Navigation:  NexusDB Guide > SQL Reference > SQL Statements > Schema Statements >

CREATE PROCEDURE statement

Previous pageReturn to chapter overviewNext page

NexusDB V3 Manual

CREATE PROCEDURE statement

button_main button_prev button_next

NexusDB Manual V3 > SQL Reference > SQL Statements > Schema Statements

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

 

© Nexus Database Systems Pty Ltd.

nexus_logo