11.1. General Principles

A stored procedure is a named piece of Virtuoso/PL code stored in the SYS_PROCEDURES table. Stored procedures are created with the create procedure statement and are used by executing a procedure call statement through the regular SQL API.

A procedure takes zero or more arguments and optionally returns a value. Procedure arguments may be input, output or input and output. In this manner a procedure may modify a variable passed to it by its caller. If the procedure is called from a call statement executed by a client process, the client process gets back the procedure's return value and the values of output parameters.

Procedures can be called with positional or keyword parameters. A call with positional parameters will bind the first argument in the call to the first parameter in the procedure parameter list and so on. A keyword parameter call allows specifying named parameters, where the argument of a given name is bound to the parameter of the same name in the procedure's parameter list. Procedure parameters may be required or optional. The combination of optional parameters and the keyword call notation make it convenient to have procedures with large numbers of parameters of which only part are used at any one time.

Procedures have local variables and cursors that are not visible to other procedures. Procedures can call each other without limitations, including recursively.

In addition to returning a value and changing values of output parameters a procedure may yield one or more result sets. The client can receive rows in result sets just like rows returned by a select statement. A procedure calling another procedure cannot receive a result set produced by the called procedure, however. While parameters and return values work equally well between procedures as between procedure and client application, a result set always goes to the client, even if the procedure has been called by another procedure. A procedure view is a separate construct which allows a procedure to iterate over another procedure's result set. See the Procedure Views section.

A procedure consists of statements and expressions similar to those of any procedural language. In addition, procedures may contain SQL statements operating on the procedure's arguments and local variables. Writing a stored procedure is thus much like using embedded SQL in C, except that a stored procedure is typically much faster.

The elements of the procedure are:

  • Procedure Declaration.  This is a create procedure statement that names the procedure and its arguments.

  • Variable Declaration.  This declares a local variable for the procedure.

  • Cursor Declaration.  This declares a cursor, A cursor allows a procedure to iterate over the rows produced by a select statement.

  • Manipulative SQL statement.  This can be a delete or update statement, either searched or positioned, a cursor manipulation or other so called routine statement.

  • Control statement.  This is any control structure, loop, assignment or procedure call.

  • Handler declaration.  This specifies what to do in a specific exception situation. Exceptions are error conditions produced by SQL statements (e.g. deadlock) or 'not found' situations.