How to Create DB2 Stored Procedures/Triggers with Examples
I have been working on Oracle/Plsql, Sql Server for over a long period and got an opportunity to work on Db2. You get to see as many links and Pdf's online,but most of them do not cover procedures with examples in DB2. I take this opportunity to share some of the knowledge on plsql that helps a beginner to understand how to create/execute procedures/functions.
Lets start with a simple stored procedure and understand how to create one,execute one.
I have created a sample table by name EMP to illustrate the examples.
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
EMPID SYSIBM INTEGER 4 0 No
EMPNAME SYSIBM VARCHAR 30 0 Yes
Example 1 : Sample Stored Procedure with no parameters passed when called
CREATE OR REPLACE PROCEDURE PROC_SAMPLE1()
BEGIN
INSERT INTO EMP VALUES(121,'CRUISE');
END
NOTE: Before you even try to compile the stored procedure, Please do make sure you change the default delimiter to any special character other than semicolon ';'
Once compiled, You can go ahead run the procedure PROC_SAMPLE1() using the below command.
CALL PROC_SAMPLE1()
output as follows:
Return Status = 0
------------------------------ Commands Entered --------------------------
SELECT * FROM EMP
------------------------------------------------------------------------------
output as follows:
EMPID EMPNAME
----------- ------------------------------
121 CRUISE
1 record(s) selected.
SELECT * FROM EMP
------------------------------------------------------------------------------------------------------------
Example 2: Sample Stored Procedure with input parameters passed
Now lets create a stored procedure that has parameters in it.Example 2 illustrates a stored procedure having an input parameter.
CREATE OR REPLACE PROCEDURE PRC_SAMPLE2(IN PARAMETER1 VARCHAR(30))
BEGIN
DECLARE v_PARAM VARCHAR(30);
DECLARE v_INT INTEGER;
SET v_PARAM=PARAMETER1;
SELECT MAX(EMPID) INTO v_INT FROM EMP;
INSERT INTO EMP SELECT v_INT+1,v_PARAM FROM SYSIBM.SYSDUMMY1;
END
Execute the stored proc using the below command.
CALL PRC_SAMPLE2('JACK JILL')
------------------------------ Commands Entered --------------------------
SELECT * FROM EMP/
------------------------------------------------------------------------------
SELECT * FROM EMP
EMPID EMPNAME
----------- ------------------------------
122 JACK JILL
121 CRUISE
2 record(s) selected.
------------------------------------------------------------------------------------------------------------
Example 3: Sample Stored Procedure with input and output parameters in it
lets create a stored procedure that has input as well as output parameters in it.
CREATE OR REPLACE PROCEDURE PRC_SAMPLE3(IN PARM VARCHAR(30),OUT o_PARAM VARCHAR(200))
BEGIN
DECLARE v_PARAM VARCHAR(30);
DECLARE v_INT INTEGER;
DECLARE v_check INT;
SET v_PARAM=PARM;
SELECT MAX(EMPID) INTO v_INT FROM EMP;
INSERT INTO EMP SELECT v_INT+1,v_PARAM FROM SYSIBM.SYSDUMMY1;
SELECT 1 into v_check from EMP WHERE EMPNAME=v_PARAM;
IF v_check=1 then
set o_PARAM=v_PARAM || ' RECORD LOADED INTO EMP TABLE' ;
END IF;
END
CALL PRC_SAMPLE3('JIMMY',?)
Value of output parameters
--------------------------
Parameter Name : O_PARAM
Parameter Value : JIMMY RECORD LOADED INTO EMP TABLE
------------------------------------------------------------------------------------------------------------
Example 4: Sample Stored Procedure with Cursors
Alright.. Let's move on and create a cursor and see how it works in DB2.
Lets create another table EMP2 having similar structure to EMP
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
EMPID SYSIBM INTEGER 4 0 No
EMPNAME SYSIBM VARCHAR 30 0 Yes
CREATE OR REPLACE PROCEDURE PRC_SAMPLE4()
BEGIN
DECLARE v_NAME VARCHAR(30);
DECLARE v_id INTEGER;
FOR V1 AS
CSR1 CURSOR FOR SELECT EMPID,EMPNAME FROM EMP
DO
SET V_ID=EMPID;
SET V_NAME=EMPNAME;
INSERT INTO EMP2 VALUES(V_ID,V_NAME);
END FOR;
END
Output as follows:
CALL PRC_SAMPLE4()
Return Status = 0
------------------------------ Commands Entered --------------------------
select * from EMP2/
-----------------------------------------------------------------------------
select * from EMP2
EMPID EMPNAME
----------- ------------------------------
121 CRUISE
123 JIMMY
122 JACK JILL
3 record(s) selected.
------------------------------------------------------------------------------------------------------------
Example 5: Exception Handling example
Now lets do something more interesting and handle exceptions in our procedures.What's exception? Well, there are lots of links that provides you an answer to that question . It holds the same in any programming language or database.
Lets try to create a procedure that handles exception while performing any DML statement.
CREATE OR REPLACE PROCEDURE EXCEPTION_HANDLING(V_EMP_ID INTEGER,V_EMP_NAME VARCHAR(30),OUT O_V_OUT VARCHAR(50))
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET O_V_OUT = SQLSTATE;
IF V_EMP_ID = '0' THEN
SIGNAL SQLSTATE '88888'
SET MESSAGE_TEXT ='INCORRECT EMPLOYEE_ID';
END IF;
INSERT INTO EMP VALUES (V_EMP_ID, V_EMP_NAME);
END
The above simple procedure allows you to enter any number as employee id except 0. when entered, it would pass a user defined error code(in this example '88888') you defined to an output variable that can be used in the called envirnoment for validations.
lets assume the table has no records.
INSERT INTO EMP
WITH CTE_DB2(PRM) AS
( VALUES
'123 BOND'
,'122 JAMES'
)SELECT SUBSTR(PRM,1,3),SUBSTR(PRM,5,5) FROM CTE_DB2
Now lets execute the procedure to see how it works.
CALL EXCEPTION_HANDLING(1,'ROCKY',?)
Value of output parameters
--------------------------
Parameter Name : O_V_OUT
Parameter Value : -
Return Status = 0
CALL EXCEPTION_HANDLING(0,'ZERO',?)
Value of output parameters
--------------------------
Parameter Name : O_V_OUT
Parameter Value : 88888
Return Status = 0
SELECT * FROM EMP
EMPID EMPNAME
----------- ------------------------------
122 JAMES
123 BOND
1 ROCKY
3 record(s) selected.
----------------------------
If you notice, the record with an employee_id '0' didnt get inserted into the table EMP.
hope this helps to have an understanding on how to create and run procedures in DB2.
------------------------------------------------------------------------------------------------------------
Example 6: Trigger Handling example
CREATE OR REPLACE TRIGGER TRIGGER_EXAMPLE
AFTER INSERT ON EMPLOYEE
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE V SMALLINT DEFAULT 0;
DECLARE VV SMALLINT DEFAULT 0;
FOR INC AS SELECT COUNT(*) AS COUNT_TEST FROM ORG_EMP_SIZE
DO
SET V= COUNT_TEST;
END FOR;
FOR INC AS SELECT COUNT(*) AS EMP_COUNT FROM EMPLOYEE
DO
SET VV= EMP_COUNT;
END FOR;
IF V=0
THEN
INSERT INTO ORG_EMP_SIZE SELECT COUNT(*) FROM EMPLOYEE;
ELSE
UPDATE ORG_EMP_SIZE SET TOTCOUNT=VV;
END IF;
END
The above trigger is such a simple trigger used to keep track of the number of employees in an organistaion. Ofcourse it's a Trigger that gets enabled after insertion of a record.
---------------------------------------------------------------------------------------------------------
Example 7: Create Temp Tables
We all know that creating temp tables in sql server no matter whether a global/local is so easy. Lets see how the same can be aquired in DB2.
-> DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE (DEPTID INT)
ON COMMIT PRESERVE ROWS NOT LOGGED
-> INSERT INTO SESSION.TEMP_TABLE VALUES(10),(20)
-> SELECT * FROM SESSION.TEMP_TABLE
DEPTID
-----------
10
20
2 record(s) selected.
I have been working on Oracle/Plsql, Sql Server for over a long period and got an opportunity to work on Db2. You get to see as many links and Pdf's online,but most of them do not cover procedures with examples in DB2. I take this opportunity to share some of the knowledge on plsql that helps a beginner to understand how to create/execute procedures/functions.
Lets start with a simple stored procedure and understand how to create one,execute one.
I have created a sample table by name EMP to illustrate the examples.
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
EMPID SYSIBM INTEGER 4 0 No
EMPNAME SYSIBM VARCHAR 30 0 Yes
Example 1 : Sample Stored Procedure with no parameters passed when called
CREATE OR REPLACE PROCEDURE PROC_SAMPLE1()
BEGIN
INSERT INTO EMP VALUES(121,'CRUISE');
END
NOTE: Before you even try to compile the stored procedure, Please do make sure you change the default delimiter to any special character other than semicolon ';'
Once compiled, You can go ahead run the procedure PROC_SAMPLE1() using the below command.
CALL PROC_SAMPLE1()
output as follows:
Return Status = 0
------------------------------ Commands Entered --------------------------
SELECT * FROM EMP
------------------------------------------------------------------------------
output as follows:
EMPID EMPNAME
----------- ------------------------------
121 CRUISE
1 record(s) selected.
SELECT * FROM EMP
------------------------------------------------------------------------------------------------------------
Example 2: Sample Stored Procedure with input parameters passed
Now lets create a stored procedure that has parameters in it.Example 2 illustrates a stored procedure having an input parameter.
CREATE OR REPLACE PROCEDURE PRC_SAMPLE2(IN PARAMETER1 VARCHAR(30))
BEGIN
DECLARE v_PARAM VARCHAR(30);
DECLARE v_INT INTEGER;
SET v_PARAM=PARAMETER1;
SELECT MAX(EMPID) INTO v_INT FROM EMP;
INSERT INTO EMP SELECT v_INT+1,v_PARAM FROM SYSIBM.SYSDUMMY1;
END
Execute the stored proc using the below command.
CALL PRC_SAMPLE2('JACK JILL')
------------------------------ Commands Entered --------------------------
SELECT * FROM EMP/
------------------------------------------------------------------------------
SELECT * FROM EMP
EMPID EMPNAME
----------- ------------------------------
122 JACK JILL
121 CRUISE
2 record(s) selected.
------------------------------------------------------------------------------------------------------------
Example 3: Sample Stored Procedure with input and output parameters in it
lets create a stored procedure that has input as well as output parameters in it.
CREATE OR REPLACE PROCEDURE PRC_SAMPLE3(IN PARM VARCHAR(30),OUT o_PARAM VARCHAR(200))
BEGIN
DECLARE v_PARAM VARCHAR(30);
DECLARE v_INT INTEGER;
DECLARE v_check INT;
SET v_PARAM=PARM;
SELECT MAX(EMPID) INTO v_INT FROM EMP;
INSERT INTO EMP SELECT v_INT+1,v_PARAM FROM SYSIBM.SYSDUMMY1;
SELECT 1 into v_check from EMP WHERE EMPNAME=v_PARAM;
IF v_check=1 then
set o_PARAM=v_PARAM || ' RECORD LOADED INTO EMP TABLE' ;
END IF;
END
CALL PRC_SAMPLE3('JIMMY',?)
Value of output parameters
--------------------------
Parameter Name : O_PARAM
Parameter Value : JIMMY RECORD LOADED INTO EMP TABLE
------------------------------------------------------------------------------------------------------------
Example 4: Sample Stored Procedure with Cursors
Alright.. Let's move on and create a cursor and see how it works in DB2.
Lets create another table EMP2 having similar structure to EMP
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
EMPID SYSIBM INTEGER 4 0 No
EMPNAME SYSIBM VARCHAR 30 0 Yes
CREATE OR REPLACE PROCEDURE PRC_SAMPLE4()
BEGIN
DECLARE v_NAME VARCHAR(30);
DECLARE v_id INTEGER;
FOR V1 AS
CSR1 CURSOR FOR SELECT EMPID,EMPNAME FROM EMP
DO
SET V_ID=EMPID;
SET V_NAME=EMPNAME;
INSERT INTO EMP2 VALUES(V_ID,V_NAME);
END FOR;
END
Output as follows:
CALL PRC_SAMPLE4()
Return Status = 0
------------------------------ Commands Entered --------------------------
select * from EMP2/
-----------------------------------------------------------------------------
select * from EMP2
EMPID EMPNAME
----------- ------------------------------
121 CRUISE
123 JIMMY
122 JACK JILL
3 record(s) selected.
------------------------------------------------------------------------------------------------------------
Example 5: Exception Handling example
Now lets do something more interesting and handle exceptions in our procedures.What's exception? Well, there are lots of links that provides you an answer to that question . It holds the same in any programming language or database.
Lets try to create a procedure that handles exception while performing any DML statement.
CREATE OR REPLACE PROCEDURE EXCEPTION_HANDLING(V_EMP_ID INTEGER,V_EMP_NAME VARCHAR(30),OUT O_V_OUT VARCHAR(50))
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET O_V_OUT = SQLSTATE;
IF V_EMP_ID = '0' THEN
SIGNAL SQLSTATE '88888'
SET MESSAGE_TEXT ='INCORRECT EMPLOYEE_ID';
END IF;
INSERT INTO EMP VALUES (V_EMP_ID, V_EMP_NAME);
END
The above simple procedure allows you to enter any number as employee id except 0. when entered, it would pass a user defined error code(in this example '88888') you defined to an output variable that can be used in the called envirnoment for validations.
lets assume the table has no records.
INSERT INTO EMP
WITH CTE_DB2(PRM) AS
( VALUES
'123 BOND'
,'122 JAMES'
)SELECT SUBSTR(PRM,1,3),SUBSTR(PRM,5,5) FROM CTE_DB2
Now lets execute the procedure to see how it works.
CALL EXCEPTION_HANDLING(1,'ROCKY',?)
Value of output parameters
--------------------------
Parameter Name : O_V_OUT
Parameter Value : -
Return Status = 0
CALL EXCEPTION_HANDLING(0,'ZERO',?)
Value of output parameters
--------------------------
Parameter Name : O_V_OUT
Parameter Value : 88888
Return Status = 0
SELECT * FROM EMP
EMPID EMPNAME
----------- ------------------------------
122 JAMES
123 BOND
1 ROCKY
3 record(s) selected.
----------------------------
If you notice, the record with an employee_id '0' didnt get inserted into the table EMP.
hope this helps to have an understanding on how to create and run procedures in DB2.
------------------------------------------------------------------------------------------------------------
Example 6: Trigger Handling example
CREATE OR REPLACE TRIGGER TRIGGER_EXAMPLE
AFTER INSERT ON EMPLOYEE
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE V SMALLINT DEFAULT 0;
DECLARE VV SMALLINT DEFAULT 0;
FOR INC AS SELECT COUNT(*) AS COUNT_TEST FROM ORG_EMP_SIZE
DO
SET V= COUNT_TEST;
END FOR;
FOR INC AS SELECT COUNT(*) AS EMP_COUNT FROM EMPLOYEE
DO
SET VV= EMP_COUNT;
END FOR;
IF V=0
THEN
INSERT INTO ORG_EMP_SIZE SELECT COUNT(*) FROM EMPLOYEE;
ELSE
UPDATE ORG_EMP_SIZE SET TOTCOUNT=VV;
END IF;
END
The above trigger is such a simple trigger used to keep track of the number of employees in an organistaion. Ofcourse it's a Trigger that gets enabled after insertion of a record.
---------------------------------------------------------------------------------------------------------
Example 7: Create Temp Tables
We all know that creating temp tables in sql server no matter whether a global/local is so easy. Lets see how the same can be aquired in DB2.
-> DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE (DEPTID INT)
ON COMMIT PRESERVE ROWS NOT LOGGED
-> INSERT INTO SESSION.TEMP_TABLE VALUES(10),(20)
-> SELECT * FROM SESSION.TEMP_TABLE
DEPTID
-----------
10
20
2 record(s) selected.
Db2 Tutorials!
ReplyDelete