--============================
-- PL/SQL --> 包的創(chuàng)建與管理
--============================
?
??? 包，是一個(gè)邏輯集合，是由 PL / SQL 類型以及 PL / SQL 子程序的集合。 PL / SQL 類型包括 table 類型， record 類型。 PL / SQL 項(xiàng)則包括游標(biāo)，游標(biāo)
變量。 PL / SQL 子程序包括過程，函數(shù)等。可以說包可謂是包羅萬象。是所有 PL / SQL 相關(guān)資源的匯總。
??? 包的使用可以簡(jiǎn)化應(yīng)用程序設(shè)計(jì)，實(shí)現(xiàn)信息掩藏，子程序重載等功能。
??? 包的優(yōu)點(diǎn)
??????? 1. 模塊化：將函數(shù)，子程序全部融合在一起，使得成為一個(gè)有機(jī)的整體，封裝了相關(guān)的結(jié)構(gòu)。
??????? 2. 易于維護(hù)：整合了子程序，更易于維護(hù) 。
??????? 3. 簡(jiǎn)化應(yīng)用程序設(shè)計(jì)：包的聲明與包體內(nèi)容相分離。
??????? 4. 隱藏信息：私有對(duì)象不可訪問，所有的包體內(nèi)代碼可以實(shí)現(xiàn)隱藏。
??????? 5. 節(jié)省 I / O ：一次編譯，多次使用。
???
一、包的組成與創(chuàng)建語法
??? 包頭：用于定義包的公共組件，如函數(shù)頭，過程頭，游標(biāo)等以及常量，變量等。包頭中定義的公共組件可以在包內(nèi)引用，也可以被其
??????? 它子程序引用。
??? 包體：用于定義包頭中定義過的過程和函數(shù)。可以單獨(dú)定義私有組件，包括變量，常量，過程和函數(shù)等。私有組件只能在包內(nèi)使用，而
??????? 不能被其它子程序所調(diào)用。
??????? 一言以蔽之，包頭定義包的聲明及描述部分，而包體則定義了對(duì)應(yīng)包的具體執(zhí)行部分。
??? 創(chuàng)建包的語法：
??????? 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 應(yīng)當(dāng)與包頭中的 package_name 相同
??????? { AS|IS} ?
??????????? private_variable_declarations |
??????????? private_type_declarations |
??????????? private_exception_declarations |
??????????? private_cursor_declarations | ?
??????????? function_declarations |
??????????? procedure_specifications ?
??????? END [package_name] ?
?
二、創(chuàng)建包 ????? ??
??? 下面演示包的創(chuàng)建，基于用戶 scott 創(chuàng)建，存儲(chǔ)過程，函數(shù)等依賴于其下的對(duì)象
??? 1. 創(chuàng)建包頭
??????? CREATE OR REPLACE PACKAGE emp_package IS ??? -- 創(chuàng)建包頭，包的名字為 emp_package
??????????? g_deptno NUMBER ( 3 ) := 30 ; ?????????????? -- 定義一個(gè)公共變量 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 ; ??????????????????????????????????????????????????? -- 聲明函數(shù)
??????? END emp_package ;
??????? /
?
??? 2. 創(chuàng)建包體 ?
??????? CREATE OR REPLACE PACKAGE BODY emp_package IS ??? -- 創(chuàng)建包體，注意，包體中包的名字必須與包頭的名字相一致
??????????? FUNCTION validate_deptno ( v_deptno NUMBER ) RETURN BOOLEAN ? -- 創(chuàng)建一個(gè)私有函數(shù) , 注 , 此私有函數(shù)不能該包外子程序調(diào)用
??????????? 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 ?? -- 創(chuàng)建添加雇員的過程
??????????????? ( eno NUMBER , name VARCHAR2 , salary NUMBER , dno NUMBER DEFAULT g_deptno ) IS
??????????? BEGIN
??????????????? IF validate_deptno ( dno ) THEN ?? -- 該過程調(diào)用了包內(nèi)的一個(gè)函數(shù) validate_deptno 來驗(yàn)證 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 ? -- 創(chuàng)建解除雇員的過程
??????????? 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 ? -- 創(chuàng)建函數(shù) 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. 創(chuàng)建僅包含包頭的包 ( 僅包含包頭的包也可以被調(diào)用，具體參照后面的包的調(diào)用 )
??????? CREATE OR REPLACE PACKAGE global_int
??????? IS
??????? ? g_positive ? CONSTANT NUMBER := 10 ;
??????? ? g_negative CONSTANT NUMBER :=- 10 ;
??????? END global_int ;
??????? ? ?????
三、包的調(diào)用
??????? 對(duì)于包的私有對(duì)象只能在包內(nèi)調(diào)用。如上面的例子中對(duì)包內(nèi)私有函數(shù) validate_deptno 進(jìn)行了直接調(diào)用
??????? 對(duì)于包的公共對(duì)象，既可以在包內(nèi)調(diào)用，也可以由其他應(yīng)用程序調(diào)用。使用其他應(yīng)用程序調(diào)用時(shí)的方法：包名 . 包對(duì)象
???????
??? 1. 調(diào)用包的公共變量
??????? scott@ORCL > exec emp_package . g_deptno := 10 ;
???????
??? 2. 調(diào)用包的公共過程
??????? scott@ORCL > exec emp_package . add_employee ( 2222 , 'Robinson' , 3000 ); -- 此調(diào)用未指定部門號(hào)，則使用缺省值 , 但前面執(zhí)行了
???????????????????????????????????????????????????????????????????????? --exec emp_package.g_deptno:=10; 故部門號(hào)變?yōu)?
??????? 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. 調(diào)用包的公共函數(shù)
??????? 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. 以不同用戶身份調(diào)用包 . 需要使用 schema 名字來調(diào)用，即：用戶名 . 包名 . 包對(duì)象名
??????? scott@ORCL > conn lion / lion ?? -- 注意帳戶需要具有執(zhí)行所調(diào)用包的權(quán)限
???????
??????? lion@ORCL > exec scott . emp_package . fire_employee ( 2222 ); ?????
???
??? 5. 調(diào)用遠(yuǎn)程數(shù)據(jù)庫包的公共對(duì)象。調(diào)用方法 : 包名 . 包對(duì)象名 @ 數(shù)據(jù)庫鏈接名
??????? 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 : ?? -- 注意遠(yuǎn)程調(diào)用時(shí)，對(duì)于缺省的參數(shù)不適用，需要明確指定參數(shù)
??????? 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 ); ? -- 下面的調(diào)用被成功執(zhí)行
?
??????? PL / SQL procedure successfully completed .
???????
??? 6. 無包體包的調(diào)用 ( 使用前面創(chuàng)建的包 global_int ) ???
??????? scott@ORCL > BEGIN
??????? ? 2 ? DBMS_OUTPUT . PUT_LINE ( 'Result is : ' || 2 * global_int . g_positive ); -- 使用包 DBMS_OUTPUT 來調(diào)用
??????? ? 3 ? END ;
??????? ? 4 ? /
??????? Result is : 20
?
??????? PL / SQL procedure successfully completed . ???
???????????
??????? scott@ORCL > CREATE OR REPLACE FUNCTION f_negative ( m number ) ?? -- 將包嵌入到函數(shù)之中
??????? ? 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. 查看包的參數(shù)
??????? 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 ?? -- 同時(shí)刪除包體和包頭
???????
??????? scott@ORCL > DROP PACKAGE global_int ;
???????
??????? 刪除包體，保留包頭
??????????? DROP PACKAGE BODY package_name ?? -- 刪除包體
???????????
??????? scott@ORCL > drop package body emp_package ;
?
??????? Package body dropped .
?
??? 4.包的編譯
????????? 重新編譯包規(guī)范和包體：alter package...compile
????????? 重新編譯包規(guī)范：alter package...compile specification
????????? 重新編譯包體：alter package...compile body
??????
五、總結(jié)
??? 創(chuàng)鍵包體之前應(yīng)該先創(chuàng)建包頭
??? 包頭應(yīng)當(dāng)僅僅包含那些希望作為公共對(duì)象的部分
??? 包頭的聲明應(yīng)包含盡可能少的結(jié)構(gòu)信息
??? 任意包頭的變更，需要重新編譯該包內(nèi)的子程序
??? 在包頭內(nèi)定義的任意公共對(duì)象可以被任意內(nèi)部或外部子程序調(diào)用
??? 包體內(nèi)的私有對(duì)象僅僅能被該包體內(nèi)的子程序調(diào)用
???
六、更多參考
有關(guān) SQL 請(qǐng)參考
??????? SQL 基礎(chǔ)--> 子查詢
??????? SQL 基礎(chǔ)--> 多表查詢
SQL 基礎(chǔ)--> ROLLUP 與CUBE 運(yùn)算符實(shí)現(xiàn)數(shù)據(jù)匯總
SQL 基礎(chǔ)--> 層次化查詢(START BY ... CONNECT BY PRIOR)
?
??? 有關(guān) PL/SQL 請(qǐng)參考
??????? PL/SQL --> 語言基礎(chǔ)
PL/SQL --> 隱式游標(biāo)(SQL%FOUND)
?
?
???????
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
