如何将这些SQL语句转换为更有效的语句 [英] How do I convert these SQL statements into more efficient statements

问题描述

我有以下代码:

  CURSOR Q1 IS
    SELECT COL1, COL2, COL3, COL4, COL5, COL6 FROM TABLE1;

  CURSOR Q2 IS
    SELECT COL11, COL22, COL33, COL44, COL55, COL66 FROM TABLE2;

  PRESENT BOOLEAN;

   FOR X IN Q1 LOOP
     PRESENT := FALSE;
     FOR Y IN Q2 LOOP
      IF (X.COL3 = Y.COL33) AND (X.COL4 = Y.COL44) THEN
        PRESENT := TRUE;
        EXIT;
      END IF;
     END LOOP;

     IF NOT PRESENT THEN
        UPDATE TABLE_X SET COL_A = 'Y';
        COMMIT;
     END IF;
   END LOOP;

我不确定是否可以将此代码转换为MERGE语句或更有效的方法，如果可以，请问有人可以帮助我转换此代码.

I am not sure if I can convert this code to MERGE statement or anything more efficient, if YES, could anyone please help me in converting this code.

提前感谢您的时间和帮助！

Thanks in advance for your time and help!

推荐答案

您只能创建一个游标并将这两个表连接在一起. 然后，如果我对您的理解正确，则可以在此光标中更新任何数据来更新table_X.

You can create only one cursor and join this two tables. Then if I understood you correctly you can update table_X if there is any data in this cursor.

declare 

CURSOR Q1 IS
SELECT count(X.COL1)
FROM TABLE1 X
join TABLE2 Y
on X.COL3 = Y.COL33 AND X.COL4 = Y.COL44;

v_Q1 int := 0;

begin

open Q1;
fetch Q1 into v_Q1;
close Q1;

If v_Q1 > 0 then
   UPDATE TABLE_X SET COL_A = 'Y';

end if;

end;
/

这是一个小演示

这篇关于如何将这些SQL语句转换为更有效的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！