Keyword Search:
Book Home

Contents
Preface

Overview
Installation Guide
Quick Start & Tours
Sample ODBC & JDBC Applications
Conceptual Overview
Administration
Virtuoso Cluster Operation
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
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

12.1. General Principles
12.2. Scope of Declarations
12.3. Data Types
12.4. Handling Result Sets
12.5. Result Sets and Array Parameters
12.6. Exception Semantics
12.7. Virtuoso/PL Syntax
12.7.1. Create Procedure Statement
12.7.2. Stored Procedures as Views & Derived Tables
12.7.3. Keyword and Optional Procedure Arguments
12.7.4. if, while, for, foreach statements
12.7.5. compound statement
12.7.6. goto, return statements
12.7.7. whenever statement
12.7.8. call, assignment statements
12.7.9. open, fetch, close, select ... into statements
12.7.10. FOR Select Statement
12.7.11. SET statement
12.7.12. SET Triggers
12.8. Execute Stored Procedures via SELECT statement
12.9. Execute Stored Procedures In Background
12.10. CREATE ASSEMBLY Syntax - External Libraries
12.11. CREATE PROCEDURE Syntax - External hosted procedures
12.12. Asynchronous Execution and Multithreading in Virtuoso/PL
12.12.1. Synchronization
12.13. Performance Tips
12.13.1. Remember the following:
12.14. Procedures and Transactions
12.15. Distributed Transaction & Two Phase Commit
12.15.1. Initiating Distributed Transactions
12.15.2. Responding to Distributed Transactions
12.15.3. 2PC Log & Recovery
12.15.4. Error Codes
12.16. Triggers
12.16.1. The CREATE TRIGGER statement
12.16.2. Triggers on Views
12.16.3. The DROP TRIGGER statement
12.16.4. Triggers and Virtual Database
12.17. Character Escaping
12.17.1. Statement Level
12.17.2. Connection Level
12.17.3. Server Default
12.18. Virtuoso/PL Scrollable Cursors
12.18.1. Declaring a Scrollable Cursor
12.18.2. Opening a Scrollable Cursor
12.18.3. Fetching Data From a Scrollable Cursor
12.18.4. Virtuoso/PL Scrollable Cursor Examples
12.18.5. FORWARD-ONLY (traditional cursor statement) Example
12.18.6. DYNAMIC (traditional cursor statement) Example
12.18.7. KEYSET (traditional cursor statement) Example
12.19. Virtuoso PL Modules
12.19.1. Syntax
12.19.2. Security
12.20. Handling Conditions In Virtuoso/PL Procedures
12.20.1. Declaring Condition Handlers
12.20.2. Stack Trace Reporting On Sql Error Generation
12.21. Procedure Language Debugger
12.21.1. Branch Coverage
12.21.2. Coverage Functions
12.22. Row Level Security
12.22.1. Row Level Security Functions