Posted on 2010-03-18 22:32
Prayer 閱讀(889)
評論(0) 編輯 收藏 引用 所屬分類:
DB2
-- for循環(huán)語句的用法
begin atomic
declare fullname char(40);
for vl as
select firstnme, midinit, lastname from employee
do
set fullname = lastname concat ','
concat firstnme concat ' ' concat midinit;
insert into tnames values (fullname);
end for
end
-- leave的用法
create procedure leave_loop(out counter integer)
language sql
begin
declare v_counter integer;
declare v_firstnme varchar(12);
declare v_midinit char(1);
declare v_lastname varchar(15);
declare at_end smallint default 0;
declare not_found condition for sqlstate '02000';
declare c1 cursor for
select firstnme, midinit, lastname
from employee;
declare continue handler for not_found
set at_end = 1;
set v_counter = 0;
open c1;
fetch_loop:
loop
fetch c1 into v_firstnme, v_midinit, v_lastname;
if at_end <> 0 then leave fetch_loop;
end if;
set v_counter = v_counter + 1;
end loop fetch_loop;
set counter = v_counter;
close c1;
end
-- if語句的用法
create procedure update_salary_if
(in employee_number char(6), inout rating smallint)
language sql
begin
declare not_found condition for sqlstate '02000';
declare exit handler for not_found
set rating = -1;
if rating = 1
then update employee
set salary = salary * 1.10, bonus = 1000
where empno = employee_number;
elseif rating = 2
then update employee
set salary = salary * 1.05, bonus = 500
where empno = employee_number;
else update employee
set salary = salary * 1.03, bonus = 0
where empno = employee_number;
end if;
end
-- loop的用法
create procedure loop_until_space(out counter integer)
language sql
begin
declare v_counter integer default 0;
declare v_firstnme varchar(12);
declare v_midinit char(1);
declare v_lastname varchar(15);
declare c1 cursor for
select firstnme, midinit, lastname
from employee;
declare continue handler for not found
set counter = -1;
open c1;
fetch_loop:
loop
fetch c1 into v_firstnme, v_midinit, v_lastname;
if v_midinit = ' ' then
leave fetch_loop;
end if;
set v_counter = v_counter + 1;
end loop fetch_loop;
set counter = v_counter;
close c1;
end
-- return的用法
begin
...
goto fail
...
success: return 0
fail: return -200
end
-- set變量 的用法
set new_var.salary = 10000, new_var.comm = new_var.salary;
or:
set (new_var.salary, new_var.comm) = (10000, new_var.salary);
set (new_var.salary, new_var.comm)
= (select avg(salary), avg(comm)
from employee e
where e.workdept = new_var.workdept);
-- whenever的用法
exec sql whenever sqlerror goto handlerr;
exec sql whenever sqlwarning continue;
exec sql whenever not found go to enddata;
-- while的用法
create procedure dept_median
(in deptnumber smallint, out mediansalary double)
language sql
begin
declare v_numrecords integer default 1;
declare v_counter integer default 0;
declare c1 cursor for
select cast(salary as double)
from staff
where dept = deptnumber
order by salary;
declare exit handler for not found
set mediansalary = 6666;
set mediansalary = 0;
select count(*) into v_numrecords
from staff
where dept = deptnumber;
open c1;
while v_counter < (v_numrecords / 2 + 1) do
fetch c1 into mediansalary;
set v_counter = v_counter + 1;
end while;
close c1;
end
-- set schema的用法
set schema rick
-- DB2保留關鍵字
add deterministic leave restart
after disallow left restrict
alias disconnect like result
all distinct linktype result_set_locator
allocate do local return
allow double locale returns
alter drop locator revoke
and dsnhattr locators right
any dssize lock