在oracle中动态游标的概念一般不常用,但有时根据客户的特殊业务,需要使用到动态游标来解决问题!在对于一条动态SQL语句而产生多条记录时,动态游标的使用将是一个很好的选择,具体参见如下在工作流项目中所应用到的用例:
CREATE OR REPLACE PROCEDURE PRO_WF_IMPORT_FROMCTAIS
AS
LV_INSID VARCHAR2(20);
LV_IDEAID VARCHAR2(20);
LV_CREATOR VARCHAR2(20);
LV_OID VARCHAR2(20);
LV_BTID VARCHAR2(20);
LV_PAYER VARCHAR(20);
LI_RECORDCOUNT NUMBER;
LV_MESSAGE VARCHAR2(20);
LV_SQL VARCHAR2(4000);
LV_SQLSTATEMENT VARCHAR2(4000);--动态SQL
LV_EXIST NUMBER;
LI_SRCURSOR INTEGER := DBMS_SQL.OPEN_CURSOR;--定义动态游标
LI_SRNORE INTEGER;--动态sql语句执行返回
LI_DBLINK VARCHAR2(20); CURSOR IMPORTWORKFLOW IS
SELECT ID, SQLSTATEMENT, REPEATE
FROM T_WF_ENG_INS_FROMCTAIS
WHERE EXECUTE = ''; ERR_GET_IDEAID EXCEPTION;
ERR_GET_FROMCTAIS_INFO EXCEPTION;
BEGIN FOR V_IMPORTRECORD IN IMPORTWORKFLOW LOOP
INSERT INTO T_WF_ENG_IMPORT_LOG(ID, SQLSTATEMENT, WFID, EXECUTETIME)
VALUES(S_IMPROT_LOG.NEXTVAL, V_IMPORTRECORD.SQLSTATEMENT, V_IMPORTRECORD.ID, SYSDATE);
COMMIT; LV_SQL := 'SELECT UPPER(SQLSTATEMENT) FROM T_WF_ENG_INS_FROMCTAIS '||
' WHERE EXECUTE = ''''' || ' AND ID=''' || V_IMPORTRECORD.ID ||'''';
EXECUTE IMMEDIATE LV_SQL INTO LV_SQLSTATEMENT;;--得到动态SQL语句.
SELECT DB_LINK INTO LI_DBLINK FROM USER_DB_LINKS;
LV_SQLSTATEMENT := REPLACE(LV_SQLSTATEMENT,' WHERE','@'||LI_DBLINK||' WHERE');--替换成dblink方式.得到最终的动态SQL语句.
LV_SQL := SUBSTR(LV_SQLSTATEMENT,INSTR(LV_SQLSTATEMENT,'FROM',1),LENGTH(LV_SQLSTATEMENT));
LV_SQL := REPLACE(LV_SQL,'FROM','SELECT COUNT(*) FROM');--用于判断此动态语句是否有记录.
EXECUTE IMMEDIATE LV_SQL INTO LV_EXIST;
IF LV_EXIST <>0 THEN --使用动态游标的开始
DBMS_SQL.parse(LI_SRCURSOR,LV_SQLSTATEMENT,DBMS_SQL.native);
DBMS_SQL.define_column(LI_SRCURSOR,1,LV_CREATOR,20);
DBMS_SQL.define_column(LI_SRCURSOR,2,LV_OID,20);
DBMS_SQL.define_column(LI_SRCURSOR,3,LV_BTID,20);
DBMS_SQL.define_column(LI_SRCURSOR,4,LV_PAYER,20);
LI_SRNORE := DBMS_SQL.execute(LI_SRCURSOR); LOOP
IF DBMS_SQL.FETCH_ROWS(LI_SRCURSOR) = 0 THEN
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(LI_SRCURSOR, 1, LV_CREATOR);
DBMS_SQL.COLUMN_VALUE(LI_SRCURSOR, 2, LV_OID);
DBMS_SQL.COLUMN_VALUE(LI_SRCURSOR, 3, LV_BTID);
DBMS_SQL.COLUMN_VALUE(LI_SRCURSOR, 4, LV_PAYER);
END IF;
---动态游标的结束(以end loop结束) IF LV_CREATOR IS NULL OR LV_OID IS NULL OR LV_BTID IS NULL OR LV_PAYER IS NULL THEN
RAISE ERR_GET_FROMCTAIS_INFO;
END IF; IF V_IMPORTRECORD.REPEATE = '' THEN
SELECT COUNT(B.INSID) INTO LI_RECORDCOUNT
FROM T_WF_ENG_INS_HSTY A, T_TAX_PRIVATE B
WHERE A.CLASSID = V_IMPORTRECORD.ID AND A.ID = B.INSID AND B.PAYER = LV_PAYER;
IF LI_RECORDCOUNT > 0 THEN
return;
END IF;
END IF; LV_INSID := FUN_WF_GET_MAX_INSID(LV_BTID);
PRO_SYS_GET_MAXID('T_WF_ENG_IDEA_HSTY',LV_IDEAID, LV_MESSAGE); IF LENGTH(LV_MESSAGE) > 0 THEN
RAISE ERR_GET_IDEAID;
END IF; SELECT COUNT(ID) INTO LV_EXIST FROM T_WF_ENG_INS_HSTY WHERE ID = LV_INSID;
IF LV_EXIST=0 THEN
SELECT COUNT(MINSID) INTO LV_EXIST FROM T_WF_ENG_IDEA_HSTY WHERE MINSID = LV_INSID;
IF LV_EXIST=0 THEN
SELECT COUNT(INSID) INTO LV_EXIST FROM T_TAX_PRIVATE WHERE INSID = LV_INSID;
IF LV_EXIST=0 THEN
INSERT INTO T_WF_ENG_INS_HSTY(ID, CLASSID, CREATOR, OID, BTID, SDAY, EDAY, STATUS)
VALUES(LV_INSID, V_IMPORTRECORD.ID, LV_CREATOR, LV_OID, LV_BTID, SYSDATE, SYSDATE, 'C'); INSERT INTO T_WF_ENG_IDEA_HSTY(ID, MINSID, MACT, USERID, STARTTIME, ENDTIME)
VALUES(LV_IDEAID, LV_INSID, '', LV_CREATOR, SYSDATE, SYSDATE); INSERT INTO T_TAX_PRIVATE(INSID, PAYER) VALUES(LV_INSID, LV_PAYER);
COMMIT;
END IF;
END IF;
END IF;
END LOOP;
END IF;
END LOOP;
COMMIT;
DBMS_SQL.CLOSE_CURSOR(LI_SRCURSOR);--最后关闭动态游标. EXCEPTION WHEN ERR_GET_IDEAID THEN
RAISE_APPLICATION_ERROR (-20001,LV_MESSAGE);
WHEN ERR_GET_FROMCTAIS_INFO THEN
RAISE_APPLICATION_ERROR (-20002,'对不起其它条件有为空的值!');
END PRO_WF_IMPORT_FROMCTAIS;