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)         ISBEGIN         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 NUMBERIS             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 NUMBERIS         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 NUMBERASBEGIN    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 onSQL> 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 9ORA-06512: 在 line 1

• PL/SQL环境：

  -- 调用无参的存储过程BEGINfirst_proc;END;-- 调用带有输入参数的存储过程BEGINproc1(1234);END;-- 调用带有输出参数的存储过程DECLAREv_avgsal NUMBER;v_count NUMBER;BEGINPROC2(10, v_avgsal, v_count);DBMS_OUTPUT.put_line('平均工资:' || v_avgsal);DBMS_OUTPUT.put_line('总人数：' || v_count);END;--调用带有输入输出参数的存储过程DECLAREv_n1 NUMBER := 5;v_n2 NUMBER := 10;BEGINPROC3(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环境：

  --调用无参数的函数BEGINdbms_output.put_line(first_func);END;-- 调用带有输入参数的函数BEGINdbms_output.put_line('部门的工资总额：' || func1(&no));END;-- 调用带有输出参数的函数DECLAREv_name emp.ename%TYPE;v_sal emp.sal%TYPE;v_salsum NUMBER;BEGINv_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;-- 调用带有输入输出参数的函数DECLAREv_n1 NUMBER := 5;v_n2 NUMBER := 6;v_sum number;BEGINv_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 NUMBERIS         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;