oracle存储过程的定义者权限与调用者权限
当某个用户定义的存储过程由另一个用户调用时，需要指定存储过程的Definer（定义者权限 )与Invoker（调用者权限）。
一、存储过程的定义者权限
存储过程默认为该权限，也可以在定义存储过程的is|as之前添加authid definer参数，格式如下：
create [or replace] procedure 名称
[(参数1 in | out | in out 类型,
参数2 in | out | in out 类型, ...)]
authid definer
is|as
--变量声明区间
begin
--业务逻辑区间
exception
--异常处理
end;
/
说明：如果一个存储过程的执行权限为定义者权限，则当另一个用户调用该存储过程时，和存储过程的所有者调用该存储过程是一样的（就相当于是存储过程的所有者调用了该存储过程）。
测试效果：
1、在scott用户下创建一张表t1，并输入数据
create table t1(
id number(6) primary key,
name varchar2(20),
age number(2),
phone varchar2(20)
);
SQL> select * from t1;
ID NAME AGE PHONE
---------- -------------------- ---------- --------------------
101 JACK 25 13603735588
102 JERRY 21 13503736645
103 TOM 32 13037350422
104 ROSE 28 13903732266
105 MARK 19 15903736633
106 JIMMI 20 15037355066
6 rows selected.
2、在scott用户下创建一个存储过程，使用默认的definer权限，查看某个用户的姓名和电话
SQL>
create or replace procedure sp_get_t1
(v_id number,
v_name out varchar2,
v_phone out varchar2)
authid definer
is
begin
select name,phone into v_name,v_phone
from t1
where id=v_id;
exception
when no_data_found then
dbms_output.put_line('没有该用户信息！');
end;
15 /
Procedure created.
3、查看该存储过程的信息
该存储过程的执行权限为DEFINER（定义者权限）。
SQL> select object_name,procedure_name,authid from user_procedures where object_name='SP_GET_T1';
OBJECT_NAME PROCEDURE_NAME AUTHID
------------------------------ -------------------- ------------
SP_GET_T1 DEFINER
4、调用该存储过程
SQL>
declare
v_id t1.id%type;
v_name t1.name%type;
v_phone t1.phone%type;
begin
v_id:=&t1_id;
sp_get_t1(v_id,v_name,v_phone);
dbms_output.put_line(
v_id||'用户的姓名：'||v_name||
',电话：'||v_phone);
end;
12 /
Enter value for t1_id: 103
old 6: v_id:=&t1_id;
new 6: v_id:=103;
103用户的姓名：TOM,电话：13037350422
PL/SQL procedure successfully completed.
5、创建一个用户JACK，在该用户下创建表t1，并输入数据
（1）创建用户JACK并授权
SQL> show user;
USER is "SYS"
SQL>
create user JACK identified by JACK;
User created.
Elapsed: 00:00:00.09
Elapsed: 00:00:00.03
SQL> grant resource,connect to JACK;
Grant succeeded.
（2）以JACK用户身份登录，创建表t1并输入数据
SQL> show user;
USER is "JACK"
SQL>
create table t1(
id number(6) primary key,
name varchar2(20),
age number(2),
phone varchar2(20)
6 );
Table created.
SQL> select * from t1;
ID NAME AGE PHONE
---------- -------------------- ---------- --------------------
101 张飞 25 100801
102 赵云 21 200258
103 关羽 32 400125
6、以scott用户身份登录，把存储过程sp_get_t1的执行权限授予用户JACK
SQL> show user;
USER is "SCOTT"
SQL> grant execute on sp_get_t1 to JACK;
Grant succeeded.
7、以用户JACK身份登录，调用存储过程sp_get_t1并查看结果
SQL> show user;
USER is "JACK"
SQL>
declare
v_id t1.id%type;
v_name t1.name%type;
v_phone t1.phone%type;
begin
v_id:=&t1_id;
sp_get_t1(v_id,v_name,v_phone);
dbms_output.put_line(
v_id||'用户的姓名：'||v_name||
',电话：'||v_phone);
end;
11 12 /
Enter value for t1_id: 103
old 6: v_id:=&t1_id;
new 6: v_id:=103;
103用户的姓名：TOM,电话：13037350422
PL/SQL procedure successfully completed.
此时发现，用户JACK调用存储过程sp_get_t1的输出结果和存储过程的定义者scott调用该存储过程的结果是完全相同的。
二、存储过程的调用者权限
可以在定义存储过程的is|as之前添加authid current_user参数，格式如下：
create [or replace] procedure 名称
[(参数1 in | out | in out 类型,
参数2 in | out | in out 类型, ...)]
authid current_user
is|as
--变量声明区间
begin
--业务逻辑区间
exception
--异常处理
end;
/
说明：如果一个存储过程的执行权限为调用者权限，则当另一个用户调用该存储过程时，就相当于该用户自己创建了一个完全相同的存储过程。
测试效果：
1、在scott用户下创建一个存储过程，使用current_user权限，查看某个用户的姓名和电话
SQL> show user;
USER is "SCOTT"
SQL>
create or replace procedure sp_get_t1_02
(v_id number,
v_name out varchar2,
v_phone out varchar2)
authid current_user
is
begin
select name,phone into v_name,v_phone
from t1
where id=v_id;
exception
when no_data_found then
dbms_output.put_line('没有该用户信息！');
end;
15 /
Procedure created.
2、查看该存储过程的信息
该存储过程的执行权限为CURRENT_USER（调用者权限）。
SQL> select object_name,procedure_name,authid from user_procedures
where object_name='SP_GET_T1_02';
OBJECT_NAME PROCEDURE_NAME AUTHID
------------------------------ -------------------- ------------
SP_GET_T1_02 CURRENT_USER
3、scott用户调用该存储过程
SQL> show user;
USER is "SCOTT"
SQL>
declare
v_id t1.id%type;
v_name t1.name%type;
v_phone t1.phone%type;
begin
v_id:=&t1_id;
sp_get_t1_02(v_id,v_name,v_phone);
dbms_output.put_line(
v_id||'用户的姓名：'||v_name||
',电话：'||v_phone);
end;
12 /
Enter value for t1_id: 103
old 6: v_id:=&t1_id;
new 6: v_id:=103;
103用户的姓名：TOM,电话：13037350422
PL/SQL procedure successfully completed.
4、以scott用户身份登录，把存储过程sp_get_t1_02的执行权限授予用户JACK
SQL> show user;
USER is "SCOTT"
SQL> SQL> grant execute on sp_get_t1_02 to JACK;
Grant succeeded.
5、以用户JACK身份登录，调用存储过程sp_get_t1_02并查看结果
SQL> show user;
USER is "JACK"
SQL>
declare
v_id t1.id%type;
v_name t1.name%type;
v_phone t1.phone%type;
begin
v_id:=&t1_id;
scott.sp_get_t1_02(v_id,v_name,v_phone);
dbms_output.put_line(
v_id||'用户的姓名：'||v_name||
',电话：'||v_phone);
end;
12 /
Enter value for t1_id: 103
old 6: v_id:=&t1_id;
new 6: v_id:=103;
103用户的姓名：关羽,电话：400125
PL/SQL procedure successfully completed.
此时发现，用户JACK调用存储过程sp_get_t1_02，就像自己编写了一个存储过程，调用的是自己的t1表。
四、查看存储过程信息
1、查看当前用户所定义的所有存储过程的名称
SQL> select object_name,procedure_name,object_type,deterministic,authid from user_procedures;
OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE DET AUTHID
------------------------------ -------------------- ------------------- --- ------------
SP_EMP_INSERT PROCEDURE NO DEFINER
SP_SET_EMP_SAL PROCEDURE NO DEFINER
SP_DISP PROCEDURE NO DEFINER
SP_CIRCLE_AREA PROCEDURE NO DEFINER
SP_UPDATE_EMP_SAL PROCEDURE NO DEFINER
SP_GET_EMP PROCEDURE NO DEFINER
SP_UPDATE_EMP_SAL_01 PROCEDURE NO DEFINER
SP_INSERT_T01_FORALL PROCEDURE NO DEFINER
SP_SCORE_GRADE PROCEDURE NO DEFINER
SP_DELETE_T1_FORALL PROCEDURE NO DEFINER
SP_INSERT_T01_FORALL02 PROCEDURE NO DEFINER
SP_UPDATE_T1_FORALL PROCEDURE NO DEFINER
SP_INSERT_T01 PROCEDURE NO DEFINER
SP_UPDATE_EMP_BULK PROCEDURE NO DEFINER
SP_UPDATE_T01_FORALL PROCEDURE NO DEFINER
SP_EMP_ENAME_DEPT PROCEDURE NO DEFINER
SP_EMP_DEPTNO11 PROCEDURE NO DEFINER
SP_EMP_DEPTNO PROCEDURE NO DEFINER
SP_FETCH_EMP PROCEDURE NO DEFINER
PS_ADD PROCEDURE NO DEFINER
SP_ADD PROCEDURE NO DEFINER
SET_SAL PROCEDURE NO DEFINER
GET_EMP PROCEDURE NO DEFINER
TEST_CREATE PROCEDURE NO DEFINER
UPDATE_SAL PROCEDURE NO DEFINER
ADD_SAL PROCEDURE NO DEFINER
26 rows selected.
2、查看存储过程的参数信息
SQL> desc get_emp;
PROCEDURE get_emp
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_EMPNO NUMBER IN
V_ENAME VARCHAR2 OUT
V_SAL NUMBER OUT
3、查看某个存储过程的代码
SQL> select text from user_source where name='SP_UPDATE_EMP_SAL';
TEXT
--------------------------------------------------------------------------------
procedure sp_update_emp_sal
as
type t_table_emp is table of emp%rowtype
index by binary_integer;
v_table_emp t_table_emp;
begin
update emp set sal=sal*1.1 where deptno=10;
select * bulk collect into v_table_emp from emp where deptno=10;
for i in v_table_emp.first..v_table_emp.last loop
dbms_output.put_line('雇员编号：'||v_table_emp(i).empno||
', 姓名：'||v_table_emp(i).ename||
', 工资：'||v_table_emp(i).sal);
end loop;
end;
14 rows selected.
五、存储过程应用举例
1、无参数存储过程
把emp表10部分所有人的工资增加10%，并且显示员工编号、姓名及工资信息。
SQL>
create or replace procedure sp_update_emp_sal
as
type t_table_emp is table of emp%rowtype
index by binary_integer;
v_table_emp t_table_emp;
begin
update emp set sal=sal*1.1 where deptno=10;
select * bulk collect into v_table_emp from emp where deptno=10;
for i in v_table_emp.first..v_table_emp.last loop
dbms_output.put_line('雇员编号：'||v_table_emp(i).empno||
', 姓名：'||v_table_emp(i).ename||
', 工资：'||v_table_emp(i).sal);
end loop;
end;
/
Procedure created.
运行存储过程，结果如下：
SQL> call sp_update_emp_sal();
雇员编号：7934, 姓名：MILLER, 工资：1574.21
雇员编号：7782, 姓名：CLARK, 工资：2965.71
雇员编号：7839, 姓名：KING, 工资：6051.21
Call completed.
注意：无参数存储过程调用时要加一对空括号。
2、带输入参数的存储过程
对上例的存储过程进行修改，通过指定一个输入参数，用户在调用时指定某个部门，则把该部门员工的工资增加10%，并显示该部门员工的工号、姓名和工资。代码如下：
SQL>
create or replace procedure sp_update_emp_sal_01
(v_deptno number)
as
type t_table_emp is table of emp%rowtype
index by binary_integer;
v_table_emp t_table_emp;
begin
update emp set sal=sal*1.1 where deptno=v_deptno;
select * bulk collect into v_table_emp from emp where deptno=v_deptno;
for i in v_table_emp.first..v_table_emp.last loop
dbms_output.put_line('雇员编号：'||v_table_emp(i).empno||
', 姓名：'||v_table_emp(i).ename||
', 工资：'||v_table_emp(i).sal);
end loop;
end;
/
Procedure created.
调用时指定20部门作为参数，查询结果如下：
SQL> call sp_update_emp_sal_01(20);
雇员编号：7788, 姓名：SCOTT, 工资：2423.63
雇员编号：7876, 姓名：ADAMS, 工资：1334.63
雇员编号：7566, 姓名：JONES, 工资：3603.38
雇员编号：7902, 姓名：FORD, 工资：3633.63
雇员编号：7369, 姓名：SMITH, 工资：971.63
Call completed.
3、指定输出参数
输入一个圆的半径，求圆的面积，代码如下：
SQL>
create or replace procedure sp_circle_area
(v_radius in number,area out number)
as
begin
area:=3.1415926*power(v_radius,2);
end;
7 /
Procedure created.
在代码块中调用该该存储过程，结果如下：
SQL>
declare
v_area number(15,8);
begin
sp_circle_area(4,v_area);
dbms_output.put_line('圆的面积为：'||v_area);
end;
7 /
圆的面积为：50.2654816
PL/SQL procedure successfully completed.
4、综合练习
为emp插入一条新记录：
（1）创建一个序列seq_emp，从9001开始编号
SQL> create sequence seq_emp start with 9001;
Sequence created.
（2）创建一个存储过程sp_emp_insert
create or replace procedure sp_emp_insert
(v_ename varchar2,
v_job varchar2 default 'SALESMAN',
v_mgr number default 7698,
v_hiredate date default sysdate,
v_sal number default 2000,
v_comm number default 0,
v_deptno number default 40)
as
begin
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(seq_emp.nextval,
v_ename,
v_job,
v_mgr,
v_hiredate,
v_sal,
v_comm,
v_deptno);
end;
/
执行存储过程：
SQL> call sp_emp_insert('Jerry');
Call completed.
Elapsed: 00:00:00.02
SQL> call sp_emp_insert('John',v_sal=>1800);
Call completed.
Elapsed: 00:00:00.00
SQL> call sp_emp_insert('Merry',v_hiredate=>sysdate-10,v_sal=>2100);
Call completed.
Elapsed: 00:00:00.01
SQL> select * from emp where empno>9000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ------
9001 Jerry SALESMAN 7698 2020-02-17 01:01:22 2000 0 40
9002 John SALESMAN 7698 2020-02-17 01:02:39 1800 0 40
9003 Merry SALESMAN 7698 2020-02-07 01:03:40 2100 0 40
5086
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
