在Oracle的存儲過程中,如果涉及到操作不同schema下的對象的時候,可以在不同的schema下寫相同的procedure,但這樣帶來的問題是維護和同步帶來了麻煩,可以在procedure中加上authid current_user,來說明procedure中操作的對象是當前連接用戶的對象而并不是procedure所屬用戶下的對象。
[@more@]USER01@HUIYI>create table t 2 ( 3 col1 varchar2(10) 4 ) 5 / Table created. USER01@HUIYI>insert into t values(user); 1 row created. USER01@HUIYI>commit; Commit complete. USER01@HUIYI>select * from t; COL1 -------------------- USER01 USER02@HUIYI>create table t 2 ( 3 col1 varchar2(10) 4 ) 5 / Table created. USER02@HUIYI>insert into t values(user); 1 row created. USER02@HUIYI>commit; Commit complete. USER02@HUIYI>select * from t; COL1 -------------------- USER02 USER02@HUIYI>create or replace procedure pro_01 2 is 3 l_col1 varchar2(10); 4 begin 5 select col1 into l_col1 from t; 6 dbms_output.put_line(l_col1); 7 end; 8 / Procedure created. USER02@HUIYI>create or replace procedure pro_02 2 3 is 4 l_col1 varchar2(10); 5 begin 6 select col1 into l_col1 from t; 7 dbms_output.put_line(l_col1); 8 end; 9 / Procedure created. USER02@HUIYI>grant all on pro_01 to public; Grant succeeded. USER02@HUIYI>grant all on pro_02 to public; Grant succeeded. USER02@HUIYI>call pro_01(); USER02 Call completed. USER02@HUIYI>call pro_02(); USER02 Call completed. USER01@HUIYI>select * from t; COL1 -------------------- USER01 USER01@HUIYI>call user02.pro_01(); USER02 Call completed. USER01@HUIYI>call user02.pro_02(); USER01 Call completed.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8799875/viewspace-903797/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8799875/viewspace-903797/