包重载
包重载实际上就是对包中的子程序的重载,之前我们已经对子程序的重载做过介绍,这里简单看下代码。
定义包规范:
CREATE OR REPLACE PACKAGE emp_action_pkg_overload IS
--定义一个增加新员工的过程
PROCEDURE newdept (
p_deptno dept.deptno%TYPE, --部门编号
p_dname dept.dname%TYPE, --部门名称
p_loc dept.loc%TYPE --位置
);
--定义一个增加新员工的过程,重载过程
PROCEDURE newdept (
p_deptno dept.deptno%TYPE, --部门编号
p_dname dept.dname%TYPE --部门名称
);
--定义一个获取员工加薪数量的函数
FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
RETURN NUMBER;
--定义一个获取员工加薪数量的函数,重载函数
FUNCTION getraisedsalary (p_ename emp.ename%TYPE)
RETURN NUMBER;
END emp_action_pkg_overload;
定义包体:
CREATE OR REPLACE PACKAGE BODY emp_action_pkg_overload IS
--公开,实现包规范中定义的newdept过程
PROCEDURE newdept (
p_deptno dept.deptno%TYPE, --部门编号
p_dname dept.dname%TYPE, --部门名称
p_loc dept.loc%TYPE --位置
)
AS
v_deptcount NUMBER; --保存是否存在员工编号
BEGIN
SELECT COUNT (*) INTO v_deptcount FROM dept
WHERE deptno = p_deptno; --查询在dept表中是否存在部门编号
IF v_deptcount > 0 --如果存在相同的员工记录
THEN --抛出异常
raise_application_error (-20002, '出现了相同的员工记录');
END IF;
INSERT INTO dept(deptno, dname, loc)
VALUES (p_deptno, p_dname, p_loc);--插入记录
END newdept;
PROCEDURE newdept (
p_deptno dept.deptno%TYPE, --部门编号
p_dname dept.dname%TYPE --部门名称
)
AS
v_deptcount NUMBER; --保存是否存在员工编号
BEGIN
SELECT COUNT (*) INTO v_deptcount FROM dept
WHERE deptno = p_deptno; --查询在dept表中是否存在部门编号
IF v_deptcount > 0 --如果存在相同的员工记录
THEN --抛出异常
raise_application_error (-20002, '出现了相同的员工记录');
END IF;
INSERT INTO dept(deptno, dname, loc)
VALUES (p_deptno, p_dname, '中国');--插入记录
END newdept;
--公开,实现包规范中定义的getraisedsalary函数
FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
RETURN NUMBER
IS
v_job emp.job%TYPE; --职位变量
v_sal emp.sal%TYPE; --薪资变量
v_salaryratio NUMBER (10, 2); --调薪比率
BEGIN
--获取员工表中的薪资信息
SELECT job, sal INTO v_job, v_sal FROM emp WHERE empno = p_empno;
CASE v_job --根据不同的职位获取调薪比率
WHEN '职员' THEN
v_salaryratio := 1.09;
WHEN '销售人员' THEN
v_salaryratio := 1.11;
WHEN '经理' THEN
v_salaryratio := 1.18;
ELSE
v_salaryratio := 1;
END CASE;
IF v_salaryratio <> 1 --如果有调薪的可能
THEN
RETURN ROUND(v_sal * v_salaryratio,2); --返回调薪后的薪资
ELSE
RETURN v_sal; --否则不返回薪资
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0; --如果没找到原工记录,返回0
END getraisedsalary;
--重载函数的实现
FUNCTION getraisedsalary (p_ename emp.ename%TYPE)
RETURN NUMBER
IS
v_job emp.job%TYPE; --职位变量
v_sal emp.sal%TYPE; --薪资变量
v_salaryratio NUMBER (10, 2); --调薪比率
BEGIN
--获取员工表中的薪资信息
SELECT job, sal INTO v_job, v_sal FROM emp WHERE ename = p_ename;
CASE v_job --根据不同的职位获取调薪比率
WHEN '职员' THEN
v_salaryratio := 1.09;
WHEN '销售人员' THEN
v_salaryratio := 1.11;
WHEN '经理' THEN
v_salaryratio := 1.18;
ELSE
v_salaryratio := 1;
END CASE;
IF v_salaryratio <> 1 --如果有调薪的可能
THEN
RETURN ROUND(v_sal * v_salaryratio,2); --返回调薪后的薪资
ELSE
RETURN v_sal; --否则不返回薪资
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0; --如果没找到原工记录,返回0
END getraisedsalary;
--私有,该函数在包规范中并不存在,只能在包体内被引用
FUNCTION checkdeptno(p_deptno dept.deptno%TYPE) RETURN NUMBER
AS
v_counter NUMBER(2);
BEGIN
SELECT COUNT(*) INTO v_counter FROM dept WHERE deptno=p_deptno;
RETURN v_counter;
END;
END emp_action_pkg_overload;
调用如下:
DECLARE
v_sal NUMBER(10,2);
BEGIN
emp_action_pkg_overload.newdept(43,'样品部','东京'); --重载过程使用示例
emp_action_pkg_overload.newdept(44,'纸品部');
v_sal:=emp_action_pkg_overload.getraisedsalary(7369); --重载函数使用示例
v_sal:=emp_action_pkg_overload.getraisedsalary('史密斯');
END;
包初始化
当会话第一次使用某个包时,会对包进行初始化,此时会初始化所有包级别的数据,对声明中的常量或变量指定赋默认值,初始化单元中的代码块。
如果默认的初始化无法满足要求,例如想执行一些较复杂的初始化工作,那么需要使用包初始化功能。包初始化单元是位于包体结尾的BEGIN语句和整个包最后的END之间的所有语句。
--定义包头,在包头中定义要公开的成员
CREATE OR REPLACE PACKAGE InitTest IS
TYPE emp_typ IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
CURSOR emp_cur RETURN emp%ROWTYPE; --定义游标
curr_time NUMBER; --当前秒数
emp_tab emp_typ; --定义集合类型的变量
--定义一个增加新员工的过程
PROCEDURE newdept (
p_deptno dept.deptno%TYPE, --部门编号
p_dname dept.dname%TYPE, --部门名称
p_loc dept.loc%TYPE --位置
);
--定义一个获取员工加薪数量的函数
FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
RETURN NUMBER;
END InitTest;
--定义包体,在包体的初始化区域对包进行初始化
CREATE OR REPLACE PACKAGE BODY InitTest IS
row_counter NUMBER:=1;
CURSOR emp_cur RETURN emp%ROWTYPE IS
SELECT * FROM emp ORDER BY sal DESC; --定义游标体
--定义一个增加新员工的过程
PROCEDURE newdept (
p_deptno dept.deptno%TYPE, --部门编号
p_dname dept.dname%TYPE, --部门名称
p_loc dept.loc%TYPE --位置
) AS
BEGIN
NULL;
END newdept;
--定义一个获取员工加薪数量的函数
FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
RETURN NUMBER IS
BEGIN
NULL;
END getraisedsalary;
BEGIN
--包初始化部分,定义包的代码
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'SSSSS')) INTO curr_time FROM dual;
FOR emp_row IN emp_cur LOOP
emp_tab(row_counter):=emp_row; --为集合赋值
row_counter:=row_counter+1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('出现了异常');
END InitTest;
调用如下:
DECLARE
v_time NUMBER;
BEGIN
v_time:=InitTest.curr_time; --获取当前的时间秒数
--输出索引表中的员工名称,以及当前的秒数。
DBMS_OUTPUT.put_line(InitTest.emp_tab(1).ename||' '||v_time);
END;
包的纯度级别
正如子程序可以在SQL语句中直接使用一样,包中的公共函数也可以在SQL语句中直接使用。同样,这些公共函数的定义也有一些限制,比如不能包含DML语句,不能读写远程包的变量。这些可以通过包的纯度级别来进行限制,定义包纯度级别的语法如下:
PRAGMA RESTRICT_REFERENCES (function_name, WNDS[,WNPS][,RNDS][,RNPS]);
- function_name
:指定已经定义的函数名
- WNDS:限制函数不能修改数据库数据,即禁止函数执行DML操作。
- WNPS:限制函数不能修改包变量,即不能为包变量赋值。
- RNDS:限制函数不能读取数据库数据,即不能执行SELECT操作。
- RNPS:限制函数不能读取包变量,即不能将包变量赋值给其他 变量。
要在包中使用包纯度级别,必须首先在包规范中定义函数,然后指定函数的纯度级别。
CREATE OR REPLACE PACKAGE purityTest IS
TYPE dept_typ IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
dept_tab dept_typ; --定义集合类型的变量
--定义一个增加新员工的过程
PROCEDURE newdept (
p_deptno dept.deptno%TYPE, --部门编号
p_dname dept.dname%TYPE, --部门名称
p_loc dept.loc%TYPE --位置
);
--定义一个获取员工加薪数量的函数
FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
RETURN NUMBER;
--设置纯度级别
PRAGMA RESTRICT_REFERENCES(newdept,WNPS);
PRAGMA RESTRICT_REFERENCES(getraisedsalary,WNDS,WNPS,RNPS);
END purityTest;
如果在函数体的定义中越过的纯度级别,那么在编译时将出现错误。
如果要编写可被SQL语句引用的包的公共函数,函数必须要符合WNDS、WNPS和RNPS这3个纯度级别。
包权限设置
要想让别的用户访问当前会话中创建的包,需要向其他用户分配EXECUTE的权限,如:
GRANT EXECUTE ON scott.purityTest TO userb;
查看和删除包
可以通过user_objects
,user_source
查询包规范和包体的状态和代码,也可以通过可视化工具来查看,如Toad,PL/SQL Developer,Oracle SQL Developer。
使用DROP PACKAGE或DROP PACKAGE BODY来对整个包或包体进行删除。
检查包的依赖性
在Oracle中,包头不依赖于包体,如果对包体进行改变,并不会影响到包头的状态,包头不需要重新编译。但是如果包头改变,将会使包体自动失效,因为包体紧密依赖包头。如果包体中引用的表的表结构改变,包体会失效,但包头不会失效。
数据字典视图user_dependencies
,all_dependencies
,dba_dependencies
也可以列出方案对象之间的依赖性关系。