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;