Manta alt

YOUR IBM i TRAINING SOURCE

spacer gif
You must allow the browser to execute JavaScript programs if you want to view product information. If your browser is set up to execute JavaScript programs, the problem is that the product ID was incorrect. Use the menu bar above to find the desired product.

Overview

Audio

This course describes how to create, code, and execute an SQL stored procedure.

The course begins by introducing the concepts of stored procedures in general and SQL stored procedures in particular. You will see the advantages of using a stored procedure in a distributed environment and how a stored procedure is invoked. Next, the course describes how to create an SQL procedure. You will see how to create an SQL procedure using both the SQL CREATE PROCEDURE statement and System i Navigator.

The course then describes how to code the statements in an SQL procedure. You will first see how to code SQL control statements, which are used to control the logic flow, declare variables, and set those variables. It then describes the SQL statements used to detect and handle warnings and exceptions in an SQL procedure. You will then see how to use an SQL cursor to access the rows returned by a query and how to develop an application that executes SQL statements dynamically.

The course ends by describing the various ways in which you can work with stored procedures.

Approximate Study Time: 3 hours

Objectives

After completing this course, you should be able to:

  • Identify the two types of stored procedures and describe the differences between them
  • Identify the advantages of using a stored procedure in a distributed environment
  • Describe the purpose of registering an SQL stored procedure, how this task is accomplished, and what effect registering a stored procedure has on the system catalog
  • Given the specifications for a stored procedure, code the SQL statement to invoke it
  • Code the CREATE PROCEDURE statement to create an SQL procedure
  • Define a compound SQL statement
  • Declare a local variable in an SQL stored procedure
  • Assign a value to a local variable or a parameter
  • Implement the following control structures in an SQL stored procedure:
    • If
    • If/Else
    • Case
    • Do While
    • Do Until
  • Use the SQLSTATE and SQLCODE variables to detect and handle errors.
  • Define and use a condition handler to detect and handle errors
  • Code an SQL procedure that processes multiple rows returned by a query
  • Code the SQL statements used to define and manipulate an SQL cursor
  • Code an SQL procedure that accepts an SQL statement as a parameter and executes that statement
  • Code an SQL procedure that executes a dynamic SQL statement which includes a parameter marker
  • Code the SQL DROP statement to delete an SQL procedure
  • Enter the CL RUNSQLSTM command to execute an SQL script
  • Use System i Navigator to:
    • Create a procedure
    • Display a list of the stored procedures in a schema
    • Delete an SQL procedure
    • Recreate an SQL procedure
    • Display an SQL procedure's properties
  • Use WDSc to:
    • Create and execute an SQL script
    • Display a list of the stored procedures in a schema
    • Delete an SQL procedure

Topic Outline

Introduction to Stored Procedures

Creating an SQL Procedure

Coding an SQL Procedure

Error Handling

Using a Cursor

Developing a Dynamic SQL Procedure

Working with SQL Procedures

Audience

This course is intended for programmers and others who will be coding and creating SQL stored procedures.

Prerequisites

This course assumes that you have a working knowledge of basic IBM i concepts and facilities. You can satisfy this prerequisite by successfully completing the courses in the following series:

The course also assume you familiar with IBM i programming concepts and facilities. You can satisfy this prerequisite by successfully completing the courses in the following series:

Finally, the course assumes that you have a basic knowledge of SQL and can code SQL statements. You can satisfy this prerequisite by successfully completing the first course in this series, SQL Fundamentals.