-- for循環語句的用法
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
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語句的用法
-- loop的用法
-- return的用法
-- set變量 的用法
set new_var.salary = 10000, new_var.comm = new_var.salary;
or:
set (new_var.salary, new_var.comm) = (10000, 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的用法
-- while的用法
-- 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
add
after
alias
all
allocate
allow
alter
and
any