Chinaunix首页 | 论坛 | 博客
  • 博客访问: 283500
  • 博文数量: 27
  • 博客积分: 368
  • 博客等级: 一等列兵
  • 技术积分: 491
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-09 21:35
个人简介

再出发..

文章分类

全部博文(27)

文章存档

2018年(1)

2014年(6)

2013年(5)

2012年(15)

我的朋友

分类: Oracle

2012-05-19 11:37:44

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>>




阅读(2547) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~