oracle 存储过程 返回结果集

1. 存储过程介绍

存储过程是一种封装了一组 SQL 语句的模块化 PL/SQL 代码块。存储过程可以在应用程序中通过引用它的名字来调用，可以减少 SQL 语句的重复，提高应用程序的性能。

在 Oracle 中，存储过程由三种类型的语句组成：声明语句、执行过程语句和控制语句。存储过程中可以包含变量、常量、游标和异常处理等特性。

2. 存储过程返回结果集

Oracle 存储过程可以返回多种类型的结果集，比如 REF CURSOR、PL/SQL 表等。其中，REF CURSOR 是一种指向结果集的游标，可以在存储过程内打开并返回，也可以在应用程序中引用。

2.1 返回 REF CURSOR

返回 REF CURSOR 的示例代码如下：

CREATE OR REPLACE PROCEDURE get_emp_list
(
    p_deptno IN NUMBER,
    p_refcur OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN p_refcur FOR
    SELECT * FROM emp WHERE deptno = p_deptno;
END;

上述代码定义了一个名为 get_emp_list 的存储过程，接收一个部门编号作为输入参数，返回一个指向该部门所有员工信息的 REF CURSOR。

在应用程序中，可以引用该存储过程返回的 REF CURSOR，示例代码如下：

DECLARE
    emp_list SYS_REFCURSOR;
    emp_rec emp%ROWTYPE;
BEGIN
    get_emp_list(10, emp_list); -- 调用存储过程
    LOOP
        FETCH emp_list INTO emp_rec;
        EXIT WHEN emp_list%NOTFOUND;
        -- 处理每条记录
    END LOOP;
    CLOSE emp_list;
END;

上述代码中，引用了存储过程 get_emp_list 的返回结果 REF CURSOR，使用 FETCH 语句逐条读取记录。

2.2 返回 PL/SQL 表

示例代码如下：

CREATE OR REPLACE PROCEDURE get_emp_list
(
    p_deptno IN NUMBER,
    p_emp_list OUT SYS.ODCIVARCHAR2LIST
)
AS
BEGIN
    SELECT ename
    BULK COLLECT INTO p_emp_list
    FROM emp
    WHERE deptno = p_deptno;
END;

上述代码定义了一个名为 get_emp_list 的存储过程，接收一个部门编号作为输入参数，返回该部门所有员工姓名的 PL/SQL 表。

在应用程序中，可以引用该存储过程返回的 PL/SQL 表，示例代码如下：

DECLARE
    emp_list SYS.ODCIVARCHAR2LIST;
BEGIN
    get_emp_list(10, emp_list); -- 调用存储过程
    FOR i IN emp_list.FIRST..emp_list.LAST LOOP
        -- 处理每个姓名
    END LOOP;
END;

上述代码中，引用了存储过程 get_emp_list 的返回结果 PL/SQL 表，使用 FOR 循环逐个遍历表中每个元素。

3. 总结

存储过程是 Oracle 中一种常见的封装 SQL 语句的方式，可以提高应用程序的性能和可维护性。存储过程可以返回多种类型的结果集，包括 REF CURSOR 和 PL/SQL 表等。在应用程序中，可以引用存储过程返回的结果集，进一步处理和展示数据。