Like 0

10. Procedure, Function & Package Editor

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, procedures and other program objects. 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.

When Procedures, Functions and Packages are invalid, they are marked in the Schema Browser with a red box icon overlay. Expanding marked objects in the Schema Tree shows which specific Package contents are invalid.

Options for determining if both the header and the body are both compiled when compile is executed can be set in File -> Options -> Procedure Editor.

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 it. To jump to the source of a compilation error, double-click on the line in the Compilation Messages grid. Options for determining if both the header and the body are both compiled when compile is executed can be set in File -> Options -> Procedure Editor.

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. When Packages, Functions or Procedures are Invalid, a red invalid indicator appears in the Schema Browser tree over the object's icon.

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 it's desired for the stored procedure to return all rows from the table with TITLE=CTO, specify the input parameter AQUA_CODE as 'CTO'. If it's all the Managers which are needed, just 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

Function Editor

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

Compile - Compiles the function, logs any compilation messages in the result grid below it. To jump to the source of a compilation error, double-click on the line in the Compilation Messages grid. Options for determining if both the header and the body are both compiled when compile is executed can be set in File -> Options -> Procedure Editor.

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. When Packages, Functions or Procedures are Invalid, a red invalid indicator appears in the Schema Browser tree over the object's icon.

Creating a Sample User Defined Function in DB2 UDB


1. Sample Table with rows	2. Right Click on Create Function	3. Create and edit the Function	4. Right Click to Execute the Function	5. Enter the value and Execute	6. Function Executes results display

Create table DDL

INSERT Statements

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. Options for determining if both the header and the body are both compiled when compile is executed can be set in File -> Options -> Procedure Editor.

The Oracle Package Editor allows users to edit packages more easily by providing both the definition (specification) and the body in one tab window. The editor also provides a drop down of all functions & procedures in the package for quick navigation. When Packages, Functions or Procedures are Invalid, a red invalid indicator appears in the Schema Browser tree over the object's icon.


1. Create Package	2. Create Package in Editor	3. Edit the Package	4. Compile the Package	5. Package Body Tab	6. Execute Package and input values	Invalid Package Indicators

SAMPLE ORACLE PACKAGE CONTENTS

Package Specification DDL

CREATE OR REPLACE PACKAGE "HR"."PACK_NEW_HELLO_WORLD_2" AS
   FUNCTION "FX_NEW_HELLO_WORLD" (MYARG IN OUT NOCOPY NUMBER)
       RETURN NUMBER;
   PROCEDURE "PROC_NEW_HELLO_WORLD" (MYARG1 IN NUMBER);
END;

Package Body DDL

CREATE OR REPLACE PACKAGE BODY "HR"."PACK_NEW_HELLO_WORLD_2" AS
   FUNCTION "FX_NEW_HELLO_WORLD" (MYARG IN OUT NOCOPY NUMBER)
       RETURN NUMBER
   IS
   BEGIN
       DBMS_OUTPUT.PUT_LINE('Hello world!');
       RETURN 1;
   END;
   PROCEDURE "PROC_NEW_HELLO_WORLD" (MYARG1 IN NUMBER)
   IS
   BEGIN
       DBMS_OUTPUT.PUT_LINE('Hello world!');
        DBMS_OUTPUT.PUT('MYARG1 = ');
        DBMS_OUTPUT.PUT_LINE(MYARG1);
                   END;
   PROCEDURE "PROC_NEW_HELLO_WORLD2" (MYARG1 IN NUMBER)
   IS
   BEGIN
       DBMS_OUTPUT.PUT_LINE('Hello world2!');
        DBMS_OUTPUT.PUT('MYARG1 = ');
        DBMS_OUTPUT.PUT_LINE(MYARG1);
                   END;
END;