存储过程:
create or replace procedure updateCwshState as
f_ywlxdm_temp varchar2(2);
f_ywid_forkyxxw_temp number(10);
f_cwsh_state_temp varchar2(2);
f_id_temp number(10);
begin
for i in(select t.id,t.f_ywlxdm,t.f_ywid_forkyxxw,t.f_cwsh_state from t_ywbltx t where (t.f_ywlxdm='04' or t.f_ywlxdm='07') and t.f_js_readstate<>'1' ) loop
f_ywlxdm_temp:=i.f_ywlxdm;
f_ywid_forkyxxw_temp:=i.f_ywid_forkyxxw;
f_cwsh_state_temp:=i.f_cwsh_state;
f_id_temp:=i.id;
if(f_ywlxdm_temp='04') then--课题
update oa.t_app_jfbx_cwbxforxm t set t.F_CWSHSTATE=DECODE(f_cwsh_state_temp,'0','财务正在审核','1','财务审核完毕') where t.id=f_ywid_forkyxxw_temp;
else--日常
update oa.t_app_jfbx_rcfybx t set t.F_CWSHSTATE=DECODE(f_cwsh_state_temp,'0','财务正在审核','1','财务审核完毕') where t.id=f_ywid_forkyxxw_temp;
end if;
if(f_cwsh_state_temp='1') then
update t_ywbltx t set t.f_js_readstate='1' where t.id=f_id_temp;
end if;
end loop;
end ;
编译时出现如下提示:
Error: PL/SQL: ORA-00942: table or view does not exist
....
Error: PL/SQL: SQL Statement ignored
....
原因是当前用户没有oa.t_app_jfbx_cwbxforxm, oa.t_app_jfbx_rcfybx 的查询和更新的权限导致的,如果A用户在存储过程中调用B用户的对象,则必须B显示的把该对象的相应权限赋予给用户A。
解决办法:
使用oa用户名登录授权给需要执行存储过程的用户;
grant select on t_App_Jfbx_Cwbxforxm to cwjk;
grant update on t_App_Jfbx_Cwbxforxm to cwjk;
grant select on t_app_jfbx_rcfybx to cwjk;
grant update on t_app_jfbx_rcfybx to cwjk;
然后编译就可以正常通过了