很多时候，我们都会使用存储过程Procedure来实现一些脚本工具。通过Procedure来实现一些数据库相关的维护、开发工作，可以大大提高我们日常工作效率。一个朋友最近咨询了关于Procedure调用的问题，觉得比较有意思，记录下来供需要的朋友不时之需。
1、问题描述
问题背景是这样，朋友在运维一个开发项目，同一个数据库中多个Schema内容相同，用于不同的测试目的。一些开发同步任务促使编写一个程序来实现Schema内部或者之间对象操作。从软件版本角度看，维护一份工具脚本是最好的方法，可以避免由于修改造成的版本错乱现象。如何实现一份存储过程脚本，在不同Schema下执行效果不同就成为问题。
将问题简化为如下描述：在Schema A里面包括一个存储过程Proc，A中还有一个数据表T1。在Proc代码中，包括了对T1的操作内容。而Schema B中也存在一个数据表T1，并且B拥有一个名为Proc的私有同义词synonym指向A.Proc。问题是如何让Proc根据执行的Schema主体不同，访问不同Schema的数据表。
也就是说，如果是A调用Proc程序包，操作的就是A Schema里面的数据表T1。如果B调用Proc程序包，就操作B Schema里面的数据表T1。
2、测试实验一
为了验证测试，我们模拟了实验环境，来观察现象。选择11gR2进行测试。
|
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for 64-bit Windows: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 – Production 创建对应Schema和数据表。 SQL> create user a identified by a; User created SQL> create user b identified by b; User created SQL> grant connect, resource to a,b; Grant succeeded SQL> grant create procedure to a,b; Grant succeeded SQL> grant create synonym to a,b; Grant succeeded |
在Schema A下面创建数据表和对应操作存储过程。
|
SQL> conn a/a@sicsdb Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as a SQL> create table a(col varchar2(10)); Table created SQL> create or replace procedure Proc(i_vc_name varchar2) is 2 begin 3 insert into a values (i_vc_name); 4 commit; 5 end Proc; 6 / Procedure created |
从Schema A进行调用动作：
|
SQL> exec proc('iii'); PL/SQL procedure successfully completed SQL> select * from a; COL ---------------------------------------- Iii SQL> grant execute on proc to b; Grant succeeded |
另外创建Schema B数据表对象，并且包括同义词对象。
SQL> conn b/b@sicsdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as b
SQL> create table a(col varchar2(10));
Table created
SQL> create synonym proc for a.proc;
Synonym created
进行默认情况测试，在Schema B中调用存储过程proc，看操作数据表是哪张：
|
SQL> conn b/b@sicsdb Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as b SQL> exec proc('JJJ'); PL/SQL procedure successfully completed SQL> select * from a; COL ---------------------------------------- |
Schema B中数据表a没有数据，查看Schema A中数据表情况：
SQL> select * from a.a;
COL
--------------------
JJJ
Iii
实验说明：在默认情况下，不同Schema对象调用相同存储过程，其中涉及到的对象都是相同的。也就是Oracle存储过程中的“所有者权限”。一旦用户拥有执行存储过程的权限，就意味着在执行体中，使用的是执行体所有者的权限体系。
那么这个问题似乎是没有办法。执行体指向的是Schema A的数据表a。
3、测试实验二
与所有者权限对应的另一种模式是“调用者权限”。也就说，对用户是否可以执行该程序体中的对象，完全取决于执行调用用户系统权限和对象权限（注意：非角色权限）。
笔者一种猜想，如果应用调用者权限，从执行用户权限角度看，是不是可以直接访问自己Schema中的对象了。下面通过实验进行证明。
|
SQL> conn a/a@sicsdb Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as a SQL> SQL> create or replace procedure Proc(i_vc_name varchar2) AUTHID CURRENT_USER is 2 begin 3 insert into a values (i_vc_name); 4 commit; 5 end Proc; 6 / Procedure created |
很多时候，我们都会使用存储过程Procedure来实现一些脚本工具。通过Procedure来实现一些数据库相关的维护、开发工作，可以大大提高我们日常工作效率。一个朋友最近咨询了关于Procedure调用的问题，觉得比较有意思，记录下来供需要的朋友不时之需。
1、问题描述
问题背景是这样，朋友在运维一个开发项目，同一个数据库中多个Schema内容相同，用于不同的测试目的。一些开发同步任务促使编写一个程序来实现Schema内部或者之间对象操作。从软件版本角度看，维护一份工具脚本是最好的方法，可以避免由于修改造成的版本错乱现象。如何实现一份存储过程脚本，在不同Schema下执行效果不同就成为问题。
将问题简化为如下描述：在Schema A里面包括一个存储过程Proc，A中还有一个数据表T1。在Proc代码中，包括了对T1的操作内容。而Schema B中也存在一个数据表T1，并且B拥有一个名为Proc的私有同义词synonym指向A.Proc。问题是如何让Proc根据执行的Schema主体不同，访问不同Schema的数据表。
也就是说，如果是A调用Proc程序包，操作的就是A Schema里面的数据表T1。如果B调用Proc程序包，就操作B Schema里面的数据表T1。
2、测试实验一
为了验证测试，我们模拟了实验环境，来观察现象。选择11gR2进行测试。
|
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for 64-bit Windows: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 – Production 创建对应Schema和数据表。 SQL> create user a identified by a; User created SQL> create user b identified by b; User created SQL> grant connect, resource to a,b; Grant succeeded SQL> grant create procedure to a,b; Grant succeeded SQL> grant create synonym to a,b; Grant succeeded |
在Schema A下面创建数据表和对应操作存储过程。
|
SQL> conn a/a@sicsdb Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as a SQL> create table a(col varchar2(10)); Table created SQL> create or replace procedure Proc(i_vc_name varchar2) is 2 begin 3 insert into a values (i_vc_name); 4 commit; 5 end Proc; 6 / Procedure created |
最新内容请见作者的GitHub页：http://qaseven.github.io/