In oracle we say Procedural language as PL/SQL. But, in DB2 we need to say like SQL Procedure.
How to declare data types
DECLARE v_salary DEC(9,2) DEFAULT 0.0;
DECLARE v_status char(3) DEFAULT ‘YES’;
DECLARE v_descrition VARCHAR(80);
DECLARE v1, v2 INT DEFAULT 0;
How to Declare Array data type
CREATE TYPE numbers as INTEGER ARRAY[100];
CREATE TYPE names as VARCHAR(30) ARRAY[];
CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];
Here, CARDINALITY(myarray) is maximum size of an array
Related: DB2 SQL and Procedure Jobs and Career options
How to use SET statement in SQL procedure
Method-1
SET var1 = 10;
SET total = (select sum(c1) from T1);
SET var2 = POSSTR(‘MYTEST’,’TEST’);
SET v_numb(10) = 20; — assign value of 20 to the 10th element
of the array v_numb
SET v_numb = ARRAY[1,2,3,4]; — fill up array with values
SET CURRENT_SCHEMA = MYSCHEMA
Method-2
VALUES 2 INTO v1;
VALUES ‘TEST’ INTO var2;
SELECT SUM(c1) INTO var1 FROM T1;
SELECT POSSTR(‘MYTEST’,’TEST’) INTO v1 FROM SYSIBM.SYSDUMMY1;
How to use Special registers
CREATE PROCEDURE get_datetime (out cdate date, out ctime time )
P1: BEGIN
VALUES CURRENT DATE INTO cdate;
VALUES CURRENT TIME INTO ctime;
END P1
Example to use CASE statement in SQL procedure
CREATE PROCEDURE sal_increase_lim1 (empid CHAR(6))
BEGIN
DECLARE years_of_serv INT DEFAULT 0;
DECLARE v_incr_rate DEC(9,2) DEFAULT 0.0;
SELECT YEAR(CURRENT DATE) – YEAR(hiredate)
INTO years_of_serv
FROM empl1
WHERE empno = empid;
CASE
WHEN years_of_serv > 30 THEN
SET v_incr_rate = 0.08;
WHEN years_of_serv > 20 THEN
SET v_incr_rate = 0.07;
WHEN years_of_serv > 10 THEN
SET v_incr_rate = 0.05;
ELSE
SET v_incr_rate = 0.04;
END CASE;
UPDATE empl1
SET salary = salary+salary*v_incr_rate
WHERE empno = empid;
END
…..
Ref:IBM DB2
Srinimf 