SQL-Invoked Routines

Wed, 03/04/2009 - 20:02 |  hdanzl

Define an SQL-invoked routine.


Syntax

<SQL-invoked routine> ::=

The following syntax elements are common to both procedures and functions:

<SQL parameter declaration list> ::=
<SQL parameter declaration> ::=

[ <parameter mode> ] parameter-name <data type>

<parameter mode> ::=

|

|

IN

OUT

INOUT
<routine characteristics> ::= [ <routine characteristic>... ]
<routine characteristic> ::=
<language clause> ::= LANGUAGE { SQL | CLR }
<deterministic characteristic> ::=

|

DETERMINISTIC

NOT DETERMINISTIC
<SQL-data access indication> ::=

|

|

|

NO SQL

CONTAINS SQL

READS SQL DATA

MODIFIES SQL DATA
<null-call clause> ::=

|

RETURNS NULL ON NULL INPUT

CALLED ON NULL INPUT
<routine body> ::=
<SQL routine body> ::= <SQL procedure statement>
<external body reference> ::= EXTERNAL [ NAME <external routine name> ]

<external routine name> ::= assembly-name [ .class-name [ .method-name ] ]

<routine invocation> ::= routine-name <SQL argument list>
<SQL argument list> ::= ( [ <SQL argument> [ { , <SQL argument> }... ] ] )
<SQL argument> ::=

SQL parameter declaration

♦ A parameter mode of IN is implicit if parameter mode is not specified.
♦ The OUT parameter has an initial value of null, and is used to output values from a procedure.
♦ The INOUT parameter is a combined IN and OUT parameter.
♦ Function definitions shall not specify a parameter mode.
♦ SQL parameter names shall conform to the rules for identifiers in NexusDB SQL.

Routine characteristics

♦ Language clause

       NexusDB SQL supports user-defined procedures and functions written in SQL or hosted in a .NET assembly.

♦ LANGUAGE SQL defines an SQL routine, and is implicit if the clause is omitted. SQL routines shall specify an SQL routine body.
♦ LANGUAGE CLR defines a .NET CLR routine. CLR routines shall specify an external body reference, which is a reference to a class method in a .NET assembly.

Note: CLR routines are supported in the Enterprise Edition only.

♦ Deterministic characteristic

       If neither DETERMINISTIC nor NOT DETERMINISTIC is specified, then NOT DETERMINISTIC is implicit. A DETERMINISTIC routine is guaranteed to produce the same result every time it is invoked with the same argument values, and may therefore be optimized by caching the result. NOT DETERMINISTIC routines are re-evaluated each time.

♦ SQL-data access indication

       Defines whether or not the routine contains SQL statements, and if the routine is allowed to read or change SQL data.

♦ NO SQL can only be used with CLR routines, and means that the external routine contains no SQL statements. NO SQL is implicit for CLR routines if no SQL-data access indication is specified.
♦ CONTAINS SQL is implicit for SQL routines if no SQL-data access indication is specified, and means that the routine may contain SQL statements, except any of the data statements.
♦ READS SQL DATA means that the routine may contain SQL statements, including the SELECT statement, but is not allowed to specify any of the data change statements.
♦ MODIFIES SQL DATA means that the routine may contain any of the SQL procedure statements, including the data-change statements.
♦ A routine is only allowed to call other routines that specify the same or a higher SQL-data access level.
♦ Null-call clause

       Is used to specify whether or not a function should be invoked if any of the arguments are null.

♦ CALLED ON NULL INPUT, which is implicit if the clause is not specified, means that the function is invoked regardless of null values in the arguments.
♦ RETURNS NULL ON NULL INPUT instructs the engine to return null as the function result if any of the arguments are null, without invoking the function itself.
♦ Procedure definitions shall not specify a null-call clause.

Routine body

♦ SQL Routines

       The body of an SQL routine is defined using an SQL routine body, which is a single SQL procedure statement. The Compound statement can be used to specify multiple SQL statements inside a BEGIN..END block.

♦ CLR Routines

       The body of a CLR routine is defined by providing an EXTERNAL body reference, which is a reference to a .NET assembly that has been registered on the NexusDB Server. Assemblies can be registered directly in the NexusDB Server GUI, or in SQL by using the CREATE ASSEMBLY statement.

♦ The NAME clause may be omitted in the rare case of the assembly name, the class name and the method name being the same as the routine name.
♦ assembly-name is the name used to register the assembly. The assembly can only be referenced by its owner and users with REFERENCES privilege on the assembly.
♦ class-name is the name of an existing class in the assembly. If the class has a multi-part name separated with periods, then the name must be delimited with double quotation marks, for example: AssemblyName."Namespace.ClassName". The class name may be omitted if the name is the same as the assembly name.
♦ method-name is the name of a method defined in the specified class. The method name may be omitted if the name is the same as the class name.

Note: CLR routines are supported in the Enterprise Edition only.


Routine invocation

♦ User-defined procedures are invoked by the CALL statement followed by the routine name and the argument list.
♦ User-defined functions are invoked by the routine name followed by the argument list only.
♦ The number and types of arguments shall match the parameter declarations in the routine definition.
♦ The parentheses in the argument list shall be specified even if the routine has no parameters.
♦ Any arguments that represent an OUT parameter must be a target specification.


Examples

1) The following example calls a user-defined procedure passing two dynamic parameter values and one local SQL variable as arguments:

       CALL setStudentName( :lastName, :firstName, studentName )

2) The following example calls a user-defined function without arguments:

       getSystemTime()

3) The following example calls a user-defined function that takes two arguments:

       getFullName( 'Tiger', 'Woods' )


Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

Core SQL

Feature B128 "Routine language SQL"

Feature T653 "SQL-schema statements in external routines"

LANGUAGE CLR
‹ Procedure LanguageupSQL Procedure Statements ›
Print this chapter |  Login or register to post comments |  Send to friend |  PDF version |  metadata Tags:
Home | Site Contents | Documentation | NexusDB Manual V4 | SQL Reference | Stored Procedures and Functions

Powered by Drupal and Drupal Theme created with Artisteer.