69、Oracle PL/SQL调用：匿名块、存储过程、函数、包（Package）、游标处理

说实话，PL/SQL 这东西，我早年刚接触时觉得挺别扭的。明明 SQL 就能查数据，干嘛还要搞一套编程语言？后来在项目中做复杂业务逻辑，才明白它的价值——把逻辑写在数据库里，减少网络往返，性能直接起飞。

今天咱们就聊聊 Oracle PL/SQL 的几种调用方式。我按自己的经验，把它们分成五个层次：匿名块、存储过程、函数、包、游标。每个都有它的用武之地。

一、匿名块：快速验证的利器

匿名块，说白了就是一段没有名字的 PL/SQL 代码。写完就扔，不存数据库。

我经常用它做两件事：一是快速测试某个逻辑对不对，二是在脚本里临时执行一段逻辑。

DECLARE
   v_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_count FROM employees;
   DBMS_OUTPUT.PUT_LINE('员工总数: ' || v_count);
END;
/

嗯，这里要注意：DBMS_OUTPUT 的输出，你得在客户端先开启 SET SERVEROUTPUT ON，不然啥也看不到。我刚开始就吃过这个亏，调试了半天以为代码写错了。

二、存储过程：封装业务逻辑

存储过程是 PL/SQL 的核心。它没有返回值，但可以通过 OUT 参数往外传数据。

我在项目中遇到过这样一个场景：每天凌晨要批量更新订单状态。用存储过程封装，定时任务一调就完事。

CREATE OR REPLACE PROCEDURE update_order_status (
   p_order_id   IN  NUMBER,
   p_new_status IN  VARCHAR2,
   p_result     OUT VARCHAR2
) IS
BEGIN
   UPDATE orders SET status = p_new_status WHERE order_id = p_order_id;
   
   IF SQL%ROWCOUNT = 0 THEN
      p_result := '订单不存在';
   ELSE
      p_result := '更新成功';
   END IF;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      p_result := '错误: ' || SQLERRM;
END;
/

调用方式很简单：

DECLARE
   v_res VARCHAR2(100);
BEGIN
   update_order_status(1001, '已发货', v_res);
   DBMS_OUTPUT.PUT_LINE(v_res);
END;
/

三、函数：必须返回一个值

函数和存储过程很像，区别在于函数必须有 RETURN 子句。说白了，函数就是「你给我参数，我还你一个结果」。

我一般用函数做计算或查询，比如根据员工 ID 算年终奖：

CREATE OR REPLACE FUNCTION calc_bonus (
   p_emp_id   IN  NUMBER,
   p_year     IN  NUMBER
) RETURN NUMBER IS
   v_salary   NUMBER;
   v_bonus    NUMBER;
BEGIN
   SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
   
   v_bonus := v_salary * 0.15;
   -- 假设每年按15%发奖金
   RETURN v_bonus;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN 0;
END;
/

调用时可以直接用在 SQL 里：

SELECT employee_id, calc_bonus(employee_id, 2024) AS bonus FROM employees;

你想想看，这比在应用层循环计算快了多少倍？

四、包（Package）：把相关功能打包

包是 PL/SQL 里我最喜欢的东西。它把相关的存储过程、函数、变量、游标打包在一起，就像一个工具箱。

包分两部分：包规范（声明）和包体（实现）。

先看包规范：

CREATE OR REPLACE PACKAGE emp_pkg IS
   -- 公共变量
   g_dept_id   NUMBER := 10;
   
   -- 函数声明
   FUNCTION get_emp_count RETURN NUMBER;
   
   -- 存储过程声明
   PROCEDURE list_emps_by_dept(p_dept_id IN NUMBER);
   
   -- 游标声明
   CURSOR c_emp_cur IS SELECT * FROM employees;
END emp_pkg;
/

再看包体：

CREATE OR REPLACE PACKAGE BODY emp_pkg IS
   
   FUNCTION get_emp_count RETURN NUMBER IS
      v_cnt NUMBER;
   BEGIN
      SELECT COUNT(*) INTO v_cnt FROM employees;
      RETURN v_cnt;
   END;
   
   PROCEDURE list_emps_by_dept(p_dept_id IN NUMBER) IS
   BEGIN
      FOR rec IN (SELECT * FROM employees WHERE department_id = p_dept_id) LOOP
         DBMS_OUTPUT.PUT_LINE(rec.last_name || ' - ' || rec.salary);
      END LOOP;
   END;
   
END emp_pkg;
/

调用时用 包名.成员名：

BEGIN
   DBMS_OUTPUT.PUT_LINE('员工总数: ' || emp_pkg.get_emp_count);
   emp_pkg.list_emps_by_dept(20);
END;
/

五、游标处理：逐行操作的艺术

游标，说白了就是指向结果集的一个指针。你用它一行一行地处理数据。

Oracle 里有三种游标：隐式游标、显式游标、REF CURSOR。

隐式游标是 Oracle 自动管理的，比如 SELECT INTO 就用了隐式游标。显式游标需要你手动声明、打开、提取、关闭。

我重点说说显式游标和 REF CURSOR。

5.1 显式游标

DECLARE
   CURSOR emp_cur IS 
      SELECT employee_id, last_name, salary 
      FROM employees 
      WHERE department_id = 50;
   
   v_emp_id   employees.employee_id%TYPE;
   v_name     employees.last_name%TYPE;
   v_salary   employees.salary%TYPE;
BEGIN
   OPEN emp_cur;
   LOOP
      FETCH emp_cur INTO v_emp_id, v_name, v_salary;
      EXIT WHEN emp_cur%NOTFOUND;
      
      DBMS_OUTPUT.PUT_LINE(v_emp_id || ' - ' || v_name || ' - ' || v_salary);
   END LOOP;
   CLOSE emp_cur;
END;
/

用 FOR 循环 更简洁，Oracle 会自动帮你打开和关闭游标：

BEGIN
   FOR rec IN (SELECT employee_id, last_name FROM employees) LOOP
      DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.last_name);
   END LOOP;
END;
/

5.2 REF CURSOR：动态游标

REF CURSOR 更灵活，你可以在运行时决定查询什么。我一般在做报表或动态查询时用它。

CREATE OR REPLACE PROCEDURE get_emps_by_dept (
   p_dept_id IN NUMBER,
   p_cursor  OUT SYS_REFCURSOR
) IS
BEGIN
   OPEN p_cursor FOR
      SELECT employee_id, last_name, salary 
      FROM employees 
      WHERE department_id = p_dept_id;
END;
/

调用方：

DECLARE
   v_cursor SYS_REFCURSOR;
   v_id     NUMBER;
   v_name   VARCHAR2(100);
   v_sal    NUMBER;
BEGIN
   get_emps_by_dept(50, v_cursor);
   
   LOOP
      FETCH v_cursor INTO v_id, v_name, v_sal;
      EXIT WHEN v_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name || ' - ' || v_sal);
   END LOOP;
   CLOSE v_cursor;
END;
/

六、知识体系总览

下面这张图，是我自己梳理的 PL/SQL 调用体系。你看一眼就能明白它们之间的关系。

七、总结一下

PL/SQL 的这五种调用方式，各有各的定位：

• 匿名块：临时用，不保存
• 存储过程：做事情，不返回值
• 函数：做计算，必须返回值
• 包：把相关功能打包，方便管理
• 游标：逐行处理结果集

我个人建议，在实际项目中优先用包来组织代码。哪怕一开始只有一个存储过程，也放进包里。等后面功能多了，你会发现这个决定有多明智。

好了，今天就聊到这儿。记住一点：PL/SQL 不是银弹，但在数据库端做逻辑处理时，它确实是最趁手的工具。

---