Oracle SQL——如何编写一个有条件且循环的insert语句?

Context: I have two tables: markettypewagerlimitgroups (mtwlg) and stakedistributionindicators (sdi). When a mtwlg is created, 2 rows are created in the sdi table which are linked to the mtwlg - each row with the same values bar 2, the id and another field (let's call it column X) which must contain a 0 for one row and 1 for the other. There was a bug present in our codebase which prevented this happening automatically, so any mtwlg's created during the time that bug was present do not have the related sdi's, causing NPE's in various places.

上下文:我有两个表:markettypewagerlimitgroups (mtwlg)和stakedistributionindicators indicators (sdi)。创建mtwlg时,2行是sdi表中创建链接到mtwlg——酒吧2具有相同值,每一行的id和另一个字段(我们叫它列X)为一行必须包含一个0和1。在我们的代码库中有一个bug阻止了这一自动发生，所以在那个bug出现的时候创建的任何mtwlg都没有相关的sdi，导致NPE出现在不同的地方。

To fix this, a patch needs to be written to loop through the mtwlg table and for each ID, search the sdi table for the 2 related rows. If the rows are present, do nothing; if there is only 1 row, check if F is a 0 or a 1, and insert a row with the other value; if neither row is present, insert them both. This needs to be done for every mtwlg, and a unique ID needs to be inserted too.

要解决这个问题，需要编写一个补丁来在mtwlg表中循环，对于每个ID，在sdi表中搜索两个相关的行。如果行存在，则什么也不做;如果只有一行，检查F是否为0或1，然后插入一行，另一个值;如果两行都不存在，那么将它们都插入。这需要对每个mtwlg执行，并且还需要插入一个唯一的ID。

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

如果它有帮助的话，这个补丁将会被应用到liquibase中。

Anyone with any advice or thoughts as to if and how this will be possible to write in SQL/a liquibase patch?

谁有任何建议或想法，如果和如何这将成为可能在SQL/一个liquibase补丁?

Thanks in advance, let me know of any other information you need.

提前谢谢，如果您还需要什么信息，请告诉我。

EDIT:

编辑:

I've actually just been advised to do this using PL/SQL, do you have any thoughts/suggestions in regards to this? Thanks again.

我刚被建议使用PL/SQL来做这个，你对此有什么想法和建议吗?再次感谢。

4 个解决方案

#1

Oooooh, an excellent 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 the MERGE variant (still pseudo-code'ish as I don't know how your data really looks):

这里是合并变体(仍然是伪代码，因为我不知道你的数据看起来如何):

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 conditional statements, just plain beautiful SQL.

没有循环，没有PL/SQL，没有条件语句，只有漂亮的SQL。

Nice alternative suggested by Boneist in the comments uses a CROSS JOIN rather than UNION ALL in the USING clause, which is likely to perform better (unverified):

Boneist在评论中建议使用交叉连接，而不是在use子句中使用联合，这可能会执行得更好(未经验证):

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

#2

Answer: you don't. There is absolutely no need to loop through anything - you can do it in a single insert. All you need to do is identify the rows that are missing, and then you just need to add them in.

答案是:你不。完全没有必要对任何东西进行循环——您可以在一个insert中完成它。您需要做的就是识别丢失的行，然后只需将它们添加进去。

Here is 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

#3

If the processing logic is too gnarly to be encapsulated in a single SQL statement, you may need to resort to cursor for loops and row types - basically allows you to do things like the following:

如果处理逻辑太过粗糙，无法在单个SQL语句中封装，那么您可能需要使用游标进行循环和行类型——这基本上允许您做如下事情:

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

You can obviously nest another loop inside this one that hits the stakedistributionindicators table, but I'll leave that as an exercise for you. You could also left join to stakedistributionindicators a couple of times in this first cursor so that you only return rows that don't already have an x=1 and x=0, again you can probably work that bit out for yourself.

显然，您可以在这个命中stakedistributionindicators表的循环中嵌套另一个循环，但我将把它作为练习留给您。您还可以在第一个游标中多次将join保留到stakedistributionindicators中，以便只返回那些x=1和x=0都没有的行，同样，您也可以自己算出这一点。

#4

If you would rather write your logic in Java vs. PL/SQL, Liquibase allows you to create custom changes. The custom change points to a Java class you write that can do whatever logic you need. A simple example can be found here

如果您愿意在Java vs. PL/SQL中编写逻辑，那么Liquibase允许您创建自定义更改。自定义更改指向您编写的Java类，该类可以执行所需的任何逻辑。这里有一个简单的例子