本文介绍了一种使用ORACLE存储过程结合动态SQL语句来循环处理数据并更新到另一表的方法。通过定义游标获取源表数据，利用获取的数据构造动态SQL进行目标表的插入操作。
ORACLE存储过程中动态SQL语句传值给游标解决方案：
create or replace procedure pro_test
as
TYPE ref_cursor_type IS REF CURSOR;
ctxx ref_cursor_type;
xhcs varchar2(10);
v_sql varchar2(100);
sqlStr varchar2(100);
begin
v_sql := 'select xh from a_test';
open ctxx for v_sql;
loop
fetch ctxx into xhcs;
exit when ctxx%notfound;
sqlStr := 'insert into a_test2 values('||xhcs||')';
execute immediate sqlStr;
end loop;
close ctxx;
commit;
end;
create or replace procedure pro_test
as
TYPE ref_cursor_type IS REF CURSOR;
ctxx ref_cursor_type;
xhcs varchar2(10);
v_sql varchar2(100);
sqlStr varchar2(100);
begin
v_sql := 'select xh from a_test';
open ctxx for v_sql;
loop
fetch ctxx into xhcs;
exit when ctxx%notfound;
sqlStr := 'insert into a_test2 values('||xhcs||')';
execute immediate sqlStr;
end loop;
close ctxx;
commit;
end;
扫一扫
3594
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
