ORA-00900: Oracle过程的无效SQL语句

Question

我正在尝试执行下面的过程，但一直收到错误(ORA-00900:无效的SQL语句)

CREATE OR REPLACE PROCEDURE RESETUSERSESSION (run IN VARCHAR2)
IS    
    cursor usersessiondetail_cur IS    
          SELECT usd.CLIENTID,usd.OPERID,usd.REGISTER,usd.MACHINE_ID,usd.SESSIONNUMBER
          FROM cashiering_dev.CSH_USER usr, cashiering_dev.CSH_USERSESSIONDETAIL usd
          WHERE usr.clientid = usd.clientid 
          AND usr.operid = usd.operid
          AND usr.register = usd.register
          AND usr.machine_id = usd.machine_id 
          AND usr.sessionnumber = usd.sessionnumber
          AND usr.Machine_ID = 'basrytest'
          AND usd.LOGOFFDATETIME IS NULL;


 BEGIN
      OPEN usersessiondetail_cur;      

        FOR vItems in usersessiondetail_cur
        LOOP
           EXECUTE IMMEDIATE 'UPDATE csh_UserSessionDetail 
                         SET ClientID =vItems.CLIENTID 
                        WHERE ClientID =vItems.CLIENTID 
                         AND OperID =vItems.OPERID 
                         AND Register =vItems.REGISTER 
                         AND Machine_ID =vItems.MACHINE_ID 
                         AND SessionNumber =vItems.SESSIONNUMBER';                     
       END LOOP;

      CLOSE usersessiondetail_cur;



END;

Answer 1

您的SQL无效，因为执行动态SQL字符串时，游标投影名称不在作用域内。您需要像这样使用占位符：

   FOR vItems in usersessiondetail_cur
    LOOP
       EXECUTE IMMEDIATE 'UPDATE csh_UserSessionDetail 
                     SET ClientID = :p1
                    WHERE ClientID = :p2 
                     AND OperID = :p3
                     AND Register = :p4 
                     AND Machine_ID = :p5 
                     AND SessionNumber = :p6' 
           using vItems.CLIENTID 
                 , vItems.CLIENTID 
                , vItems.OPERID 
                , Items.REGISTER 
                , vItems.MACHINE_ID 
                , vItems.SESSIONNUMBER;                     
   END LOOP;

您的动态代码不是匿名PL/SQL块或CALL语句，因此参数是按位置传递的，而不是按名称传递的，这意味着您必须传递两次vItems.CLIENTID。Find out more。

Other observations

• 首先也是最重要的是，绝对没有必要为这个SQL实现动态执行。
• OPEN和CLOSE cursor语句没有与for cursor循环一起使用。
• 对于这个查询，您不需要显式的游标声明。
• 与基于集合的UPDATE循环相比，逐行更新row UPDATE是一种糟糕的做法，与基于集合的UPDATE相比，效率也是不必要的低效...< run >H216
• ...但是游标具有MACHINE_ID的硬编码字符串。
• 最后，UPDATE语句实际上并不更改表的状态，因为它设置了CLIENT_ID = CLIENT_ID，所以整个过程是没有意义的。

除此之外，一切都很好。

我假设您编写本文是为了测试如何使用动态SQL，而不是作为业务逻辑的实现。但是，即使它是一个测试，最好是写一段适当的代码来做一些事情。尤其是当你在StackOverflow上与其他人分享代码时。发布有这么多问题的代码会让人分心，因为潜在的受访者不知道该解决哪一个问题。

Answer 2

一种简单得多的方法，只使用FOR循环。在这种情况下，我们不需要打开Close游标，因为这是由Oracle内部处理的。我也不明白是否需要再次更新客户端ID。如果我们在where子句中选择客户端ID，则没有更新的意义。无论如何，请尽情享受:)

CREATE OR REPLACE
PROCEDURE RESETUSERSESSION(
    run IN VARCHAR2)
AS
BEGIN
  FOR vItems IN
  (SELECT usd.CLIENTID,
    usd.OPERID,
    usd.REGISTER,
    usd.MACHINE_ID,
    usd.SESSIONNUMBER
  FROM cashiering_dev.CSH_USER usr,
    cashiering_dev.CSH_USERSESSIONDETAIL usd
  WHERE usr.clientid      = usd.clientid
  AND usr.operid          = usd.operid
  AND usr.register        = usd.register
  AND usr.machine_id      = usd.machine_id
  AND usr.sessionnumber   = usd.sessionnumber
  AND usr.machine_id      = 'basrytest'
  AND usd.LOGOFFDATETIME IS NULL
  )
  LOOP
    UPDATE csh_UserSessionDetail
    SET ClientID      =vItems.CLIENTID
    WHERE ClientID    =vItems.CLIENTID
    AND OperID        =vItems.OPERID
    AND Register      =vItems.REGISTER
    AND Machine_ID    =vItems.MACHINE_ID
    AND SessionNumber =vItems.SESSIONNUMBER;
  END LOOP;
END;
/