甲骨文立即执行不执行没有任何错误 [英] oracle execute immediate not executing without any error

问题描述

我有一个游标可以返回要在EXECUTE IMMEDIATE

I have a cursor to return record to be used in EXECUTE IMMEDIATE

 CURSOR c1
 IS
 SELECT crs_cust.CUSTOMER_ID AS CUSTOMER_ID, subset.NEW_CUSTOMER_REFERENCE_ID AS 
 CUSTOMER_REF_ID FROM CRS_CUSTOMERS crs_cust INNER JOIN 
 DAY0_SUBSET subset ON 
 crs_cust.CUSTOMER_ID=subset.CURRENT_CUSTOMER_ID;

下面的块中的EXECUTE IMMEDIATE查询未执行.

The EXECUTE IMMEDIATE queries in below block are not executing.

OPEN c1;
 LOOP
  EXIT WHEN c1%NOTFOUND;
  EXIT WHEN (c1%ROWCOUNT <> p_SCBCount);
   FOR i in c1 LOOP
     EXECUTE IMMEDIATE 'UPDATE CRS_CUSTOMERS SET REF_ID = ' || i.CUSTOMER_REF_ID ||'WHERE CUSTOMER_ID = ' || i.CUSTOMER_ID; 
     p_TotalUpdatedCRS := p_TotalUpdatedCRS + 1;

     EXECUTE IMMEDIATE 'UPDATE CRS_REVIEWS SET 
     REF_ID = ' || i.CUSTOMER_REF_ID || 'WHERE CUSTOMER_ID = ' || i.CUSTOMER_ID; 
     EXECUTE IMMEDIATE 'UPDATE CRS_EVENT SET REF_ID = ' || i.CUSTOMER_REF_ID || 'WHERE UNIQUE_ID = ' || i.CUSTOMER_ID;
     EXECUTE IMMEDIATE 'UPDATE ALERT_HEADER SET CUSTOMER_SOURCE_REF_ID = ' || i.CUSTOMER_REF_ID || 'WHERE CUSTOMER_ID = ' || i.CUSTOMER_ID; 
 END LOOP;
    DBMS_OUTPUT.PUT_LINE ('The total updates to CRS table = ' || p_TotalUpdatedCRS); 
END LOOP;      
 CLOSE c1; 

当我使用SQL Developer执行过程时，也不会打印出DBMS输出.

The DBMS output is also not printed out when I execute procedure using SQL developer.

推荐答案

您的代码不执行任何操作的原因是:

The reason why your code does nothing is this:

OPEN c1;
 LOOP
  EXIT WHEN c1%NOTFOUND;   
  EXIT WHEN (c1%ROWCOUNT <> p_SCBCount);

在执行提取之前，您正在测试c1%ROWCOUNT.因此它的值为0；我猜想p_SCBCount在那个时候不为零(因为您将它初始化为DECLARE块中的某个值)，以便测试评估为true并退出程序.

You are testing for c1%ROWCOUNT before you have executed a fetch. So its value is 0; I'm guessing p_SCBCount is not zero at that point (because you initialised it to some value in the DECLARE block) so that test evaluates to true and the program exits.

或者，问题是这样的:

OPEN c1;
 LOOP
   ...
   FOR i in c1 LOOP

我们不能将FOR ... IN与显式光标一起使用.您已经打开了光标.然后FOR尝试再次将其打开，从而弹出ORA-06511: PL/SQL: cursor already open.如果您没有看到此错误，则必须具有一个抑制该错误的异常处理程序(例如WHEN others then null;).

We can't use the FOR ... IN with an explicit cursor. You have opened the cursor. Then the FOR tries to open it again which hurls ORA-06511: PL/SQL: cursor already open. If you're not seeing this error you must have an exception handler which suppresses it (e.g. WHEN others then null;).

基本上，完全不需要外部循环，应该将其丢弃.

Basically the outer loop is completely unnecessary and you should discard it.

很少需要显式循环控制:只需使用FOR ... IN构造并让Oracle控制流程即可.

Explicit loop control is rarely necessary: just use the FOR ... IN construct and let Oracle control the flow.

所有动态SQL也是不必要的. SQL使用变量，因此您只需要编写引用游标属性的静态SQL:

Also unnecessary is all the dynamic SQL. SQL works with variables so you just need to write static SQL which references the cursor attributes:

 FOR i in (SELECT crs_cust.CUSTOMER_ID AS CUSTOMER_ID
                 , subset.NEW_CUSTOMER_REFERENCE_ID AS CUSTOMER_REF_ID 
           FROM CRS_CUSTOMERS crs_cust 
           INNER JOIN  DAY0_SUBSET subset
           ON crs_cust.CUSTOMER_ID=subset.CURRENT_CUSTOMER_ID )
 LOOP
     UPDATE CRS_CUSTOMERS 
     SET REF_ID = i.CUSTOMER_REF_ID
     WHERE CUSTOMER_ID = i.CUSTOMER_ID; 
     p_TotalUpdatedCRS := p_TotalUpdatedCRS + 1;

     UPDATE CRS_REVIEWS
     SET REF_ID =  i.CUSTOMER_REF_ID
     WHERE CUSTOMER_ID =  i.CUSTOMER_ID; 

     UPDATE CRS_EVENT 
     SET REF_ID = i.CUSTOMER_REF_ID 
     WHERE UNIQUE_ID = i.CUSTOMER_ID;

     UPDATE ALERT_HEADER 
     SET CUSTOMER_SOURCE_REF_ID = i.CUSTOMER_REF_ID 
     WHERE CUSTOMER_ID = i.CUSTOMER_ID; 
END LOOP;
DBMS_OUTPUT.PUT_LINE ('The total updates to CRS table = ' || p_TotalUpdatedCRS); 

---

我不确定c1%ROWCOUNT <> p_SCBCount的目的.我的预感是多余的，因为FOR LOOP可以精确控制取指令.实际上，我怀疑您添加了它是为了避免嵌套循环的副作用.而且我怀疑您只是引入了嵌套循环，因为您的原始代码被甩了PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop(只是个疯狂的猜测).

---

I'm not sure of the purpose of the c1%ROWCOUNT <> p_SCBCount. My hunch is it's superfluous, because the FOR LOOP controls the fetches precisely. In fact I suspect you added it to avoid the side-effects of the nested loops; and I suspect you only introduced the nested loops because you're original code hurled PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop (just a wild guess).

但是，如果确实可以实现一些真正的业务逻辑，则可以以某种方式将其添加到循环中.

However, if it does serve to implement some genuine business logic you can add it into the loop somehow.

这篇关于甲骨文立即执行不执行没有任何错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！