| LevSelector.com |
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;
--------------------------------------------