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