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 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)
|
2. Insert Value |
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
|
2. Right-click 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
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 a 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.
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 (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.
|
2. Create Package in Editor |
3. Edit the Package |
4. Compile the Package |
5. Package Body Tab |
|
7. Invalid Package Indicators |
|---|
SAMPLE ORACLE PACKAGE CONTENTS
Package Specification DDL
CREATE OR REPLACE PACKAGE "C##HR"."PACK_HELLO_WORLD" AS
FUNCTION "FX_HELLO_WORLD" (MYARG IN OUT NOCOPY NUMBER)
RETURN NUMBER;
PROCEDURE "PROC_HELLO_WORLD" (MYARG IN NUMBER);
END;
Package Body DDL
CREATE OR REPLACE PACKAGE BODY "HR"."PACK_HELLO_WORLD" AS
FUNCTION "FX_HELLO_WORLD" (MYARG IN OUT NOCOPY NUMBER)
RETURN NUMBER
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello world!');
RETURN 1;
END;
PROCEDURE "PROC_HELLO_WORLD" (MYARG IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello world!');
END;
END;
