将PL/SQL代码封装在灵巧的包中 - 数据库编程

将代码封装在灵巧的包中
http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13plsql-1872456.html

绝大多数基于PL/SQL的应用都是由成千上万甚至上百万行代码组成，这里面包含了详细多变的用户需求。
商业逻辑的实现最初是由存储过程和函数完成，但是开发者需要考虑将这些过程和函数放在包中维护。

何为包？
包是一组PL/SQL代码元素（游标、类型、变量、过程、函数）集合的程序单元。

通常由包声明（对象声明）和包体（具体实现）组成。

为什么要使用包？
1）组织和维护一组功能相关的对象；
2）对外隐藏具体实现；
3）提升性能，这一点要说一下：
当你第一次调用包时，整个包被加载入内存。接下来对同一包元素进行调用无需额外的磁盘I/O。
另外，包级别变量可以再会话级别（session-level）缓存起来，从而降低数据读取时间。
4）最小化程序单元重编译
外部程序（没有定义在包中）仅能调用包声明中的子程序。如果你改变并重新编译了包体，那些外部程序
将不会失效。

下面展示一下包的魅力：

1 一个简单的包：
假设我的employees表定义如下：

SQL> desc employees

Name             Type
————————————     —————————————
EMPLOYEE_ID      NUMBER(38)
FIRST_NAME       VARCHAR2(30)
LAST_NAME        VARCHAR2(50)

下面我需要定义一个process_employee的过程，返回员工全名（last_name, first_name）以供其他程序调用。

Code Listing 1: The process_employee procedure

CREATE OR REPLACE PROCEDURE process_employee ( employee_id_in IN employees.employee_id%TYPE) IS l_fullname VARCHAR2(100); BEGIN SELECT last_name || ',' || first_name INTO l_fullname FROM employees WHERE employee_id = employee_id_in; ... END;

仔细看，这个过程有几个问题： 1）l_fullname 长度固定为100？ 2）l_fullname的表达式固定为 last_name || ‘,’ || first_name？万一哪天客户改变主意：我们想在所有报告和信息中显示：first_name【空格】last_name咋办？如果你在N个过程中都已经使用了这种结构，那你是不是去一一找出来修改掉？ 3）最后一点，我们很有可能在不同的过程中编写一些重复SQL，这样会大大降低效率和性能

这个时间，我们需要将这种通用逻辑藏在包中，保证一处维护处处受益：

CREATE OR REPLACE PACKAGE employee_pkg 2 AS 3 SUBTYPE fullname_t IS VARCHAR2 (100); 4 5 FUNCTION fullname ( 6 last_in employees.last_name%TYPE, 7 first_in employees.first_name%TYPE) 8 RETURN fullname_t; 9 10 FUNCTION fullname ( 11 employee_id_in IN employees.employee_id%TYPE) 12 RETURN fullname_t; 13 END employee_pkg;

回头再改写过程，可以这样：

CREATE OR REPLACE PROCEDURE process_employee ( employee_id_in IN employees.employee_id%TYPE) IS l_name employee_pkg.fullname_t; employee_id_in employees.employee_id%TYPE := 1; BEGIN l_name := employee_pkg.fullname (employee_id_in); ... END;

代码变整洁了，还有你压根不需要关心employee_pkg.fullname 如何实现！多省心！

来看下包体是如何实现的：

CREATE OR REPLACE PACKAGE BODY employee_pkg 2 AS 3 FUNCTION fullname ( 4 last_in employees.last_name%TYPE, 5 first_in employees.first_name%TYPE 6 ) 7 RETURN fullname_t 8 IS 9 BEGIN 10 RETURN last_in || ', ' || first_in; 11 END; 12 13 FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE) 14 RETURN fullname_t 15 IS 16 l_fullname fullname_t; 17 BEGIN 18 SELECT fullname (last_name, first_name) INTO l_fullname 19 FROM employees 20 WHERE employee_id = employee_id_in; 21 22 RETURN l_fullname; 23 END; 24 END employee_pkg;

这里用到了函数重载，使得外部过程只需要传入不同参数即可调用不同版本的函数。最终都会返回fullname！

2 包级别数据此类数据由包声明和包体中全局的variables 和 constants组成。

例如：

CREATE OR REPLACE PACKAGE plsql_limits IS c_varchar2_length CONSTANT PLS_INTEGER := 32767; g_start_time PLS_INTEGER; END;

当你在一个子程序或匿名块中声明一个变量，称为本地变量，其声明周期限制在一次子程序调用或匿名块执行。

而包级别数据是在整个会话期间都会存活。

如果你在包体中定义包数据（变量和常量），该数据同样在会话期间存活，但是这类数据只能被包中程序使用，即为私有数据。另一方面，如果是在包声明中定义包数据则对所有具有执行包权限的程序都可使用。

来看一个例子： DBMS_UTILITY包中GET_CPU_TIME函数可用来计算你的程序耗时

Code Listing 5: DBMS_UTILITY.GET_CPU_TIME measures

DECLARE l_start PLS_INTEGER; BEGIN /* Get and save the starting time. */ l_start := DBMS_UTILITY.get_cpu_time; /* Run your code. */ FOR indx IN 1 .. 10000 LOOP NULL; END LOOP; /* Subtract starting time from current time. */ DBMS_OUTPUT.put_line ( DBMS_UTILITY.get_cpu_time - l_start); END; /

看着足够简单了吧，但是你还是需要声明一个本地变量来存放耗时！ so，我们有更快捷的方式，使用自定

将PL/SQL代码封装在灵巧的包中(一)