Chapter11.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