Oracle專題15之包

1、包的概述

a、什么是包？

• 包是一組相關過程、函數(shù)、變量、常量和游標等PL/SQL程序設計元素的組合。

  b、包的特點？

• 它具有面向?qū)ο蟪绦蛟O計語言的特點，是對PL/SQL程序設計元素（過程、函數(shù)、變量等）的封裝。
• 它使程序設計模塊化。

  c、包中的程序元素

• 包中的程序元素分為兩種：公用元素（公用組件）、私用元素（私用組件）。

  e、包的組成

• 一個包由兩個分開的部分組成：
• 包規(guī)范（包定義）：用于定義包的公用組件，包括常量、變量、游標、過程和函數(shù)等。
• 包體（包主體）：用于實現(xiàn)包規(guī)范所定義的公用過程和函數(shù)。包體不僅可用于實現(xiàn)公用過程和函數(shù)，而且還可以定義包的私有組件（變量、游標、過程、函數(shù)等）。

  f、簡單代碼示例

• 創(chuàng)建包規(guī)范和包體的示例代碼

  --創(chuàng)建包規(guī)范
  CREATE OR REPLACE PACKAGE first_package
  IS
      v_no emp.deptno%TYPE := 10;
      --過期
      PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no, v_avgsal OUT NUMBER, v_cnt OUT NUMBER);
  END first_package;
  --創(chuàng)建包體
  CREATE OR REPLACE PACKAGE BODY first_package
  IS
               PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no, v_avgsal) OUT NUMBER, v_cnt OUT NUMBER)
               IS
               BEGIN
                   SELECT avg(sal), count(*) INTO v_avgsal, v_cnt FROM emp WHERE deptno = v_deptno;
               EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                       dbms_output.put_line('沒有此部門');   
                   WHEN OTHERS THEN
                       dbms_output.put_line(SQLERRM);
                   END;
  END first_package;

• 調(diào)用包中的存儲過程：

  DECLARE
      v_avgsal NUMBER;
      v_cnt NUMBER;
  BEGIN
      first_package.query_emp(20, v_avgsal, v_cnt);
      DBMS_OUTPUT.put_line('平均工資：' || v_avgsal);
      DBMS_OUTPUT.put_line('總?cè)藬?shù)：' || v_cnt);
  END;

  2、包的創(chuàng)建

  a、創(chuàng)建包規(guī)范

• 語法格式如下：

  CREATE [OR REPLACE] PACKAGE package_name
  IS | AS
  -- 定義公用常量、變量、游標、過程、函數(shù)等
  END [package_name];

• 示例代碼：

  CREATE OR REPLACE PACKAGE emp_package
  IS
               --添加員工信息的存儲過程
               PROCEDURE add_emp_proc
               (v_empno IN emp.empno%TYPE,
               v_ename IN emp.ename%TYPE,
               v_sal IN emp.sal%TYPE,
               v_deptno IN emp.deptno%TYPE);
  
               --刪除員工信息的存儲過程
               PROCEDURE del_emp_proc
               (v_empno IN emp.empno%TYPE);
  END emp_package;

  b、創(chuàng)建包體

• 語法格式如下：

  CREATE [OR REPLACE] PACKAGE BODY package_name 
  IS | AS
  --定義私有常量、變量、游標、過程和函數(shù)等
  --實現(xiàn)公用過程和函數(shù)
  END [package_name];

• 示例代碼：

  CREATE OR REPLACE PACKAGE BODY emp_package
  IS    
              --添加員工信息的存儲過程
               PROCEDURE add_emp_proc
               (v_empno IN emp.empno%TYPE,
               v_ename IN emp.ename%TYPE,
               v_sal IN emp.sal%TYPE,
               v_deptno IN emp.deptno%TYPE)
               IS
                                  e_2291 EXCEPTION;
                                  PRAGMA EXCEPTION_INIT(e_2291, -2291);
               BEGIN
                   INSERT INTO emp(empno, ename, sal, deptno) VALUES(v_empno, v_ename, v_sal, v_deptno);
               EXCEPTION
                   WHEN DUP_VAL_ON_INDEX THEN
                       RAISE_APPLICATION_ERROR(-20001, '員工號不能重復');
                   WHEN e_2291 THEN
                       RAISE_APPLICATION_ERROR(-20002, '部門號不存在');
               END;
  
               --刪除員工信息的存儲過程
               PROCEDURE del_emp_proc
               (v_empno IN emp.empno%TYPE)
               IS
               BEGIN
                   --根據(jù)員工號刪除指定的員工信息
                   DELETE FROM emp WHERE empno = v_empno;
                   --判斷是否刪除成功
                   IF SQL%NOTFOUND THEN
                       RAISE_APPLICATION_ERROR(-20009, '指定刪除的員工不存在');
                   ELSE
                       DBMS_OUTPUT.PUT_line('刪除成功');
                   END IF;
               END;
  END emp_package;

  c、包創(chuàng)建的過程案例代碼

• 根據(jù)員工號查詢工資，如果工資小于等于3000，工資漲500。

  --創(chuàng)建包規(guī)范
  CREATE OR REPLACE PACKAGE emp_sal_pkg
  IS
               FUNCTION get_sal(eno NUMBER) RETURN NUMBER;
  
               PROCEDURE upd_sal(eno NUMBER, salary NUMBER);
  END emp_sal_pkg;
  
  --包體
  CREATE OR REPLACE PACKAGE BODY emp_sal_pkg
  IS
               FUNCTION get_sal(eno NUMBER) RETURN NUMBER
               IS
                                  v_sal emp.sal%TYPE := 0;
               BEGIN
                                  SELECT sal INTO v_sal FROM emp WHERE empno = eno;
                                  RETURN v_sal;
               EXCEPTION
                                  WHEN NO_DATA_FOUND THEN
                                      RAISE_APPLICATION_ERROR(-20010,'此員工號不存在');
               END;
  
               PROCEDURE upd_sal(eno NUMBER, salary NUMBER)
               IS
               BEGIN
                   IF salary <=3000 THEN
                       UPDATE emp SET sal = sal + 500 WHERE empno = eno;
                   END IF;
               END;
  END emp_sal_pkg;  

  3、包的調(diào)用和刪除

  a、包的調(diào)用

• 對包內(nèi)共有元素（公用組件）的調(diào)用格式為：包名.元素名稱（組件名稱）
• 例如：emp_package.del_emp_proc();

  b、 包的調(diào)用示例1

• 調(diào)用emp_package包下添加員工信息的存儲過程。（在本專題的第2節(jié)包的創(chuàng)建，已經(jīng)在Oracle數(shù)據(jù)庫中創(chuàng)建emp_package包規(guī)范和包體）
  

  DECLARE 
      v_empno emp.empno%TYPE := &empno;
      v_ename emp.ename%TYPE := '&name';
      v_sal emp.sal%TYPE := &salary;
      v_deptno emp.deptno%TYPE := &deptno;
      e_dup_val EXCEPTION;
      e_no_dept EXCEPTION;
  
      PRAGMA EXCEPTION_INIT(e_dup_val, -20001);
      PRAGMA EXCEPTION_INIT(e_no_dept, -20002);
  BEGIN
      emp_package.add_emp_proc(v_empno, v_ename, v_sal, v_deptno);
      COMMIT;
  EXCEPTION
      WHEN e_dup_val THEN
          DBMS_OUTPUT.put_line(SQLERRM);
      WHEN e_no_dept THEN
          DBMS_OUTPUT.put_line(SQLERRM);
          ROLLBACK;
  END;

  b、包的調(diào)用示例2

• 調(diào)用emp_package包下刪除員工信息的存儲過程。（在本專題的第2節(jié)包的創(chuàng)建，已經(jīng)在Oracle數(shù)據(jù)庫中創(chuàng)建emp_package包規(guī)范和包體）
  

  DECLARE 
      v_empno emp.empno%TYPE := &empno;
      e_no_emp EXCEPTION;
      PRAGMA EXCEPTION_INIT(e_no_emp, -20009);
  BEGIN
      emp_package.del_emp_proc(v_empno);
      COMMIT;
  EXCEPTION
      WHEN e_no_emp THEN
          DBMS_OUTPUT.put_line(SQLERRM);
          ROLLBACK;
  END;

  c、包的調(diào)用示例3

• 調(diào)用emp_sal_pkg包下的過程和函數(shù)：（在本專題的第2節(jié)包的創(chuàng)建，已經(jīng)在Oracle數(shù)據(jù)庫中創(chuàng)建emp_sal_pkg包規(guī)范和包體）
  
  1、調(diào)用根據(jù)員工號返回員工工資的函數(shù)
  2、調(diào)用更新滿足條件的員工工資的過程

  DECLARE
      v_empno emp.empno%TYPE := &empno;
      v_salary emp.sal%TYPE;
      e_no_emp EXCEPTION;
      PRAGMA EXCEPTION_INIT(e_no_emp, -20010);
  BEGIN
      v_salary := emp_sal_pkg.get_sal(v_empno);
      emp_sal_pkg.upd_sal(v_empno, v_salary);
      COMMIT;
  EXCEPTION
      WHEN e_no_emp THEN
          DBMS_OUTPUT.put_line(SQLERRM);
  END;

  d、如何在SQLPlus窗口中進行包的調(diào)用

• 示例1：在SQLPlus窗口中進行emp_sal_pkg包的調(diào)用，查詢員工的工資信息：

  SQL> VAR v_empno NUMBER
  SQL> EXEC :v_empno := &no
  
  PL/SQL procedure successfully completed
  
  v_empno
  ---------
  1234
  
  SQL> VAR v_salary NUMBER
  SQL> EXEC :v_salary := emp_sal_pkg.get_sal(:v_empno)
  begin :v_salary := emp_sal_pkg.get_sal(:v_empno); end;
  
  ORA-20010: 此員工號不存在
  ORA-06512: 在 "SCOTT.EMP_SAL_PKG", line 11
  ORA-06512: 在 line 1
  v_salary
  ---------
  v_empno
  ---------
  1234

• 示例2：在SQLPlus窗口中進行emp_sal_pkg包的調(diào)用，更新員工的工資信息：（變量v_empno已經(jīng)在示例1中定義）

  SQL> EXEC :v_empno := &no
  
  PL/SQL procedure successfully completed
  
  v_empno
  ---------
  7369
  
  SQL> EXEC :v_salary := emp_sal_pkg.get_sal(:v_empno)
  
  PL/SQL procedure successfully completed
  
  v_salary
  ---------
  800
  v_empno
  ---------
  7369
  
  SQL> EXEC emp_sal_pkg.upd_sal(:v_empno, :v_salary)
  
  PL/SQL procedure successfully completed
  
  v_empno
  ---------
  7369
  v_salary
  ---------
  800
  
  SQL> SELECT ename, sal from emp WHERE empno = 7369;
  
  ENAME            SAL
  ---------- ---------
  G_EASON      1300.00

• 在SQLPlus環(huán)境中，可以使用 [VAR 變量名 變量類型]命令定義變量。

  e、包的刪除

• 可以使用DROP PACKAGE命令對不需要的包進行刪除，語法如下：

  DROP PACKAGE [BODY] [user.] package_name;

  4、子程序重載

  a、什么是子程序重載？

• 所謂重載是指兩個或者多個子程序有相同的名稱，但是擁有不同的參數(shù)變量、參數(shù)順序或者參數(shù)數(shù)據(jù)類型。

  b、示例代碼

• 示例：1、根據(jù)員工號或者員工姓名獲取員工信息；2、根據(jù)員工號或者員工姓名刪除員工的信息。

  --創(chuàng)建包規(guī)范
  CREATE OR REPLACE PACKAGE overload_pkg
  IS
               FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE;
               FUNCTION get_info(name VARCHAR) RETURN emp%ROWTYPE;
  
               PROCEDURE del_emp(eno NUMBER);
               PROCEDURE del_emp(name VARCHAR);
  END;
  --創(chuàng)建包體
      CREATE OR REPLACE PACKAGE BODY overload_pkg
  IS
               FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE
               IS
                                  emp_record emp%ROWTYPE;
               BEGIN
                                  SELECT * INTO emp_record FROM emp WHERE empno = eno;
                                  RETURN emp_record;
               EXCEPTION
                                  WHEN NO_DATA_FOUND THEN
                                           RAISE_APPLICATION_ERROR(-20020, '不存在此員工');
               END;
  
               FUNCTION get_info (name VARCHAR) RETURN emp%ROWTYPE
               IS
                                  emp_record emp%ROWTYPE;
               BEGIN
                                  SELECT * INTO emp_record FROM emp WHERE ename= name;
                                  RETURN emp_record;
               EXCEPTION
                                  WHEN NO_DATA_FOUND THEN
                                      RAISE_APPLICATION_ERROR(-20020, '不存在此員工');
               END;
  
               PROCEDURE del_emp(eno NUMBER)
               IS
               BEGIN
                   DELETE FROM emp WHERE empno = eno;
                   IF SQL%NOTFOUND THEN
                       RAISE_APPLICATION_ERROR(-20020, '不存在此員工');
                   END IF;
               END;
  
               PROCEDURE del_emp(name VARCHAR)
               IS
               BEGIN
                   DELETE FROM emp WHERE ename = name;
                   IF SQL%NOTFOUND THEN
                       RAISE_APPLICATION_ERROR(-20020, '不存在此員工');
                   END IF;
               END;
  END overload_pkg;

• overload_pkg包的調(diào)用示例：

  --根據(jù)員工號查詢員工信息
  DECLARE
      emp_record emp%rowtype;
      e_no_emp EXCEPTION;
      PRAGMA EXCEPTION_INIT(e_no_emp, -20020);
  BEGIN 
      emp_record := overload_pkg.get_info(&no);
      DBMS_OUTPUT.put_line('員工號：' || emp_record.empno || '，姓名：' || emp_record.ename || '，工資：' || 
      emp_record.sal);
  EXCEPTION
      WHEN e_no_emp THEN
          DBMS_OUTPUT.put_line(SQLERRM);
  END;

  --根據(jù)員工姓名查詢員工信息
  DECLARE
      emp_record emp%rowtype;
      e_no_emp EXCEPTION;
      PRAGMA EXCEPTION_INIT(e_no_emp, -20020);
  BEGIN 
      emp_record := overload_pkg.get_info('&ename');
      DBMS_OUTPUT.put_line('員工號：' || emp_record.empno || '，姓名：' || emp_record.ename || '，工資：' || 
      emp_record.sal);
  EXCEPTION
      WHEN e_no_emp THEN
          DBMS_OUTPUT.put_line(SQLERRM);
  END;

  --根據(jù)員工號刪除員工信息
  DECLARE 
      e_no_emp EXCEPTION;
      PRAGMA EXCEPTION_INIT(e_no_emp, -20020);
  BEGIN
      overload_pkg.del_emp(&no);
      COMMIT;
  EXCEPTION
      WHEN e_no_emp THEN
          DBMS_OUTPUT.put_line(SQLERRM);
      ROLLBACK;
  END;

  
  --根據(jù)員工姓名刪除員工信息
  DECLARE 
      e_no_emp EXCEPTION;
      PRAGMA EXCEPTION_INIT(e_no_emp, -20020);
  BEGIN
      overload_pkg.del_emp('&ename');
      COMMIT;
  EXCEPTION
      WHEN e_no_emp THEN
          DBMS_OUTPUT.put_line(SQLERRM);
      ROLLBACK;
  END;