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;
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.