12开发包-CSDN博客

本文链接：https://blog.csdn.net/ar7043/article/details/3880423

包：用于逻辑组合PL/SQL的类型，项，和子程序。由包规范，包体组成。

一：建立包规范

包规范是与应用程序的接口,用于定义包的公用接口，常量，变量，游标，过程，函数等。包规范中所定义的公用组件不仅可以在包内引用。而且可以在其他子程序中引用。为了信息隐藏在包规范中只定义公用组件。

CREATE OR REPLACE PACKAGE emp_package IS

g_deptno NUMBER(10):=30;

PROCEDURE add_employee(eno NUMBER,name VARCHAR2,salary NUMBER,

dno NUMBER DEFAULT g_deptno);

PROCEDURE fire_employee(eno NUMBER);

FUNCTION get_sal(eno NUMBER) RETURN NUMBER;

END emp_package;

二：建立包体

包体用于实现包规范所定义的过程和函数。在包体中定义的私有组件只能在包内使用。而不能由其他子程序引用。

CREATE OR REPLACE PACKAGE BODY emp_package IS

FUNCTION validate_deptno(v_deptno NUMBER)

RETURN BOOLEAN

v_temp INT;

BEGIN

SELECT 1 INTO v_temp FROM dept WHERE deptno=v_deptno;

RETURN TRUE;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN FALSE;

END;

PROCEDURE add_employee(eno NUMBER,name VARCHAR2,salary NUMBER,dno NUMBER DEFAULT g_deptno)

BEGIN

IF validate_deptno(dno) THEN

INSERT INTO emp (empno,ename,sal,deptno)

VALUES(eno,name,salary,dno);

ELSE

RAISE_APPLICATION_ERROR(-20000,'不存在该部门');

END IF;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

RAISE_APPLICATION_ERROR(-20001,'该雇员已存在');

END;

PROCEDURE fire_employee(eno NUMBER) IS

BEGIN

DELETE FROM emp WHERE empno=eno;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20012,'该雇员不存在');

END IF;

END;

FUNCTION get_sal(eno NUMBER) RETURN NUMBER

v_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v_sal FROM emp WHERE empno=eno;

RETURN v_sal;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20012,'该雇员不存在');

END;

END emp_package;

1.调用包内公用变量

exec emp_package.g_deptno:=20

2.调用包内公用过程

Exec emp_package.add_employee(1111,’CLARK’,2000,10)

3.查看包源代码

SELECT text FROM user_source WHERE name=’EMP_PACKAGE’ AND type=’PACKAGE’;

三：使用包重载

使用重载特性，可以使用户在调用同名组件时使用不同参数传递数据。

1. 建立包规范：同名的过程和函数必须具有不同的输入参数。注：同名函数返回值的数据类型必须完全相同

CREATE OR REPLACE PACKAGE overload IS

FUNCTION get_sal(eno NUMBER) RETURN NUMBER;

FUNCTION get_sal(name VARCHAR2) RETURN NUMBER;

PROCEDURE fire_employee(eno NUMBER);

PROCEDURE fire_employee(name VARCHAR2);

END;

CREATE OR REPLACE PACKAGE BODY overload IS

FUNCTION get_sal(eno NUMBER) RETURN NUMBER

v_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v_sal FROM emp WHERE empno=eno;

RETURN v_sal;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20020,'该雇员不存在');

END;

FUNCTION get_sal(name VARCHAR2) RETURN NUMBER

v_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v_sal FROM emp

WHERE upper(ename)=upper(name);

RETURN v_sal;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20020,'该雇员不存在');

END;

PROCEDURE fire_employee(eno NUMBER) IS

BEGIN

DELETE FROM emp WHERE empno=eno;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20020,'该雇员不存在');

END iF;

END;

PROCEDURE fire_employee(name VARCHAR2) IS

BEGIN

DELETE FROM emp WHERE upper(ename)=upper(name);

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20020,'该雇员不存在');

END IF;

END;

四：使用包构造过程：类似于构造函数可用于初始化公共变量。

在所有函数定义完成后

BEGIN

SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;

END;

五：使用纯度级别

当时用包的公用函数时可以作为表达式的一部分使用。可以在SQL语句中使用。但如果要在SQL语句中引用包的公用函数。则不能包含DML语句。

WNDS（Write No Database State）：限制函数不能修改数据库数据

RNDS（Read NO Database State）：限制函数不能读取数据库数据（不能使用SELECT）

WNPS（Write No Package State）：限制函数不能修改包变量（不能给包变量赋值）

RNPS：限制函数不能读取包变量（不能将包变量赋值给其他变量）

CREATE OR REPLACE PACKAGE purity IS

2 minsal NUMBER(6,2);

3 maxsal NUMBER(6,2);

4 FUNCTION max_sal RETURN NUMBER;

5 FUNCTION min_sal RETURN NUMBER;

6 PRAGMA RESTRICT_REFERENCES(max_sal,WNPS);

7 PRAGMA RESTRICT_REFERENCES(min_sal,WNPS);

8 END;

习题：根据输入的订单号返回客户名和订单总价。如果订单不存在，则显示自定义错误。

FUNCTION get_info(id NUMBER) RETURN VARCHAR2

v_result VARCHAR2(100);

BEGIN

SELECT '客户名：'||b.name||',总金额:'||a.total

INTO v_result FROM ord a,customer b

WHERE a.customer_id = b.customer_id

AND a.ord_id=id;

RETURN v_result;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20004,'input correct oid');

EMD;