oracle的动态游标,Oracle动态游标⼊门说明：下⾯的存储过程在Oracle817下全部通过测试，编译和运⾏均是正确的
⼀、最简单的⼀个动态游标：
CREATE OR REPLACE PROCEDURE test_cur
is
strSql1 varchar(1000);
TYPE TCUR IS REF CURSOR;
CUR TCUR;
AC_WHERE VARCHAR2(100);
AC VARCHAR2(100);
BEGIN
AC_WHERE := '(52228,52230)';
OPEN CUR FOR 'SELECT bill_id FROM bill_main WHERE bill_id IN '|| AC_WHERE;
LOOP
FETCH CUR INTO AC;
EXIT WHEN CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(AC);
END LOOP;
CLOSE CUR;
end test_cur;
⼆、动态游标中使⽤动态的SQL语句并执⾏：
CREATE OR REPLACE PROCEDURE test_cur
(
p_orgid_wins string
)
is
strSql1 varchar2(1000);
oracle游标的使用TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指⽰CUR_1的类型为My_CurType，⽽My_CurType是游标类型
AC_WHERE VARCHAR2(100);
AC VARCHAR2(100);
BEGIN
AC_WHERE := '(52228,52230)';
OPEN CUR_1 FOR 'SELECT bill_id FROM bill_main WHERE bill_id IN '|| AC_WHERE;--打开动态游标
LOOP
FETCH CUR_1 INTO AC;
EXIT WHEN CUR_1%NOTFOUND;
strSql1:='delete bill_main where bill_id='||AC;
DBMS_OUTPUT.PUT_LINE(strSql1);
DBMS_OUTPUT.PUT_LINE(AC);
execute immediate strSql1;--执⾏⼀个动态的SQL语句
commit;
END LOOP;
CLOSE CUR_1;
end test_cur;
三、动态游标中执⾏动态DQL语句：
CREATE OR REPLACE PROCEDURE test_cur
(
p_orgid_wins string
)
is
strSql1 varchar2(1000);
strSql2 varchar2(1000);
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指⽰CUR_1的类型为My_CurType，⽽My_CurType是游标类型AC_WHERE VARCHAR2(100);
t_to_orgid number;
t_bill_id number;
BEGIN
AC_WHERE := '(98978,98980)';
strSql1:='SELECT bill_id,to_orgid FROM bill_main WHERE bill_id IN '|| AC_WHERE; DBMS_OUTPUT.PUT_LINE(strSql1);
OPEN CUR_1 FOR strSql1;--打开动态游标
LOOP
FETCH CUR_1 INTO t_bill_id,t_to_orgid;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('t_to_orgid='||t_to_orgid);
strSql2:='delete bill_main where bill_id='||t_bill_id;
strSql2:=strSql2|| 'and start_no='||'16506';
DBMS_OUTPUT.PUT_LINE(strSql2);
DBMS_OUTPUT.PUT_LINE(t_bill_id);
execute immediate strSql1;--执⾏⼀个动态的SQL语句commit;
END LOOP;
CLOSE CUR_1;
end test_cur;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。