# 9. SQL Procedure Language Guide

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Procedure Language Guide
General Principles
Scope of Declarations
Data Types
Handling Result Sets
Result Sets and Array Parameters
Exception Semantics
Virtuoso/PL Syntax
CREATE ASSEMBLY Syntax - External Libraries
CREATE PROCEDURE Syntax - External hosted procedures
Asynchronous Execution and Multithreading in Virtuoso/PL
Performance Tips
Procedures and Transactions
Distributed Transaction & Two Phase Commit
Triggers
Character Escaping
Virtuoso/PL Scrollable Cursors
Virtuoso PL Modules
Handling Conditions In Virtuoso/PL Procedures
Procedure Language Debugger
Row Level Security

## 9.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.

Previous Contents of SQL Procedure Language Guide | Chapter Contents | Next Scope of Declarations