--============================
-- PL/SQL --> 包的創建與管理
--============================
?
??? 包，是一個邏輯集合，是由 PL / SQL 類型以及 PL / SQL 子程序的集合。 PL / SQL 類型包括 table 類型， record 類型。 PL / SQL 項則包括游標，游標
變量。 PL / SQL 子程序包括過程，函數等。可以說包可謂是包羅萬象。是所有 PL / SQL 相關資源的匯總。
??? 包的使用可以簡化應用程序設計，實現信息掩藏，子程序重載等功能。
??? 包的優點
??????? 1. 模塊化：將函數，子程序全部融合在一起，使得成為一個有機的整體，封裝了相關的結構。
??????? 2. 易于維護：整合了子程序，更易于維護 。
??????? 3. 簡化應用程序設計：包的聲明與包體內容相分離。
??????? 4. 隱藏信息：私有對象不可訪問，所有的包體內代碼可以實現隱藏。
??????? 5. 節省 I / O ：一次編譯，多次使用。
???
一、包的組成與創建語法
??? 包頭：用于定義包的公共組件，如函數頭，過程頭，游標等以及常量，變量等。包頭中定義的公共組件可以在包內引用，也可以被其
??????? 它子程序引用。
??? 包體：用于定義包頭中定義過的過程和函數。可以單獨定義私有組件，包括變量，常量，過程和函數等。私有組件只能在包內使用，而
??????? 不能被其它子程序所調用。
??????? 一言以蔽之，包頭定義包的聲明及描述部分，而包體則定義了對應包的具體執行部分。
??? 創建包的語法：
??????? CREATE [OR REPLACE] PACKAGE package_name ???? -- 定義包頭
??????? { AS|IS}
??????????? public_variable_declarations |
??????????? public_type_declarations |
??????????? public_exception_declarations | ?
??????????? public_cursor_declarations |
??????????? function_declarations |
??????????? procedure_specifications
??????? END [package_name] ?
???????
??????? CREATE [OR REPLACE] PACKAGE BODY package_name ?? -- 定義包體，包體中的 package_name 應當與包頭中的 package_name 相同
??????? { AS|IS} ?
??????????? private_variable_declarations |
??????????? private_type_declarations |
??????????? private_exception_declarations |
??????????? private_cursor_declarations | ?
??????????? function_declarations |
??????????? procedure_specifications ?
??????? END [package_name] ?
?
二、創建包 ????? ??
??? 下面演示包的創建，基于用戶 scott 創建，存儲過程，函數等依賴于其下的對象
??? 1. 創建包頭
??????? CREATE OR REPLACE PACKAGE emp_package IS ??? -- 創建包頭，包的名字為 emp_package
??????????? g_deptno NUMBER ( 3 ) := 30 ; ?????????????? -- 定義一個公共變量 g_deptno
??????????? PROCEDURE add_employee ( eno NUMBER , name VARCHAR2 , salary NUMBER , dno NUMBER DEFAULT g_deptno ); -- 聲明過程
??????????? PROCEDURE fire_employee ( eno NUMBER ); ?????????????????????????????????????????????????????????? -- 聲明過程
??????????? FUNCTION get_sal ( eno NUMBER ) RETURN NUMBER ; ??????????????????????????????????????????????????? -- 聲明函數
??????? END emp_package ;
??????? /
?
??? 2. 創建包體 ?
??????? CREATE OR REPLACE PACKAGE BODY emp_package IS ??? -- 創建包體，注意，包體中包的名字必須與包頭的名字相一致
??????????? FUNCTION validate_deptno ( v_deptno NUMBER ) RETURN BOOLEAN ? -- 創建一個私有函數 , 注 , 此私有函數不能該包外子程序調用
??????????? IS
??????????????? v_temp INT ;
??????????? BEGIN
??????????????? SELECT 1 INTO v_temp FROM dept WHERE deptno = v_deptno ;
??????????????? RETURN TRUE ;
??????????? EXCEPTION
??????????????? WHEN NO_DATA_FOUND THEN
??????????????????? RETURN FALSE ;
??????????? END ;
???????????
??????????? PROCEDURE add_employee ?? -- 創建添加雇員的過程
??????????????? ( eno NUMBER , name VARCHAR2 , salary NUMBER , dno NUMBER DEFAULT g_deptno ) IS
??????????? BEGIN
??????????????? IF validate_deptno ( dno ) THEN ?? -- 該過程調用了包內的一個函數 validate_deptno 來驗證 dno 的有效性
??????????????????? INSERT INTO emp ( empno , ename , sal , deptno ) VALUES ( eno , name , salary , dno );
??????????????? ELSE
??????????????????? RAISE_APPLICATION_ERROR (- 20000 , ' 不存在該部門 ' );
??????????????? END IF ;
??????????? EXCEPTION
??????????????? WHEN DUP_VAL_ON_INDEX THEN
??????????????????? RAISE_APPLICATION_ERROR (- 20011 , ' 該雇員已存在 ' );
??????????? END ;
?
??????????? PROCEDURE fire_employee ( eno NUMBER ) IS ? -- 創建解除雇員的過程
??????????? BEGIN
??????????????? DELETE FROM emp WHERE empno = eno ;
??????????????? IF SQL % NOTFOUND THEN
??????????????????? RAISE_APPLICATION_ERROR (- 20012 , ' 該雇員不存在 ' );
??????????????? END IF ;
??????????? END ;
?
??????????? FUNCTION get_sal ( eno NUMBER ) RETURN NUMBER IS ? -- 創建函數 get_sal 返回雇員的薪水
??????????????? 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 (- 20012 , ' 該雇員不存在 ' );
??????????? END ;
??????? END emp_package ;
??????? /
???
??? 3. 創建僅包含包頭的包 ( 僅包含包頭的包也可以被調用，具體參照后面的包的調用 )
??????? CREATE OR REPLACE PACKAGE global_int
??????? IS
??????? ? g_positive ? CONSTANT NUMBER := 10 ;
??????? ? g_negative CONSTANT NUMBER :=- 10 ;
??????? END global_int ;
??????? ? ?????
三、包的調用
??????? 對于包的私有對象只能在包內調用。如上面的例子中對包內私有函數 validate_deptno 進行了直接調用
??????? 對于包的公共對象，既可以在包內調用，也可以由其他應用程序調用。使用其他應用程序調用時的方法：包名 . 包對象
???????
??? 1. 調用包的公共變量
??????? scott@ORCL > exec emp_package . g_deptno := 10 ;
???????
??? 2. 調用包的公共過程
??????? scott@ORCL > exec emp_package . add_employee ( 2222 , 'Robinson' , 3000 ); -- 此調用未指定部門號，則使用缺省值 , 但前面執行了
???????????????????????????????????????????????????????????????????????? --exec emp_package.g_deptno:=10; 故部門號變為
??????? scott@ORCL > exec emp_package . add_employee ( 3333 , 'Jackson' , 4000 , 20 );
?
??????? scott@ORCL > select * from emp where empno in( 2222 , 3333 );
?
??????????? ? EMPNO ENAME ????? JOB ????????????? MGR HIREDATE ???????? SAL ?????? COMM ???? DEPTNO
??????? ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
??????????? ? 2222 Robinson ??????????????????????????????????????? 3000 ??????????????????? 10
??????????? ? 3333 Jackson ???????????????????????????????????????? 4000 ??????????????????? 20 ??????
???
??? 3. 調用包的公共函數
??????? scott@ORCL > var sal number
??????? scott@ORCL > exec : sal := emp_package . get_sal ( 7788 );
?
??????? PL / SQL procedure successfully completed .
?
??????? scott@ORCL > print sal
?
??????????? ?? SAL
??????? ----------
??????????? ? 310 ??????
??????????? ?
??? 4. 以不同用戶身份調用包 . 需要使用 schema 名字來調用，即：用戶名 . 包名 . 包對象名
??????? scott@ORCL > conn lion / lion ?? -- 注意帳戶需要具有執行所調用包的權限
???????
??????? lion@ORCL > exec scott . emp_package . fire_employee ( 2222 ); ?????
???
??? 5. 調用遠程數據庫包的公共對象。調用方法 : 包名 . 包對象名 @ 數據庫鏈接名
??????? sys@ASMDB > create database link orcl
??????? ? 2 ? connect to lion identified by lion
??????? ? 3 ? using 'orcl' ;
?
??????? Database link created .
???????
??????? sys@ASMDB > exec scott . emp_package . add_employee@orcl ( 4444 , 'Richard' , 4000 );
??????? BEGIN scott . emp_package . add_employee@orcl ( 4444 , 'Richard' , 4000 ); END ;
?
??????????? ? *
??????? ERROR at line 1 :
??????? ORA - 06550 : line 1 , column 7 : ?? -- 注意遠程調用時，對于缺省的參數不適用，需要明確指定參數
??????? PLS - 00424 : RPC defaults cannot include Package State
??????? ORA - 06550 : line 1 , column 7 :
??????? PL / SQL : Statement ignored ??
???????
??????? sys@ASMDB > exec scott . emp_package . add_employee@orcl ( 4444 , 'Richard' , 4000 , 20 ); ? -- 下面的調用被成功執行
?
??????? PL / SQL procedure successfully completed .
???????
??? 6. 無包體包的調用 ( 使用前面創建的包 global_int ) ???
??????? scott@ORCL > BEGIN
??????? ? 2 ? DBMS_OUTPUT . PUT_LINE ( 'Result is : ' || 2 * global_int . g_positive ); -- 使用包 DBMS_OUTPUT 來調用
??????? ? 3 ? END ;
??????? ? 4 ? /
??????? Result is : 20
?
??????? PL / SQL procedure successfully completed . ???
???????????
??????? scott@ORCL > CREATE OR REPLACE FUNCTION f_negative ( m number ) ?? -- 將包嵌入到函數之中
??????? ? 2 ? RETURN NUMBER
??????? ? 3 ? IS
??????? ? 4 ? BEGIN
??????? ? 5 ??? RETURN ( m * global_int . g_negative );
??????? ? 6 ? END f_negative ;
??????? ? 7 ? /
?
??????? Function created .
?
??????? scott@ORCL > EXEC DBMS_OUTPUT . PUT_LINE ( f_negative ( 2 ));
??????? - 20
?
??????? PL / SQL procedure successfully completed .
四、包的管理
??? 1. 查看包
??????? scott@ORCL > select line , text from user_source ??????? -- 查看包頭
??????? ? 2 ? where name = 'EMP_PACKAGE' and type = 'PACKAGE' ;
?
??????????? ? LINE TEXT
??????? ---------- ------------------------------------------------------------
??????????????? ? 1 PACKAGE emp_package IS
??????????????? ? 2 ?? g_deptno NUMBER ( 3 ) := 30 ;
??????????????? ? 3 ?? PROCEDURE add_employee ( eno NUMBER , name VARCHAR2 , salary N
??????????????? ?? UMBER , dno NUMBER DEFAULT g_deptno );
?
??????????????? ? 4 ?? PROCEDURE fire_employee ( eno NUMBER );
??????????????? ? 5 ?? FUNCTION get_sal ( eno NUMBER ) RETURN NUMBER ;
??????????????? ? 6 END emp_package ;
?
???????????
??????? scott@ORCL > select line , text from user_source ???? -- 查看包體
??????? ? 2 ? where name = 'EMP_PACKAGE' and type = 'PACKAGE BODY' ;
?
??????????? ? LINE TEXT
??????? ---------- --------------------------------------------------------------------------------
??????????????? ? 1 PACKAGE BODY emp_package IS
??????????????? ? 2 ?? FUNCTION validate_deptno ( v_deptno NUMBER ) RETURN BOOLEAN IS
??????????? ??? ? 3 ???? v_temp INT ;
??????????????? ? 4 ?? BEGIN
??????????????? ? 5 ???? SELECT 1 INTO v_temp FROM dept WHERE deptno = v_deptno ;
??????????????? ? 6 ???? RETURN TRUE ;
??????????????????????????? ......................
??????????????? ?
??? 2. 查看包的參數
??????? scott@ORCL > desc emp_package ;
??????? PROCEDURE ADD_EMPLOYEE
??????? ? Argument Name ????????????????? Type ??????????????????? In/ Out Default ?
??????? ? ------------------------------ ----------------------- ------ --------
??????? ? ENO ??????????????????????????? NUMBER ????????????????? IN
??????? ? NAME ?????????????????????????? VARCHAR2 ??????????????? IN
??????? ? SALARY ???????????????????????? NUMBER ????????????????? IN
??????? ? DNO ??????????????????????????? NUMBER ????????????????? IN ???? DEFAULT
??????? PROCEDURE FIRE_EMPLOYEE
??????? ? Argument Name ????????????????? Type ??????????????????? In/ Out Default ?
??????? ? ------------------------------ ----------------------- ------ --------
??????? ? ENO ??????????????????????????? NUMBER ????????????????? IN
??????? FUNCTION GET_SAL RETURNS NUMBER
??????? ? Argument Name ????????????????? Type ??????????????????? In/ Out Default ?
??????? ? ------------------------------ ----------------------- ------ --------
??????? ? ENO ??????????????????????????? NUMBER ????????????????? IN
??????? ?
??? 3. 包的刪除
??????? DROP PACKAGE package_name ?? -- 同時刪除包體和包頭
???????
??????? scott@ORCL > DROP PACKAGE global_int ;
???????
??????? 刪除包體，保留包頭
??????????? DROP PACKAGE BODY package_name ?? -- 刪除包體
???????????
??????? scott@ORCL > drop package body emp_package ;
?
??????? Package body dropped .
?
??? 4.包的編譯
????????? 重新編譯包規范和包體：alter package...compile
????????? 重新編譯包規范：alter package...compile specification
????????? 重新編譯包體：alter package...compile body
??????
五、總結
??? 創鍵包體之前應該先創建包頭
??? 包頭應當僅僅包含那些希望作為公共對象的部分
??? 包頭的聲明應包含盡可能少的結構信息
??? 任意包頭的變更，需要重新編譯該包內的子程序
??? 在包頭內定義的任意公共對象可以被任意內部或外部子程序調用
??? 包體內的私有對象僅僅能被該包體內的子程序調用
???
六、更多參考
有關 SQL 請參考
??????? SQL 基礎--> 子查詢
??????? SQL 基礎--> 多表查詢
SQL 基礎--> ROLLUP 與CUBE 運算符實現數據匯總
SQL 基礎--> 層次化查詢(START BY ... CONNECT BY PRIOR)
?
??? 有關 PL/SQL 請參考
??????? PL/SQL --> 語言基礎
?
?
???????
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
