利用PL/SQL可以進行模塊化程式設計。 在一個PL/SQL塊中，可以定義若幹個子程式。 把一些功能相對獨立、需要經常執行的代碼定義為一個子程式，在需要時根據子程式的名字進行調用。這樣不僅便於程式設計和編碼，而且利於程式的調試。PL/SQL有兩種形式的子程式，即過程和函數。 在子程式中也可以定義變數 ...
利用PL/SQL可以進行模塊化程式設計。
在一個PL/SQL塊中，可以定義若幹個子程式。
把一些功能相對獨立、需要經常執行的代碼定義為一個子程式，在需要時根據子程式的名字進行調用。
這樣不僅便於程式設計和編碼，而且利於程式的調試。
PL/SQL有兩種形式的子程式，即過程和函數。
在子程式中也可以定義變數、類型、游標等，也可以進行異常處理。
在調用於程式時，可以向子程式傳遞參數。
過程與函數的區別在於函數具有返回值，可以向調用者返回執行結果，而過程沒有返回值。
如何使用過程
子程式的定義出現在PL/SQL塊的聲明部分，而它的調用則出現在PL/SQL塊的可執行部分。
過程的定義格式如下：
PROCEDURE 過程名稱 （參數1 定義， 參數2 定義 ...）
AS 或 IS
變數聲明部分
BEGIN
可執行部分
EXCEPTION
異常處理部分
END;
在過程中可以定義參數，在調用該過程時，可以向過程傳遞實際參數。
如果沒有參數，則過程名後面的圓括弧及參數列表可以省略。
參數的定義形式為:
參數名 參數傳遞模式 數據類型 := 預設值
其中參數名和數據類型是必不可少的部分，其他兩部分是可以省略的。
參數傳遞模式包括IN 、OUT以及IN OUT三種形式，其中IN是預設的傳遞模式，如果沒有指定，則預設為IN ，它指的是從調用者向過程中傳遞一個實際參數。
OUT是指從過程中向調用者傳遞參數，如果要使用這種傳遞模式，則需要明確指定。
在調用過程時，過程的執行情況會影響這個變數的值。
“ IN OUT”是一種雙向傳遞模式，一方面從調用者向過程傳遞參數，另一方面從過程向調用者傳遞結果，如果要使用這種形式，則需要明確指定。
IN OUT IN OUT
形式參數的作用 一個常量 沒有初始化的變數 經過初始化的變數
實際參數的形式 常量、表達式、變數 必須是一個變數 必須是一個變數
參數預設值的作用是在調用過程時，如果沒有提供實際參數，則將此預設值作為實際參數傳遞給過程。
數據類型用來指定參數的類型，在參數定義中不能指定對參數的約束條件，即不能指定參數的長度和是否為空等屬性。
在下麵的PL/SQL塊中定義了兩個過程，其中prompt過程用於對某部門的員工增加工資和獎金，而total_income過程用於計算某部門員工的總收入和應繳的稅。
DECLARE
dno number;
procedure promption(salary IN integer, commiss IN integer, d_no IN integer:=0)
IS
BEGIN
if d_no=0 then --表示所有部門
UPDATE emp
set sal=sal+salary,comm=comm+commiss;
else --僅表示指定的部門
UPDATE emp
set sal=sal+salary,comm=comm+commiss
WHERE deptno=d_no;
END if;
END;
procedure total_income(d_no IN integer:=0)
IS
empno integer;
total number;
tax number;
BEGIN
if d_no=0 then --表示所有部門
SELECT sum(sal+nvl(comm, 0)), sum(sal*0.03) INTO total,tax FROM emp;
else --僅表示指定的部門
SELECT sum(sal+nvl(comm, 0)), sum(sal*0.03) INTO total,tax
FROM emp
WHERE deptno=d_no;
END if;
dbms_output.put_line ('總收入：' || total || '總稅款： ' || tax) ;
END;
BEGIN --PL/SQL塊的可執行部分
dno:=10;
promption(100,0,dno);
total_income(dno);
END;
在過程promption 中定義了三個參數，其中參數d_no帶有預設值，這樣在調用該過程時，如果沒有為該參數提供實際參數，則使用預設值。
例如，在上述例子中，為部門10 中的員工增加了100元工資。
如果採用下麵的調用形式，則為所有部門的員工增加100元工資：
promption(100, 0);
在調用過程時，需要為過程中的參數提供實際參數，它們在順序上是對應的。
為了保證將參數正確地傳給過程，要求在定義過程時，將所有帶預設值的參數集中放在參數列表的右邊。
因為只有這樣才能將其他實際參數一對一地賦給前面的不帶預設值的參數。
如果過程有多個參數，在調用過程時，也可以不按照參數列表的順序提供實際參數，這時需要兩種參數之間的對應關係。
例如，過程promption可以採用以下調用形式：
promption(d_no=>dno,commiss=>0, salary=>100);
過程total_inco1ne 中的參數d_no也帶有預設值，這樣在調用該過程時，如果提供了實際參數，則計算指定部門的員工總收入和總稅款，如果採用以下調用形式：
total_income ( ) ;
則計算所有部門的員工總收入和總稅款。
在上述兩個過程中，所有參數的傳遞模式都是IN ，即把實際參數從調用者傳遞給過程。
這種形式是預設的，可以省略的關鍵字。
如果需要把過程的執行情況反映給調用者，則需要使用OUT形式，或者“ IN OUT”形式。
例如，為了計算員工總收入和總稅款，並把結果反映到主程式中，對過程total_ income進行了一些改造，增加了兩個參數，傳遞模式都是OUT 。
改造後的PL/SQL塊代碼如下：
DECLARE
dno number;
abed number;
xyz number;
procedure total_income(d_no IN integer,total OUT number,tax OUT number)
AS
empno integer;
BEGIN
if d_no=0 then --表示所有部門
SELECT sum(sal+nvl(comm,0)), sum(sal*0.03) INTO total,tax FROM emp;
else --僅表示指定的部門
SELECT sum(sal+nvl(comm,0)), sum(sal*0.03) INTO total,tax
FROM emp
WHERE deptno=d_no;
END if;
END;
BEGIN
dno:=10;
--PL/SQL塊的可執行部分
total_income(dno,abed,xyz);
dbms_output.put_line ('總收入： ' || abed || ' 總稅款： ' || xyz ) ;
END;
在調用過程total_income時，提供了三個實際參數，其中參數abed和xyz沒有實際的值，即使有，在這裡也不起任何作用，因為這兩個參數的傳遞模式是OUT過程在執行時，將參數total和tax的值傳分別賦給abed和xyz ，這樣就將過程中的數據傳給了調用者。
註：IN類型參數是不能作為變數賦值的。
如何使用函數
函數是另一種形式的子程式，它不僅可以像過程那樣定義數據和類型，傳遞參數，還可以向調用者返回執行結果。
函數的定義語法格式如下：
FUNCTION 函數名 （參數1， 參數2 ...）RETURN 數據類型
IS
聲明部分
BEGIN
可執行部分
EXCEPTION
異常處理部分
END;
其中參數的定義、傳遞模式都與在過程中的情況是相同的。
例如，下麵這個函數的功能是根據參數n的值，計算1+2+3 ＋…＋n的值，最後將結果返回。
FUNCTION total(n IN integer) RETURN integer
IS
result integer:=0;
i integer;
BEGIN
FOR i in 1 .. n LOOP
result:=result+i;
END LOOP;
RETURN result;
END;
從函數中應當向調用者傳遞一個返回值。
在函數參數列表之後的RETURN語句規定了函數的返回值類型，它可以是簡單類型，也可以是記錄、集合等複雜類型。
在函數的可執行部分應當至少包含一個RETURN語句，用於向調用者返回執行結果。
任何一條RETURN語句的執行都將導致函數的執行結束，並返回調用者。
除了具有返回值外，函數在其他方面的用法與過程基本相同。
在調用函數時，可以將函數的返回值賦給一個變數，變數的類型必須與函數的返回值相同。
例如，考慮下麵的PL/SQL塊的代碼：
DECLARE
total_income number;
total_tax number;
--PL/SQL塊中的變數
function income(d_no emp.deptno%type) RETURN number --函數income
is
total number;
BEGIN
SELECT sum(sal+nvl(comm, 0)) INTO total
FROM emp
WHERE deptno=10;
RETURN total;
END;
function tax(d_no emp.deptno%type) RETURN number --函數tax
is
total number;
BEGIN --函數income的可執行部分
SELECT sum(sal+nvl(comm, 0))* 0.03 INTO total
FROM emp
WHERE deptno=10;
RETURN total;
END;
BEGIN --PL/SQL塊的可執行部分
total_income:=income(10);
dbms_output.put_line ('總收入為2 ' || total_income) ;
total_tax:=tax( 10);
dbms_output.put_line ('總共應交稅：' || total_tax);
END;
在這個PL/SQL塊中定義了兩個函數income和tax ，分別用於計算指定部門的員工的總收入和應當交納的所得稅。
兩個函數都使用了一個參數，代表需要處理的部門號，由於沒有指定參數的傳遞模式，故採用預設的IN方式。
在兩個函數中經過計算後返回計算結果。
在PL/SQL塊的可執行部分，分別調用了這兩個函數，並將函數的返回值賦給了變數total_income和total_ tax ，然後進行列印。
在PL/SQL塊的主程式和函數中都可以定義變數，主程式的變數定義在該塊的聲明部分，變數的作用範圍是整個塊，包括各個函數。
函數的變數定義在函數的聲明部分，其作用範圍僅限於該函數。
如果PL/SQL塊的變數與函數的變數同名，那麼主程式的變數在該函數中不起作用。
這個規則也適用於過程。
為了說明PL/SQL塊的變數和函數變數的關係，我們再看看下麵的PL/SQL塊：
DECLARE
total number:=100;
procedure fun1
is
total number:=0;
BEGIN
dbms_output.put_line ('在函數fun1中total 的值為：' || total) ;
END;
procedure fun2
IS
BEGIN
dbms_output.put_line ( '在函數fun2 中total 的值為：' || total) ;
END;
BEGIN
total:=total+100;
fun1;
fun2;
dbms_output.put_line ('在塊中total 的值為：' || total) ;
END;
在塊的聲明部分定義了一個變數total ，並賦初值為100 。
在函數fun1 中也定義了一個同名的變數，並賦初值為0 ，在該函數中起作用是自己的變數，所以調用函數fun1時變數total的值為0。
在函數fun2 中沒有定義同名的變數，該函數中的變數total就是主程式中的變數，在調用該函數之前已經使這個變數增加了100 ，所以調用函數fun2時變數total的值為200 。
在主程式中當然使用的是自己的變數，而不可能是函數中的變數。
函數與過程的重載
函數與過程是一段具有特定功能的程式段，在PL/SQL塊中根據它們的名字進行調用。
函數和過程的名字原則上可以由用戶任意指定，只要滿足命名規則即可，但是最好能夠體現這段代碼的功能。
在調用函數或過程時，根據它們的定義可能需要提供實際參數。
如果實際參數的類型或數目與形式參數不一致，子程式將出現錯誤信息，並停止執行。
如果需要對不同類型或不同數目的參數進行相似的處理，我們可以定義多個名字相同的函數，這就是子程式的重載。
重載子程式的名字相同，但是參數的類型或數目不同，返回值也可能不同。
如果完全相同，就不是重載了，而是重覆定義，這是不允許的。
在調用重載子程式時，主程式將根據實際參數的類型和數目，自動確定調用哪個子程式。
例如，要對整數和浮點數分別求整數次幕，可以編寫兩個重載子程式，參數的類型分別是整數和浮點數。
這樣在調用時，如果提供的實際參數是浮點數，則自動調用對浮點數求幕的子程式。
如果提供的實際參數是整數，則自動調用對整數求幕的子程式。
例如，在下麵的代碼中，定義了兩個重載函數，分別對整數8和浮點數8.8求10次幕。
DECLARE
m integer;
int_number integer;
int_result integer;
float_number float;
float_result float;
function power(x integer,n integer) RETURN integer --對整數x求n次冪
is
result integer:=1;
BEGIN
for i in 1 .. n loop
result:=result*x;
END loop;
RETURN result;
END;
function power(x float,n integer) RETURN float --對浮點數x求n次冪
is
result float:=1;
BEGIN
for i in 1 .. n loop
result:=result*x;
END loop;
RETURN result;
END;
BEGIN
m:=10;
int_number:=8;
int_result:=power(int_number,m);
dbms_output.put_line ('對整數求冪的結果為:'|| int_result); --調用第一個power 函數
float_number:=0.8;
float_result:=power ( float_number, m) ; --調用第二個power 函數
dbms_output.put_line ('對浮點數求冪的結果為： '|| float_result);
END;
註：根據實際參數的類型，可以自動確定調用那個子程式，這個例子不好，float和integer類型相近，沒法區分。
上面的PL/SQL塊提供了兩個重載函數，在調用時根據參數的類型不同自動確定調用哪個函數。
下麵再看一個例子，根據參數的數目不同自動確定調用哪個函數。
DECLARE
procedure increase_salary(d_no emp.deptno%type, amount float)
IS
BEGIN
UPDATE emp set sal=sal+amount WHERE deptno=d_no;
END;
procedure increase_salary(amount float)
IS
BEGIN
UPDATE emp set sal=sal+amount;
END;
BEGIN
increase_salary(10,100.50); --調用第一個increase_salary過程
increase_salary(200); --調用第二個increase_salary過程
END;
在這個例子中定義了兩個重載過程，用於為員工增加工資。
第一個過程有兩個參數，分別是部門號和增加的額度。
第二個過程只布一個參數，即增加的額度。
在調用過程時，如果提供了部門號和增加額度兩個實際參數，則調用第一個increase salary過程，為指定部門的員工增加工資。
如果只提供了增加額度這一個參數， 則調用第二個increase_salary過程，為所有員工增加工資。
函數和過程的遞歸調用
子程式定義好以後，需要在主程式或其他子程式中調用後才能執行，執行完後返回到調用者。
在有些情況下，子程式在執行過程中還可能要調用自己，調用結束後返回當前調用的地方。
子程式自己調用自己的現象稱為遞歸調用。
考慮求整數n的階乘的情況。
n ！的值為n*（ n- 1 ) ! ，為了求n的階乘，首先要求出（ n -1 ) ! 。
同樣，要計算（ n-1 ) ！，首先要計算（ n-2) ！的值，一直到1 的階乘，而 1的階乘的值是已知的。
如果編寫一個函數fact ，這個函數可以求得任何整數的階乘，那麼這個函數就是一個遞歸函數。
下麵是求階乘的遞歸過程：
fact(n)=n*fact( n-1)
=n*(n-1)*fact｛n－ 2)
...
= n*(n-1) * (n-2)* ... *fact(1)
在調用函數fact求n的階乘時，首先要求n-1的階乘，這時需要調用函數自己，不過這次傳遞的參數是n-1 。
同樣，求n-l 的階乘時，需要再次調用函數本身，求得n-2的階乘，這次傳遞的參數是n-2 。
依此類推，最後要調用fact 函數求1的階乘，而1的階乘是已知的，這是遞歸返回的條件。
求得1 的階乘後，便可返回到調用fact ( 1 ）的地方，求得2的階乘。
求得2的階乘後再返回到調用fact ( 2 ）的地方，求得3的階乘。
這樣每返回一次，就可求得上一個數的階乘，直到求得n的階乘。
下麵是一個求整數m的階乘的PL/SQL塊。
DECLARE
m integer;
result integer;
function fact(n integer)
RETURN integer
is
BEGIN
if n=1 then
RETURN 1;
else
RETURN n*fact(n-1); --遞歸調用
END if;
END;
BEGIN
m:=10;
result:=fact(m); --調用函數，求整數10 的階乘
dbms_output.put_line(m|| '的階乘為：' || result);
END;
再來看一個遞歸調用的例子。
表emp中存放的是公司員工的信息，其中包括員工號、員工姓名以及經理編號等信息。
以下是表emp中這三個列的數據：
SQL> SELECT empno, ename, mgr FROM emp;
EMPNO ENAME MGR
----- ---------- -----
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
從查詢的結果可以看出，除員工KING外，其他人都有一個經理，而經理同時也是一個員工，其中KING是公司的最高領導。
如果指定任何一個員工號，希望得到這個員工的經理，以及這個經理的經理，一直到最高領導這樣的垂直、直接領導關係。
藉助於子程式的遞歸調用，可以完成這樣的要求。
以下是用過程遞歸的方法編寫的一個PL/SQL塊：
DECLARE
procedure manager(employee_no emp.empno%type)
IS
name emp.ename%type; --員工姓名
manager_no emp.empno%type; --員工經理的編號
manager_name emp.ename%type; --員工經理的姓名
BEGIN
SELECT ename,mgr INTO name,manager_no
FROM emp WHERE empno=employee_no;
if manager_no is not null then --如果員工的經理編號不為空，則查詢其姓名
SELECT ename INTO manager_name FROM emp WHERE empno=manager_no;
dbms_output.put_line(name || '->' || manager_name);
manager(manager_no); --遞歸調用，查詢該經理的經理
else --如果員工的經理編號為空，說明該員工即為最高領導
dbms_output.put_line(name|| '是最高層領導');
END if;
END;
BEGIN
manager(7369);
EXCEPTION
when NO_DATA_FOUND then
dbms_output.put_line ('沒有這樣的員工');
END;
KING是最高層領導
過程manager以人一個員工號為參數，首先查詢該員工的經理編號。
如果經理編號為空，則說明該員工為公司的最高領導，這時列印相應的信息，並結束過程的執行。
否則查詢該經理的姓名，並列印他們之間的領導關係，然後遞歸調用過程本身，以該經理的編號作為參數，繼續查詢他的經理的信息。
從程式的運行結果可以看出，編號為7369 的員工姓名為SMITH ，他的經理為FORD ，而
FORD的經理是JONES ，這樣可以－直向上追溯到最高領導KINGO 。
為了防止在程式中指定一個不存在的員工號而導致程式執行出錯，在程式中增加了異常處理，如果沒有查詢到任何信息，則列印相應的出錯信息。
