　　在Oracle的存儲過程中，如果涉及到操作不同schema下的對象的時候，可以在不同的schema下寫相同的procedure，但這樣帶來的問題是維護和同步帶來了麻煩，可以在procedure中加上authid current_user，來說明procedure中操作的對象是當前連接用戶的對象而并不是procedure所屬用戶下的對象。

[@more@]


 -- USER01
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
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    authid current_user
  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
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博客

AUTHID CURRENT_USER

最新文章