ORACLE 建立多個遊標並嵌套迴圈

需求：每遍曆遊標2的記錄時分別去遍曆遊標1的資料，並替換後插入暫存資料表

CREATE OR REPLACE PROCEDURE TESTQ

IS

DownDictate VARCHAR2(200) DEFAULT '';

t_IsExist NUMBER:=0;

use_DownDictate VARCHAR2(200) DEFAULT '';

BEGIN

--判斷指令是否會錯誤        

SELECT DOWNDICTATE INTO DownDictate FROM T_MS_AUTOPLANDICTATE WHERE PROCEDURENAME='PKG_MEDI_IMM.QUERY_MEDI_IMM';          

IF DownDictate IS NULL THEN          

RETURN;        

END IF;        

--開始定義遊標

DECLARE   

---定義遊標       1

CURSOR C_PARAMETERS IS           

SELECT FUNCTIONNODECODE,FUNCTIONNODENAME FROM T_MS_FUNCTIONNODES WHERE FUNCTIONID =            (SELECT FUNCTIONID FROM T_MS_FUNCTION WHERE FUNCTIONPROCE='PKG_MEDI_IMM.QUERY_MEDI_IMM');        

---定義遊標          2

CURSOR C_MEDIIMME IS           

SELECT * FROM (  SELECT              A.GRANTMASTER_BARCODE,A.GRANTMASTER_DATE,B.UNITINFO_NAME,C.USERINFO_REALNAME FROM T_LABEL_GRANTMASTER A              LEFT OUTER JOIN T_ER_UNITINFO B ON A.GRANTMASTER_FARMID=B.UNITINFO_ID              LEFT OUTER JOIN T_SYS_USERINFO C ON A.GRANTMASTER_FARMERSID = C.USERINFO_ID              WHERE C.USERINFO_MOBILE IS NOT NULL OR C.USERINFO_PHONE IS NOT NULL) T;

--定義遊標1的行定義

TYPE paraType IS RECORD(           

code     VARCHAR2(20),           

codecn   VARCHAR2(50)         

);         

paraRowType paraType;

---定義遊標2的行定義

 TYPE rtype IS RECORD(            

yzpch    VARCHAR2(50),            

g_date   DATE,            

yzc  VARCHAR2(100),            

yznh   VARCHAR2(50)         

);         

c_rtype rtype;

 

--開始執行         

BEGIN           

OPEN C_MEDIIMME;        --開啟遊標 2              

LOOP            

FETCH C_MEDIIMME INTO c_rtype;            

EXIT WHEN C_MEDIIMME%NOTFOUND;            

use_DownDictate := DownDictate;            

OPEN C_PARAMETERS;       --開啟遊標1      (需要每遍曆遊標2的一條資料時開啟一次遊標1)

--遍曆參數表            

BEGIN              

LOOP                

FETCH C_PARAMETERS INTO paraRowType;                

EXIT WHEN C_PARAMETERS%NOTFOUND;                

IF paraRowType.code = 'yzpch' THEN                   

use_DownDictate := REPLACE(use_DownDictate,paraRowType.codecn||'#',c_rtype.yzpch);                

ELSIF paraRowType.code = 'yznh' THEN                   

use_DownDictate := REPLACE(use_DownDictate,paraRowType.codecn||'#',c_rtype.yznh);                

ELSIF paraRowType.code = 'yzc' THEN                   

use_DownDictate := REPLACE(use_DownDictate,paraRowType.codecn||'#',c_rtype.yzc);                

END IF;              

END LOOP;             

END;             

CLOSE C_PARAMETERS;    --關閉遊標1          

--插入暫存資料表              

INSERT INTO T_MEDIIMMETABLE(ReplaceResult,DateResult)              

VALUES(use_DownDictate,c_rtype.g_date);            

END LOOP;                      

CLOSE C_MEDIIMME;  --關閉遊標2       

END;

END TESTQ;