在前面的部分介紹了PL/SQL塊的基本編寫方法。
在SQL*plus 中編寫PL/SQL程序，並在SQL*plus 中執行它， PL/SQL塊的代碼就存放在SQL*plus的緩沖區中。
如果在SQL*plus 中執行了其他的SQL語句或PL/SQL塊，緩沖區中就會存放新的代碼，原來的PL/SQL塊就會被從緩沖區中清除出去。
這種沒有名稱只是臨時存放在緩沖區中的PL/SQL塊叫做匿名塊。
匿名塊就是沒有名字的PL/SQL塊，它僅存放在緩沖區中，只能在當前SQL*plus環境中執行。
如果希望PL/SQL塊能隨時被調用執行，並且能被數據庫用戶共享，就需要創建存儲程序。
存儲程序是有名字的PL/SQL塊，用戶可以根據它的名字進行多次調用。
存儲程序在創建時經過了編譯與優化，被存放在數據庫中，任何用戶只要有適當的權限，就可以調用它。
而且在調用時無需再進行編譯，因此能以很快的速度執行。
與匿名塊相比，存儲程序是作為數據庫對象存儲在數據庫中的，因此，首先要在數據庫中創建存儲程序。
存儲程序的調用可以在SQL語句中、應用程序中、SQL*plus 中以及其他PL/SQL塊中進行。
在第一次被調用時，存儲程序的代碼被裝載到系統全局區的共享池中，以後再次調用時直接從共享池中取出代碼即可執行。
存儲程序與前面介紹的子程序的區別在於子程序是完成某個特定功能的程序段，它本身並不能單燭執行，只能作為一個模塊，在一個PL/SQL塊內部被調用執行。
而存儲程序是一個可單獨執行的程序，它可以包含多個子程序，可以在SQL語句中、應用程序中、SQL*plus 中以及其他PL/SQL塊中被調用執行。
存儲程序的形式包括：存儲過程、存儲函數、觸發器和程序包等。
存儲過程
如果用戶要在自己的模式中創建存儲過程，需要具有CREATE PROCEDURE系統權限，如果要在其他用戶的模式中創建存儲過程，則需要具有CREATE ANY PROCEDURE 系統權限。
創建存儲過程的語法為：
CREATE OR REPLACE PROCEDURE 過程名（參數1，參數2 ...）
AUTHID CURRENT_USER | DEFINER
AS
聲明部分
BEGIN
可執行部分
EXCEPTION
異常處理部分
END;
其中OR REPLACE選項的作用是當同名的存儲過程存在時，首先將其刪除，再創建新的存儲過程。
當然，條件是當前用戶具有刪除原存儲過程的權限。
存儲過程在創建過程中已經進行了編譯和優化。
如果需要對存儲過程進行修改，不能直接修改它的源代碼，只能執行CREATE命令重新創建。
存儲過程、存儲函數、程序包都是這樣的情況。
存儲過程可以帶有參數，這樣在調用存儲過程時就需要指定相應的實際參數。
如果沒有參數，過程名後面的圓括號和參數列表就可以省略了。
每個參數的定義格式為：
參數名 參數傳遞模式 數據類型 := 默認值
參數各定義中各部分的用法與子程序中的參數完全相同。
AUTHID 選項用來規定存儲過程執行時的權限。
這個選項有兩個可選值，即CURRENT_USER和DEFINER ，二者只能選擇其中一個。
過程的執行者和創建者可能不是同一個用戶，如果使用CURRENT_USER創建存儲過程，那麼在調用時，該過程以當前登錄用戶的身份執行。
為此，過程的創建者必須授予當前用戶執行該過程的權限。
如果以DEFINER創建存儲過程，那麼在調用時，該過程將以創建者身份執行，這是創建存儲過程時默認的選項。
授予其他用戶執行存儲過程的權限：
GRANT EXECUTE ON 過程名稱 TO 其他用戶;
用戶在執行其他用戶的過程時，要在過程名前加上模式名。
在存儲過程中可以定義變量、類型、子程序、游標等元素，定義的方法與在匿名塊中完全相同。
這裡不再詳細描述。
存儲過程的聲明部分開始於關鍵宇AS ，結束於關鍵字BEGIN ，而且不需要使用關鍵字DECLARE 。
存儲過程的可執行部分是它的主要部分，它可以包含SQL語句和流控制語句，是存儲過程功能的集中體現。
異常處理部分用來處理存儲過程在執行過程中可能出現的錯誤。
例如，下面的代碼用來創建存儲過程total_income ，它的功能是計算某部門員工的總收入。
這個過程有一個參數，代表部門編號，並指定了默認值。
這樣，在調用時，如果提供了參數，則計算指定部門的數據，否則將計算所有員工的數據。
create or replace
PROCEDURE total_income(d_no IN integer:=0)
AUTHID DEFINER
AS
total number;
BEGIN
if d_no=0 then --表示所有部門
SELECT sum(sal+nvl(comm, 0)) INTO total FROM emp;
else --僅表示指定的部門
SELECT sum(sal+nvl(comm, 0)) INTO total
FROM emp
WHERE deptno=d_no;
END if;
dbms_output.put_line ('總收入：'|| total);
END;
存儲過程創建以後，就可以隨時調用執行了。
在SQL*plus 中調用存儲過程的命令是EXECUTE ，命令的使用格式為：
EXECUTE 過程名（實際參數）
例如，要計算部門10的員工總收入和應繳的稅，則可以以下形式調用剛才創建的存儲過程total income 。
EXECUTE total_income(10)
如果要在一個PL/SQL塊中調用存儲過程，則不需要EXECUTE命令，只要通過過程名和實際參數就可以調用，調用的格式為:
過程名（實際參數）；
每個用戶都可以執行自己創建的存儲過程，如果要執行其他用戶的存儲過程，則需要具有對該存儲過程的EXECUTE權限。
為此，存儲過程的所有者要將EXECUTE權限授予這個用戶。
授予EXECUTE權限的語句格式為：
GRANT EXECUTE ON 過程名 TO 用戶;
例如，存儲過程total_income的所有者要將它的執行權限授予用戶scott，則可以執行下面的SQL語句：
GRANT EXECUTE ON total_income TO scott;
如果要刪除一個存儲過程，可以執行DROP命令，這個命令的格式為：
DROP PROCEDURE 過程名
存儲函數
存儲函數也是一種存儲程序，它被創建後便存儲在數據庫中，用戶可以直接調用。
存儲函數與存儲過程的區別在於，存儲函數必須向調用環境返回一個執行結果。
一般情況我們是把存儲函數作為一個表達式來使用的，它可用於普通表達式能夠使用的場合，這是因為每個函數都有一個返回值，在調用存儲函數時，這個返回值便是存儲函數的執行結果。
例如，可以將存儲函數賦給一個變量，或者將這個函數與另一個表達式進行計算等。
創建存儲函數的語法格式為：
CREATE OR REPLACE FUNCTION 函數名（參數1，參數2 ...）RETURN 返回類型
AUTHID CURRENT_USER | DEFINER
AS
聲明部分
BEGIN
可執行部分
EXCEPTION
異常處理部分
END;
可以看出，創建存儲函數的格式與創建存儲過程的格式大致相同，只有三個不同的地方，第一，用FUNCTION關鍵字代替了PROCEDURE關鍵字，以表明創建的對象是存儲函數，第二，在參數列表之後用RETURN關鍵字規定了存儲函數返回值的類型， 第三，在存儲函數的可執行部分至少有一條RETURN語句，將執行結果返回給調用者。
在存儲函數的可執行部分中，可能會出現多條RETURN語句，用於向調用者返回不同的數據，但是經過邏輯處理後，只能有一條RETURN語句被執行，保證從存儲函數中返回一個確定的數據，這樣就符合了程序的“單出口”的原則。
如果用戶要在自己的模式中創建存儲函數，需要具有CREATE FUNCTION的系統權限，如果要在其他模式中創建存儲函數，則需要具有CREATE ANY FUNCTION的系統權限。
例如，下面的存儲函數用來計算每個員工的總收入。
這個函數有兩個參數，即工資和獎金，它的功能是求出工資和獎金之和，然後將結果返回。
創建這個函數的語句為：
create or replace
FUNCTION total_income_1(sal number,comm number)
RETURN number
AS
result number:=0;
BEGIN
result:=sal+nvl(comm,0);
RETURN result;
END;
如果要利用這個存儲函數求員工的總收入，可以將這個函數用在SELECT語句中，作為SELECT語句的一個表達式，並且向它傳遞實際參數，最後得到它的計算結果。
例如：
SELECT ename,total_income_1(sal,comm) as total FROM emp;
再比如，下面的存儲函數用於計算員工應繳的個人所得稅，這個函數以部門號為參數，計算該部門中全部員工的所得稅總和。
假設稅率為3% ，該函數用SUM 函數計算全體員工的工資總和，然後乘以3% ，並將最後的結果返回。
函數的代碼如下所示：
create or replace
FUNCTION tax_per_depart(dno integer) RETURN number
AS
result number:=0;
BEGIN
SELECT sum(sal)*0.03 INTO result FROM emp
WHERE deptno=dno
GROUP BY deptno;
RETURN result;
END;
與其他存儲函數一樣，這個函數可以用在SELECT語句中，也可以在其他匿名塊、存儲過程、存儲函數中調用執行。
例如，在下面的匿名塊中調用了該函數，計算部門20 的所得稅。
DECLARE
dno integer;
total_tax number;
BEGIN
dno :=20;
total_tax:=tax_per_depart(dno);
dbms_output.put_line('Total tax of department' || dno || 'is：' || total_tax);
END;
每個用戶都可以直接調用自己創建的存儲函數，如果要調用其他用戶的存儲函數，則需要具有對相應存儲函數的EXECUTE權限。
為此，存儲函數的所有者要將EXECUTE權限授予適當的用戶。
授予EXECUTE權限的語句格式為：
GRANT EXECUTE ON 函數名 TO 用戶名;
例如，存儲函數total_income的所有者要將它的執行權限授予用戶scott，則可以執行下面的SQL語句：
GRANT EXECUTE ON total_income TO scott;
如果要刪除一個存儲函數，可以執行DROP命令，這個命令的格式為：
DROP FUNCTION 函數名;
總之，存儲過程和存儲函數都是存儲程序，它們的區別在於存儲過程沒有返回值，只能被單獨調用執行，在功能上類似於一條命令，而存儲函數有返回值，可以用在SELECT語句和運算表達式中，它的作用相當於一個普通的表達式。
在存儲過程和存儲函數中都可以定義子程序，這裡把重點放在了存儲過程和存儲函數本身的使用上，對子程序在存儲過程和存儲函數中的用法沒有進行描述，實際上這也是很簡單的。
程序包
程序包是一種Oracle數據庫對象，它是一組邏輯上相關的數據類型、變量、過程、函數和游標等的集合。
程序包被創建後，存儲在數據庫中，用戶可以直接使用包中的數據類型和變量，也可以直接調用包中的過程和函數。
程序包有兩種形式，一種是用戶根據需要創建的程序包，一種是系統預定義的程序包。
這裡介紹自定義程序包的創建、使用、刪除等操作，以及預定義程序包的使用方法。
用戶可以根據需要創建自己的程序包。
在程序包中可以定義數據類型、變量、過程、函數、異常和游標等元素，這些元素具有全局的特性，可以在程序包中使用，也可以在程序包之外使用。
一個程序包由兩部分組成：程序包的頭部和包體。
其中頭部用來定義類型、變量、異常、聲明游標、過程和函數，它的作用相當於程序包的接口。
在包體中可以利用頭部的類型定義變量，定義過程、游標和函數的代碼。
在創建程序包時，頭部和包體是分別創建的，並且頭部必須在包體之前創建。
程序包創建之後，如果要對其功能進行修改，這時只需修改包體的代碼即可，不用修改頭部，僅當需要改變參數類型、參數個數等信息時，才需要修改程序包的頭部。
創建程序包頭部的命令是CREATE PACKAGE ，這條命令的語法格式為：
CREATE [OR REPLACE] PACKAGE 包名稱
AUTHID CURRENT_USER | DEFINER
AS
類型的定義；
變量的定義；
子程序的聲明；
游標的聲明；
異常的聲明；
END;
其中OR REPLACE選項的作用是當指定的包已經存在時重新創建它。
AUTHID選項用來規定程序包以哪個用戶的身份執行。
這個選項有兩個可選值，即CURRENT_USER和DEFINER,二者只能選擇其中一個。
子程序的聲明就是定義過程和函數的原型，即子程序的名稱、參數和返回值，不包含它的代碼部分。
類型定義部分允許用戶根據需要創建自己的數據類型。
例如，要對部門員工的總收入和所得稅進行統計，為此需要編寫一個程序包。
在程序包中首先定義了一個記錄類型total ，然後聲明了一個函數tax_per_depart ，用來統計某個部門的所得稅，過程total_ per_depart用來統計各個部門的員工總收入。
最後還定義了一個游標c1。
需要注意的是，在程序包的頭部定義游標時需要指定它的返回類型。
以下是創建程序包employee頭部的代碼：
create or replace
PACKAGE employee
AS
type total is record(
dno emp.deptno%type,
total_income number
);
function tax_per_depart(dno integer) RETURN number;
procedure total_per_depart;
cursor c1 RETURN total;
END;
程序包的包體是對頭部的實現，主要用來定義過程和函數的可執行代碼。
創建包體的命令是CREATE PACKAGE BODY ，這條命令的語法格式為：
CREATE [OR REPLACE] PACKAGE BODY 包名
AS
游標的實現；
子程序的實現；
END;
其中包名與創建頭部時使用的名字完全相同。
游標的實現是指定游標中所使用的SELECT語句。
子程序的實現是寫出過程和函數的代碼，過程和函數的編寫方法與以前介紹的方法完全相同。
以下是創建程序包employee的包體的代碼。
CREATE OR REPLACE PACKAGE BODY employee
AS
CURSOR c1 RETURN total is --定義游標
SELECT deptno, sum(sal) FROM emp GROUP BY deptno;
FUNCTION tax_per_depart(dno integer) --定義函數tax_per_depart
RETURN number
AS
result number;
BEGIN
SELECT SUM(sal)*0.03 INTO result FROM emp
WHERE deptno=dno;
RETURN result;
END; --函數tax_per_depart結束
PROCEDURE total_per_depart --定義過程total_per_depart
AS
depart TOTAL;
BEGIN
OPEN c1;
FETCH c1 INTO depart; --利用取出游標中的數據
WHILE c1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('部門'||depart.dno||'總收入'||depart.total_income);
FETCH c1 INTO depart;
END LOOP;
CLOSE c1;
END;
END;
定義了程序包employee後，用戶就可以在PL/SQL塊或者SQL*Plus 中使用這個包中的類型、游標、變量、過程和函數了，使用的方法為：
包名.元素名
例如，要利用程序包employee 中的過程total_per_depart統計各個部門員工的總收入，在SQL*Plus 中調用這個過程：
exec employee.total_per_depart
再比如，在一個匿名塊中調用程序包employee 中的函數tax_per_depart ，計算部門20的所得稅，這個匿名塊的代碼為：
DECLARE
dno integer;
total_tax number;
BEGIN
dno := 20;
total_tax:=employee.tax_per_depart(dno);
dbms_output.put_line('Total tax of department '||dno||' is: '||total_tax );
END;
如果一個程序包不再需要，我們可以將其從數據庫中刪除。
刪除程序包時，可以選擇只刪除包體，或者刪除整個包。
刪除整個程序包的命令是DROP PACKAGE ，它的格式為：
DROP PACKAGE 包名;
這樣，程序包的頭部和包體都將從數據庫中被刪除。
如果只刪除包體，相應的命令為DROP PACKAGE BODY ，它的格式為：
DROP PACKAGE BODY 包名;
系統預定義的程序包
Oracle提供了一些預定義的程序包，利用這些包可以完成一些復雜的操作。
這些程序包提供了一些常用的類型、變量、過程和函數，用戶可以在PL/SQL塊和應用程序中直接使用它們。
正確地使用這些預定義的程序包，可以使開發工作達到事半功倍的效果。
常用的預定義程序包及其用途如下所示：
DBMS_OUTPUT 顯示基本的輸入輸出功能
UTL_FILE 對操作系統文件進行讀，寫等操作
DBMS_SQL 執行DDL語句
DBMS_PIPE 用於在兩個進程間以管道方式進行通信
DBMS_JOB 管理數據庫中的作業
下面將對最常用的程序包DBMS_OUTPUT 、UTL_FILE和DBMS_SQL做簡單的介紹。
1. DBMS_OUTPUT程序包
DBMS_OUTPUT包的功能是將PL/SQL塊的執行結果顯示在屏幕上，這種輸出操作是通過緩沖區來完成的。
SQL*Plus 為存儲程序、PL/SQL塊、觸發器的執行提供了－個緩沖區，用於存放程序執行期間所產生的數據，這個緩沖區以“先進先出”的方式管理其中的數據。
在默認情況下， PL/SQL塊的執行結果是輸出到緩沖區裡的，如果進行一些特殊的設置，緩沖區中的數據就會輸出到屏幕上，然後從緩沖區中清除。
DBMS_OUTPUT包提供了對緩沖區進行設置、讀和寫等操作的功能，它提供了一系列的過程和函數，分別對緩沖區進行設置、讀和寫等操作。
用戶利用DBMS_ OUTPUT包中的過程或函數可以向緩沖區中寫人數據，也可以從緩沖區中讀數據。
緩沖區的設置操作主要包括使其可用和不可用等操作。
使緩沖區不可用的過程是DISABLE,這個過程可以在SQL*Plus 中以如下形式執行：
EXEC dbms_output.disable
如果要在存儲程序、PL/SQL塊和觸發器中調用這個過程，則不需要EXEC命令，可以直接調用執行。
與DISABLE相對的操作是ENABLE過程，它可以使緩沖區可用，並且可以設置緩沖區的大小。
它的調用形式為:
EXEC dbms_output.enable(緩沖區的大小)
如果在調用這個過程時不指定任何參數，則結果是使緩沖區可用，並將其大小設置為默認大小，即20 000字節。
例如，要將緩沖區的大小設置為1024字節，這個過程的調用形式為：
exec dbms_output.enable(1024)
緩沖區的寫操作指的是向緩沖區中寫入數據，目前允許的數據類型有數字型、字符串型和日期型。
寫操作涉及的過程有以下幾個：
•PUT （參數）：將指定的參數寫入緩沖區。
•PUT_LINE （參數）：將指定的參數寫入緩沖區，並在行末寫一個換行符。
•NEW_LINE ：在緩沖區中當前位置處寫一個換行符。
緩沖區中的數據是以行的形式組織的，每行最多存儲255個字符，一行寫滿時，自動從下一行開始繼續寫。
由於緩沖區的大小有限，寫數據的原則是“先進先出”，當緩沖區寫滿時，如果還要繼續寫，那麼最先寫入緩沖區中的數據就會被從緩沖區中清除出去，以便騰出空間容
納新數據。
PUT和PUT_LINE過程的作用都是向緩沖區當前位置處寫入一行數據，它們之間的區別是，PUT_LINE在寫完數據後在當前行的末尾寫入一個換行符，而PUT過程不寫入換行符。
過程NEW_LINE 的作用僅僅是在緩沖區當前位置處寫入一個換行符。
實際上，調用－次過程PUT_LINE ，相當於先調用一次過程PUT ，然後再調用一次過程NEW_LINE 。
如果要使緩沖區中的數據顯示在顯示器上，必須使選項SERVEROUTPUT有效，這個選項的作用就是使緩沖區中的數據可以輸出到屏幕上。
為了使這個選項有效，在SQL*Plus 中執行SET命令：
SET serveroutput ON
這個選項的另一個可選值是OFF ，它的作用正好與ON相反。
為了說明這幾個過程的用法，首先觀察下面這個PL/SQL塊的執行情況：
DECLARE
data1 integer := 100;
data2 varchar2(10) := 'Hello' ;
data3 date DEFAULT sysdate;
BEGIN
dbms_output.put(data1);
dbms_output.put_line(data2);
dbms_output.put_line(data3);
end;
緩沖區的讀操作是指將緩沖區中的數據以行的形式讀出來。
與緩沖區的讀操作有關的過程有兩個：
• GET_LINE ：從緩沖區中讀一行。
• GET_LINES ：從緩沖區中讀多行。
過程GET_LINE的作用是將目前緩沖區中最先寫入的一行數據讀出，並將這一行數據從緩沖區中刪除。
它的調用形式為：
GET_LINE（變量，狀態）
其中變量用於存放從緩沖區中讀出的數據，它的類型必須與要讀的數據一致。
狀態也是一個變量，用來表示本次讀操作是否成功，它的傳遞模式為OUT 。
在這個過程執行結束後，如果狀態變量的值為0 ，表示成功，如果為1 ，則表示緩沖區中沒有數據。
過程GET_LINES 的作用是將目前緩沖區中最先寫入的幾行數據讀出，並將它們從緩沖區中刪除。
它的調用形式為：
GET_LINES(變量，行數)
其中變量是一個集合類型變量，用來存放讀到的幾行數據。
行數也是一個變量，在讀操作之前，這個參數用於指定需要讀的行數，在讀操作之後，這個參數表示實際讀到的數據行數。
下面再通過一個例子說明讀操作和寫操作的綜合應用。
DECLARE
data integer; --表示數據的變量
stat integer; --表示狀態的變量
BEGIN
dbms_output.put(100);
dbms_output.put_line(200);
dbms_output.get_line(data,stat);
dbms_output.put_line ('緩沖區中的數據： ' || data) ;
dbms_output.put_line ('狀態： '|| stat);
END;
