摘要:PLSQL & SQL - 兩種 CURSOR 用法大不同
幾天前為了幫客戶洗檔,小寫了一個預存程序來處理,但是在用到 CURSOR 時踢到了鐵板。因為小呆很直覺的用 T-SQL 的方式來寫 PL/SQL,小呆的寫法如下:
Code:
DECLARE C_DATA_1 SCROLL CURSOR FOR SELECT F_NAME FROM F_TABLE_1
OPEN C_DATA_1
DECLARE @F_NAME NVARCHAR(100)
FETCH NEXT FROM C_DATA_1 INTO @F_NAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE C_DATA_2 SCROLL CURSOR FOR SELECT F_NAME_1, F_NAME_2 FROM F_TABLE_1 WHERE F_NAME_1 = @F_NAME
OPEN C_DATA_2
DECLARE @F_NAME_1 NVARCHAR(50)
DECLARE @F_NAME_2 NVARCHAR(50)
FETCH NEXT FROM E_TMP4 INTO @F_NAME_1, @F_NAME_2
WHILE (@@FETCH_STATUS = 0)
BEGIN
.
.
.
FETCH NEXT FROM C_DATA_2 INTO @F_NAME_1, @F_NAME_2
CONTINUE
END
CLOSE C_DATA_2
DEALLOCATE C_DATA_2
FETCH NEXT FROM C_DATA_1 INTO @F_NAME
CONTINUE
END
CLOSE C_DATA_1
DEALLOCATE C_DATA_1
但在 PL/SQL 的寫法卻不是這樣,因為:
1.在寫 PL/SQL 的預存程序時,只要是 CURSOR 都應該宣告在 BEGIN 之前
2.上寫的這種雙 CURSOR 的寫法,在 PL/SQL 中有自己的寫法,不要像小呆
一樣浪費時間的試
寫法如下:
Code:
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME IS
CURSOR C_Table_A IS Select Column_1 From Table_A;
CURSOR C_Table_B(P_Column_1 IN VARCHAR2) IS Select Column_2, Column_3 From Table_B;
V_Column_1 varchar2(50) := '';
BEGIN
FOR F_C_Table_A IN C_Table_A LOOP
V_Column_1 := F_C_Table_A.Column_1;
FOR F_C_CUST_COMMODITY_NO IN C_CUST_COMMODITY_NO(V_Column_1) LOOP
.
.
.
END LOOP;
END LOOP;
END;