--============================
-- 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元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
