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
SQL Reference
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
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

9.1. General Principles
9.2. Scope of Declarations
9.3. Data Types
9.4. Handling Result Sets
9.5. Result Sets and Array Parameters
9.6. Exception Semantics
9.7. Virtuoso/PL Syntax
9.7.1. Create Procedure Statement
9.7.2. Grand Execute Statement
9.7.3. Stored Procedures as Views & Derived Tables
9.7.4. Keyword and Optional Procedure Arguments
9.7.5. if, while, for, foreach statements
9.7.6. compound statement
9.7.7. goto, return statements
9.7.8. whenever statement
9.7.9. call, assignment statements
9.7.10. open, fetch, close, select ... into statements
9.7.11. FOR Select Statement
9.7.12. SET statement
9.7.13. SET Triggers
9.8. Execute Stored Procedures via SELECT statement
9.9. Execute Stored Procedures In Background
9.10. CREATE ASSEMBLY Syntax - External Libraries
9.11. CREATE PROCEDURE Syntax - External hosted procedures
9.12. Asynchronous Execution and Multithreading in Virtuoso/PL
9.12.1. Synchronization
9.13. Performance Tips
9.13.1. Remember the following:
9.14. Procedures and Transactions
9.15. Distributed Transaction & Two Phase Commit
9.15.1. Initiating Distributed Transactions
9.15.2. Responding to Distributed Transactions
9.15.3. 2PC Log & Recovery
9.15.4. Error Codes
9.16. Triggers
9.16.1. The CREATE TRIGGER statement
9.16.2. Triggers on Views
9.16.3. The DROP TRIGGER statement
9.16.4. Triggers and Virtual Database
9.17. Character Escaping
9.17.1. Statement Level
9.17.2. Connection Level
9.17.3. Server Default
9.18. Virtuoso/PL Scrollable Cursors
9.18.1. Declaring a Scrollable Cursor
9.18.2. Opening a Scrollable Cursor
9.18.3. Fetching Data From a Scrollable Cursor
9.18.4. Virtuoso/PL Scrollable Cursor Examples
9.18.5. FORWARD-ONLY (traditional cursor statement) Example
9.18.6. DYNAMIC (traditional cursor statement) Example
9.18.7. KEYSET (traditional cursor statement) Example
9.19. Virtuoso PL Modules
9.19.1. Syntax
9.19.2. Security
9.20. Handling Conditions In Virtuoso/PL Procedures
9.20.1. Declaring Condition Handlers
9.20.2. Stack Trace Reporting On Sql Error Generation
9.21. Procedure Language Debugger
9.21.1. Branch Coverage
9.21.2. Coverage Functions
9.22. Row Level Security
9.22.1. Row Level Security Functions