--==========================
-- PL/SQL --> 包重載、初始化
--==========================
?
??? 包的重載功能類似于 C ++ 中函數的重載功能，即擁有多個同名的子程序，每個同名子程序使用不同的參數。用戶可以傳遞不同的參數來調
用同名但參數不同的子程序，此即為包的重載功能。簡言之，不管傳遞什么樣的參數，所完成的任務是相同的。假定需要查詢部門所在的位置
，輸入參數部門編號或部門名稱都會返回同樣的結果。對外部程序而言，似乎是調用的同一個子程序，但其始質調用了不同的子程序，執行了
不同的代碼。
??? 有關包的創建與管理請參考： PL/SQL --> 包的創建與管理
?
一、使用重載特性建立包頭
??? 在包中，具有重載特性的子程序必須使用不同的輸入參數。同名函數返回值數據類型必須完全相同。
??? 以下情況不能實現重載
??????? a . 如果兩個子程序的參數僅在名稱和類型上不同 , 這兩個程序不能重載。
??????????? PROCEDURE overloadproc ( o_parameter IN NUMBER );
??????????? PROCEDURE overloadproc ( o_parameter OUT NUMBER );
??????????? IN , OUT 為參數類型 , NUMBER 為數據類型 . 兩個過程僅在類型上不同時不能重載。
??????? b . 函數使用不同的返回類型時不能進行重載
??????????? FUNCTION overloadfunc ( f_parameter NUMBER ) RETURN DATE ;
??????????? FUNCTION overloadfunc ( f_parameter VARCHAR2 ) RETURN NUMBER ;
??????? c . 重載子程序的參數的類族必須不同 , 如由于 NUMBER 和 INTEGER 屬性同一類族 , 所以不能實現重載。
??????????? PROCEDURE overloadproc ( o_parameter ? NUMBER );
??????????? PROCEDURE overloadproc ( o_parameter ? INTEGER );
???????????
??? -- 下面使用重載特性建立包頭，包含了重載函數 get_sal ，以及重載過程 fire_employee
??????? CREATE OR REPLACE PACKAGE overload IS
??????????? FUNCTION get_sal ( eno NUMBER ) RETURN NUMBER ;
??????????? FUNCTION get_sal ( name VARCHAR2 ) RETURN NUMBER ;
??????????? PROCEDURE fire_employee ( eno NUMBER );
??????????? PROCEDURE fire_employee ( name VARCHAR2 );
??????? END ;
???????
二、創建重載特性的包體
??? 對于包中具有重載特性的函數或過程，需要依次對其創建不同的包體，即使用不同的執行代碼。
??? 對前面創建的包頭，我們對其創建如下包體
??? 通過調用 get_sal 函數來返回雇員的薪水，可以使用雇員編號或雇員名字作為參數
??? 通過調用 fire_employee 來解雇雇員，可以使用雇員編號或雇員名字作為參數
??????? CREATE OR REPLACE PACKAGE BODY overload IS
??????????? FUNCTION get_sal ( eno NUMBER ) RETURN NUMBER IS
??????????????? v_sal emp . sal % TYPE ;
??????????? BEGIN
??????????????? SELECT sal INTO v_sal FROM emp WHERE empno = eno ;
??????????????? RETURN v_sal ;
??????????? EXCEPTION
??????????????? WHEN NO_DATA_FOUND THEN
??????????????????? RAISE_APPLICATION_ERROR (- 20020 , 'The Employee is not exist !' );
??????????? END ;
?
??????????? FUNCTION get_sal ( name VARCHAR2 ) RETURN NUMBER IS
??????????????? v_sal emp . sal % TYPE ;
??????????? BEGIN
??????????????? SELECT sal INTO v_sal FROM emp WHERE upper ( ename ) = upper ( name );
??????????????? RETURN v_sal ;
??????????? EXCEPTION
??????????????? WHEN NO_DATA_FOUND THEN
??????????????????? RAISE_APPLICATION_ERROR (- 20020 , 'The Employee is not exist !' );
??????????? END ;
?
??????????? PROCEDURE fire_employee ( eno NUMBER ) IS
??????????? BEGIN
??????????????? DELETE FROM emp WHERE empno = eno ;
??????????????? IF SQL % NOTFOUND THEN
??????????????????? RAISE_APPLICATION_ERROR (- 20020 , 'The Employee is not exist !' );
??????????????? END IF ;
??????????? END ;
?
??????????? PROCEDURE fire_employee ( name VARCHAR2 ) IS
??????????? BEGIN
??????????????? DELETE FROM emp WHERE UPPER ( ename ) = UPPER ( name );
??????????????? IF SQL % NOTFOUND THEN
??????????????????? RAISE_APPLICATION_ERROR (- 20020 , 'The Employee is not exist !' );
??????????????? END IF ;
??????????? END ;
??????? END ;
???????????
三、重載子程序的調用 ???????
??? 在對使用了重載特性的子程序進行調用時， PL / SQL 會自動根據所提供的參數尋找同名且參數相符的子程序來執行其代碼
???
??????? scott@ORCL > var sal_1 number ;
??????? scott@ORCL > var sal_2 number ;
??????? scott@ORCL > exec : sal_1 := overload . get_sal ( 'king' );
??????? scott@ORCL > exec : sal_2 := overload . get_sal ( 7788 );
??????? scott@ORCL > print sal_1 sal_2 ;
?
??????????? ? SAL_1
??????? ----------
??????????? ? 5800
?
?
??????????? ? SAL_2
??????? ----------
??????????? ? 3900
???
四、包的初始化
??? 包的初始化，也稱之為包的構造過程。即當包被首次使用時，會自動執行其構造過程，并且該構造過程在同一會話內僅僅被執行一次。
??? 對于包的初始化，其通常的辦法是包體的末尾增加一段匿名 SQL 代碼。如下
??????? CREATE OR REPLACE PACKAGE BODY package_name
??????? IS
??????????? PROCEDURE procedure_name
??????????????? ····
??????????? FUNCTION function_name
??????????????? ····
???????????????
??????? BEGIN
??????????? Initialization_code ; -- 要運行的初始化代碼
??????? END
???????????
??? -- 下面首先聲明包頭
???
??????? CREATE OR REPLACE PACKAGE emp_package IS
??????????? minsal NUMBER ( 6 , 2 ); ?? -- 定義公共變量 minsal ，用于存放雇員最低薪水
??????????? maxsal NUMBER ( 6 , 2 ); ?? -- 定義公共變量 maxsal ，用于存放雇員最高薪水
??????????? PROCEDURE add_employee ( eno NUMBER , name VARCHAR2 , salary NUMBER , dno NUMBER );
??????????? PROCEDURE upd_sal ( eno NUMBER , salary NUMBER ); ?? -- 對 upd_sal 過程實現重載
??????????? PROCEDURE upd_sal ( name VARCHAR2 , salary NUMBER );
??????? END ;
???
??? -- 下面定義包體
??????? CREATE OR REPLACE PACKAGE BODY emp_package IS
??????????? PROCEDURE add_employee ( eno NUMBER , name VARCHAR2 , salary NUMBER , dno NUMBER ) IS
??????????? BEGIN
??????????????? IF salary BETWEEN minsal AND maxsal THEN
??????????? ??????? INSERT INTO emp ( empno , ename , sal , deptno ) VALUES ( eno , name , salary , dno );
??????????????? ELSE
??????????????????? RAISE_APPLICATION_ERROR (- 20001 , 'The salary is over specified range.' );
??????????????? END IF ;
??????????? EXCEPTION
??????????????? WHEN DUP_VAL_ON_INDEX THEN
??????????????????? RAISE_APPLICATION_ERROR (- 20002 , 'The employee is exists.' );
??????????? END ;
?
??????????? PROCEDURE upd_sal ( eno NUMBER , salary NUMBER ) IS
??????????? BEGIN
??????????????? IF salary BETWEEN minsal AND maxsal THEN
??????????????????? UPDATE emp SET sal = salary WHERE empno = eno ;
??????????????????? IF SQL % NOTFOUND THEN
??????????????????????? RAISE_APPLICATION_ERROR (- 20003 , 'The employee is not exists.' );
??????????????????? END IF ;
??????????????? ELSE
??????????????????? RAISE_APPLICATION_ERROR (- 20001 , 'The salary is over specified range.' );
??????????????? END IF ;
??????????? END ;
?
??????????? PROCEDURE upd_sal ( name VARCHAR2 , salary NUMBER ) IS
??????????? BEGIN
??????????????? IF salary BETWEEN minsal AND maxsal THEN
??????????????????? UPDATE emp SET sal = salary WHERE UPPER ( ename ) = UPPER ( name );
??????????????????? IF SQL % NOTFOUND THEN
??????????????????????? RAISE_APPLICATION_ERROR (- 20004 , 'The employee is not exists.' );
??????????????????? END IF ;
??????????????? ELSE
??????????????????? RAISE_APPLICATION_ERROR (- 20001 , 'The salary is over specified range.' );
??????????????? END IF ;
??????????? END ;
?
??????? BEGIN
??????????? SELECT min ( sal ), max ( sal ) INTO minsal , maxsal FROM emp ; ? -- 初始化公共變量 minsal, maxsal
??????? END ; ???
???
??? -- 調用
??????? scott@ORCL > exec emp_package . add_employee ( 1234 , 'Henry' , 3500 , 20 );
?
??????? scott@ORCL > exec emp_package . upd_sal ( 'Henry' , 3500 );
?
??????? scott@ORCL > exec emp_package . upd_sal ( 'Henry' , 100 ); ? -- 當范圍超出最高和最小薪水則返回錯誤信息，且更新失敗
??????? BEGIN emp_package . upd_sal ( 'Henry' , 100 ); END ;
?
??????? *
??????? ERROR at line 1 :
??????? ORA - 20001 : The salary is over specified range .
??????? ORA - 06512 : at "SCOTT.EMP_PACKAGE" , line 34
??????? ORA - 06512 : at line 1 ???
?
五、前置聲明
??? 前置聲明指的是在包體內，假定過程 A 調用了過程 B ，而 B 在 A 之后定義，這樣的話，將會收到錯誤信息。對此，我們可以不改變過程 A ， B 的
書寫順序及其代碼，而將 B 事先聲明，此之為前置聲明。如下面的例子：
??? -- 未使用前置聲明時的代碼
??????? CREATE OR REPLACE PACKAGE BODY forward_pack IS
??????????? PROCEDURE award_bonus (...)
??????????? IS
??????????? BEGIN
??????????????? cal_rating (...); ?? -- 在此例中過程 cal_rating 在過程 award_bonus 之后定義，這樣即為非法調用
??????????? END ;
???????????
??????????? PROCEDURE cal_rating (...)
??????????? IS
??????????? BEGIN
??????????????? ...
??????????? END ;
??????? END forward_pack ; ??
???????
??? -- 使用前置聲明后的代碼
??????? CREATE OR REPLACE PACKAGE BODY forward_pack IS
??????????? PROCEDURE cal_rating (...) ； -- 在此處增加一行用于聲明過程 cal_rating ，僅僅列出過程名及參數信息
??????????? PROCEDURE award_bonus (...)
??????????? IS
??????????? BEGIN
??????????????? cal_rating (...); ??
??????????? END ;
???????????
??????????? PROCEDURE cal_rating (...)
??????????? IS
??????????? BEGIN
??????????????? ...
??????????? END ;
??????? END forward_pack ; ??????
?
六、函數純度級別
??? Oracle 函數可以在 SQL 語句中調用，也可以作為表達式的一部分，基于函數的一些特殊性，在包中使用 SQL 語句調用公共函數時，同樣也存
??? 在一些限制，其限制主要如下：
??????? 公用函數不能包含 DML 語句
??????? 公用函數不能讀寫遠程包變量
??? 對此可以使用純度級別來現在公用函數的某些操作 ???
??? 定義語法
??????? PRAGMA RESTRICT_REFERENCES ( function_name , WNDS[,WNPS][,RNDS][RNPS] );
???????
??????? WNDS : 限制函數不能修改數據庫 ( 即執行 DML 操作 )
??????? WNPS ：限制函數不能修改包變量，即不能給包變量賦值
??????? RNDS ：限制函數不能讀取數據庫數據 ( 即禁止 SELECT 操作 )
??????? RNPS ：限制函數不能讀取包變量，即不能將包變量賦值給其它變量
?
??? -- 下面的代碼創建使用純度即被的包頭 ?????
??????? CREATE OR REPLACE PACKAGE purity IS
??????????? minsal NUMBER ( 6 , 2 ); ?? -- 定義公共變量 minsal
??????????? maxsal NUMBER ( 6 , 2 ); ?? -- 定義公共變量 maxsal
??????????? FUNCTION max_sal RETURN NUMBER ; ????? -- 定義公共函數
??????????? FUNCTION min_sal RETURN NUMBER ;
??????????? PRAGMA RESTRICT_REFERENCES ( max_sal , WNPS ); ?? -- 指定函數所使用的純度級別
??????????? PRAGMA RESTRICT_REFERENCES ( min_sal , WNPS );
??????? END ; ???????
???????
??? -- 下面的代碼創建使用純度級別的包體
??????? CREATE OR REPLACE PACKAGE BODY purity IS
??????????? FUNCTION max_sal RETURN NUMBER IS
??????????? BEGIN
??????????????? SELECT max ( sal ) INTO maxsal FROM emp ;
??????????????? RETURN maxsal ;
??????????? END ;
?
??????????? FUNCTION min_sal RETURN NUMBER IS
??????????? BEGIN
??????????????? SELECT min ( sal ) INTO minsal FROM emp ;
??????????????? RETURN minsal ;
??????????? END ;
??????? END ; ???
???????
??? -- 創建包體后，收到了如下的錯誤信息，因為兩個公共函數指定了純度級別為 WNPS ，而且函數內的代碼對變量進行了賦值
??????? scott@ORCL > show errors package body purity ; ?
??????? Errors for PACKAGE BODY PURITY :
?
??????? LINE / COL ERROR
??????? -------- -----------------------------------------------------------------
??????? 2 / 1 ????? PLS - 00452 : Subprogram 'MAX_SAL' violates its associated pragma
??????? 8 / 1 ????? PLS - 00452 : Subprogram 'MIN_SAL' violates its associated pragma
???????
??? -- 下面使用初始化包的方法來為變量賦值
?
??????? CREATE OR REPLACE PACKAGE BODY purity IS
??????????? FUNCTION max_sal RETURN NUMBER IS
??????????? BEGIN
??????????????? RETURN maxsal ; ??? -- 函數可以讀取包初始化后變量的值
??????????? END ;
?
??????????? FUNCTION min_sal RETURN NUMBER IS
??????????? BEGIN
??????????????? RETURN minsal ; ?? -- 函數可以讀取包初始化后變量的值
??????????? END ;
??????? BEGIN
??????????? SELECT min ( sal ), max ( sal ) INTO minsal , maxsal FROM emp ; -- 對公共變量進行初始化
??????? END ; ???
???
??? -- 下面調用限定的公用函數
?
??????? scott@ORCL > var minsal number ;
??????? scott@ORCL > var maxsal number ;
??????? scott@ORCL > exec : minsal := purity . minsal ;
??????? scott@ORCL > exec : maxsal := purity . maxsal ;
??????? scott@ORCL > print minsal maxsal ;
?
??????????? MINSAL
??????? ----------
??????????? ?? 800
?
??????????? MAXSAL
??????? ----------
??????????? ? 5800 ?
???
七、包內游標一致性狀態
??? 可以在包內定義一個公共游標，該包內的所有子程序調用該游標來實現相應的功能。如何確保子程序調用游標采取順序一致性性調用，
??? 而不會出現獲得重復的游標記錄，下面給出的例子中說明了包內游標一致性狀態的使用。
??? -- 創建包頭，并且定義了一個公共游標，兩個公共過程
??????? CREATE OR REPLACE PACKAGE pack_cur
??????? IS
??????????? CURSOR cur IS
??????????????? SELECT empno , ename FROM emp ORDER BY empno ;
??????????? PROCEDURE return1_3rows ;
??????????? PROCEDURE return4_6rows ;
??????? END pack_cur ;
??????? /
?
??? -- 創建包體
??????? CREATE OR REPLACE PACKAGE BODY pack_cur
??????? IS
??????????? v_empno emp . empno % TYPE ; ????? -- 定義用于存儲游標結果的變量
??????????? v_ename emp . ename % TYPE ; ????? -- 定義用于存儲游標結果的變量
???????????
??????????? PROCEDURE return1_3rows ? IS
??????????? BEGIN ??
??????????????? OPEN cur ; ??????????????? -- 在第一個過程中打開游標
??????????????? DBMS_OUTPUT . PUT_LINE ( 'Empno ???? Ename' );
??????????????? LOOP
??????????????????? FETCH cur INTO v_empno , v_ename ;
??????????????????? DBMS_OUTPUT . PUT_LINE ( v_empno|| ' ???? ' || v_ename );
??????????????????? EXIT WHEN cur % ROWCOUNT >= 3 ; ??? -- 指定游標退出的條件
??????????????? END LOOP ;
??????????? END return1_3rows ;
?
??????????? PROCEDURE return4_6rows IS
??????????? BEGIN
??????????????? DBMS_OUTPUT . PUT_LINE ( 'Empno ???? Ename' );
??????????????? LOOP
??????????????????? FETCH cur INTO v_empno , v_ename ; ?? -- 因為在第一個過程中游標已打開，在此可以直接從游標提取數據
??????????????????? DBMS_OUTPUT . PUT_LINE ( v_empno|| ' ???? ' || v_ename );
??????????????????? EXIT WHEN cur % ROWCOUNT >= 6 ; ????? -- 指定游標退出的條件
??????????????? END LOOP ;
??????????????? CLOSE cur ; ??????????????????????????? -- 關閉游標
??????????? END return4_6rows ;
??????? END ;
??????? /
?
??? -- 調用示例及其結果 ?
??????? scott@ORCL > set serveroutput on ;
??????? scott@ORCL > exec pack_cur . return1_3rows ;
??????? Empno ???? Ename
??????? 1234 ???? Henry
??????? 3333 ???? Jackson
??????? 4444 ???? Richard
???????
??????? scott@ORCL > exec pack_cur . return4_6rows ;
??????? Empno ???? Ename
??????? 7369 ???? SMITH
??????? 7499 ???? ALLEN
??????? 7521 ???? WARD
???
八、在包內使用自定義類型
??? -- 創建包頭
??????? CREATE OR REPLACE PACKAGE cust_type IS
??????????? TYPE emp_tb_type IS TABLE OF emp % ROWTYPE ??? -- 定義一個 PL/SQL 索引表
??????????????? INDEX BY BINARY_INTEGER ;
??????????? PROCEDURE read_emp_table ( p_emp_table OUT emp_tb_type ); ? -- 定義一個過程
??????? END cust_type ;
??????? /
???
??? -- 創建包體
??????? CREATE OR REPLACE PACKAGE BODY cust_type IS
??????????? PROCEDURE read_emp_table ( p_emp_table OUT emp_tb_type ) IS ? -- 定義了輸出參數的類型為 emp_tb_type
??????????????? i BINARY_INTEGER := 0 ;
??????????? BEGIN
??????????????? FOR emp_record IN ( SELECT * FROM emp ) ?? -- 提取記錄使用 FOR 循環
??????????????? LOOP
??????????????????? p_emp_table ( i ):= emp_record ; ???????? -- 將提取的記錄存放到 PL/SQL 索引表
??????????????????? i := i + 1 ;
??????????????? END LOOP ;
??????????? END read_emp_table ;
??????? END cust_type ;
??????? /
?
??? -- 下面使用匿名的 PL/SQL 塊來過程來調用包
?
??????? ? DECLARE
??????????? v_emp_table cust_type . emp_tb_type ;
??????? ? BEGIN
??????????? cust_type . read_emp_table ( v_emp_table );
??????????? DBMS_OUTPUT . PUT_LINE ( 'An example: ' || v_emp_table ( 3 ). ename );
??????? ? END ;
?
??????? An example : WARD
?
九、更多參考
有關 SQL 請參考
??????? SQL 基礎--> 子查詢
??????? SQL 基礎--> 多表查詢
SQL 基礎--> ROLLUP 與CUBE 運算符實現數據匯總
SQL 基礎--> 層次化查詢(START BY ... CONNECT BY PRIOR)
?
??? 有關 PL/SQL 請參考
??????? PL/SQL --> 語言基礎
???
?
???????
???
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
