LevSelector.com New York
home > DB2 SQL PL

DB2 SQL PL (Procedural Language) - db2 ver.7.1, v.8
 
This page
- intro, links, books
- basic syntax
- cursor, returning result sets 
- temp. tables, UDFs


 
intro, links, books home - top of the page -

DB2 finally (in ver. 7.1 of db2 udb) has a Procedural Language (db2 sql pl) similar to Oracle's PL/SQL or Sybase/Microsoft's Transact-SQL, or Informix SPL (Stored Procedure Language). Before DB2 v.7.1 developers were supposed to write stored procedures in C (or java), or to do processing on the client.

DB2's SQL  PL is a subset of an ANSI/ISO standard for SQL "Persistent Stored Modules (SQL/PSM).
The description here follows ver.8 of db2.

Books:
- DB2 SQL Procedural Language for Linux, Unix and Windows (2003) - by Paul Yip et al


 
basic syntax home - top of the page -

----- example of a simple stored procedure
create procedure sum( in p_a integer, in p_b integer, out p_s integer)
specific sum_ab
language sql
begin
  set p_s = p_a + p_b;
end

call sum(100,200,?)

----- there may be several SPs with the same name - but different number of paramters:
create procedure sum( in p_a integer, in p_b integer, in p_c integer, out p_s integer)
specific sum_abc
language sql
begin
  set p_s = p_a + p_b + p_c;
end

call sum(100,200,300,?)

comment on specific procedure sub_ab is 'This is the 2-param version of the procedure sum'
comment on specific procedure sub_abc is 'This is the 3-param version of the procedure sum'

drop procedure sum_ab
drop procedure sum(integer,integer,integer)

----- 2 types of comments:
-- this is a one-line comment
/*
 this is a multiple-line comment
*/

----- Variable declararions
begin
declare v_rcount integer;
declare v_empno char(4);
declare v_max integer default 0;
declare v_total integer default 0;
declare v_date1, v_date2 date;
set v_total = v_total + 1;
select max(empno) into v_max from players;
values current date into v_date1;
values current date, current_date into v_date1, v_date2;
delete from employee where ...;
get diagnostics rcount = row_count;
return v_max; -- can return a single integer
end

NOTE:  Variable declarations should be placed in begin/end block BEFORE and procedural statements. In fact, the order in the block should be like this:
BEGIN
  declarations - variables
  declarations - conditions
  declarations - cursor
  declarations - handler
  assignment, flow of control, SQL stateemnts, etc.
END

NOTE:  A procedure can return a single integer via a return statements. It can return many valuesusing the "out" parameters. And it can return result set(s).

NOTE:  DB2 supports rollback and commit, also savepoint and "rollback to savepoint". Or you can use "ATOMIC" compaund statement (it all either succeeds or not).

----- IF ELSE
if rating = 1 then
  -- do something
elseif rating = 2 then
  -- do something else
else
  -- do something else
end if;

----- CASE when ... then  ... when ... then  ... else ... END CASE;
case rating
  when rating <=1 then -- do something
  when 2 then -- do something
  else -- do something
end case;

----- FOR loop
for v_row as select fname,lname from employee
do
  set v_name = v_row.lname || ', ' || v_row.fname
  insert into tname values (v_name)
end for;

----- WHILE loop
while (v_name != '') do
  -- do something
end while;

----- REPEAT UNTIL loop
repeat
  -- do something
until (v_name = '')
end repeat;

----- LOOP loop (infinite loop) - and the LEAVE and INTERATE statements
L1: loop
  -- do something
  if (something) then
     leave L1;
  else
     iterate L1;
  end if;
end loop;

----- labels and GOTO
mylabel:
  -- something
goto mylabel;


 
cursors, returning result set(s) home - top of the page -

NOTE: to return a result set - we need:
  - specify a "DYNAMIC RESULT SET(s) in  CREATE PROCEDURE statement
  - declare the cursor using the "WITH RETURN TO CALLER" (or ... TO CLIENT) clause
  - keep cursor open for the client application

----- using cursor
tr: begin
-- some declarations
declare c_emp cursor for
   select a,b,c from employee where ...;
open c_emp;
fetch from c_emp into va,vb,vc;
while (sqlstate = '00000' ) do
  -- do something
  -- delete from employee where current of c_emp;  -- this is called "positioined delete" - delete current row
  -- update employee set .... where current of c_emp;  -- "positioined update" - update current row
  fetch from c_emp into va,vb,vc;
end while;
close c_emp;
end tr;

----- Example of a stored procedure returning one result set
create procedure create_and_return
dynamic result sets 1
language sql
cr: begin
create table mytable (sid int);
insert into mytable values(1); insert into mytable values(2);
begin
  declare c_cur cursor with return for
     select * from mytable;
  open c_cur;
  end;
end cr;

ch.5 - condition handling (exceptioins, sqlstate, sqlcode), returning errors
ch.6 - dynamic and static sql, prepare and execute
ch.7 - nested SQL procedures:

----- calling one procedure from another:
declare v_total int default 0;
declare v_rc int;  -- return code
call mycount(emp_no, v_total);  -- this call will put the result into v_total
get diagnostics v_rc = return_status;
if ( v_rc = 99 ) then
  -- do something - everything is good
elseif (v_rc = 1000) then
  -- doesn't exists
else
  -- something else was wrong
end if;

----- returning result set
in sp:
begin
 -- ...
 declare v_cur cursor with return to caller for
     select  ... from ... where ...
  open  v_cur
end

in caller sp:
  declare myparam int;
  call mysp(myparam);
  associate result set locator (v_rs) with procedure mysp;
  allocate v_rscur cursor for result set v_rs;
  while (sqlstate - '00000') do
      -- ...
      fetch from v_rscur into v_fname, v_lname
  end while;
end;

NOTE: it is possible to receive mltiple result sets
NOTE: DB2 supports up to 16 levels of nesting stored procedures


 
temp.tables and UDFs home - top of the page -

ch.8 - identity, nextval/prevval, user-temporary tables, savepoints for transactions, using large objects,

----- temp tables for the session - always in schema 'SESSION'.
create user temporary tablespace usertempspace
managed by system using ('d:\sqlsp\usertempspace');

declare global temporary table mytemp ( ...)
with replace
on commit preserve rows
not logged
in usertempspace;

ch.9 - deploying procedures
ch.10 - working with triggers and UDFs (User-Defined Functions).

----- example of creating a User-Defined Function:
create function nvl (p_var1 varchar(30), p_var2 varchar(30))
specific nvlvarchar30
returns varchar(30)
begin
  return coalesce(p_var1, p_var2)
end;

--------------------------------------------