DB2存储过程语法

86565656

　　CREATE PROCEDURE 存储过程名(IN 输入变量名 输入变量类型,OUT 输出变量名 输出变量类型)
　　紧跟其后的是存储过程属性列表
　　经常使用的有：LANGUAGE SQL、MODIFIES SQL DATA、RESULT SETS 1(返回结果集个数)
　　l 存储过程体以begin开始
　　l 存储过程体以end结束
　　存储过程约束规则
　　存储过程中调用存储过程
　　CALL 存储过程名（参数1，参数2，参数n）
　　例：
　　call spco_init_custom(bankcode,errno,errmsg);
　　GET DIAGNOSTICS retval=RETURN_STATUS;
　　if(retval<>0) then
　　set errno=errno;
　　set errmsg=errmsg;
　　return errno;
　　end if;
　　变量的定义
　　变量使用前必须先定义，要领为
　　DECLARE 变量名 变量类型 （default 默认值）
　　例：
　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　DECLARE inum INTEGER DEFAULT 0;
　　DECLARE curtime char(8);
　　DECLARE bcode char(6);
　　DECLARE sqlstate char⑸;
　　if 表达式
　　if 条件1 then
　　逻辑体；
　　elseif 条件2 then
　　逻辑体；
　　else
　　逻辑体；
　　end if;
　　例：
　　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;
　　case表达式
　　case 变量名 when
　　变量值1 then
　　. . .
　　when
　　变量值2 then
　　- - -
　　else
　　. . .
　　end case;
　　或
　　case when
　　变量名=变量值1 then
　　. . .
　　when
　　变量名=变量值2 then
　　- - -
　　else
　　. . .
　　end case;
　　例一：
　　CASE v_workdept
　　WHEN 'A00'
　　THEN UPDATE department
　　SET deptname = 'DATA ACCESS 1';
　　WHEN 'B01'
　　THEN UPDATE department
　　SET deptname = 'DATA ACCESS 2';
　　ELSE UPDATE department
　　SET deptname = 'DATA ACCESS 3';
　　END CASE；
　　例二：
　　CASE
　　WHEN v_workdept = 'A00'
　　THEN UPDATE department
　　SET deptname = 'DATA ACCESS 1';
　　WHEN v_workdept = 'B01'
　　THEN UPDATE department
　　SET deptname = 'DATA ACCESS 2';
　　ELSE UPDATE department
　　SET deptname = 'DATA ACCESS 3';
　　END CASE；
　　for 表达式
　　for 循环名 as
　　游标名或select 表达式
　　do
　　sql表达式；
　　end for;
　　例：
　　1）
　　DECLARE fullname CHAR(40);
　　FOR vl AS
　　SELECT firstnme, midinit, lastname FROM employee
　　DO
　　SET fullname = lastname || ',' || firstnme ||' ' || midinit;
　　INSERT INTO tnames VALUE (fullname);
　　END FOR
　　2）
　　for loopcs1 as cousor1 cursor as
　　select market_code as market_code
　　from tb_market_code
　　for update
　　do
　　end for;
　　goto表达式
　　goto 标明名；
　　标明名：
　　逻辑体；
　　例：
　　GOTO FAIL；
　　...
　　SUCCESS: RETURN 0
　　FAIL: RETURN -200
　　whellole表达式
　　whellole 条件表达式 do
　　逻辑体；
　　end whellole；
　　LOOP表达式
　　LOOP... END LOOP；
　　例：
　　OPEN c1;
　　ins_loop:
　　LOOP
　　FETCH c1 INTO v_dept, v_deptname, v_admdept;
　　IF at_end = 1 THEN
　　LEAVEins_loop; --中断循环
　　ELSEIF v_dept = 'D11' THEN
　　ITERATEins_loop; --下一个循环
　　END IF;
　　INSERT INTO department (deptno, deptname, admrdept)
　　VALUES ('NEW', v_deptname, v_admdept);
　　END LOOP;
　　CLOSE c1;
　　关于游标
　　定义游标：
　　DECLARE 游标名 CURSOR FOR
　　Select 语句；
　　打开游标：
　　OPEN 游标名；
　　取值：
　　FETCH 游标名 INTO 变量列表
　　例：
　　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;
　　注：游标的说明要是放在中心段，要用”begin。。。end;”.段分割标记分割开；
　　动态sql
　　1) declare stmt varchar(1024);
　　set stmt='create table zhouhaiming( f1 smallint, f2 varchar⑼, f3 char⑸ )';
　　prepare s1 from stmt;
　　execute s1;
　　set stmt='insert into zhouhaiming values (1,'www','aaa')';
　　prepare s1 from stmt;
　　execute s1;
　　2) DECLARE CURSOR C1 FOR STMT1;
　　PREPARE STMT1 FROM
　　'ALLOCATE C2 CURSOR FOR RESULT SET ?';
　　临时表的建立
　　DECLARE GLOBAL TEMPORARY TABLE TABLE_NAME
　　AS (FULLSELECT) DEFINITION ONLY
　　EXCLUDING IDENTITY COLUMN ATTRIBUTES
　　ON COMMIT DELETE ROWS
　　NOT LOGGED IN 临时表空间名with replace；
　　第一行规定临时表的名称.
　　第二行规定临时表的列的定义.
　　第三行规定不是从源结果表定义中复制的恒等列.
　　第四行规定要是没有打开WITH GOLD光标,将会删除表的所有行.
　　第五行规定不对表的改变进行记录.
　　With replace选项会隐式的自己主动删除该临时表。
　　例如:
　　DECLARE GLOBAL TEMPORARY TABLE DEC_BSEMPMS
　　AS (SELECT * FROM BSEMPMS) DEFINITION ONLY
　　EXCLUDING IDENTITY COLUMN ATTRIBUTES
　　ON COMMIT DELETE ROWS
　　NOT LOGGED；
　　DB2中的几个全局变量
　　n ROW_COUNT—影响行数
　　UPDATE CORPDATA.PROJECT
　　SET PRSTAFF = PRSTAFF + 1.5
　　WHERE DEPTNO = deptnbr;
　　GET DIAGNOSTICSrcount = ROW_COUNT;
　　n RETURN_STATUS--返回状态
　　CALL TRYIT;--调用存储过程
　　GET DIAGNOSTICSRETVAL = RETURN_STATUS;
　　IF RETVAL <> 0 THEN
　　...
　　LEAVE A1;
　　ELSE
　　...
　　END IF;
　　n SQLSTATE—SQL返回纰缪代码
　　注：使用前必先定义
　　declare sqlstate char⑸;
　　declare state char⑸;
　　insert into tbname values(…)
　　set state=sqlstate;
　　if(state<> '00000') then
　　return -1;
　　end if;
　　关于ATOMIC以及NOT ATOMIC
　　P1:BEGIN ATOMIC –P1段的事务会自己主动回滚
　　P1:BEGIN NOT ATOMIC –P1段的事务不会自己主动回滚
　　DB2中的条件句柄
　　句柄类型:
　　n CONTINUE
　　n EXIT
　　n UNDO
　　条件类型：
　　n SQLSTATE string
　　n SQLEXCEPTION
　　n SQLWARNING
　　n NOT FOUND
　　例：
　　1）DECLARE EXIT HANDLER FOR NOT FOUND
　　SET medianSalary = 6666;
　　2） DECLARE not_found CONDITION FOR SQLSTATE '02000';
　　DECLARE EXIT HANDLER FOR not_found
　　SET rating = -1;
　　3) DECLARE not_found CONDITION FOR SQLSTATE '02000';
　　DECLARE c1 CURSOR FOR
　　SELECT deptno, deptname, admrdept
　　FROM department
　　ORDER BY deptno;
　　DECLARE CONTINUE HANDLER FOR not_found
　　SET at_end = 1;
　　怎样抽取/提交存储过程
　　db2 "get routine into 文件名 from procedure 存储过程名"
　　抽取存储过程；
　　提交存储过程
　　db2 "put routine from 文件名"
　　安装已经编译好的存储过程。
　　怎样在命令符下提交存储过程
　　在存储过程的最后加之@符号，之后在命令符下打入：db2 -td@ -vf procfile.sql 就可以生成过程。
　　非存储过程的SQL文件，在命令符下打入：db2 –tvf sqlfile.sql
　　从存储过程返回结果集(游标)的用法
　　一、建一sp返回结果集
　　CREATE PROCEDURE DB2INST1.Proc1 (
　　LANGUAGE SQL
　　result sets 2 --(返回两个结果集)
　　P1: BEGIN
　　declare c1 cursor with return to caller for
　　select market_code
　　from tb_market_code;
　　--指定该结果集用于返回给调用者
　　declare c2 cursor with return to caller for
　　select market_code
　　from tb_market_code;
　　open c1;
　　open c2;
　　END P1
　　2、建一SP调该sp且使用它的结果集
　　CREATE PROCEDURE DB2INST1.Proc2 (
　　out out_market_code char⑴)
　　LANGUAGE SQL
　　P1: BEGIN
　　declare loc1,loc2 result_set_locator varying;
　　--建立一个结果集数组
　　call proc1;
　　--调用该SP返回结果集。
　　associate result set locator(loc1,loc2) with procedure proc1;
　　--将返回结果集以及结果集数组关联
　　allocate cursor1 cursor for result set loc1;
　　allocate cursor2 cursor for result set loc2;
　　--将结果集数组分配给cursor
　　fetch cursor1 into out_market_code;
　　--直接从结果集中赋值
　　close cursor1;
　　END P1