Informix Guide to SQL: Tutorial
Chapter 14: Creating and Using SPL Routines
Home
Contents
Index
Master Index
New Book
Creating and Using SPL Routines
Introduction to SPL Routines
Writing SPL Routines
Using the CREATE PROCEDURE or CREATE FUNCTION Statement
Beginning and Ending the Routine
Specifying a Routine Name
Adding a Specific Name
Adding a Parameter List
Adding a Return Clause
Specifying a Document Clause
Specifying a Listing File
Adding Comments
Defining and Using Variables
Declaring Local Variables
Scope of Local Variables
Declaring Built-In Type Variables
Declaring Variables for Simple Large Objects
Declaring Collection Variables
Declaring Row-Type Variables
Declaring Opaque- and Distinct-Type Variables
Declaring Variables for Column Data with the LIKE Clause
Declaring PROCEDURE Type Variables
Using Subscripts with Variables
Variable and Keyword Ambiguity
Declaring Global Variables
Assigning Values to Variables
The LET Statement
Other Ways to Assign Values to Variables
Writing the Statement Block
Implicit and Explicit Statement Blocks
Using Cursors
The FOREACH Loop
Using an IF - ELIF - ELSE Structure
Expressions in an IF Statement
Adding WHILE and FOR Loops
Exiting a Loop
Returning Values from an SPL Function
Returning a Single Value
Returning Multiple Values
Handling Collections
Collection Examples
The First Steps
Declaring a Collection Variable
Declaring an Element Variable
Selecting a Collection into a Collection Variable
Inserting Elements into a Collection Variable
Inserting into a SET or MULTISET
Inserting into a LIST
Checking the Cardinality of a LIST Collection
Syntax of the VALUES Clause
Selecting Elements from a Collection
The Collection Query
Adding the Collection Query to the SPL Routine
Deleting a Collection Element
Updating the Collection in the Database
Deleting the Entire Collection
Updating a Collection Element
Updating a Collection with a Variable
Updating the Entire Collection
Updating a Collection of Row Types
Updating a Nested Collection
Inserting into a Collection
Inserting into a Nested Collection
Handling Row Types
Updating a Row-Type Column
Precedence of Dot Notation
Executing Routines
The EXECUTE Statements
How to Use the Statements
Using the CALL Statement
Executing Routines in Expressions
Executing Cursor Functions from an SPL Routine
Dynamic Routine-Name Specification
Privileges on Routines
Privileges for Registering a Routine
Privileges for Executing a Routine
Granting and Revoking the Execute Privilege
Privileges on Objects Associated with a Routine
Executing a Routine as DBA
Effect of DBA Privileges on Objects and Nested Routines
Finding Errors in an SPL Routine
Looking at Compile-Time Warnings
Generating the Text of the Routine
Debugging an SPL Routine
Exception Handling
Trapping an Error and Recovering
Scope of Control of an ON EXCEPTION Statement
User-Generated Exceptions
Simulating SQL Errors
Using RAISE EXCEPTION to Exit Nested Code
Checking the Number of Rows Processed in an SPL Routine
Summary
Informix Guide to SQL: Tutorial
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.
