Oracle PLSQL创建员工历史查询存储过程报错及参数默认值设置问题

原代码报错核心原因 #

1. 未给p_rws参数设置默认值20，单参数调用语法不合法
2. 多行SELECT查询未使用BULK COLLECT INTO关键字，且INTO后直接填写类型名而非实际声明的变量
3. 存储过程未定义输出载体，查询结果无法对外返回

最优修复方案（无需额外创建全局对象/表类型，逻辑全在存储过程内） #

我们直接用Oracle自带的SYS_REFCURSOR类型返回结果集，不需要预先创建access_history_obj和access_history_table全局类型，完全满足你希望逻辑本地化的需求：

CREATE OR REPLACE PROCEDURE LAST_EMPLOYEE_HISTORY( 
    p_employee_id IN NUMBER,
    p_rws IN NUMBER DEFAULT 20, -- 设置默认返回20条
    p_result OUT SYS_REFCURSOR -- 输出游标承载结果集
)
AS
BEGIN
    OPEN p_result FOR
    WITH rws AS (
        SELECT e.employee_id, 
            e.first_name,
            e.last_name,
            e.card_num,
            l.location_id,
            l.location_name,
            a.access_date,
            ROW_NUMBER() OVER (
                PARTITION BY e.employee_id
                ORDER BY a.access_date DESC
            ) rn  
        FROM employees e
        JOIN access_history a ON a.employee_id = e.employee_id
        JOIN locations l ON l.location_id = a.location_id
    )
    SELECT employee_id, 
        first_name,
        last_name,
        card_num,
        location_id,
        location_name,
        access_date
    FROM rws 
    WHERE employee_id = p_employee_id 
    AND rn <= p_rws
    ORDER BY access_date DESC;
END;
/

调用方法 #

在SQL*Plus、PL/SQL Developer命令窗口等环境可直接执行以下代码查看结果：

-- 调用1：只传员工ID，默认返回最近20条记录
VAR res REFCURSOR;
EXEC LAST_EMPLOYEE_HISTORY(1, :res);
PRINT res;

-- 调用2：传员工ID+返回行数，返回指定条数记录
VAR res REFCURSOR;
EXEC LAST_EMPLOYEE_HISTORY(1, 50, :res);
PRINT res;

兼容已创建全局类型的方案（BULK COLLECT实现） #

如果你要沿用之前创建的全局对象和表类型，修复后的代码如下：

CREATE OR REPLACE PROCEDURE LAST_EMPLOYEE_HISTORY( 
    p_employee_id IN NUMBER,
    p_rws IN NUMBER DEFAULT 20,
    p_result OUT access_history_table -- 输出你定义的表类型
)
AS
BEGIN
    WITH rws AS (
        SELECT e.employee_id, 
            e.first_name,
            e.last_name,
            e.card_num,
            l.location_id,
            l.location_name,
            a.access_date,
            ROW_NUMBER() OVER (
                PARTITION BY e.employee_id
                ORDER BY a.access_date DESC
            ) rn  
        FROM employees e
        JOIN access_history a ON a.employee_id = e.employee_id
        JOIN locations l ON l.location_id = a.location_id
    )
    SELECT access_history_obj(
        employee_id, 
        first_name,
        last_name,
        card_num,
        location_id,
        location_name,
        access_date
    ) 
    BULK COLLECT INTO p_result -- 批量插入集合
    FROM rws 
    WHERE employee_id = p_employee_id 
    AND rn <= p_rws
    ORDER BY access_date DESC;
END;
/

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