Oracle存储过程中如何用游标批量插入/更新多条记录？

错误原因 #

你遇到的PLS-00382: expression is of wrong type错误，根源在于错误地将SYS_REFCURSOR作为输入参数传递待处理的多行数据。SYS_REFCURSOR的设计初衷是从存储过程返回查询结果集，而非接收外部传入的数据集。当你在存储过程中执行OPEN P_NGTSHFTALL_CUR时，该参数的类型与可打开游标类型不匹配，因此触发类型错误。

另外代码还有两处小问题：

1. 存储过程名称拼写错误：INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS少了一个'T'，应改为INSERT_UPDATE_NIGHTSHIFTALLOWANCES_RECORDS
2. 判断id=''多余且错误：id是自增主键，类型应为数值型，不能与空字符串比较，会引发类型不匹配错误

最优解决方案：自定义集合+FORALL批量操作 #

Oracle中处理多行数据的高效方式是使用自定义集合类型配合FORALL语句，相比循环游标，批量操作能大幅提升性能，同时避免游标类型错误。

步骤1：创建自定义记录与集合类型 #

-- 创建与表结构匹配的记录类型
CREATE OR REPLACE TYPE NIGHTSHIFTALLOWANCE_REC AS OBJECT (
    ID             NUMBER,
    REQUESTNUMBER  VARCHAR2(100), -- 根据实际表字段类型调整
    SHIFTTYPE      VARCHAR2(50),
    STARTDATE      DATE,
    ENDDATE        DATE,
    SHIFTTIME      VARCHAR2(50)
);
/

-- 创建记录类型的集合
CREATE OR REPLACE TYPE NIGHTSHIFTALLOWANCE_TBL AS TABLE OF NIGHTSHIFTALLOWANCE_REC;
/

步骤2：重写存储过程 #

CREATE OR REPLACE PROCEDURE INSERT_UPDATE_NIGHTSHIFTALLOWANCES_RECORDS (
    P_NGTSHFTALL_TBL  IN  NIGHTSHIFTALLOWANCE_TBL,
    SPRESULT          OUT VARCHAR2,
    SPRESPONSECODE    OUT VARCHAR2,
    SPRESPONSEMESSAGE OUT VARCHAR2
) 
AS  
BEGIN
    -- 批量更新现有记录
    FORALL i IN 1..P_NGTSHFTALL_TBL.COUNT
        WHERE P_NGTSHFTALL_TBL(i).ID IS NOT NULL
        UPDATE NIGHTSHIFTALLOWANCES 
        SET REQUESTNUMBER = P_NGTSHFTALL_TBL(i).REQUESTNUMBER,
            SHIFTTYPE = P_NGTSHFTALL_TBL(i).SHIFTTYPE,
            STARTDATE = P_NGTSHFTALL_TBL(i).STARTDATE,
            ENDDATE = P_NGTSHFTALL_TBL(i).ENDDATE,
            SHIFTTIME = P_NGTSHFTALL_TBL(i).SHIFTTIME
        WHERE ID = P_NGTSHFTALL_TBL(i).ID;

    -- 批量插入新记录
    FORALL i IN 1..P_NGTSHFTALL_TBL.COUNT
        WHERE P_NGTSHFTALL_TBL(i).ID IS NULL
        INSERT INTO NIGHTSHIFTALLOWANCES(REQUESTNUMBER, SHIFTTYPE, STARTDATE, ENDDATE, SHIFTTIME)
        VALUES (P_NGTSHFTALL_TBL(i).REQUESTNUMBER,
                P_NGTSHFTALL_TBL(i).SHIFTTYPE,
                P_NGTSHFTALL_TBL(i).STARTDATE,
                P_NGTSHFTALL_TBL(i).ENDDATE,
                P_NGTSHFTALL_TBL(i).SHIFTTIME);

    COMMIT;
    SPRESULT := 'OK';
    SPRESPONSECODE := 'INSUPDNGTSHFTALL-001';
    SPRESPONSEMESSAGE := '夜班津贴记录插入/更新成功';       
    
EXCEPTION 
    WHEN OTHERS THEN
        ROLLBACK;
        SPRESULT := 'NOK';
        SPRESPONSECODE := 'INSUPDNGTSHFTALL-002';
        SPRESPONSEMESSAGE := SQLERRM;
END INSERT_UPDATE_NIGHTSHIFTALLOWANCES_RECORDS;
/

原游标方案的修复（不推荐） #

如果坚持使用游标方案，需调整参数设计：调用方负责打开游标并绑定数据，存储过程仅读取游标，而非在内部打开。修改后的代码如下：

CREATE OR REPLACE PROCEDURE INSERT_UPDATE_NIGHTSHIFTALLOWANCES_RECORDS (
    P_NGTSHFTALL_CUR   IN  SYS_REFCURSOR, -- 改为IN参数，调用方负责打开
    SPRESULT           OUT VARCHAR2,
    SPRESPONSECODE     OUT VARCHAR2,
    SPRESPONSEMESSAGE  OUT VARCHAR2
) 
AS  
    id             nightshiftallowances.id%TYPE;
    requestnumber  nightshiftallowances.requestnumber%TYPE;
    shifttype      nightshiftallowances.shifttype%TYPE;
    startdate      nightshiftallowances.startdate%TYPE;
    enddate        nightshiftallowances.enddate%TYPE;
    shifttime      nightshiftallowances.shifttime%TYPE;
BEGIN
    -- 移除OPEN语句，调用方已打开游标
    LOOP
        FETCH P_NGTSHFTALL_CUR INTO id, requestnumber, shifttype, startdate, enddate, shifttime;
        EXIT WHEN P_NGTSHFTALL_CUR%NOTFOUND;
        
        -- 移除id=''的判断，id为数值型无需与空字符串比较
        IF id IS NULL THEN
            INSERT INTO NIGHTSHIFTALLOWANCES(REQUESTNUMBER, SHIFTTYPE, STARTDATE, ENDDATE, SHIFTTIME) 
            VALUES (requestnumber, shifttype, startdate, enddate, shifttime);
        ELSE
            UPDATE NIGHTSHIFTALLOWANCES SET REQUESTNUMBER=requestnumber, SHIFTTYPE=shifttype, STARTDATE=startdate, ENDDATE=enddate, SHIFTTIME=shifttime 
            WHERE ID=id;
        END IF;
    END LOOP;
    COMMIT;
    SPRESULT:='OK';
    SPRESPONSECODE:='INSUPDNGTSHFTALL-001';
    SPRESPONSEMESSAGE:='夜班津贴记录插入/更新成功';       
    
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    SPRESULT:='NOK';
    SPRESPONSECODE:='INSUPDNGTSHFTALL-002';
    SPRESPONSEMESSAGE:=SQLERRM;
END INSERT_UPDATE_NIGHTSHIFTALLOWANCES_RECORDS;
/

注意：该方案性能远低于批量操作，仅适用于数据量极小的场景。

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