logo标签列表

Oracle SQL - 如何编写一个有条件和循环的插入语句？

sqloraclesql-updateconditional-statementsliquibase
3
背景： 我有两个表：markettypewagerlimitgroups（mtwlg）和stakedistributionindicators（sdi）。当创建一个mtwlg时，sdi表中会创建2行与mtwlg相关联的行，每行除了id和另一个字段（我们称之为X列）外，其余值相同。其中一行的X列必须包含0，而另一行的X列必须包含1。 我们代码库中存在一个错误，导致这个过程无法自动完成，因此在该错误存在期间创建的任何mtwlg都没有相关的sdi，从而在各个地方引发NPE。
为了解决这个问题，需要编写一个补丁来循环遍历mtwlg表，并针对每个ID，在sdi表中搜索2个相关的行。如果已经存在这些行，则不进行任何操作；如果只有1行，则检查F是否为0或1，并插入具有另一个值的行；如果两行都不存在，则插入它们。这需要针对每个mtwlg执行，并且需要插入唯一的ID。
伪代码：
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

如果有用的话-修补程序将使用liquibase应用。任何人都有任何建议或想法是否以及如何在SQL / liquibase补丁中编写这个？提前感谢，让我知道您需要的其他信息。编辑：实际上，我已被建议使用PL / SQL来完成此操作，您对此有任何想法/建议吗？再次感谢。
4个回答
4

哦，一个关于MERGE的优秀工作。

这是您的伪代码：

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

这是MERGE变体（仍然是伪代码，因为我不知道您的数据实际上是什么样子）：
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);

不需要循环，不需要PL/SQL，也不需要条件语句，只需要简单优美的SQL。

在评论中，Boneist提出了一个不错的替代方案，使用CROSS JOIN而不是UNION ALL在USING子句中，这可能会更有效率（未经验证）：

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);
1
“我能简单地使用MAX(d.id)+1吗？” 不行！绝不能那样做 :-) 这可能会出现大量错误（主要是竞争条件）。理想情况下，您应该使用序列，例如由user boneist在此处建议的。 可以显式或通过触发器使用它。 - Lukas Eder
1
为了提高性能，您可以将那个union all替换为一个虚拟的2行表/子查询上的cross join。 - Boneist
@Boneist：当然！感谢提示。 - Lukas Eder
我刚刚被同事推荐使用PL/SQL来解决这个问题，这应该会使问题变得简单。你有什么想法？ - James
@James：我不认为在这里使用PL/SQL有什么优势。虽然你当然可以使用它... - Lukas Eder
显示剩余2条评论
2

回答：你不需要。完全没有必要循环任何内容 - 你可以在一个单独的插入中完成。你只需要识别出缺失的行，然后添加它们即可。

以下是一个示例：

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
0
如果您更喜欢使用Java而不是PL/SQL编写逻辑，Liquibase允许您创建自定义变更。自定义变更指向您编写的Java类，可以执行所需的任何逻辑。这里可以找到一个简单的示例链接。
0
如果处理逻辑过于复杂，无法封装在单个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;
/

你显然可以在这个循环中嵌套另一个循环，以访问stakedistributionindicators表，但我会把它留给你作为练习。你也可以在第一个游标中左连接到stakedistributionindicators几次，这样你只返回没有x=1和x=0的行，你可能可以自己解决这个问题。
在正常使用情况下，ID是如何生成的？大多数Oracle数据库版本12之前都会使用序列来实现此目的，如果您的系统正在使用序列，则可以直接使用相同的序列。 - ninesided
另外，假设stakedistributionindicators表基于id有一个主键，那么与其担心问题的条件部分，你可以尝试插入x的两个值，但捕获主键冲突异常并不做任何操作。这样，你就能保证始终获得两行数据，并且逻辑会更加简单。 - ninesided
为什么建议使用游标循环（也称逐行，逐步）而不是在单个SQL语句中轻松完成呢？如果您以这种方式执行，性能会慢得多。 - Boneist
因为这是一个有效的选项？因为也许如果逻辑更复杂，就不能在单个SQL语句中完成？我猜测你也给了负评，谢谢。 - ninesided
1
如果您绝对无法在单个SQL语句中完成逻辑操作，那么您应该仅依赖于游标循环。这相当于OP问道：“我有一浴缸水，一汤匙和一个水壶；我应该用哪个来把水从浴缸里取出来”，而您回答“使用水壶”。这当然是一个有效的答案，但大多数人会选择“拔掉塞子！”因为这样更快更容易！ - Boneist
还有，你建议嵌套循环？不要啊！{捂脸} 通过这样建议，你实际上是在建议 OP 重新发明连接。为什么要绕过 Oracle 放在优化器中的所有代码，以便让 SQL 决定使用最佳类型的连接呢？如果你绝对必须使用游标循环（有时是无法避免的），至少将所有逻辑封装到一个单独的 SQL 语句中，然后通过循环执行它！ - Boneist
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接