189 8069 5689

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。
      Oracle专题14之存储过程与函数
  • 调用存储过程 
    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个存储过程。
    Oracle专题14之存储过程与函数

    10余年的四方台网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。成都全网营销的优势是能够根据用户设备显示端的尺寸不同，自动调整四方台建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。成都创新互联公司从事“四方台网站设计”,“四方台网站推广”以来，每个客户项目都认真落实执行。

  • 调用无参数的存储过程：直接引用过程名即可。
  • 调用带有输入参数的存储过程。
  • 调用带有输出参数的参数过程。

  • 调用带有输入输出参数的存储过程。

  • 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;

网站题目：Oracle专题14之存储过程与函数
网址分享：http://cdxtjz.com/article/jcsehc.html

其他资讯