Oracle SQL - How do I write an insert statement that is conditional and looping?
Context: I have two tables: markettypewagerlimitgroups (mtwlg) and lock indices (sdi). When mtwlg is created, 2 rows are created in the sdi table that are associated with mtwlg - each row with the same rows of values 2, id and another field (let's call its column X), which should contain 0 for one row and 1 for the other. There was a bug in our codebase that prevented this automatically, so any mtwlg generated during the time the bug occurred has no sdi associated with it, which causes NPEs in various places.
To fix this, the patch must be looped through the mtwlg table and for each identifier, the sdi table must be searched for two related rows. If lines are present, do nothing; if there is only 1 row, check if F is 0 or 1 and insert the row with a different value; if neither line is present, insert both. This needs to be done for each mtwlg, and a unique identifier must be inserted as well.
pseudocode:
For each market type wager limit group ID
Check if there are 2 rows with that id in the stake distributions table, 1 where column X = 0 and one where column X = 1
if none
create 2 rows in the stake distributions table with unique id's; 1 for each X value
if one
create the missing row in the stake distributions table with a unique id
if 2
do nothing
If it helps at all, the patch will be applied using lipibase.
Anyone with any advice or thoughts on how and how this could be written in the SQL / Liquibase patch?
Thanks in advance, let me know any other information you need.
EDIT:
Actually I was just advised to do this with PL / SQL, do you have any thoughts / suggestions on this? Thanks again.
Oooooh, great job for MERGE
.
Here's your pseudo code again:
For each market type wager limit group ID Check if there are 2 rows with that id in the stake distributions table, 1 where column X = 0 and one where column X = 1 if none create 2 rows in the stake distributions table with unique id's; 1 for each X value if one create the missing row in the stake distributions table with a unique id if 2 do nothing
Here's an option MERGE
(still pseudocode as I don't know how your data really looks like):
MERGE INTO stake_distributions d
USING (
SELECT limit_group_id, 0 AS x
FROM market_type_wagers
UNION ALL
SELECT limit_group_id, 1 AS x
FROM market_type_wagers
) t
ON (
d.limit_group_id = t.limit_group_id AND d.x = t.x
)
WHEN NOT MATCHED THEN INSERT (d.limit_group_id, d.x)
VALUES (t.limit_group_id, t.x);
No loops, no PL / SQL, no conditionals, just pretty SQL.
A nice alternative suggested by Boneist in the comments is using CROSS JOIN
, rather than UNION ALL
in a suggestion USING
, which is likely to work better (untested):
MERGE INTO stake_distributions d
USING (
SELECT w.limit_group_id, x.x
FROM market_type_wagers w
CROSS JOIN (
SELECT 0 AS x FROM DUAL
UNION ALL
SELECT 1 AS x FROM DUAL
) x
) t
ON (
d.limit_group_id = t.limit_group_id AND d.x = t.x
)
WHEN NOT MATCHED THEN INSERT (d.limit_group_id, d.x)
VALUES (t.limit_group_id, t.x);
The answer is no. There is absolutely no need to iterate over anything - you can do it in one insert. All you have to do is identify the lines that are missing and then you just need to add them.
Here's an example:
drop table t1;
drop table t2;
drop sequence t2_seq;
create table t1 (cola number,
colb number,
colc number);
create table t2 (id number,
cola number,
colb number,
colc number,
colx number);
create sequence t2_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
insert into t1 values (1, 10, 100);
insert into t2 values (t2_seq.nextval, 1, 10, 100, 0);
insert into t2 values (t2_seq.nextval, 1, 10, 100, 1);
insert into t1 values (2, 20, 200);
insert into t2 values (t2_seq.nextval, 2, 20, 200, 0);
insert into t1 values (3, 30, 300);
insert into t2 values (t2_seq.nextval, 3, 30, 300, 1);
insert into t1 values (4, 40, 400);
commit;
insert into t2 (id, cola, colb, colc, colx)
with dummy as (select 1 id from dual union all
select 0 id from dual)
select t2_seq.nextval,
t1.cola,
t1.colb,
t1.colc,
d.id
from t1
cross join dummy d
left outer join t2 on (t2.cola = t1.cola and d.id = t2.colx)
where t2.id is null;
commit;
select * from t2
order by t2.cola;
ID COLA COLB COLC COLX
---------- ---------- ---------- ---------- ----------
1 1 10 100 0
2 1 10 100 1
3 2 20 200 0
5 2 20 200 1
7 3 30 300 0
4 3 30 300 1
6 4 40 400 0
8 4 40 400 1
If the processing logic is too coarse to be encapsulated in a single SQL statement, you may need to resort to cursor for loops and row types - this basically allows you to do the following things:
DECLARE
r_mtwlg markettypewagerlimitgroups%ROWTYPE;
BEGIN
FOR r_mtwlg IN (
SELECT mtwlg.*
FROM markettypewagerlimitgroups mtwlg
)
LOOP
-- do stuff here
-- refer to elements of the current row like this
DBMS_OUTPUT.PUT_LINE(r_mtwlg.id);
END LOOP;
END;
/
Obviously, you can nest another loop inside this that ends up in the table stakedistributionindicators
, but I'll leave that as an exercise for you. You could also join a stakedistributionindicators
couple of times in that first cursor so that you only return rows that don't already have x = 1 and x = 0, again you can probably do this for yourself.
If you prefer to write your logic in Java over PL / SQL, Liquibase allows you to create custom changes . The custom change points to a Java class that you write that can do whatever logic you need. A simple example can be found here