--==========================
-- PL/SQL --> 包重載、初始化
--==========================
?
??? 包的重載功能類似于 C ++ 中函數(shù)的重載功能，即擁有多個同名的子程序，每個同名子程序使用不同的參數(shù)。用戶可以傳遞不同的參數(shù)來調(diào)
用同名但參數(shù)不同的子程序，此即為包的重載功能。簡言之，不管傳遞什么樣的參數(shù)，所完成的任務(wù)是相同的。假定需要查詢部門所在的位置
，輸入?yún)?shù)部門編號或部門名稱都會返回同樣的結(jié)果。對外部程序而言，似乎是調(diào)用的同一個子程序，但其始質(zhì)調(diào)用了不同的子程序，執(zhí)行了
不同的代碼。
??? 有關(guān)包的創(chuàng)建與管理請參考： PL/SQL --> 包的創(chuàng)建與管理
?
一、使用重載特性建立包頭
??? 在包中，具有重載特性的子程序必須使用不同的輸入?yún)?shù)。同名函數(shù)返回值數(shù)據(jù)類型必須完全相同。
??? 以下情況不能實現(xiàn)重載
??????? a . 如果兩個子程序的參數(shù)僅在名稱和類型上不同 , 這兩個程序不能重載。
??????????? PROCEDURE overloadproc ( o_parameter IN NUMBER );
??????????? PROCEDURE overloadproc ( o_parameter OUT NUMBER );
??????????? IN , OUT 為參數(shù)類型 , NUMBER 為數(shù)據(jù)類型 . 兩個過程僅在類型上不同時不能重載。
??????? b . 函數(shù)使用不同的返回類型時不能進(jìn)行重載
??????????? FUNCTION overloadfunc ( f_parameter NUMBER ) RETURN DATE ;
??????????? FUNCTION overloadfunc ( f_parameter VARCHAR2 ) RETURN NUMBER ;
??????? c . 重載子程序的參數(shù)的類族必須不同 , 如由于 NUMBER 和 INTEGER 屬性同一類族 , 所以不能實現(xiàn)重載。
??????????? PROCEDURE overloadproc ( o_parameter ? NUMBER );
??????????? PROCEDURE overloadproc ( o_parameter ? INTEGER );
???????????
??? -- 下面使用重載特性建立包頭，包含了重載函數(shù) 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 ;
???????
二、創(chuàng)建重載特性的包體
??? 對于包中具有重載特性的函數(shù)或過程，需要依次對其創(chuàng)建不同的包體，即使用不同的執(zhí)行代碼。
??? 對前面創(chuàng)建的包頭，我們對其創(chuàng)建如下包體
??? 通過調(diào)用 get_sal 函數(shù)來返回雇員的薪水，可以使用雇員編號或雇員名字作為參數(shù)
??? 通過調(diào)用 fire_employee 來解雇雇員，可以使用雇員編號或雇員名字作為參數(shù)
??????? 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 ;
???????????
三、重載子程序的調(diào)用 ???????
??? 在對使用了重載特性的子程序進(jìn)行調(diào)用時， PL / SQL 會自動根據(jù)所提供的參數(shù)尋找同名且參數(shù)相符的子程序來執(zhí)行其代碼
???
??????? 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
???
四、包的初始化
??? 包的初始化，也稱之為包的構(gòu)造過程。即當(dāng)包被首次使用時，會自動執(zhí)行其構(gòu)造過程，并且該構(gòu)造過程在同一會話內(nèi)僅僅被執(zhí)行一次。
??? 對于包的初始化，其通常的辦法是包體的末尾增加一段匿名 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 過程實現(xiàn)重載
??????????? 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 ; ???
???
??? -- 調(diào)用
??????? 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 ); ? -- 當(dāng)范圍超出最高和最小薪水則返回錯誤信息，且更新失敗
??????? 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 ???
?
五、前置聲明
??? 前置聲明指的是在包體內(nèi)，假定過程 A 調(diào)用了過程 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 之后定義，這樣即為非法調(diào)用
??????????? END ;
???????????
??????????? PROCEDURE cal_rating (...)
??????????? IS
??????????? BEGIN
??????????????? ...
??????????? END ;
??????? END forward_pack ; ??
???????
??? -- 使用前置聲明后的代碼
??????? CREATE OR REPLACE PACKAGE BODY forward_pack IS
??????????? PROCEDURE cal_rating (...) ； -- 在此處增加一行用于聲明過程 cal_rating ，僅僅列出過程名及參數(shù)信息
??????????? PROCEDURE award_bonus (...)
??????????? IS
??????????? BEGIN
??????????????? cal_rating (...); ??
??????????? END ;
???????????
??????????? PROCEDURE cal_rating (...)
??????????? IS
??????????? BEGIN
??????????????? ...
??????????? END ;
??????? END forward_pack ; ??????
?
六、函數(shù)純度級別
??? Oracle 函數(shù)可以在 SQL 語句中調(diào)用，也可以作為表達(dá)式的一部分，基于函數(shù)的一些特殊性，在包中使用 SQL 語句調(diào)用公共函數(shù)時，同樣也存
??? 在一些限制，其限制主要如下：
??????? 公用函數(shù)不能包含 DML 語句
??????? 公用函數(shù)不能讀寫遠(yuǎn)程包變量
??? 對此可以使用純度級別來現(xiàn)在公用函數(shù)的某些操作 ???
??? 定義語法
??????? PRAGMA RESTRICT_REFERENCES ( function_name , WNDS[,WNPS][,RNDS][RNPS] );
???????
??????? WNDS : 限制函數(shù)不能修改數(shù)據(jù)庫 ( 即執(zhí)行 DML 操作 )
??????? WNPS ：限制函數(shù)不能修改包變量，即不能給包變量賦值
??????? RNDS ：限制函數(shù)不能讀取數(shù)據(jù)庫數(shù)據(jù) ( 即禁止 SELECT 操作 )
??????? RNPS ：限制函數(shù)不能讀取包變量，即不能將包變量賦值給其它變量
?
??? -- 下面的代碼創(chuàng)建使用純度即被的包頭 ?????
??????? CREATE OR REPLACE PACKAGE purity IS
??????????? minsal NUMBER ( 6 , 2 ); ?? -- 定義公共變量 minsal
??????????? maxsal NUMBER ( 6 , 2 ); ?? -- 定義公共變量 maxsal
??????????? FUNCTION max_sal RETURN NUMBER ; ????? -- 定義公共函數(shù)
??????????? FUNCTION min_sal RETURN NUMBER ;
??????????? PRAGMA RESTRICT_REFERENCES ( max_sal , WNPS ); ?? -- 指定函數(shù)所使用的純度級別
??????????? PRAGMA RESTRICT_REFERENCES ( min_sal , WNPS );
??????? END ; ???????
???????
??? -- 下面的代碼創(chuàng)建使用純度級別的包體
??????? 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 ; ???
???????
??? -- 創(chuàng)建包體后，收到了如下的錯誤信息，因為兩個公共函數(shù)指定了純度級別為 WNPS ，而且函數(shù)內(nèi)的代碼對變量進(jìn)行了賦值
??????? 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 ; ??? -- 函數(shù)可以讀取包初始化后變量的值
??????????? END ;
?
??????????? FUNCTION min_sal RETURN NUMBER IS
??????????? BEGIN
??????????????? RETURN minsal ; ?? -- 函數(shù)可以讀取包初始化后變量的值
??????????? END ;
??????? BEGIN
??????????? SELECT min ( sal ), max ( sal ) INTO minsal , maxsal FROM emp ; -- 對公共變量進(jìn)行初始化
??????? END ; ???
???
??? -- 下面調(diào)用限定的公用函數(shù)
?
??????? 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 ?
???
七、包內(nèi)游標(biāo)一致性狀態(tài)
??? 可以在包內(nèi)定義一個公共游標(biāo)，該包內(nèi)的所有子程序調(diào)用該游標(biāo)來實現(xiàn)相應(yīng)的功能。如何確保子程序調(diào)用游標(biāo)采取順序一致性性調(diào)用，
??? 而不會出現(xiàn)獲得重復(fù)的游標(biāo)記錄，下面給出的例子中說明了包內(nèi)游標(biāo)一致性狀態(tài)的使用。
??? -- 創(chuàng)建包頭，并且定義了一個公共游標(biāo)，兩個公共過程
??????? 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 ;
??????? /
?
??? -- 創(chuàng)建包體
??????? CREATE OR REPLACE PACKAGE BODY pack_cur
??????? IS
??????????? v_empno emp . empno % TYPE ; ????? -- 定義用于存儲游標(biāo)結(jié)果的變量
??????????? v_ename emp . ename % TYPE ; ????? -- 定義用于存儲游標(biāo)結(jié)果的變量
???????????
??????????? PROCEDURE return1_3rows ? IS
??????????? BEGIN ??
??????????????? OPEN cur ; ??????????????? -- 在第一個過程中打開游標(biāo)
??????????????? 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 ; ??? -- 指定游標(biāo)退出的條件
??????????????? END LOOP ;
??????????? END return1_3rows ;
?
??????????? PROCEDURE return4_6rows IS
??????????? BEGIN
??????????????? DBMS_OUTPUT . PUT_LINE ( 'Empno ???? Ename' );
??????????????? LOOP
??????????????????? FETCH cur INTO v_empno , v_ename ; ?? -- 因為在第一個過程中游標(biāo)已打開，在此可以直接從游標(biāo)提取數(shù)據(jù)
??????????????????? DBMS_OUTPUT . PUT_LINE ( v_empno|| ' ???? ' || v_ename );
??????????????????? EXIT WHEN cur % ROWCOUNT >= 6 ; ????? -- 指定游標(biāo)退出的條件
??????????????? END LOOP ;
??????????????? CLOSE cur ; ??????????????????????????? -- 關(guān)閉游標(biāo)
??????????? END return4_6rows ;
??????? END ;
??????? /
?
??? -- 調(diào)用示例及其結(jié)果 ?
??????? 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
???
八、在包內(nèi)使用自定義類型
??? -- 創(chuàng)建包頭
??????? 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 ;
??????? /
???
??? -- 創(chuàng)建包體
??????? CREATE OR REPLACE PACKAGE BODY cust_type IS
??????????? PROCEDURE read_emp_table ( p_emp_table OUT emp_tb_type ) IS ? -- 定義了輸出參數(shù)的類型為 emp_tb_type
??????????????? i BINARY_INTEGER := 0 ;
??????????? BEGIN
??????????????? FOR emp_record IN ( SELECT * FROM emp ) ?? -- 提取記錄使用 FOR 循環(huán)
??????????????? LOOP
??????????????????? p_emp_table ( i ):= emp_record ; ???????? -- 將提取的記錄存放到 PL/SQL 索引表
??????????????????? i := i + 1 ;
??????????????? END LOOP ;
??????????? END read_emp_table ;
??????? END cust_type ;
??????? /
?
??? -- 下面使用匿名的 PL/SQL 塊來過程來調(diào)用包
?
??????? ? 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
?
九、更多參考
有關(guān) SQL 請參考
??????? SQL 基礎(chǔ)--> 子查詢
??????? SQL 基礎(chǔ)--> 多表查詢
SQL 基礎(chǔ)--> ROLLUP 與CUBE 運算符實現(xiàn)數(shù)據(jù)匯總
SQL 基礎(chǔ)--> 層次化查詢(START BY ... CONNECT BY PRIOR)
?
??? 有關(guān) PL/SQL 請參考
??????? PL/SQL --> 語言基礎(chǔ)
PL/SQL --> 隱式游標(biāo)(SQL%FOUND)
???
?
???????
???
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
