[SQL Complex Interview Ques]
[SQL Complex Interview Ques]
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

Advertisements