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;