Oracle 存储过程详解（procedure）

1 概述

1. 存储过程是什么：事先经过编译并存储在数据库中的一套 sql 语句

2. 存储过程的优点
   (1) 提高 sql 执行效率   ：'预编译' 后存储于内存中，故使用时，无需再次编译
   (2) 减少 '网络流量(I/0)'：减少 sql 与服务器之间的上下文交换
   (3) 提高系统的 '安全性'  ：减少 sql 注入的危险、权限控制

3. 存储过程的缺点
   (1) 增加数据库服务器的 '负荷'

2 语法

2.1 创建

create or replace procedure schema.过程名(变量名 in|out 数据类型) 
is | as -- 此处 is 和 as 等同
   声明语句段;
begin
   执行语句段;
exception 
   异常处理语句段;
end 过程名;

示例：两数相除并输出结果

-- *****************************************************************
-- 功能说明：计算两数相除
-- 参数说明:	i_a      除数
--         	i_b      被除数
--         	o_result 结果 
--         	io_flag  程序执行标识
-- 创建时间：2021-03-16 yoyo
-- 最近更新：
-- *****************************************************************
create or replace procedure scott.p_procedure_demo(i_a      in number,
                                                   i_b      in number,
                                                   o_result out number,
                                                   io_flag  in out varchar2) is
  v_message varchar2(30) := '存储过程模板';
begin
  io_flag := 'Y';

  -- i_b = 0 时，可验证报错
  o_result := i_a / i_b;

  -- 格式调整  1/2 = .5 => 0.5
  o_result := regexp_replace(o_result, '^\.', '0.');
exception
  when others then
    io_flag := 'N';
    dbms_output.put_line(sqlcode || ', ' || sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/

参数说明（in | out）：默认 in，可省略

1. 无参数时: 不用带 '()'
   (1) scott.p_procedure_demo

2. 有参数时: '固定参数类型' 或 '动态参数类型'（推荐，不用考虑变量的数据类型） 
   (1) 仅有入参 in：默认，可以省略
       scott.p_procedure_demo(p_ename in varchar2)
       scott.p_procedure_demo(p_ename in scott.emp.ename%type)
       
   (2) 仅有出参 out：不可以省略
       scott.p_procedure_demo(p_ename out scott.emp.ename%type)

   (3) 既有入参 in，也有出参 out
       -- 多个参数时
       scott.p_procedure_demo(p_empno in scott.emp.empno%type,
                              p_ename out scott.emp.ename%type)
       -- 一个参数时：既是入参，也是出参
       scott.p_procedure_demo(p_empno in out scott.emp.empno%type)

2.2 调用

1. 在 pl/sql 中调用存储过程('任何情况均适用')
   declare
   begin
      procedure_name; -- 若有参，则加上 "()"
   end;

2. 在命令窗口(请注意：仅在 '无参' 或 '仅有入参' 时适用)
   exec procedure_name;
   execute procedure_name; -- exec 是 execute 的缩写

   call procedure_name;

   说明：exec 和 call 的区别：
   (1) exec 只能在命令窗口执行
   (2) call 即可以在命令窗口执行又可以在 pl/sql 程序窗口中执行
       但必须有括号，即使没有参数

2.3 修改、删除

1. create or replace... -- 支持重复操作

2. drop procedure scott.p_procedure_demo; -- 删除

3 Debug

1. 选中过程名，'添加调试信息'
   -- 若已添加，则跳过此步骤（若没有，则不会进入该过程）
   
2. 选择 '测试'，进入 '测试窗口'

3. 调试（Debug）

步骤1：任选一个过程名，‘添加调试信息’，若已勾选，则跳过
步骤2： 选择 ‘测试’，进入 '测试窗口’

步骤3：调试（Debug）