Home | Previous Page | Next Page

Creating and Using SPL Routines

In This Chapter

Introduction to SPL Routines

What You Can Do with SPL Routines

SPL Routine Behavior for Extended Parallel Server

Writing SPL Routines

Using the CREATE PROCEDURE or CREATE FUNCTION Statement

Beginning and Ending the Routine

Specifying a Routine Name

Adding a Specific Name (IDS)

Adding a Parameter List

Adding a Return Clause

Adding Display Labels (IDS)

Specifying Whether or Not the SPL Function is Variant

Adding a Modifier (IDS)

Specifying a Document Clause

Specifying a Listing File

Adding Comments

Example of a Complete Routine

Creating an SPL Routine in a Program

Routines in Distributed Operation

Defining and Using Variables

Declaring Local Variables

Scope of Local Variables

Declaring Built-In Type Variables

Declaring Variables for Smart Large Objects (IDS)

Declaring Variables for Simple Large Objects

Declaring Collection Variables (IDS)

Declaring Row-Type Variables (IDS)

Declaring Opaque- and Distinct-Type Variables (IDS)

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

Expressions in SPL Routines

Writing the Statement Block

Implicit and Explicit Statement Blocks

Using the FOREACH Loop to Define Cursors

Using an IF - ELIF - ELSE Structure

Adding WHILE and FOR Loops

Returning Values from an SPL Function

Returning a Single Value

Returning Multiple Values

Handling Row-Type Data (IDS)

Precedence of Dot Notation

Updating a Row-Type Expression

Handling Collections (IDS)

Using Collection Data Types

Preparing for Collection Data Types (IDS)

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

Executing Routines

Using the EXECUTE Statements

Using the CALL Statement

Executing Routines in Expressions

Executing an External Function with the RETURN Statement

Executing Cursor Functions from an SPL Routine

Dynamic Routine-Name Specification

Rules for Dynamic Routine-Name Specification

Privileges on Routines

Privileges for Registering a Routine

Privileges for Executing a Routine

Granting and Revoking the Execute Privilege

Execute Privileges with COMMUTATOR and NEGATOR Functions (IDS)

Privileges on Objects Associated with a Routine

DBA Privileges for Executing a Routine

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

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]