Package(包)
自定义类型、变量、常量、游标、过程、函数的逻辑组合，可简化应用开发，提高应用性能，
还可以实现信息隐藏以及子程序重载（overload）
在包规范中定义函数和过程时，只需要定义函数头和过程头
在包体中实现函数和过程时，跟一般创建过程和函数相同，只是略去'CREATE OR REPLACE',直接以'PROCEDURE IS ...'开始.
PACKAGE
|
|_____Package Specification(包规范)
| 定义包的公用组件（TYPE,VARIABLES,CONSTANT,CURSOR,PROCEDURE,FUNCTION）
|
|
|_____Package Body(包体)
| |
| |___包规范中定义的各组件的实现
| |
| |___包的私有组件（只能在包内引用）
|
|_____Overload (重载)
| 在包中定义多个同名的子程序，这样在调用子程序时可以使用不同的参数传递数据
| 从而简化应用的开发（比如调用同一个子程序名，输入empno或者ename都能查到sal）
|
|_____构造过程
| 初始化包内的共用变量，在回话内第一次调用包公用组件时，会自动执行构造过程，一次回话只会执行一次构造过程.
| (在实现了其他函数和过程了之后，在包体结尾部分以BEGIN开始，END结束)
|
|
|
|_____Purity Level(纯度级别)
| 限制包公用函数对公用变量的读取和赋值，以及对数据库的DML和SELECT操作
|____在包规范中实现
| PRAGMA RESTRICT_REFERENCES(function_name,WNPS);
| PRAGMA RESTRICT_REFERENCES(function_name,WNDS,RNDS);
|____WNDS:禁止函数执行DML
|____WNPS:禁止函数给包变量赋值
|____RNDS:禁止函数执行SELECT
|____RNPS:禁止函数将包变量赋值给其他变量
==============================emp_package==================================
emp_package
|
|___Variable: g_deptno
|
|___Function: validate_deptno (该函数未在包规范中指出，所以是私有组件)
|
|___Procedure: add_employee
|
|___Procedure: fire_employee
|
|___Function: get_sal
创建包规范：
SCOTT@uplookin>> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PACKAGE emp_package IS
2 g_deptno emp.deptno%TYPE :=30;
3 --FUNCTION validate_deptno(
4 -- v_deptno dept.deptno%TYPE)
5 -- RETURN BOOLEAN;
6 PROCEDURE add_employee(
7 empno emp.empno%TYPE,
8 ename emp.ename%TYPE,
9 job emp.job%TYPE,
10 mgr emp.mgr%TYPE,
11 hiredate emp.hiredate%TYPE,
12 sal emp.sal%TYPE,
13 comm emp.comm%TYPE,
14 deptno emp.deptno%TYPE);
15 PROCEDURE fire_employee(
16 v_empno emp.empno%TYPE,
17 v_ename OUT emp.ename%TYPE);
18 FUNCTION get_sal(
19 v_empno emp.empno%TYPE,
20 v_ename OUT emp.ename%TYPE)
21 RETURN NUMBER;
22* END emp_package;
SCOTT@uplookin>> /
Package created.
创建包体：
SCOTT@uplookin>> get emp_body.sql
1 CREATE OR REPLACE PACKAGE BODY emp_package
2 IS
3 FUNCTION validate_deptno(v_deptno dept.deptno%TYPE)
4 RETURN BOOLEAN IS
5 temp INT;
6 BEGIN
7 SELECT COUNT(*) INTO temp FROM dept WHERE deptno=v_deptno;
8 IF temp=0 THEN
9 RETURN FALSE;
10 ELSE
11 RETURN TRUE;
12 END IF;
13 END;
14 PROCEDURE add_employee(
15 empno emp.empno%TYPE,
16 ename emp.ename%TYPE,
17 job emp.job%TYPE,
18 mgr emp.mgr%TYPE,
19 hiredate emp.hiredate%TYPE,
20 sal emp.sal%TYPE,
21 comm emp.comm%TYPE,
22 deptno emp.deptno%TYPE)
23 IS
24 BEGIN
25 IF validate_deptno(deptno) THEN
26 INSERT INTO t1 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno);
27 ELSE
28 DBMS_OUTPUT.PUT_LINE('No such department...');
29 END IF;
30 END;
31 PROCEDURE fire_employee(
32 v_empno emp.empno%TYPE,
33 v_ename OUT emp.ename%TYPE)
34 IS
35 no_employee_found EXCEPTION;
36 BEGIN
37 DELETE FROM t1 WHERE empno=v_empno returning ename into v_ename;
38 IF SQL%NOTFOUND THEN
39 RAISE no_employee_found;
40 END IF;
41 EXCEPTION
42 WHEN no_employee_found THEN
43 DBMS_OUTPUT.PUT_LINE('No such employee...');
44 END;
45 FUNCTION get_sal(
46 v_empno emp.empno%TYPE,
47 v_ename OUT emp.ename%TYPE)
48 RETURN NUMBER
49 IS
50 permission_denied EXCEPTION;
51 v_sal emp.sal%TYPE;
52 BEGIN
53 IF v_empno=7839 THEN
54 RAISE permission_denied;
55 ELSE
56 SELECT sal,ename INTO v_sal,v_ename FROM EMP WHERE empno=v_empno;
57 RETURN v_sal;
58 END IF;
59 EXCEPTION
60 WHEN no_data_found THEN
61 DBMS_OUTPUT.PUT_LINE('No such employee...');
62 WHEN permission_denied THEN
63 DBMS_OUTPUT.PUT_LINE('Missing privileges...');
64 END;
65* END emp_package;
SCOTT@uplookin>> /
Package body created.
引用包中的函数：
SCOTT@uplookin>>
SCOTT@uplookin>> declare
2 v_ename emp.ename%type;
3 begin
4 dbms_output.put_line(emp_package.get_sal(&empno,v_ename));
5 dbms_output.put_line(v_ename);
6 end;
7 /
Enter value for empno: 7788
3000
SCOTT
PL/SQL procedure successfully completed.
SCOTT@uplookin>>
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝包的重载特性＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
SCOTT@uplookin>> CREATE OR REPLACE PACKAGE employee_package IS
2 FUNCTION get_sal(v_ename emp.ename%TYPE) RETURN NUMBER;
3 FUNCTION get_sal(v_empno emp.empno%TYPE) RETURN NUMBER;
4 END employee_package;
5 /
Package created.
SCOTT@uplookin>>
SCOTT@uplookin>> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PACKAGE BODY employee_package IS
2 FUNCTION get_sal(v_ename emp.ename%TYPE) RETURN NUMBER
3 IS
4 v_sal emp.sal%TYPE;
5 BEGIN
6 SELECT sal INTO v_sal FROM emp WHERE ename=v_ename;
7 RETURN v_sal;
8 END;
9 FUNCTION get_sal(v_empno emp.empno%TYPE) RETURN NUMBER
10 IS
11 v_sal emp.sal%TYPE;
12 BEGIN
13 SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
14 RETURN v_sal;
15 END;
16* END employee_package;
SCOTT@uplookin>> /
Package body created.
SCOTT@uplookin>> SELECT employee_package.get_sal(7788) FROM dual;
EMPLOYEE_PACKAGE.GET_SAL(7788)
------------------------------
3000
SCOTT@uplookin>> SELECT employee_package.get_sal('SCOTT') FROM dual;
EMPLOYEE_PACKAGE.GET_SAL('SCOTT')
---------------------------------
3000
SCOTT@uplookin>>
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝构建过程＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
SCOTT@uplookin>> ED
Wrote file afiedt.buf
1 CREATE OR REPLACE PACKAGE department_package IS
2 max_deptno dept.deptno%TYPE;
3 min_deptno dept.deptno%TYPE;
4 PROCEDURE add_dept(
5 v_deptno dept.deptno%TYPE,
6 v_dname dept.dname%TYPE,
7 v_loc dept.loc%TYPE);
8* END;
SCOTT@uplookin>> /
Package created.
SCOTT@uplookin>> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PACKAGE BODY department_package IS
2 PROCEDURE add_dept(
3 v_deptno dept.deptno%TYPE,
4 v_dname dept.dname%TYPE,
5 v_loc dept.loc%TYPE)
6 IS
7 invalid_deptno EXCEPTION;
8 BEGIN
9 IF v_deptno BETWEEN min_deptno AND max_deptno THEN
10 INSERT INTO t2 VALUES(v_deptno,v_dname,v_loc);
11 ELSE
12 RAISE invalid_deptno;
13 END IF;
14 EXCEPTION
15 WHEN invalid_deptno THEN
16 DBMS_OUTPUT.PUT_LINE('The department number is invalid...');
17 END;
18 BEGIN
19 SELECT MAX(deptno),MIN(deptno) INTO max_deptno,min_deptno FROM t2;
20* END;
SCOTT@uplookin>> /
Package body created.
SCOTT@uplookin>>
SCOTT@uplookin>> EXEC department_package.add_dept(15,'GOLDPAC','ZHUHAI');
PL/SQL procedure successfully completed.
SCOTT@uplookin>> EXEC department_package.add_dept(50,'GOLDPAC','ZHUHAI');
The department number is invalid...
PL/SQL procedure successfully completed.
SCOTT@uplookin>>
=================================PURITY LEVEL========================
在包规范中定义了禁止函数get_maxsal给包变量赋值（WNPS）
所以在创建包体时会报错子程序违反了相关编译指示
SCOTT@uplookin>> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PACKAGE purity_test AS
2 max_sal emp.sal%TYPE;
3 FUNCTION get_maxsal RETURN NUMBER;
4 PRAGMA RESTRICT_REFERENCES(get_maxsal,WNPS);
5* END purity_test;
SCOTT@uplookin>> /
Package created.
SCOTT@uplookin>> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PACKAGE BODY purity_test AS
2 FUNCTION get_maxsal RETURN NUMBER
3 IS
4 BEGIN
5 SELECT MAX(sal) INTO max_sal FROM emp;
6 RETURN max_sal;
7 END;
8* END purity_test;
SCOTT@uplookin>> /
Warning: Package Body created with compilation errors.
SCOTT@uplookin>> show err
Errors for PACKAGE BODY PURITY_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PLS-00452: Subprogram 'GET_MAXSAL' violates its associated pragma
SCOTT@uplookin>>
阅读(2543) | 评论(0) | 转发(0) |
