一。理解包体
包体是对包说明的实现。也就是说包体中包含了包说明中声明的每一个游标和子程序的实现。一定要记住，包体内实现的内容只有在包说明中声明之后才能在包外引用。为了匹配包说明和包体，PL/SQL做了一个token-by-token的子程序头比较。所以，除了空白内容，头部内容必须完全一致。否则，PL/SQL就会抛出异常，如下例所示：
CREATE PACKAGE emp_actions AS
...
PROCEDURE calc_bonus(date_hired emp.hiredate%TYPE, ...);
END emp_actions;
CREATE PACKAGE BODY emp_actions AS
...
PROCEDURE calc_bonus(date_hired DATE, ...) IS
-- parameter declaration raises an exception because 'DATE'
-- does not match 'emp.hiredate%TYPE' word for word
BEGIN ... END;
END emp_actions;
包体能包含私有声明，可以定义类型和其它所需的内容。这些声明的内容对于包体来说是本地的。因此，声明的内容在包体之外是无法访问的。与包说明部分不同的是，包体的声明部分可以包含子程序体。
在包体的声明部分之后是一个可选的初始化部分，一般是用于初始化包中变量。
包初始化部分起到的作用并不大，与子程序不同，它不能被调用或是接受参数。因此，包初始化部分只能在我们首次引用包的时候运行一次。
请记住，如果一个包说明只声明了类型、常量、变量、异常和调用说明，那么，包体部分就不是必需的了。但是，包体可以用于初始化包说明中声明的内容。
二、包特性的例子
下面是一个名为emp_actions的包。包说明声明了类型、游标、异常和子程序：
- 类型EmpRecTyp和DeptRecTyp
- 游标desc_salary
- 异常invalid_salary
- 函数hire_employee和raise_salary
- 过程fire_empire和raise_salary
在编写包之后，我们就可以开发引用它声明的类型，调用它的子程序、游标和异常的应用程序。创建包时，它就会被存放在Oracle数据库中供广泛地调用。
CREATE PACKAGE emp_actions AS
/* Declare externally visible types, cursor, exception. */
TYPE emprectyp IS RECORD(
emp_id INT,
salary REAL
);
TYPE deptrectyp IS RECORD(
dept_id INT,
LOCATION VARCHAR2
);
CURSOR desc_salary RETURN emprectyp;
invalid_salary EXCEPTION;
/* Declare externally callable subprograms. */
FUNCTION hire_employee(
ename VARCHAR2,
job VARCHAR2,
mgr REAL,
sal REAL,
comm REAL,
deptno REAL
)
RETURN INT;
PROCEDURE fire_employee(emp_id INT);
PROCEDURE raise_salary(emp_id INT, grade INT, amount REAL);
FUNCTION nth_highest_salary(n INT)
RETURN emprectyp;
END emp_actions;
CREATE PACKAGE BODY emp_actions AS
number_hired INT; -- visible only in this package
/* Fully define cursor specified in package. */
CURSOR desc_salary RETURN emprectyp IS
SELECT empno, sal
FROM emp
ORDER BY sal DESC;
/* Fully define subprograms specified in package. */
FUNCTION hire_employee(
ename VARCHAR2,
job VARCHAR2,
mgr REAL,
sal REAL,
comm REAL,
deptno REAL
)
RETURN INT IS
new_empno INT;
BEGIN
SELECT empno_seq.NEXTVAL
INTO new_empno
FROM DUAL;
INSERT INTO emp
VALUES (new_empno, ename, job, mgr, SYSDATE, sal, comm, deptno);
number_hired := number_hired + 1;
RETURN new_empno;
END hire_employee;
PROCEDURE fire_employee(emp_id INT) IS
BEGIN
DELETE FROM emp
WHERE empno = emp_id;
END fire_employee;
/* Define local function, available only inside package. */
FUNCTION sal_ok(RANK INT, salary REAL)
RETURN BOOLEAN IS
min_sal REAL;
max_sal REAL;
BEGIN
SELECT losal, hisal
INTO min_sal, max_sal
FROM salgrade
WHERE grade = RANK;
RETURN (salary >= min_sal) AND(salary <= max_sal);
END sal_ok;
PROCEDURE raise_salary(emp_id INT, grade INT, amount REAL) IS
salary REAL;
BEGIN
SELECT sal
INTO salary
FROM emp
WHERE empno = emp_id;
IF sal_ok(grade, salary + amount) THEN
UPDATE emp
SET sal = sal + amount
WHERE empno = emp_id;
ELSE
RAISE invalid_salary;
END IF;
END raise_salary;
FUNCTION nth_highest_salary(n INT)
RETURN emprectyp IS
emp_rec emprectyp;
BEGIN
OPEN desc_salary;
FOR i IN 1 .. n LOOP
FETCH desc_salary
INTO emp_rec;
END LOOP;
CLOSE desc_salary;
RETURN emp_rec;
END nth_highest_salary;
BEGIN -- initialization part starts here
INSERT INTO emp_audit
VALUES (SYSDATE, USER, 'emp_actions');
number_hired := 0;
END emp_actions;
请记住，包初始化部分只是在我们首次引用包的时候执行一次。所以，在上面的例子中，只有一行数据被插入数据表emp_audit。同样，变量number_hired也只被初始化一次。
每次hire_employee被调用的时候，变量nubmer_hired就会被更新。但是，number_hired所记录的数字是与特定的会话相关的。也就是说，计数的结果是与一个用户所处理的新雇员的个数，而不是所有会话处理过的雇员个数。
在下面的例子中，我们把一些典型的银行事务进行打包。假设借款与贷款事务都是在营业时间之后通过自动出纳机处理，然后在第二天早上应用到账户中去。
CREATE PACKAGE bank_transactions AS
/* Declare externally visible constant. */
minimum_balance CONSTANT REAL := 100.00;
/* Declare externally callable procedures. */
PROCEDURE apply_transactions;
PROCEDURE enter_transaction(acct INT, kind CHAR, amount REAL);
END bank_transactions;
CREATE PACKAGE BODY bank_transactions AS
/* Declare global variable to hold transaction status. */
new_status VARCHAR2(70) := 'Unknown';
/* Use forward declarations because apply_transactions
calls credit_account and debit_account, which are not
yet declared when the calls are made. */
PROCEDURE credit_account(acct INT, credit REAL);
PROCEDURE debit_account(acct INT, debit REAL);
/* Fully define procedures specified in package. */
PROCEDURE apply_transactions IS
/* Apply pending transactions in transactions table
to accounts table. Use cursor to fetch rows. */
CURSOR trans_cursor IS
SELECT acct_id, kind, amount
FROM transactions
WHERE status = 'Pending'
ORDER BY time_tag
FOR UPDATE OF status; -- to lock rows
BEGIN
FOR trans IN trans_cursor LOOP
IF trans.kind = 'D' THEN
debit_account(trans.acct_id, trans.amount);
ELSIF trans.kind = 'C' THEN
credit_account(trans.acct_id, trans.amount);
ELSE
new_status := 'Rejected';
END IF;
UPDATE transactions
SET status = new_status
WHERE CURRENT OF trans_cursor;
END LOOP;
END apply_transactions;
PROCEDURE enter_transaction(
/* Add a transaction to transactions table. */
acct INT, kind CHAR, amount REAL) IS
BEGIN
INSERT INTO transactions
VALUES (acct, kind, amount, 'Pending', SYSDATE);
END enter_transaction;
/* Define local procedures, available only in package. */
PROCEDURE do_journal_entry(
/* Record transaction in journal. */
acct INT, kind CHAR, new_bal REAL) IS
BEGIN
INSERT INTO journal
VALUES (acct, kind, new_bal, SYSDATE);
IF kind = 'D' THEN
new_status := 'Debit applied';
ELSE
new_status := 'Credit applied';
END IF;
END do_journal_entry;
PROCEDURE credit_account(acct INT, credit REAL) IS
/* Credit account unless account number is bad. */
old_balance REAL;
new_balance REAL;
BEGIN
SELECT balance
INTO old_balance
FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance; -- to lock the row
new_balance := old_balance + credit;
UPDATE accounts
SET balance = new_balance
WHERE acct_id = acct;
do_journal_entry(acct, 'C', new_balance);
EXCEPTION
WHEN NO_DATA_FOUND THEN
new_status := 'Bad account number';
WHEN OTHERS THEN
new_status := SUBSTR(SQLERRM, 1, 70);
END credit_account;
PROCEDURE debit_account(acct INT, debit REAL) IS
/* Debit account unless account number is bad or
account has insufficient funds. */
old_balance REAL;
new_balance REAL;
insufficient_funds EXCEPTION;
BEGIN
SELECT balance
INTO old_balance
FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance; -- to lock the row
new_balance := old_balance - debit;
IF new_balance >= minimum_balance THEN
UPDATE accounts
SET balance = new_balance
WHERE acct_id = acct;
do_journal_entry(acct, 'D', new_balance);
ELSE
RAISE insufficient_funds;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
new_status := 'Bad account number';
WHEN insufficient_funds THEN
new_status := 'Insufficient funds';
WHEN OTHERS THEN
new_status := SUBSTR(SQLERRM, 1, 70);
END debit_account;
END bank_transactions;
在这个包中，我们没有使用初始化部分。
三、包中私有项和公有项
再看一下包emp_actions。包体声明了一个名为number_hired的变量，它被初始化为零。与在包说明中声明的内容不同，包体中的内容只局限于在当前包内使用。因此，在包外的PL/SQL代码就不能引用变量number_hired，这样的内容就是私有的。
但是，在emp_actions的说明中声明的内容，如异常invalid_salary，它对包外的程序来说就是可见的。因此，任何PL/SQL代码都可以访问它。这样的内容就是公有的。
当我们必须通过一个会话或跨事务来维护一些内容时，就需要把它们放在包体的声明部分。例如，number_hired的值在同一个会话中会被不断的更新。当会话结束时，这个值就会丢失。
如果我们必须把某些内容声明为公有的，就要把它们放在包的说明中。例如，声明在包bank_transactions中的常量minimum_balance就可以被广泛地使用。
四、重载包级子程序
PL/SQL允许两个或多个包级子程序拥有相同的名称。当我们想让一个子程序接受类型不同的相似的参数集合时，这个功能就很有效。例如，下面的包就定义了两个名为journalize过程：
CREATE PACKAGE journal_entries AS
...
PROCEDURE journalize(amount REAL, trans_date VARCHAR2);
PROCEDURE journalize(amount REAL, trans_date INT);
END journal_entries;
CREATE PACKAGE BODY journal_entries AS
...
PROCEDURE journalize(amount REAL, trans_date VARCHAR2) IS
BEGIN
INSERT INTO journal
VALUES (amount, TO_DATE(trans_date, 'DD-MON-YYYY'));
END journalize;
PROCEDURE journalize(amount REAL, trans_date INT) IS
BEGIN
INSERT INTO journal
VALUES (amount, TO_DATE(trans_date, 'J'));
END journalize;
END journal_entries;
第一个过程接受一个字符串trans_date，而第二个过程接受一个数字类型trans_date。每个过程都对数据进行了适当的处理操作。
五、包STANDARD是如何定义PL/SQL环境的
STANDARD包定义了PL/SQL环境。这个包的说明部分定义了全局类型、异常和子程序，这些内容对PL/SQL程序来说都是可用的。例如，STANDARD包声明了能返回一个数字的绝对值的ABS函数：
FUNCTION ABS (n NUMBER) RETURN NUMBER;
包STANDARD的内容对应用程序是可见的。我们不需要在引用这些内容的前面加限定修饰词。例如，我们可以从数据库触发器、存储子程序、Oracle工具或3GL应用程序中直接调用ABS：
abs_diff := ABS(x - y);
如果我们在PL/SQL中重新声明了ABS，我们的本地声明就会覆盖掉全局声明。但是，我们仍然可以通过添加限定修饰词来引用内部函数ABS：
abs_diff := STANDARD.ABS(x - y);
大多数的内部函数都被重载了。例如，包STANDARD包含了下面几种声明：
FUNCTION TO_CHAR (right DATE) RETURN VARCHAR2;
FUNCTION TO_CHAR (left NUMBER) RETURN VARCHAR2;
FUNCTION TO_CHAR (left DATE, right VARCHAR2) RETURN VARCHAR2;
FUNCTION TO_CHAR (left NUMBER, right VARCHAR2) RETURN VARCHAR2;
PL/SQL会按照形参与实参的个数和类型来解析对函数TO_CHAR的调用。
六、系统包一览
Oracle和各种Oracle工具都提供了系统包来帮助我们建立基于PL/SQL的应用程序。例如，Oracle提供了许多工具包，下面介绍一下其中比较典型的包。
1、关于DBMS_ALERT包
DBMS_ALERT能让数据库触发器在特定的数据库值发生变化时向应用程序发送报警。报警是基于事务的并且是异步的(也就是它们的操作与定时机制无关)。例如，当新的股票和债券上市时公司就可以通过这个包更新来他的投资总额。
2、关于DBMS_OUTPUT包
包DBMS_OUTPUT能让我们显示来自PL/SQL块和子程序中的输出内容，这样就会很容易地进行测试和调试。过程put_line能把信息输出到SGA的一个缓存中。我们可以通过调用过程get_line或在SQL*Plus中设置SERVEROUTPUT ON就能显示这些信息。假设我们创建了下面的存储过程：
CREATE PROCEDURE calc_payroll(payroll OUT NUMBER) AS
CURSOR c1 IS
SELECT sal, comm
FROM emp;
BEGIN
payroll := 0;
FOR c1rec IN c1 LOOP
c1rec.comm := NVL(c1rec.comm, 0);
payroll := payroll + c1rec.sal + c1rec.comm;
END LOOP;
/* Display debug info. */
DBMS_OUTPUT.put_line('Value of payroll: ' || TO_CHAR(payroll));
END;
使用下面的命令时，SQL*Plus就能显示出payroll的值：
SQL> SET SERVEROUTPUT ON
SQL> VARIABLE num NUMBER
SQL> CALL calc_payroll(:num);
Value of payroll: 31225
3、关于DBMS_PIPE包
包DBMS_PIPE允许不同的会话通过命名管道来进行通信(管道就是一块内存区域，进程使用这个区域把消息传递给另外一个进程)。我们可以使用过程pack_message和send_message把消息封装到一个管道，然后把消息发送到同一个实例中的另一个会话中。
管道的另一个终端，我们可以使用过程recieve_message和unpack_message来接受并打开要读取的消息。命名管道在很多地方都很有用。例如，我们可以用C语言编写一个收集信息的程序，然后把信息通过管道传递给存储过程。
4、关于UTL_FILE包
包UTL_FILE能让我们的PL/SQL程序读写操作系统(OS)文本文件。它提供了标准的OS流文件I/O，包括open、put、get和close操作。
当我们想要读写文件的时候，我们可以调用函数fopen，它能返回一个在后续过程调用中使用到的文件句柄。例如，过程put_line能往打开的文件中写入文本字符串，并在后边添加一个换行符，过程get_line能从打开的文件读取一行内容到放到一个输出缓存中。
5、关于UTL_HTTP包
包UTL_HTTP可以让我们PL/SQL程序使用超文本传输协议(HTTP)进行通信。它可以从互联网接收数据或调用Oracle Web服务器的cartridge。这个包有两个入口点，每一个都接受一个URL(统一资源定位器)字符串，然后连接到一个指定的网站并返回所请求的数据，这些数据通常是超文本标记语言HTML格式。
七、包编写准则
在编写包时，尽量让它们保持通用性，这样就能在以后的程序中多次使用。避免编写那些与Oracle已经提供的特性相同的包。
包说明反映了我们的应用程序设计。所以，一定在包体之前定义它们。只有那些对包用户必须可见的内容才可以放在说明部分。这样，其他的开发人员就不会滥用包中的内容了。
为了减少因代码改变而引起的重编译，尽量不要在包说明部分放置过多的内容。对包体内容的改变不需要编译其他独立的过程，但是，如果包说明发生改变，Oracle就得重新编译每一个引用到那个包的存储子程序了。
1482
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
