13. Procedure, Function & Package Editor (Revision #49)

Aqua Data Studio can access and edit Procedures, Functions and Packages through the Schema Browser.

Stored Procedures are subroutines stored in a database's dictionary and are often used for data validation and access control. They collect logic usually spread across several statements in complex actions for access from a single call.

Functions are blocks of code to perform a series of actions used to extend the functionality of a database server. User defined functions are defined with CREATE FUNCTION in SQL statements. They can be used for converting and evaluating data or performing other actions.

Packages, in Oracle, are used for encapsulating a group of functions and procedures. Packages have specifications (definitions) and bodies. They provide containers for related items.

Depending on the database version and vendor, Package, Procedure and Function debugging is also available. See the SQL Debuggers section for more.

Procedure Editor

Aqua Data Studio’s PL/SQL and Procedure editor offers the user a compilation and execution environment for the development of stored procedures.

When a Procedure has been opened with the Procedure Editor, the button normally associated with Executing a statement becomes associated with compiling. To the right of it is a similar button with a gear behind it for executing the procedure.

• Compile - Compiles the procedure, logs any compilation messages in the result grid below.
  • To jump to the source of a compilation error, double-click on the line in the Compilation Messages grid
• Execute Procedure - Displays the Execute Procedure dialog to let the user specify parameters before execution.
  • To set a value, simply click on the parameter’s Value cell and enter a value.
  • To execute, click the Execute button or click the Cancel button to abort execution

An enhanced feature in the ADS Procedure Editor is that tables can be sorted within stored procedures now.

Creating and Executing a Sample Stored Procedure in Aqua Data Studio (DB2 UDB)

1. Create Table	2. Insert Values	3. Create Stored Procedure	4. Execute Stored Procedure

5. Execute Bind on the Stored Procedure	6. Call Stored Procedure and Input Parameter	7. Execute for Results

Create table DDL

CREATE TABLE "DB2ADMIN"."AQUA_CUSTOMER" (
    "F_NME"     VARCHAR(25),
    "L_NME"     VARCHAR(25),
    "TITLE"     VARCHAR(10),
    "ADDR_1"    VARCHAR(25),
    "ADDR_2"    VARCHAR(25),
    "CITY"      VARCHAR(25),
    "ZIP"       VARCHAR(25)
    )

Insert Statements

INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('James', 'Smith', 'Manager', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Andrew', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Drake', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Micheal', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Drew', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Barry', 'Hike', 'CEO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO

SAMPLE DB2 UDB STORED PROCEDURE

This stored procedure when executed will return all rows from the table DB2ADMIN.AQUA_CUSTOMER based on the condition that Aqua_Code parameter is equal to the specified TITLE column in the table. For example, if I want the stored procedure to return all rows from the table with TITLE=CTO, I specify the input parameter AQUA_CODE as 'CTO'. If its all the Managers I want, just need to specify input parameter AQUA_CODE as 'MANAGER'.

A cursor is declared which loops through each row in the Table.

CREATE PROCEDURE "DB2ADMIN"."SAMPLE_SP_1" ( IN Aqua_Code CHARACTER(10) )

DYNAMIC RESULT SETS 1

------------------------------------------------------------------------

-- SQL Stored Procedure

------------------------------------------------------------------------

P1: BEGIN

-- Declare cursor

DECLARE cursor1 CURSOR WITH RETURN FOR

SELECT F_NME,L_NME,TITLE,CITY,ZIP FROM DB2ADMIN.AQUA_CUSTOMER

WHERE TITLE = Aqua_Code;

-- Cursor left open for client application

OPEN cursor1;

END P1

• Compile - Compiles the function, logs any compilation messages in the result grid below.
  • To jump to the source of a compilation error, double-click on the line in the Compilation Messages grid
• Execute Function - Displays the Execute Function dialog to let the user specify parameters before execution.
  • To set a value, simply click on the parameter’s Value cell and enter a value.
  • To execute, click the Execute button or click the Cancel button to abort execution

Creating a Sample User Defined Function in DB2 UDB

Count(*) Sample Table	Click on Create Function	Create UDF	Execute	Executing a UDF	Result of UDF

Create table DDL

CREATE TABLE "DB2ADMIN"."AQUA_CUSTOMER" (
    "F_NME"     VARCHAR(25),
    "L_NME"     VARCHAR(25),
    "TITLE"     VARCHAR(10),
    "ADDR_1"    VARCHAR(25),
    "ADDR_2"    VARCHAR(25),
    "CITY"      VARCHAR(25),
    "ZIP"       VARCHAR(25)
    )

Insert Statements

INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('James', 'Smith', 'Manager', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Andrew', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Drake', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Micheal', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Drew', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Barry', 'Hike', 'CEO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO

SAMPLE DB2 USER DEFINED FUNCTION

This User Defined Function when executed will delete row from the table DB2ADMIN.AQUA_CUSTOMER based on the input parameter Aqua_Code. This parameter is matched to the specified TITLE column in the table. For example, if you want the UDF to delete rows from the table with TITLE=Manager, you need to specify the input parameter AQUA_CODE as 'Manager'.

-----------------------------------------------------------------------------------------------------------
 -- This UDF takes in a code and removes rows in the table with that CODE
-----------------------------------------------------------------------------------------------------------

CREATE FUNCTION DB2ADMIN.UDF_SAMPLE_1 (AQUA_CODE varchar (10))
  RETURNS TABLE (title  varchar (10),
                 f_nme varchar(25),
                 l_nme varchar(25))
  MODIFIES SQL DATA
  LANGUAGE SQL
  BEGIN ATOMIC

    DECLARE  V_F_NME varchar(25);
    DECLARE  V_L_NME varchar(25);

    SET (V_F_NME, V_L_NME) = (SELECT f_nme, l_nme
                               FROM OLD TABLE (DELETE FROM DB2ADMIN.AQUA_CUSTOMER
                                                WHERE AQUA_CUSTOMER.TITLE = AQUA_CODE ));

    RETURN VALUES (AQUA_CODE,V_F_NME, V_L_NME);
  END

Executing DB2 USER DEFINED FUNCTION

Connect to  the Database Server and Open a Query Analyzer. Type the following and Execute to see the UDF result

SELECT * FROM TABLE(DB2ADMIN.UDF_SAMPLE_1('Manager')) AS FUNCTABLE
GO

Package Editor

Aqua Data Studio’s PL/SQL and Package Editor offers the user a compilation and execution environment for the development of packages.

• Compile - Compiles the package, logs any compilation messages in the result grid below.
  • To jump to the source of a compilation error, double-click on the line in the Compilation Messages grid
• Execute Procedure - Displays the Execute Procedure dialog to let the user specify parameters before execution.
  • To set a value, simply click on the parameter’s Value cell and enter a value.
  • To execute, click the Execute button or click the Cancel button to abort execution

The Oracle Package Editor allows users to edit packages more easily by providing both the definition and the body in one tab window. The editor also provides a drop down of all functions & procedures in the package for quick navigation.