SQL代码封装在灵巧的包中

绝大多数基于PL/SQL的应用都是由成千上万甚至上百万行代码组成，这里面包含了详细多变的用户需求。
商业逻辑的实现最初是由存储过程和函数完成，但是开发者需要考虑将这些过程和函数放在包中维护。何为包？
包是一组PL/SQL代码元素（游标、类型、变量、过程、函数）集合的程序单元。通常由包声明（对象声明）和包体（具体实现）组成。为什么要使用包？
1）组织和维护一组功能相关的对象；
2）对外隐藏具体实现；
3）提升性能，这一点要说一下：
当你第一次调用包时，整个包被加载入内存。接下来对同一包元素进行调用无需额外的磁盘I/O。
另外，包级别变量可以再会话级别（session-level）缓存起来，从而降低数据读取时间。
4）最小化程序单元重编译
外部程序（没有定义在包中）仅能调用包声明中的子程序。如果你改变并重新编译了包体，那些外部程序
将不会失效。下面展示一下包的魅力：1 一个简单的包：
假设我的employees表定义如下：

SQL> desc employeesName Type———————————— —————————————EMPLOYEE_IDNUMBER（38）FIRST_NAME VARCHAR2（30）LAST_NAMEVARCHAR2（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 employeesWHERE 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_pkg2AS3SUBTYPE fullname_t IS VARCHAR2 （100）;4 5FUNCTION fullname （6 last_inemployees.last_name%TYPE,7 first_inemployees.first_name%TYPE）8 RETURN fullname_t;9 10FUNCTION fullname （11 employee_id_in IN employees.employee_id%TYPE）12 RETURN fullname_t;13END 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_pkg2AS3 FUNCTION fullname （4last_in employees.last_name%TYPE,5first_in employees.first_name%TYPE6 ）7RETURN fullname_t8 IS9 BEGIN10 RETURN last_in || ", " || first_in;11END;12 13FUNCTION fullname （employee_id_in IN employee.employee_id%TYPE）14 RETURN fullname_t15IS16 l_fullname fullname_t;17BEGIN18 SELECT fullname （last_name, first_name） INTO l_fullname19 FROM employees20WHERE employee_id = employee_id_in;21 22 RETURN l_fullname;23 END;24END employee_pkg;

这里用到了函数重载，使得外部过程只需要传入不同参数即可调用不同版本的函数。
最终都会返回fullname！2 包级别数据
此类数据由包声明和包体中全局的variables 和 constants组成。例如：CREATE OR REPLACE PACKAGE plsql_limitsIS 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 LOOPNULL; END LOOP; /* Subtract starting time from current time. */ DBMS_OUTPUT.put_line （DBMS_UTILITY.get_cpu_time - l_start）;END;/

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

CREATE OR REPLACE PACKAGE timer_pkgIS PROCEDURE start_timer; PROCEDURE show_elapsed （message_in IN VARCHAR2 := NULL）;END timer_pkg;/CREATE OR REPLACE PACKAGE BODY timer_pkgIS g_start_time NUMBER := NULL; PROCEDURE start_timer IS BEGINg_start_time := DBMS_UTILITY.get_cpu_time; END; PROCEDURE show_elapsed （message_in IN VARCHAR2 := NULL） IS BEGINDBMS_OUTPUT.put_line （message_in || ": " || TO_CHAR （DBMS_UTILITY.get_cpu_time - g_start_time））;start_timer; END;END timer_pkg;/

改写之前的匿名块，如下：

BEGIN timer_pkg.start_timer; FOR indx IN 1 .. 10000 LOOPNULL; END LOOP; timer_pkg.show_elapsed （"10000 Nothings"）;END;/

哇哦！good job！不再需要声明本地变量，不再需要理解GET_CPU_TIME function 如何工作！3 子程序重载
我们都知道DBMS_OUTPUT.PUT_LINE用于往控制台打印字符数据，BEGIN DBMS_OUTPUT.PUT_LINE （100）;END;其有一个弊端，只能输出字符类型！

SQL> BEGIN2 DBMS_OUTPUT.PUT_LINE （TRUE）;3END;4/ DBMS_OUTPUT.PUT_LINE （TRUE）; *ERROR at line 2:ORA-06550: line 2, column 4:PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’

多尴尬！比较BOOLEAN类型无法转成字符类型！很多开发者不得不这么搞：IF l_student_is_registeredTHEN DBMS_OUTPUT.PUT_LINE （"TRUE"）;ELSE DBMS_OUTPUT.PUT_LINE （"FALSE"）;END IF;不得不说精神可嘉！
但是，我们有更好的方式：
Code Listing 7: The my_output package without overloading

CREATE OR REPLACE PACKAGE my_outputIS PROCEDURE put_line （value_in IN VARCHAR2）; PROCEDURE put_line （value_in IN BOOLEAN）; PROCEDURE put_line （value_in IN DATE,mask_inIN VARCHAR2 DEFAULT "YYYY-MM-DD HH24:MI:SS"）;END my_output;/

这就充分发挥了重载的价值！4 包状态及ORA-04068错误
这个问题是任何开发包的人都无法回避的。
包有状态？
当一个包有至少一个常量或变量声明在包级别，该包就有了状态！
当一个会话调用有状态包，PGA将包所有包级别数据存储起来！如果一个状态包重新编译，所有使用该包的会话在下次调用时都会抛出：ORA-04068错误。
因为存储在PGA中包级别数据已经过期了（out of date）！所以包必须再次初始化！此外，一旦ORA-04068抛出，会话中所有状态包，例如，DBMS_OUTPUT都将标识为未初始化。这通常意味着用户
必须断开会话重新连接。这个潜在的错误意味着当IT部门需要升级应用，他们需要确保所有用户已注销。但是在7*24的互联网世界这是
不能容忍的。所以在Oracle 11g r2中，oracle提供了基于版本的重定义功能（Edition-Based Redefinition feature）。详细请参考：oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf and docs.oracle.com/cd/E11882_01/appdev.112/e10471/adfns_editions.htmOracle数据库之PL/SQL程序基础设计 http://www.linuxidc.com/Linux/2015-06/119013.htmPL/SQL Developer实用技巧分享 http://www.linuxidc.com/Linux/2014-09/107391.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址