如何聲明一個存儲過程
CREATE PROCEDURE 存儲過程名(IN 輸入變量名 輸入變量類型,OUT 輸出變量名 輸出變量類型)
緊跟其后的是存儲過程屬性列表
????????????常用的有：LANGUAGE SQL、MODIFIES SQL DATA、RESULT SETS 1(返回結果集個數(shù))
l?????????存儲過程體以begin開始
l?????????存儲過程體以end結束
存儲過程約束規(guī)則
存儲過程中調(diào)用存儲過程
CALL 存儲過程名（參數(shù)1，參數(shù)2，參數(shù)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 循環(huán)名 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; --中斷循環(huán)
ELSEIF v_dept = 'D11' THEN
ITERATEins_loop; --下一個循環(huán)
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;”.段分割標志分割開；
動態(tài)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；
第一行規(guī)定臨時表的名稱.
???第二行規(guī)定臨時表的列的定義.
???第三行規(guī)定不是從源結果表定義中復制的恒等列.
???第四行規(guī)定如果沒有打開WITH GOLD光標,將會刪除表的所有行.
???第五行規(guī)定不對表的改變進行記錄.
???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—影響行數(shù)
UPDATE CORPDATA.PROJECT
SET PRSTAFF = PRSTAFF + 1.5
WHERE DEPTNO = deptnbr;
GET DIAGNOSTICSrcount = ROW_COUNT;
n????????RETURN_STATUS--返回狀態(tài)
CALL TRYIT;--調(diào)用存儲過程
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
例：
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
從存儲過程返回結果集(游標)的用法
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;
?????????--指定該結果集用于返回給調(diào)用者
?????????declare c2 cursor???with return to caller for
?????????????select???market_code
?????????????from?????tb_market_code;
??????????open c1;
??????????open c2;
END P1???????????????????????????????????????

2、建一SP調(diào)該sp且使用它的結果集

CREATE PROCEDURE DB2INST1.Proc2 (
out out_market_code char(1))
?????LANGUAGE SQL
P1: BEGIN
declare loc1,loc2 result_set_locator varying;
--建立一個結果集數(shù)組
call proc1;
--調(diào)用該SP返回結果集。
associate result set locator(loc1,loc2) with procedure proc1;
--將返回結果集和結果集數(shù)組關聯(lián)
allocate cursor1 cursor for result set loc1;
allocate cursor2 cursor for result set loc2;
--將結果集數(shù)組分配給cursor
fetch???cursor1 into out_market_code;
--直接從結果集中賦值
close cursor1;?????????
END P1

本文來自CSDN博客，轉載請標明出處：http://blog.csdn.net/aawanghuan/archive/2008/02/20/2108951.aspx

轉載于:https://www.cnblogs.com/benbenduo/p/3978926.html

總結

以上是生活随笔為你收集整理的DB2存储过程语法规则的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。