DB2存储过程的一些基础知识

简介：DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 语言标准的一个子集。通过 SQL PL 当前的语句集合和语言特性，可以用 SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。

一、变量的声明

1. 简单的变量声明

declare 变量名（1...*） 类型 [default  值] --注：[]代表可选（可以设置默认值）

注意：从DB2 version 9.5开始才支持在一个 DECLARE 语句中声明多个相同数据类型的变量。

2.数组数据类型

SQL 过程从 9.5 版开始支持数组类型的变量和参数。

使用时需要先创建这种数组类型，然后在程序中声明使用

--创建类型的语法如下，声明和简单变量方式一样

create type [模式名.]数组类型名 as 类型 array[长度] -- 长度是可选的。

注意：

1》数组类型名一般要加上模式名加以限定，并且在当前服务器上应该是唯一的

2》LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。

3》取数组中的值使用：数组变量名(subindex )  --其中 subindex 必须介于 1 到数组的基数之间

4》db2有些自带的函数可以操作数组， cardinality(myarray)返回一个数组中元素的个数。

 

二、变量赋值

1. set

set   变量名 = 值 --变量名可以是一个本地变量，全局变量，数组元素的名称

例子：

set a = 5;  --integer int 等数的赋值

set  str = ‘aaabbbb’；--char varchar 等字符串的赋值

set arr[10] = 5; --给指定数组下标的位置赋值

set arr = ARRAY[1,2,3,4] --给数组赋值

此外，赋值语句的右边可以是select 出来的一个结果

比如：set num = （select sum(id) from tablename）；

2. values into ，select into ，fetch into

values 值 into 变量名；

select 值into 变量名 from 表名；

fetch 值 into 变量； --在游标处常使用

例子：

values 5 into a;

select sum(id) into num;

select 'aaabbbb' into str;

fetch mycursor into name,sex; -- name, sex 是两个变量保存游标中的值

三、几种语句 --与一般语言没什么大的区别简单略过

1. 条件语句

a. IF语句

例子：

if  salary > 2000 then

    set  wage = salary * 0.7;

elseif salary > 5000 then  --可选

    set wage = salary * 0.6;

else --可选

    set wage = salary;

endif; 

b. CASE语句

例子：

case

when salary > 2000 then

    set wage = salary * 0.7;

when salary > 5000 then

    set wage = salary * 0.6;

else

    set wage = salary;

end case;

2. 循环语句 ---常用来迭代处理游标

a. loop 循环

例子：

declare end_sign int default 1;

declare temp_name varchar(50);

declare temp_sex varchar(10);

declare mycursor cursor for select name,sex from students where id > 5000;

declare continue handler for not found set end_sign = 0;

open mycursor;

loopget :loop

fetch mycursor into temp_name,temp_sex;

if end_sign <> 1 then

    leave loopget;

endif;

insert into temp_table values(temp_name,temp_sex);

end loopget;

b. while 循环

例子：

declare end_sign int default 1;

declare temp_name varchar(50);

declare temp_sex varchar(10);

declare mycursor cursor for select name,sex from students where id > 5000;

declare continue handler for not found set end_sign = 0;

open mycursor;

fetch mycursor into temp_name,temp_sex;

while(end_sign =1)

do

insert into temp_table values(temp_name,temp_sex);

fetch mycursor into temp_name,temp_sex;

end while;

c. repeat 循环

例子：

declare end_sign int default 1;

declare temp_name varchar(50);

declare temp_sex varchar(10);

declare mycursor cursor for select name,sex from students where id > 5000;

declare continue handler for not found set end_sign = 0;

open mycursor;

repeat

fetch mycursor into temp_name,temp_sex;

insert into temp_table values(temp_name,temp_sex);//这一步至少执行一次，可能会插入空值，这种情况不适合使用repeat

until end_sign =0

end repeat;

d. for 循环 --隐藏游标,相比上面的而言，这个for语句更简洁，方便。不用声明游标和开关游标

for loopget as select name,sex from students where id > 5000

do

insert into temp_table values(loopget.name,loopget.sex);

end for;

四、游标的使用

1.声明游标  》》2.打开游标 》》3.处理游标 》》4.关闭游标

1.声明游标

declare 游标名 cursor for select语句;

例子: declare  mycursor cursor for select name,sex from students where id > 5000;

在sql语句中不能包含参数占位符，但是他可以引用在游标之前声明的本地变量。

declare num int default 5000；

declare  mycursor cursor for select name,sex from students where id > num;

在声明游标时也可以通过其他语句来支持返回结果集和游标处理 --目前没怎么用过，不了解详细用途

1》with hold / without hold

这用来定义commit操作以后的游标状态。默认是without hold。 如果定义了with hold 则在commit之后游标状态仍然是open；在rollback之后所有游标状态均是close状态。

例子：

declare mycursor cursor with hold from select name,sex from students where id > 5000;

2》with return / without return

这用来定义游标的结果表是否用来作为一个结果集返回给调用者。创建过程时要制定dynamic result sets子句。

with  return 有两种方式：

a. with return to caller :默认选项， 将结果集返回给调用者，后者可以是另一个过程或者一个客户机应用程序。

b. with return to client :将结果集返回给客户机应用程序，绕过任何中间的嵌套过程。

例子：

create procedure mypro()

dynamic result sets

p :begin

declare mycursor cursor with return for select name,sex from students where id > 5000;

open mycursor;--一定要保证游标是打开的，否则结果集将不能返回。 

end p;

2. 打开游标

open 游标名；

3. 处理游标

见上面语句介绍里面的循环语句介绍

4. 关闭游标

五、简单的存储过程的例子

--drop procedure clearTest;删除存储过程

--存储过程实现的是删除掉表db2info.test1中已经在数据库中存在的表。
create procedure clearTest()
begin
  declare ownsheme varchar(50);
  declare tabname varchar(100);
  declare counts integer default 0;
  declare mycur cursor for SELECT B.CREATOR,B.NAME FROM DB2INFO.TEST1 A,SYSIBM.SYSTABLES B WHERE A.OWNER = B.CREATOR AND A.TABNAME=B.NAME;
  select count(*) into counts FROM DB2INFO.TEST1 A,SYSIBM.SYSTABLES B WHERE A.OWNER = B.CREATOR AND A.TABNAME=B.NAME;
  OPEN mycur;
  while(counts > 0)
   do 
   --insert into db2info.aaaa values(counts);
   FETCH mycur INTO ownsheme,tabname;
   set tabname = ownsheme||'.'||tabname;
   set tabname = 'drop table '||tabname;
   PREPARE S1 FROM tabname;--动态sql语句的执行
   EXECUTE S1;
   set counts = counts - 1;
   end while;
   close mycur;
end;

--call clearTest;调用存储过程