SQL Procedures, Triggers, and User-Defined Functions on ...
Front cover
SQL Procedures, Triggers, and
Functions on IBM DB2 for i
Jim Bainbridge
Hernando Bedoya
Rob Bestgen
Mike Cain
Dan Cruikshank
Jim Denton
Doug Mack
Tom Mckinley
Simona Pacchiarini
Redbooks
International Technical Support Organization
SQL Procedures, Triggers, and Functions on IBM DB2
for i
April 2016
SG24-8326-00
Note: Before using this information and the product it supports, read the information in ¡°Notices¡± on
page ix.
First Edition (April 2016)
This edition applies to Version 7, Release 2, of IBM i (product number 5770-SS1).
? Copyright International Business Machines Corporation 2016. All rights reserved.
Note to U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP Schedule
Contract with IBM Corp.
Contents
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .x
IBM Redbooks promotions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Now you can become a published author, too! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Comments welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Stay connected to IBM Redbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
xiii
xiii
xvi
xvi
xvi
Chapter 1. Introduction to data-centric programming . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1 Data-centric programming. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.2 Database engineering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Chapter 2. Introduction to SQL Persistent Stored Module . . . . . . . . . . . . . . . . . . . . . . . 5
2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.2 System requirements and planning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.3 Structure of an SQL PSM program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.4 SQL control statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
2.4.1 Assignment statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
2.4.2 Conditional control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
2.4.3 Iterative control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
2.4.4 Calling procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.4.5 Compound SQL statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.5 Dynamic SQL in PSM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.5.1 DECLARE CURSOR, PREPARE, and OPEN . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.5.2 PREPARE then EXECUTE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
2.5.3 EXECUTE IMMEDIATE statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.6 Error handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.6.1 The basic database error indicators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
2.6.2 Conditions and handlers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
2.6.3 GET DIAGNOSTICS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.6.4 SIGNAL and RESIGNAL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
2.6.5 RETURN statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
2.6.6 Direct SQLSTATE and SQLCODE usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
2.6.7 Error handling in nested compound statements . . . . . . . . . . . . . . . . . . . . . . . . . . 39
2.7 Transaction management in procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
2.7.1 Transaction management example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Chapter 3. SQL fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.1 SQL concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.1.1 Schemas and libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.1.2 Unqualified object names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.1.3 SQL PATH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.1.4 Global variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2 Common information for SQL routines and triggers . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2.1 Routine and trigger creation process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2.2 IBM i names for generated SQL objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
? Copyright IBM Corp. 2016. All rights reserved.
47
48
48
48
51
52
55
55
56
iii
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- postgresql functions by example
- sql server database coding standards and guidelines
- sql procedures triggers and user defined functions on
- 083 2011 developing user defined functions in sas a
- workingt sql smartaccess user definedfunctions
- sql server execute statement with result set clause
- chapter 5 procedures functions and triggers
- user defined financial functions for ms sql server
- views stored procedures functions and triggers
Related searches
- statistics functions on ti 84
- piecewise defined functions pdf
- user defined function matlab
- tableau user defined function
- how to identify functions on a graph
- programmer defined functions matlab
- user defined function in c
- triggers and coping skills worksheets
- anxiety triggers and warning signs
- piecewise defined functions worksheet with answers
- sql user defined function
- python user defined type