The support for user-defined procedures and functions, commonly known as stored procedures, makes NexusDB SQL a programmable database language, allowing users to automate tasks and extend the built-in functionality with custom routines.

 

NexusDB SQL supports user-defined procedures and functions written in SQL, called SQL routines, or hosted in a .NET assembly, called CLR routines (Enterprise Edition only). Since user-defined procedures and functions share many common characteristics and syntactic elements, they are both called SQL-invoked routines in standard SQL.

 

The procedure language in NexusDB adds power and flexibility to the functionality of user-defined procedures and functions written in SQL.

 

 

User-defined procedures

 

A user-defined procedure (UDP) can have both IN and OUT parameters. The OUT parameter is used to return a value from the procedure, and is equivalent to the result of a scalar-valued function. Procedures can also return a cursor by specifying a SELECT statement as the last statement in the routine body.

 

User-defined procedures are defined with the CREATE PROCEDURE statement and are invoked in SQL by the CALL statement.

 

The following example shows a procedure definition and how it is called in SQL:

 


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;



 


CALL addCourse( :courseID, :courseName, :department, :numCredits );



 

 

User-defined functions

 

Like functions in traditional programming languages, a user-defined function (UDF) always returns a value. The function result can be either a scalar value (scalar-valued function) or a table derived from a cursor specification (table-valued function), by specifying TABLE in the RETURNS clause of the function definition. A scalar-valued function can be referenced everywhere in SQL a scalar value is expected, while a table-valued function can be referenced in the FROM clause of a SELECT statement or invoked by calling the function as a separate SQL statement.

 

User-defined functions are defined with the CREATE FUNCTION statement and are invoked in SQL by the function name, see routine invocation.

 

The following example shows a function definition and how it is invoked in SQL:

 


CREATE FUNCTION getFullName ( firstName VARCHAR(30), lastName VARCHAR(30) )


RETURNS VARCHAR(61)


/*


LANGUAGE SQL is implicit


NOT DETERMINISTIC is implicit


CONTAINS SQL is implicit


*/


RETURNS NULL ON NULL INPUT -- We don't invoke the function if any of the arguments are null


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


BEGIN




DECLARE name VARCHAR(61);


SET name = firstName || ' ' || lastName;


RETURN name;




END;



 


UPDATE students


SET studentName = getFullName( firstName, lastName )


WHERE studentID = 211;



 

 


 

Procedure Language

SQL-Invoked Routines

SQL Procedure Statements