www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

Overview
Installation Guide
Quick Start & Tours
Sample ODBC & JDBC Applications
Conceptual Overview
Administration
Data Access Interfaces
Virtual Database Engine
SQL Reference
Virtuoso Cluster Programming
SQL Procedure Language Guide
General Principles
Scope of Declarations
Data Types
Handling Result Sets
Result Sets and Array Parameters
Exception Semantics
Virtuoso/PL Syntax
Execute Stored Procedures via SELECT statement
Execute Stored Procedures In Background
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
Vectored Execution and Query Parallelization
Database Event Hooks
Data Replication, Synchronization and Transformation Services
Web Application Development
XML Support
RDF Data Access and Data Management
Web Services
Runtime Hosting
Internet Services
Free Text Search
TPC C Benchmark Kit
Using Virtuoso with Tuxedo
Appendix
Virtuoso Functions 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. Grand 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