Oracle存储过程完整语法详解与实战示例

远山来客

发布时间：2026-10-14| 5人浏览过

原创

本文详细介绍Oracle存储过程的编写规范，从基础骨架到复杂的游标遍历、自定义类型（类数组）应用，再到包含事务控制与异常处理的完整案例，帮助开发者掌握PL/SQL核心语法。

结果或目标

了解Oracle存储过程的基本结构、参数类型、控制流语句及游标使用方法，能够独立编写具备数据处理和异常捕获能力的存储过程。

准备条件

已安装Oracle数据库并拥有相应权限。
熟悉SQL基本查询语句。
理解PL/SQL块的基本概念。

真实操作顺序

第1步：创建存储过程基础骨架

目的是创建一个空壳存储过程，用于验证语法结构和权限。

入口：Oracle SQL编辑器。
动作：使用 CREATE OR REPLACE PROCEDURE 声明过程名，使用 AS 或 IS 引入PL/SQL体，在 BEGIN...END 之间至少保留一条语句（如 NULL;）。
完成状态：执行脚本无报错，存储过程创建成功。

CREATE OR REPLACE PROCEDURE 存储过程名
IS
BEGIN
  NULL;
END;

第2步：定义输入与输出参数

目的是让存储过程接收外部数据并返回结果。

入口：过程名后的括号内。
动作：
- param IN type：传入参数，只读。
- param OUT type：输出参数，可在过程内赋值并传回调用方。
- 类型可使用任意Oracle合法类型（如 VARCHAR2, NUMBER）。

CREATE OR REPLACE PROCEDURE 存储过程名 (
    param1 IN VARCHAR2,
    param2 OUT NUMBER
) AS ...

第3步：变量声明与赋值

目的是在过程内部保存临时计算结果。

入口：IS 之后，BEGIN 之前的声明区。
动作：
- 定义变量： 变量名类型(长度); 例如 vs_msg VARCHAR2(4000);
- 赋值方式： v_var := value; 或使用 SELECT ... INTO ... 从数据库查询结果赋值。

-- 简单赋值
vs_ym_beg := '202301';

-- 查询赋值
SELECT COUNT(*) INTO v_count FROM table_a WHERE id = p_id;

第4步：实现逻辑判断与循环

目的是根据条件执行不同分支或重复执行SQL语句。

判断语句 IF
格式： IF condition THEN ... ELSIF ... ELSE ... END IF;

IF x > 0 THEN
  x := 0 - x;
ELSIF x = 0 THEN
  x := 1;
ELSE
  RAISE NO_DATA_FOUND; -- 触发预定义异常
END IF;

For循环（遍历集合）
格式： FOR variable IN collection LOOP ... END LOOP;
注意：Oracle中数组本质为索引表，起始索引通常为1。

FOR rec IN cursor_name LOOP
  DBMS_OUTPUT.PUT_LINE(rec.name);
END LOOP;

While循环
格式： WHILE condition LOOP ... END LOOP;

WHILE i < 10 LOOP
  i := i + 1;
END LOOP;

第5步：处理结果集（游标 CURSOR）

目的是高效遍历多行查询结果。

静态游标 (Strongly Typed)
在声明区定义，不可作为参数传递。

CURSOR cur_1 IS SELECT col1, col2 FROM table_b;

强类型REF CURSOR (Strongly Typed Ref Cursor)
需关联特定记录类型，可作为参数传递。

-- 声明带类型的Ref Cursor
TYPE t_ref_cursor IS REF CURSOR RETURN table_type%ROWTYPE;

-- 使用
OPEN cur_variable FOR SELECT ...;

弱类型REF CURSOR (Weakly Typed SYS_REFCURSOR)
最常用，可返回任何查询结果，适合做输出参数。

CREATE OR REPLACE PROCEDURE get_data(out_cur OUT SYS_REFCURSOR) IS
BEGIN
  OPEN out_cur FOR SELECT * FROM target_table;
END;

遍历Ref Cursor通常采用 Loop 结构：

LOOP
  FETCH cursor_name INTO var1, var2;
  EXIT WHEN cursor_name%NOTFOUND;
  -- 处理逻辑
END LOOP;

第6步：模拟数组功能（自定义Type与Package）

目的是在PL/SQL中处理列表数据。Oracle没有原生数组，需使用嵌套表（Nested Table）或索引表（Index-by Table）替代。

定义类型（建议放在Package中管理）：

CREATE OR REPLACE PACKAGE my_package IS
  TYPE record_info IS RECORD (
      name  VARCHAR2(20),
      value NUMBER
  );
  -- Index by Binary_Integer 创建的索引表类似数组，无需初始化即可赋值
  TYPE arr_info IS TABLE OF record_info INDEX BY BINARY_INTEGER;
END my_package;

使用：

DECLARE
  my_arr my_package.arr_info;
BEGIN
  my_arr(1).name := 'Test';
  my_arr(1).value := 100;
END;

第7步：添加异常处理与事务控制

目的是保证数据一致性及便于调试。

操作：
- 关键操作前后配合 COMMIT; 或 ROLLBACK;。
- 使用 EXCEPTION WHEN OTHERS THEN 捕获未知错误。

BEGIN
  -- 业务逻辑
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- 记录错误日志
    INSERT INTO error_log(proc_name, err_msg) VALUES('my_proc', SQLERRM);
    ROLLBACK;
    RAISE; -- 或者RETURN结束
END;