DB2 9.5 SQL Procedure Developer Certification Examination 735 preparation, 1th part

Before you start

About this series

This series, which contains 6 DB2 SQL Procedure Developer tutorials, covers all the basic structures and methods of SQL procedural Language (SQL PL) and shows how to store procedures, user-defined functions, and triggers, Includes the use of SQL PL in error handling and deployment. This series also describes some of the DB2 9.5 advanced features, such as optimistic locks (optimistic locking), hierarchical queries (hierarchical query), and declared global temporary tables (declared global temporary table). In addition, this series provides specific information about how to call stored procedures and how to share data between procedures and functions. This series also describes the DB2 development tools, including IBM Data Studio. These tutorials provide a solid foundation for each part of the test. However, these tutorials should not be used as the only material to prepare for the test.

About this tutorial

In this tutorial, you learn DB2 9.5 SQL procedural Language, including variables, conditions, handler declarations, control flow and iteration statements, and error-handling mechanisms. This tutorial is the first in a series of 6 tutorials that can be used to prepare DB2 9.5 SQL Procedure Developer exam 735.

Goal

In this tutorial, you will:

Learn the basic elements of SQL PL.

Understand how to declare variables, conditions, and handlers.

Learn to control flow statements.

Learn about cursor processing and how to return result sets.

Understand the error handling mechanism.

Prerequisite

To participate in the DB2 9.5 SQL Procedure Developer examination, the DB2 9 Family test (Fundamentals 730) must have been passed. You can use the DB2 Family Fundamentals Tutorial Series to prepare for that test (see Resources). It is a very popular series that has helped many people understand the basics of the DB2 product family.

To use this tutorial, you should have a background on how relational databases work and database and database programming.

System Requirements

You can complete this tutorial without installing DB2 9.5. However, in order to take full advantage of this tutorial, you can download the free trial version of DB2 Express-c in conjunction with this tutorial.

Brief introduction

DB2 SQL procedural Language (SQL PL) is a subset of the SQL Persistent Stored module language standard. The standard combines the convenience of SQL access to data and the flow control of programming languages. With SQL PL's current statement collection and language features, you can use SQL to develop integrated, advanced programs, such as functions, stored procedures, and triggers. This allows the business logic to be encapsulated into easily maintainable database objects, thereby improving the performance of the database application.

SQL PL supports local and global variables, including declarations and assignments, and also supports conditional and iterative statements, transfer of control statements, error management statements, and methods for returning result sets. These topics will be discussed in this tutorial.

Variable declaration

SQL procedures allow the use of local variables to give and obtain SQL values to support all SQL logic. In the SQL process, you declare the local variables in your code before you use them.

The diagram in Listing 1 illustrates the syntax for variable declarations:

Listing 1. Syntax for variable declarations

.-,-----------------.
　　　　　　　　　　　　　　　 V　　　　　　　　　 |
|--DECLARE----SQL-variable-name-+------------------------------->
　　　　　　　 .-DEFAULT NULL------.
>--+-data-type--+-------------------+-+-------------------------|
　|　　　　　　'-DEFAULT--constant-' |　

Sql-variable-name defines the name of a local variable. The name cannot be the same as another variable or parameter name or the same as the column name.

Figure 1 shows the supported DB2 data types:

Figure 1. DB2 data type