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

ustbwang

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