如何创建可针对特定参数返回多行的Oracle PL/SQL函数（Oracle界面调用）

方法1：自定义集合类型+表函数 #

这种方式适合固定返回结构的场景，先定义对应数据类型，再通过函数返回集合，最后用TABLE()函数解析结果。

步骤1：创建自定义类型 #

-- 定义单条记录的结构
CREATE OR REPLACE TYPE emp_record_type AS OBJECT (
    emp_id NUMBER,
    emp_name VARCHAR2(100),
    job_title VARCHAR2(100),
    salary NUMBER
);
/

-- 定义存储多条记录的嵌套表类型
CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_record_type;
/

步骤2：编写PL/SQL函数 #

CREATE OR REPLACE FUNCTION get_employee_details(p_emp_id NUMBER)
RETURN emp_table_type
IS
    v_emp_table emp_table_type := emp_table_type();
BEGIN
    -- 批量查询数据并存入集合
    SELECT emp_record_type(employee_id, first_name || ' ' || last_name, job_id, salary)
    BULK COLLECT INTO v_emp_table
    FROM employees
    -- 可按需修改条件，比如返回该员工的所有下属：WHERE manager_id = p_emp_id
    WHERE employee_id = p_emp_id;
    
    RETURN v_emp_table;
END;
/

调用方式 #

在Oracle界面（SQL*Plus、PL/SQL Developer等）直接执行：

SELECT * FROM TABLE(get_employee_details(100));

方法2：返回SYS_REFCURSOR（游标） #

这种方式无需预先定义类型，直接返回游标，灵活度高，适合快速开发和界面工具调用。

编写PL/SQL函数 #

CREATE OR REPLACE FUNCTION get_employee_cursor(p_emp_id NUMBER)
RETURN SYS_REFCURSOR
IS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name || ' ' || last_name AS emp_name, job_id, salary
        FROM employees
        WHERE employee_id = p_emp_id;
        -- 按需修改条件返回关联多行数据
    
    RETURN v_cursor;
END;
/

调用方式 #

• 在PL/SQL Developer中：直接执行函数，工具会自动解析游标并展示结果
• 在SQL*Plus中：

VAR rc REFCURSOR;
EXEC :rc := get_employee_cursor(100);
PRINT rc;

注意事项 #

• 若返回关联数据（如员工的订单、下属），只需修改函数内SELECT语句的WHERE条件
• 自定义集合类型属于数据库对象，创建后可复用；SYS_REFCURSOR是内置类型，无需额外创建

内容的提问来源于stack exchange，提问作者Eyei