I am trying to write stored procedure to insert OR update multiple rows in a table with single stored procedure invocation. Very high level requirement is that,

1. User will see on UI the records that are there in the NIGHTSHIFTALLOWANCES table and then will update these records
2. User will add multiple new records on the UI
3. User will add multiple new records and will update existing records

Depending on user action I need to either insert or update the record in NIGHTSHIFTALLOWANCES table. Here, I am planning to use the SYS_REFCURSOR to get all records from UI and then loop on that cursor to insert/update records. Please feel free to suggest a better way of doing it if there is any. Below is the stored proc I came up with, however I am facing 'PLS-00382: expression is of wrong type' at line number 15 while opening cursor. Here, 'NIGHTSHIFTALLOWANCES.ID' is a primary key which is auto incremented.

CREATE OR REPLACE PROCEDURE INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS (
    P_NGTSHFTALL_CUR   IN OUT  SYS_REFCURSOR,
    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 P_NGTSHFTALL_CUR;
        LOOP
            FETCH P_NGTSHFTALL_CUR INTO id, requestnumber, shifttype, startdate, enddate, shifttime;
            EXIT WHEN P_NGTSHFTALL_CUR%NOTFOUND;
            IF id IS NULL OR id='' 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:='NIGHT SHIFT ALLOWANCE RECORD INSER/UPDATE IS SUCCESSFUL';       
        
        EXCEPTION WHEN OTHERS THEN
            SPRESULT:='NOK';
            SPRESPONSECODE:='INSUPDNGTSHFTALL-002';
            SPRESPONSEMESSAGE:=SQLERRM;

END INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS;

CodePudding user response：

Error you got is due to OPEN P_NGTSHFTALL_CUR; - you don't open ref cursor here. Appart from that, is ref cursor really IN OUT? You aren't returning anything; should be just IN.

As of your code: merge might substitute separate INSERT and UPDATE (and you don't need IF-THEN-ELSE in that case.

Something like this:

CREATE OR REPLACE PROCEDURE INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS (
   P_NGTSHFTALL_CUR   IN     SYS_REFCURSOR,
   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
   LOOP
      FETCH P_NGTSHFTALL_CUR
         INTO id,
              requestnumber,
              shifttype,
              startdate,
              enddate,
              shifttime;

      EXIT WHEN P_NGTSHFTALL_CUR%NOTFOUND;

      MERGE INTO NIGHTSHIFTALLOWANCES a
           USING (SELECT id,
                         requestnumber,
                         shifttype,
                         startdate,
                         enddate,
                         shifttime
                    FROM DUAL) b
              ON (a.id = b.id)
      WHEN MATCHED
      THEN
         UPDATE SET a.requestnumber = b.requestnumber,
                    a.shifttype = b.shifttype,
                    a.startdate = b.startdate,
                    a.enddate = b.enddte,
                    a.shifttime = b.shifttime
      WHEN NOT MATCHED
      THEN
         INSERT     (REQUESTNUMBER,
                     SHIFTTYPE,
                     STARTDATE,
                     ENDDATE,
                     SHIFTTIME)
             VALUES (b.REQUESTNUMBER,
                     b.SHIFTTYPE,
                     b.STARTDATE,
                     b.ENDDATE,
                     b.SHIFTTIME);
   END LOOP;

   COMMIT;
   SPRESULT := 'OK';
   SPRESPONSECODE := 'INSUPDNGTSHFTALL-001';
   SPRESPONSEMESSAGE :=
      'NIGHT SHIFT ALLOWANCE RECORD INSER/UPDATE IS SUCCESSFUL';
EXCEPTION
   WHEN OTHERS
   THEN
      SPRESULT := 'NOK';
      SPRESPONSECODE := 'INSUPDNGTSHFTALL-002';
      SPRESPONSEMESSAGE := SQLERRM;
END INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS;