Need validation of my pl/sql code and need forall version of mine

Question

Team I have a table ace_1

STATUS CHARGE_1 CHARGE_2
N   5   20
Y   7   25
N   9   30
N   11  35
Y   13  40

Need to INSERT INTO table ace_2 two rows when STATUS=N for each CHARGE_1/ACTION=CREATE & CHARGE_2/ACTION=UPDATE and INSERT only one row when STATUS=N with ACTION=CREATE and CHARGES=CHARGE_1 as follows

ace_2

STATUS ACTION CHARGES
N   CREATE  5
N   UPDATE  20
Y   CREATE  7
N   CREATE  9
N   UPDATE  30
N   CREATE  11
N   UPDATE  35
Y   CREATE  13

My below procedure works fine. But is there any other option to fine tune the code with FORALL LOOP? The real time data will be in 0.1 to 1 Million. Performance need to be addressed.

PROCEDURE test1 (
        x_status_code   OUT VARCHAR2,
        x_error_message OUT VARCHAR2
    ) IS

        CURSOR cur_a IS
        SELECT
            *
        FROM
            ace_1
        WHERE
            1 = 1;

        TYPE ace_1_tbl_type IS
            TABLE OF cur_a%rowtype INDEX BY PLS_INTEGER;
        lvar_1 ace_1_tbl_type;
    BEGIN
        OPEN cur_a;
        LOOP
            FETCH cur_a
            BULK COLLECT INTO lvar_1 LIMIT 100;
            EXIT WHEN lvar_1.count = 0;
            FOR idx IN 1..lvar_1.count LOOP
                IF lvar_1(idx).status = 'Y' THEN
                    INSERT INTO ace_2 VALUES (
                        lvar_1(idx).status,
                        'CREATE',
                        lvar_1(idx).charge_1
                    );

                ELSIF lvar_1(idx).status = 'N' THEN
                    INSERT INTO ace_2 VALUES (
                        lvar_1(idx).status,
                        'CREATE',
                        lvar_1(idx).charge_1
                    );

                    INSERT INTO ace_2 VALUES (
                        lvar_1(idx).status,
                        'UPDATE',
                        lvar_1(idx).charge_2
                    );

                END IF;

                END IF;

            END LOOP;

        END LOOP;

        CLOSE cur_a;
         dbms_output.put_line('END WELL');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('INTO EXCEPTION' || x_error_message);
            x_status_code := sqlcode;
            x_error_message := sqlerrm;
            dbms_output.put_line('fine 123');
    END test1;

Answer 1

You can do this with a single insert statement, e.g.:

insert into ace_2 (status, action, charges)
with dummy as (select level id
               from   dual
               connect by level <= 2)
select a1.status,
       case when d.id = 1 then 'CREATE' else 'UPDATE' end action,
       case when d.id = 1 then a1.charge_1 else a1.charge_2 end charges
from   ace_1 a1
       inner join dummy d on a1.status = 'N' or (a1.status = 'Y' and d.id = 1);

This works by joining the rows in your ace_1 table to a set of data that contains 2 rows (created here in the dummy subquery using the connect by trick to generate a row for the number of specified levels) such that rows with a status of Y only match to the first dummy row, whereas status N rows match to both dummy rows.

Once you have that data, you simply insert that into the ace_2 table.

However, the data you provided doesn't have any columns that imply ordering or keys to link the sets of rows, so the results you get won't necessarily look like your expected output, as you can see from this db<>fiddle.

Assuming your actual data has those columns, it should be easy enough for you to apply the above technique, and then order your output accordingly.