Oracle專題14之存儲過程與函數

1、存儲過程與函數的概述

• 存儲過程和存儲函數：是存儲在數據庫中的被命名的PLSQL塊，供所有用戶程序調用，完成特定功能的子程序。

  a、存儲過程和存儲函數的區別

• 是否使用return返回值。（即存儲過程不返回值，存儲函數返回值）

  b、第一個存儲過程與函數的程序

• 用存儲過程或者函數實現輸出‘Hello Everyone!’。

      --創建存儲過程
      CREATE OR REPLACE PROCEDURE first_proc
      IS
      BEGIN
          DBMS_OUTPUT.put_line('我的過程');
          DBMS_OUTPUT.put_line('Hello Everyone!');
      END;
      --創建函數
      CREATE OR REPLACE FUNCTION first_func
      RETURN VARCHAR
      IS 
      BEGIN
          DBMS_OUTPUT.put_line('我的函數');
          RETURN 'hello everyone';
      END;

  • 可以看到，當執行完代碼之后，左側的all objects中已經的Functions和Procedures中已經分別保存了存儲過程FIRST_FUNC和存儲函數FREST_PROC。
    
• 調用存儲過程

  BEGIN 
      first_proc;
  END;

• 調用存儲函數

  BEGIN
      DBMS_OUTPUT.put_line(first_func);
  END;

  2、存儲過程的創建

  a、創建存儲過程的語法

  CREATE [OR REPLACE] PROCEDURE procedure_name 
  [(argument1 [{ IN | OUT | IN OUT}] type, argument2 [ { IN | OUT | IN OUT}] type, ...)] （IN 為輸入參數、OUT輸出參數、IN | OUT為輸入輸出參數）
  { IS | AS }（即使沒有聲明部分，IS | AS 也不能夠省略，IS或者AS選擇哪一個都可以）
      聲明部分，類型.變量的說明
  BEGIN
      執行部分
  EXCEPTION
      可選的異常錯誤處理部分
  END;

  b、代碼示例：創建輸入參數的存儲過程

      CREATE OR REPLACE PROCEDURE proc1 
      (v_empno IN emp01.empno%TYPE)
      IS 
      BEGIN
          --根據員工號刪除指定的員工信息
          DECLARE FROM emp01 WHERE empno = v_empno;
          --判斷是否刪除成功
          IF SQL%NOTFOUND THEN
              -- -20000 ~ -20999之間
              RAISE_APPLICATION_ERROR(-20008, '指定刪除的員工不存在');
          ELSE
              DBMS_OUTPUT。put_line('刪除成功');
      END;

• 在存儲過程或者是存儲函數中，一般不需要在其中添加COMMIT或ROLLBACK，誰調用由誰添加COMMIT或ROLLBACK

  c、創建帶有輸出參數的存儲過程

  CREATE OR REPLACE PROCEDURE proc2
          (v_deptno IN NUMBER, 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;

  d、創建帶有輸入輸出參數的存儲過程

  CREATE OR REPLACE PROCEDURE proc3
  (v_num1 IN OUT NUMBER, v_num2 IN OUT NUMBER)
  AS
           v_temp NUMBER := 0;
  BEGIN
           v_temp := v_num1;
           v_num1 := v_num2;
           v_num2 := v_temp;
  END;  

3、存儲函數的創建

a、創建存儲函數的語法

    CREATE [OR REPLACE] FUNCTION function_name
    [ argument1[ { IN | OUT | IN OUT }] type, 
    argument2[{IN | OUT | IN OUT }] type, ...)]
    RETURN return_type
    { IS | AS}
        聲明部分，類型.變量的說明
    BEGIN
        執行部分，函數體
    EXCEPTION
        可選的異常錯誤處理部分
    END;

b、創建帶有輸入參數的存儲函數

• 根據部門編號返回該部門的總工資：

  CREATE OR REPLACE FUNCTION func1
  (v_deptno IN NUMBER)
  RETURN NUMBER
  IS
               v_sumsal NUMBER;
  BEGIN
               SELECT SUM(SAL) INTO v_sumsal FROM emp WHERE deptno = v_deptno;
               RETURN v_sumsal;
  EXCEPTION
               WHEN NO_DATA_FOUND THEN
                   DBMS_OUTPUT.put_line('沒有此部門');
               WHEN OTHERS THEN 
                   DBMS_OUTPUT.put_line(SQLERRM);
  END;

  c、創建帶有輸出參數的存儲函數

• 根據員工號輸出員工的姓名和員工的工資，并且返回員工的年收入：

  CREATE OR REPLACE FUNCTION func2
  (v_empno IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE)
  RETURN NUMBER
  IS 
          v_salsum NUMBER;
  BEGIN
          SELECT ename, sal, (sal + nvl(comm, 0)) * 12 INTO v_name, v_sal, v_salsum
          FROM emp WHERE empno = v_empno;
          RETURN v_salsum;
  EXCEPTION
          WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.put_line('沒有此員工');
          WHEN OTHERS THEN 
              DBMS_OUTPUT.put_line(SQLERRM);
  END;

  d、創建帶有輸入輸出參數的存儲函數

• 求兩個數的平方和，并輸出兩個數的平方

  CREATE OR REPLACE FUNCTION func3
  (n1 IN OUT NUMBER, n2 IN OUT NUMBER)
  RETURN NUMBER
  AS
  BEGIN
      n1 := n1*n1;
      n2 := n2*n2;
      RETURN n1+n2;
  END;

4、存儲過程的調用和刪除

a、調用存儲過程

• 方法一：ORACLE使用EXECUTE語句來實現對存儲過程的調用：（屬于SQLPlus命令）

  EXEC[UTE] Procedure_name(parameter1, parameter2...)

• 方法二：在PL/SQL代碼中直接調用，如：

  BEGIN
      procedure_name(parameter1, parameter2...)
  END;

  b、調用存儲過程的不同情況

  注意：本專題第2節存儲過程的創建后，Oracle數據庫中保存了4個存儲過程。
  

• 調用無參數的存儲過程：直接引用過程名即可。
• 調用帶有輸入參數的存儲過程。
• 調用帶有輸出參數的參數過程。

• 調用帶有輸入輸出參數的存儲過程。

• SQLPlus環境：

  -- 調用無參的存儲過程
  SQL> set serveroutput on
  SQL> exec first_proc
  我的過程
  Hello Everyone!
  PL/SQL procedure successfully completed
  -- 調用帶有輸入參數的存儲過程
  SQL> exec proc1(1234);
  begin proc1(1234); end;
  ORA-20008: 指定刪除的員工不存在
  ORA-06512: 在 "SCOTT.PROC1", line 9
  ORA-06512: 在 line 1

• PL/SQL環境：

  -- 調用無參的存儲過程
  BEGIN
  first_proc;
  END;
  -- 調用帶有輸入參數的存儲過程
  BEGIN
  proc1(1234);
  END;
  -- 調用帶有輸出參數的存儲過程
  DECLARE
  v_avgsal NUMBER;
  v_count NUMBER;
  BEGIN
  PROC2(10, v_avgsal, v_count);
  DBMS_OUTPUT.put_line('平均工資:' || v_avgsal);
  DBMS_OUTPUT.put_line('總人數：' || v_count);
  END;
  --調用帶有輸入輸出參數的存儲過程
  DECLARE
  v_n1 NUMBER := 5;
  v_n2 NUMBER := 10;
  BEGIN
  PROC3(v_n1, v_n2);
  DBMS_OUTPUT.put_line('N1:' || v_n1);
  DBMS_OUTPUT.put_line('N2:' || v_n2);
  END;

  c、刪除存儲過程的語法格式

• 使用DROP PROCEDURE命令，語法如下：

  DROP PROCEDURE [user.] Procedure_name

  5、存儲函數的調用和刪除

• 注意：本專題第3節存儲函數的創建后，Oracle數據庫中保存了4個存儲函數。

a、調用存儲函數

• 存儲過程和存儲函數的調用都是一樣，可分為：
  1、調用無參數的存儲函數；
  2、調用帶有輸入參數的存儲函數；
  3、調用帶有輸出參數的存儲函數；
  4、調用帶有輸入輸出參數的存儲函數。

• PL.SQL環境：

  --調用無參數的函數
  BEGIN
  dbms_output.put_line(first_func);
  END;
  -- 調用帶有輸入參數的函數
  BEGIN
  dbms_output.put_line('部門的工資總額：' || func1(&no));
  END;
  -- 調用帶有輸出參數的函數
  DECLARE
  v_name emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  v_salsum NUMBER;
  BEGIN
  v_salsum := func2(&no, v_name, v_sal);
  DBMS_OUTPUT.put_line('姓名：' || v_name);
  DBMS_OUTPUT.put_line('工資：' || v_sal);
  DBMS_OUTPUT.put_line('年收入：' || v_salsum);
  END;
  -- 調用帶有輸入輸出參數的函數
  DECLARE
  v_n1 NUMBER := 5;
  v_n2 NUMBER := 6;
  v_sum number;
  BEGIN
  v_sum := func3(v_n1, v_n2);
  DBMS_OUTPUT.put_line('n1的平方：' || v_n1);
  DBMS_OUTPUT.put_line('n2的平方：' || v_n2);
  DBMS_OUTPUT.put_line('n1和n2的平方和：' || v_sum);
  END;

  b、刪除函數過程

• 可以使用DROP FUNCTION命令，語法如下：

  DROP FUNCTION [user.] Function_name

  6、附加說明

• 參數默認值的問題：
• 過程和函數都可以在聲明過程或者函數參數時，使用DEFAULT關鍵字為輸入參數指定默認值。
• 示例代碼：求部門的年收入。（存儲函數）

  CREATE OR REPLACE FUNCTION func2
  (v_empno IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE)
  RETURN NUMBER
  IS 
          v_salsum NUMBER;
  BEGIN
          SELECT ename, sal, (sal + nvl(comm, 0)) * 12 INTO v_name, v_sal, v_salsum
          FROM emp WHERE empno = v_empno;
          RETURN v_salsum;
  EXCEPTION
          WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.put_line('沒有此員工');
          WHEN OTHERS THEN 
              DBMS_OUTPUT.put_line(SQLERRM);
  END;

• 調用此存儲函數：

  --調用
  DECLARE
      v_totalsal NUMBER;
  BEGIN
      v_totalsal := func5;
      DBMS_OUTPUT.put_line(v_totalsal);
  END;