PL/SQL的存储过程和函数

全栈精英 2021-08-25

1129

（本文以Oracle19c的Scott表为例）

Oracle的存储过程与函数是命名的PL/SQL块，被编译后存储在数据库中，以备重用

存储过程和函数区别是：函数有返回值，而过程没有返回值

创建过程

格式：

 CREATE [OR REPLACE] PROCEDURE 存储过程名
 (
     [参数1 [ IN | OUT | IN OUT ]] 数据类型 [DEFAULT 默认值1],
       [参数2 [ IN | OUT | IN OUT ]] 数据类型 [DEFAULT 默认值2]],
    ......
       [参数n [ IN | OUT | IN OUT ]] 数据类型 [DEFAULT 默认值n]
 )
 [ AUTHID DEFINER | CURRENT_USER ]
 { IS | AS }
   <声明部分> 
 BEGIN
   <执行部分>
 EXCEPTION
   <可选的异常错误处理程序>
 END 存储过程名;

说明：

IN 输入型的参数，只能将实参传递给形参，在函数内部，只能读不能写
OUT 输出型的参数，该形参的初始值总是NULL，但在函数内部可以被读或写
IN OUT 输入和输出型的参数，具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量

例：使用存储过程删除指定的员工

CREATE OR REPLACE PROCEDURE sp_delemp -- 创建或替换存储过程  
 (
     v_empno IN emp.empno%TYPE         -- 存储过程的输入参数：员工编号
 ) 
 AS                                    -- 声明部分
     no_result EXCEPTION;              -- 声明自定义异常
 BEGIN                                 -- 过程开始
    -- 删除员工
    DELETE FROM emp WHERE empno = v_empno;
    -- 判断隐式游标删除是否成功
    IF SQL%NOTFOUND THEN
       -- 如果没有成功，抛出自定义异常
       RAISE no_result;
    END IF;
    -- 删除成功打印提示
    DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!');
 -- 异常处理
 EXCEPTION
    -- 处理自定义异常
    WHEN no_result THEN
       DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
    -- 处理其它可能的异常
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
 END sp_delemp;

调用存储过程

格式：

 EXEC 过程名( 实参1, 实参2 … );

例：调用 sp_delemp 存储过程

 EXEC sp_delemp(1739);

删除存储过程

格式：

 DROP 过程名;

例：删除 sp_delemp 存储过程

 DROP sp_delemp;

输出参数的用法

例：创建存储过程并调用：计算指定部门的工资总和，并统计其中的职工数量

CREATE OR REPLACE PROCEDURE sp_empcount
 (
     dept_no NUMBER DEFAULT 10,  -- 部门号：默认值10 （输入参数）
     sal_sum OUT NUMBER,         -- 工资总和（输出参数）
     emp_count OUT NUMBER        -- 员工总数（输出参数）
 )
 AS
 BEGIN
   -- 统计查询工资总和和员工总数，保存到输出参数
   SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
   FROM emp WHERE deptno = dept_no;
 -- 异常处理
 EXCEPTION
    -- 处理查询的异常
    WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
    -- 处理其它异常
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
 END sp_empcount;
 
 -- 调用 sp_empcount 过程
 DECLARE
   V_num NUMBER;                 -- 声明变量1：人数总和
   V_sum NUMBER(8, 2);           -- 声明变量2：工资总和
 BEGIN
   -- 调用存储过程
   sp_empcount (30, v_sum, v_num);
   -- 打印变量的值（注意：由 sp_empcount 存储过程的输出参数赋予的值）
   DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num);
 END;

创建函数

函数在数据库中分为预定义函数和自定义函数，此处为自定义函数的写法

格式：

 CREATE [OR REPLACE] FUNCTION 函数名
 (
     [参数1 [ IN | OUT | IN OUT ]] 数据类型 [DEFAULT 默认值1],
       [参数2 [ IN | OUT | IN OUT ]] 数据类型 [DEFAULT 默认值2]],
    ......
       [参数n [ IN | OUT | IN OUT ]] 数据类型 [DEFAULT 默认值n]
 )
  [ AUTHID DEFINER | CURRENT_USER ]
 RETURN 返回值类型 
  IS | AS
     <类型.变量的声明部分> 
 BEGIN
     执行部分
     RETURN 返回值
 EXCEPTION
     异常处理部分
 END 函数名;

注意：只能为输入参数设置默认值，而不能为输入/输出参数设置默认值

例：创建函数 fun_demo 返回个人信息

CREATE OR REPLACE FUNCTION demo_fun
(
  Name VARCHAR2,
  Age INTEGER,
  Sex VARCHAR2 DEFAULT '男'
)
  RETURN VARCHAR2 
AS
  V_var VARCHAR2(32);
BEGIN
  V_var := name||'：'||TO_CHAR(age)||'岁.'||sex;
  RETURN v_var;
END;

调用函数

在函数调用时，如果没有为参数提供实际参数值，函数将使用该参数的默认值

格式：

位置表示法: 在调用时按形参的排列顺序，依次写出实参的名称，而将形参与实参关联起来传递
函数名(参数值1 [,参数值2 …])

名称表示法: 即在调用时按形参的名称与实参的名称，写出实参对应的形参，而将形参与实参关联起来传递
函数名(参数名 => 参数值 [,…])

组合传递: 即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数

例：调用函数 fun_demo

DECLARE
 var VARCHAR(32);
BEGIN
 Var := demo_fun('张三', sex => '女', age => 20);
 DBMS_OUTPUT.PUT_LINE(var);
END;

删除函数

格式：

DROP FUNCTION 函数名;

例：删除 demo_fun 函数

DROP FUNCTION demo_fun;

数据库

文章转载自全栈精英，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。