Top

Chapter 11. SQL Procedure Language Guide

Abstract

Stored procedures are a key component of database performance. The fewer messages are sent between the client and the server for a given transaction, the faster it will complete.

Virtuoso/PL is a simple and straightforward language for writing stored procedures and triggers in Virtuoso. Its syntax is a combination of SQL and C, making learning it as easy as possible. It offers the features commonly found in database procedure languages in a simple, efficient and concise package. This document presents the primary concepts of the language and ends with a reference section.

Table of Contents

11.1. General Principles
11.2. Scope of Declarations
11.3. Data Types
11.4. Handling Result Sets
11.5. Result Sets and Array Parameters
11.6. Exception Semantics
11.7. Virtuoso/PL Syntax
11.7.1. Create Procedure Statement
11.7.2. Grant Execute Statement
11.7.3. Stored Procedures as Views & Derived Tables
11.7.4. Keyword and Optional Procedure Arguments
11.7.5. if, while, for, foreach statements
11.7.6. compound statement
11.7.7. goto, return statements
11.7.8. whenever statement
11.7.9. call, assignment statements
11.7.10. open, fetch, close, select ... into statements
11.7.11. FOR Select Statement
11.7.12. SET statement
11.7.13. SET Triggers
11.7.14. Vectored Procedures
11.7.15. FOR VECTORED Statement
11.7.16. Limitations on Vectored Code
11.7.17. Data Types and Vectoring
11.8. Execute Stored Procedures via SELECT statement
11.9. Execute Stored Procedures In Background
11.10. CREATE ASSEMBLY Syntax - External Libraries
11.11. CREATE PROCEDURE Syntax - External hosted procedures
11.12. Asynchronous Execution and Multithreading in Virtuoso/PL
11.12.1. Synchronization
11.13. Performance Tips
11.13.1. Remember the following:
11.14. Procedures and Transactions
11.15. Distributed Transaction & Two Phase Commit
11.15.1. Initiating Distributed Transactions
11.15.2. Responding to Distributed Transactions
11.15.3. 2PC Log & Recovery
11.15.4. Error Codes
11.16. Triggers
11.16.1. The CREATE TRIGGER statement
11.16.2. Triggers on Views
11.16.3. The DROP TRIGGER statement
11.16.4. Triggers and Virtual Database
11.17. Character Escaping
11.17.1. Statement Level
11.17.2. Connection Level
11.17.3. Server Default
11.18. Virtuoso/PL Scrollable Cursors
11.18.1. Declaring a Scrollable Cursor
11.18.2. Opening a Scrollable Cursor
11.18.3. Fetching Data From a Scrollable Cursor
11.18.4. Virtuoso/PL Scrollable Cursor Examples
11.18.5. FORWARD-ONLY (traditional cursor statement) Example
11.18.6. DYNAMIC (traditional cursor statement) Example
11.18.7. KEYSET (traditional cursor statement) Example
11.19. Virtuoso PL Modules
11.19.1. Syntax
11.19.2. Security
11.20. Handling Conditions In Virtuoso/PL Procedures
11.20.1. Declaring Condition Handlers
11.20.2. Stack Trace Reporting On Sql Error Generation
11.21. Procedure Language Debugger
11.21.1. Branch Coverage
11.21.2. Coverage Functions
11.22. Row Level Security
11.22.1. Row Level Security Functions
11.23. Vectored Execution and Query Parallelization
11.23.1. Automatic Query Parallelization
11.23.2. Configuration Parameters for Vectoring and Parallelization

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.

Prev     Next
10.9. Troubleshooting  Home  11.2. Scope of Declarations