DB2存储过程及编程基础

如何声明一个存储过程
CREATE PROCEDURE 存储过程名(IN 输入变量名 输入变量类型,OUT 输出变量名 输出变量类型)
紧跟其后的是存储过程属性列表
            常用的有:LANGUAGE SQLMODIFIES 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(5);
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
while表达式
while 条件表达式 do
       逻辑体;
end while;
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;
注:游标的申明如果放在中间段,要用”beginend;”.段分割标志分割开;
动态sql
1) declare stmt varchar(1024);
set stmt=’create table zhouhaiming( f1 smallint, f2 varchar(9), f3 char(5) )’;
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(5);
declare state char(5);
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
例:
1DECLARE 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
从存储过程返回结果集(游标)的用法
1、建一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(1))
     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 
                </div>
阅读更多
文章标签: db2
个人分类: 数据库
上一篇Oracle临时表空间总结
下一篇centos 7 安装MYSQL8.0
想对作者说点什么? 我来说一句

DB2编程基础要点 sql 存储过程

2010年01月08日 59KB 下载

DB2 SQL存储过程基础

2012年12月19日 30KB 下载

DB2存储过程基础培训

2014年02月21日 518KB 下载

db2存储过程基础

2013年07月17日 129KB 下载

存储过程db2存储过程

2009年07月05日 216KB 下载

DB2存储过程官方教程

2012年11月13日 910KB 下载

没有更多推荐了,返回首页

关闭
关闭