各位大侠好:
小弟项目中要用个存储过程,实现从一个结果集中取数据,然后用这个结果集来更新表(本例中t_sys_region_info),我的逻辑是先插入如果插入失败(主键约束异常)那么就更新,但是我的存储过程编译失败,总是提示错误。不知道是为什么,我的逻辑很简单 插入失败就更新。请各位大侠给予指导,谢谢。
提示信息:
PROCEDURE WJJS.TEST_P 编译错误
错误:PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时:
begin case declare
end exit for goto if loop mod null pragma raise return select
update while with
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
行:25
文本:exception
错误:PLS-00103: 出现符号 "CLOSE"在需要下列之一时:
end not pragma final
instantiable order overriding static member constructor map
行:33
文本:close c1;
文本:END test_p;
存储过程源码:
create or replace procedure test_p is
begin
DECLARE
-- r_code wjjs_org_info.code%TYPE;
-- r_mc wjjs_org_info.mc%TYPE;
-- r_p_dm wjjs_org_info.p_dm%TYPE;
r_org_info wjjs_org_info%Rowtype;
--err_code NUMBER;
--invalid_id exception;
CURSOR c1 is
select * from wjjs_org_info A
where not exists (
select *
from t_sys_region_info B
where A.code=B.org_code
and A.mc=B.org_name
and A.p_dm=B.parent_org_code
) ;
BEGIN
open c1;
loop
fetch c1 into r_org_info;
exit when c1%notfound ;
insert into t_sys_region_info(org_code,org_name,parent_org_code)values(r_org_info.code,r_org_info.mc,r_org_info.p_dm);
exception
When dup_val_on_index
Then
Dbms_output.put_line(SQLCODE);
Dbms_output.put_line(SUBSTR(SQLERRM, 1, 100));
update t_sys_region_info set org_name = r_org_info.mc, parent_org_code = r_org_info.p_dm
where org_code = r_org_info.code;
end loop;
close c1;
commit;
END;
end;
END test_p;