orcal 游标使用

create or replace procedure P_TEST_XMBH(sReturn out varchar2 ) is

v_xmbh varchar2(200);

cursor cur_ht is
select P.XMBH
from scm_mdm_project p
where p.xm_zl_zt='Y' AND P.M_PRO='1';

begin
sReturn:='0';
open cur_ht;
loop
fetch cur_ht into v_xmbh;
exit when cur_ht%notfound;

insert into xm_test
(XMBH)
select v_xmbh from dual;
commit;
end loop;
close cur_ht;--关闭游标
commit;
sReturn:='100';
exception when others then
rollback;
p_pub_error_log(0,sqlcode,sqlerrm,'P_TEST_XMBH');
sReturn:='-1:数据处理错误!';
end P_TEST_XMBH;