orcle中如何使⽤动态游标来对变量进⾏赋值!
在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 = '1';
儿童美术画画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 = ''1''' || ' 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.par(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 := ute(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 = '0' 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, '1', LV_CREATOR, SYSDATE, SYSDATE);
不谋而合的近义词
INSERT INTO T_TAX_PRIVATE(INSID, PAYER) VALUES(LV_INSID, LV_PAYER);
vivo手机怎么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;
