db2存储过程、触发器、游标练习

drop table person
go
create table person
(
NUM INTEGER,
NAME VARCHAR(20),
AGE INTEGER,
RANK INTEGER,
COMMENT VARCHAR(100)
)
go
describe select * from person
go
--存储过程
create or replace procedure insertBatch(num integer,age integer,rank integer) --默认为in
language sql
begin
declare name varchar(20);
declare comment varchar(100);
declare num integer;
declare age integer;
declare rank integer;
set num = 1;
set age = 10;
set rank = 100;
while num <= 9
do
set name = 'gaoke'||num;
set comment = 'cccccc'||num;
insert into person values(num,name,age,rank,comment);
set num = num+1;
set age = age+1;
set rank = rank + 1;
end while;
end
go
--调用存储过程
call insertBatch (1,10,100)
go
commit
go
select * from person
go

--创建一个相关表
drop table update_count
go
create table update_count
(
id integer,
count integer
)
go
insert into update_count values(1,0)
go
select * from update_count
go
--函数
create or replace function afun()
returns integer
begin
return
(select count from update_count where id = 1);
end
go
--触发器
create or replace trigger atrigger
after update of age on person
referencing new as nnn old as ooo
for each row mode db2sql
begin atomic
update update_count set count = count+1 where id = 1;
end
go


--测试用，类似pl/sql的代码段，游标的使用(年龄大于12的人在原来的年龄上依次加1、2、3、4……)
begin
declare id integer;
declare n integer;
declare i integer;
declare step integer;
declare sqlcode integer default 0;
set i = 1;
begin
declare cur cursor for select num,age from person;

open cur;
cursorLoop:
loop
fetch cur into id,n;
if sqlcode = 100
then leave cursorLoop;
else
if n > 12
then
set step = n + i;
update person set age = step where num = id ;
set i = i + 1;
end if;
end if;
end loop;
close cur;
end;
end
go
select * from person
go
select afun() from update_count
go