17,377
社区成员
发帖
与我相关
我的任务
分享
scott@ORCL> create or replace package pck_fsum
2 as
3 function fun_sumsal(c_deptno emp.deptno%type) return number;
4 end;
5 /
程序包已创建。
scott@ORCL> ed
已写入 file afiedt.buf
1 create or replace package body pck_fsum
2 as
3 function fun_sumsal(c_deptno emp.deptno%type) return number
4 as
5 c_sal number;
6 begin
7 select sum(sal) into c_sal from emp where deptno=c_deptno;
8 return c_sal;
9 end;
10* end;
scott@ORCL> /
程序包体已创建。
1* create user test1 identified by sys
scott@ORCL> /
用户已创建。
scott@ORCL> grant connect,resource to test1
2 /
授权成功。
scott@ORCL> ed
已写入 file afiedt.buf
1* grant execute on pck_fsum to test1
scott@ORCL> /
授权成功。
scott@ORCL> conn test1/sys
已连接。
test1@ORCL> ed
已写入 file afiedt.buf
1* select scott.pck_fsum.fun_sumsal(10) from dual
test1@ORCL> /
SCOTT.PCK_FSUM.FUN_SUMSAL(10)
-----------------------------
8750
----查看包头包体的定义
test1@ORCL> conn scott/sys
已连接。
scott@ORCL> grant select on all_source to test1
2 /
授权成功。
scott@ORCL> grant debug on pck_fsum to test1 ---关键这里
2 /
授权成功。
scott@ORCL> set long 1000
scott@ORCL> conn test1/sys
已连接。
test1@ORCL> set pagesize 200
test1@ORCL> select text from all_source a where a.type in('PACKAGE','PACKAGE BODY')
2 and a.name=upper('pck_fsum')
3 /
TEXT
--------------------------------------------------------------------------------
package pck_fsum
as
function fun_sumsal(c_deptno emp.deptno%type) return number;
end;
package body pck_fsum
as
function fun_sumsal(c_deptno emp.deptno%type) return number
as
c_sal number;
begin
select sum(sal) into c_sal from emp where deptno=c_deptno;
return c_sal;
end;
end;
已选择14行。
test1@ORCL>
SQL> select * from session_privs where PRIVILEGE like '%PROCEDURE%';
PRIVILEGE
----------------------------------------
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
DEBUG ANY PROCEDURE