代码之家  ›  专栏  ›  技术社区  ›  Usman Farooq

oracle中逗号分隔的varchar和Number字段问题

  •  0
  • Usman Farooq  · 技术社区  · 7 年前

    我在Oracle中编写了以下存储过程:

    CREATE OR REPLACE PROCEDURE TMS.SP_BOOKING_CANCEL_SMPL(P_BOOK_TERMINAL      NUMBER,
                                                           P_BOOK_CODE          NUMBER,
                                                           P_BOOK_NO            VARCHAR2,
                                                           P_CANCELLATION_SEATS VARCHAR2,
                                                           P_CANCEL_QTY         NUMBER,
                                                           P_CANCEL_AMOUNT      NUMBER,
                                                           P_CANCEL_SEAT_QTY    NUMBER,
                                                           P_SEAT_QTY           NUMBER,
                                                           P_UNCANCELLED_ID     VARCHAR2,
                                                           P_UNCANCELLED_QTY    NUMBER,
                                                           P_CANCEL_TERMINAL    NUMBER,
                                                           P_CANCEL_SITE        NUMBER,
                                                           P_CANCEL_SEQ         NUMBER,
                                                           P_CANCEL_TYPE        CHAR,
                                                           P_USER_ID            VARCHAR2,
                                                           P_SYNC               CHAR,
                                                           P_CREATE_IP          VARCHAR2,
                                                           P_CREATE_PC          VARCHAR2)
      IS
        d_sql         VARCHAR2(32767);
        V_CANCEL_CODE NUMBER;
      BEGIN
        d_sql := 'UPDATE TMS_BOOKD SET BOOKD_CANCEL_YN = ''Y'', BOOKD_CANCEL_DATE = SYSDATE, BOOKD_CANCEL_USER = :UserId, BOOKD_GENDER = NULL
                  WHERE BOOKD_TERMINAL = :BookDTerminal AND BOOKD_CODE = :BookDCode AND BOOKD_SEAT in (:cancellationIds)';
    
        EXECUTE IMMEDIATE d_sql
        USING P_USER_ID, P_BOOK_TERMINAL, P_BOOK_CODE,P_CANCELLATION_SEATS ;
          --;
    
        IF P_CANCEL_SEAT_QTY = P_SEAT_QTY
        THEN
          d_sql := 'UPDATE TMS_BOOKM SET BOOKM_SET_SEATS = NULL, BOOKM_SET_QTY = NULL, BOOKM_SET_AMOUNT = NULL, BOOKM_CANCEL = 1 WHERE BOOKM_TERMINAL = :BookDTerminal
                    AND BOOKM_CODE = :BookM_Code AND BOOKM_BOOKNO = :BookM_No';
    
          EXECUTE IMMEDIATE d_sql
          USING P_BOOK_TERMINAL, P_BOOK_CODE, P_BOOK_NO;
        ELSE
          d_sql := 'UPDATE TMS_BOOKM SET BOOKM_SET_SEATS = :BOOKM_SET_SEATS, BOOKM_SET_QTY = :BOOK_SET_QTY, BOOKM_CANCEL = 1 WHERE BOOKM_TERMINAL = :BookDTerminal
                    AND BOOKM_CODE = :BookM_Code AND BOOKM_BOOKNO = :BookM_No';
    
          EXECUTE IMMEDIATE d_sql
          USING P_UNCANCELLED_ID, P_UNCANCELLED_QTY, P_BOOK_TERMINAL, P_BOOK_CODE, P_BOOK_NO;
        END IF;
      END;
    /
    

    我使用参数“P\u CANCELLATION\u SEATS”执行此存储过程,其值为:“10,12” 然而,它引发了异常:Ora-01722(无效数字)在第一个查询中,问题很可能是in子句比较了参数中的数字类型列值和字符串类型值。 谁能告诉我如何解决这个问题?

    2 回复  |  直到 7 年前
        1
  •  2
  •   XING    7 年前

    在运行Proc之前,您必须记住的事情很少。第一个问题是你是否需要 DYNAMIC SQL . 在您的情况下,根本不需要动态SQL。你可以直接这样做,如我下面的代码所示。其次,您已经确定了您的表列 BOOKD_SEAT 是一个 NUMBER 库曼和你正试图与 String 所以很明显,它将通过错误实现。您需要将这些值作为集合传递。见下文:

    您修改了代码:

    --Create a type of Number to hold your input values
    CREATE OR REPLACE TYPE var IS TABLE OF NUMBER;
    /
    
    CREATE OR REPLACE PROCEDURE TMS.SP_BOOKING_CANCEL_SMPL (
       P_BOOK_TERMINAL         NUMBER,
       P_BOOK_CODE             NUMBER,
       P_BOOK_NO               VARCHAR2,
       P_CANCELLATION_SEATS    var, -- Declare the input as  type of NUMBER
       P_CANCEL_QTY            NUMBER,
       P_CANCEL_AMOUNT         NUMBER,
       P_CANCEL_SEAT_QTY       NUMBER,
       P_SEAT_QTY              NUMBER,
       P_UNCANCELLED_ID        VARCHAR2,
       P_UNCANCELLED_QTY       NUMBER,
       P_CANCEL_TERMINAL       NUMBER,
       P_CANCEL_SITE           NUMBER,
       P_CANCEL_SEQ            NUMBER,
       P_CANCEL_TYPE           CHAR,
       P_USER_ID               VARCHAR2,
       P_SYNC                  CHAR,
       P_CREATE_IP             VARCHAR2,
       P_CREATE_PC             VARCHAR2)
    IS  
       V_CANCEL_CODE   NUMBER;
    BEGIN
       UPDATE TMS_BOOKD
          SET BOOKD_CANCEL_YN = 'Y',
              BOOKD_CANCEL_DATE = SYSDATE,
              BOOKD_CANCEL_USER = P_USER_ID,
              BOOKD_GENDER = NULL
        WHERE     BOOKD_TERMINAL = P_BOOK_TERMINAL
              AND BOOKD_CODE = P_BOOK_CODE
              AND BOOKD_SEAT IN (select column_value from table(P_CANCELLATION_SEATS) );
           -- Note you can also use MEMBER of operator and change query as 
           --BOOKD_SEAT MEMBER OF P_CANCELLATION_SEATS
    
       IF P_CANCEL_SEAT_QTY = P_SEAT_QTY
       THEN
          UPDATE TMS_BOOKM
             SET BOOKM_SET_SEATS = NULL,
                 BOOKM_SET_QTY = NULL,
                 BOOKM_SET_AMOUNT = NULL,
                 BOOKM_CANCEL = 1
           WHERE     BOOKM_TERMINAL = P_BOOK_TERMINAL
                 AND BOOKM_CODE = P_BOOK_CODE
                 AND BOOKM_BOOKNO = P_BOOK_NO;
       ELSE
          UPDATE TMS_BOOKM
             SET BOOKM_SET_SEATS = P_UNCANCELLED_ID,
                 BOOKM_SET_QTY = P_UNCANCELLED_QTY,
                 BOOKM_CANCEL = 1
           WHERE     BOOKM_TERMINAL = P_BOOK_TERMINAL
                 AND BOOKM_CODE = P_BOOK_CODE
                 AND BOOKM_BOOKNO = P_BOOK_NO;
       END IF;
    END;
    /
    

    执行:

    DECLARE
       v_var   var := var ();
    BEGIN
       v_var.EXTEND (2);
    
       --Populate all the values which you want to evalued in IN calsue.
       v_var (1) := 1;
       v_var (2) := 2;
    
       TMS.SP_BOOKING_CANCEL_SMPL (P_BOOK_TERMINAL => <give your value>
                                    P_BOOK_CODE   =>  <give your value>
                                    P_BOOK_NO     => <give your value>
                                    --- pass all the value which you want to be evaluted in IN clause of your query
                                    P_CANCELLATION_SEATS  => v_var
                                    P_CANCEL_QTY     =>  <give your value>
                                   P_CANCEL_AMOUNT    =>  <give your value>
                                    P_CANCEL_SEAT_QTY    =>  <give your value>
                                    P_SEAT_QTY          =>  <give your value>
                                    P_UNCANCELLED_ID     =>  <give your value>
                                    P_UNCANCELLED_QTY    => <give your value>
                                    P_CANCEL_TERMINAL    => <give your value>
                                    P_CANCEL_SITE      =>  <give your value>
                                    P_CANCEL_SEQ      =>  <give your value>
                                    P_CANCEL_TYPE     =>  <give your value>
                                    P_USER_ID     =>  <give your value>
                                    P_SYNC        =>  <give your value>
                                    P_CREATE_IP    =>  <give your value>
                                    P_CREATE_PC     => <give your value>      )
    
    end;
    
        2
  •  1
  •   San    7 年前

    参数P_CANCELLATION_SEAT是一个逗号分隔值的列表,您希望在IN子句中传递这些值。您试图实现的方法在技术上是错误的,您必须先将逗号分隔值分解为值列表,然后再在IN子句中使用它。

    d_sql := 'UPDATE TMS_BOOKD SET BOOKD_CANCEL_YN = ''Y'', BOOKD_CANCEL_DATE = SYSDATE, BOOKD_CANCEL_USER = :UserId, BOOKD_GENDER = NULL
                      WHERE BOOKD_TERMINAL = :BookDTerminal AND BOOKD_CODE = :BookDCode AND BOOKD_SEAT in (SELECT to_number(regexp_substr(vlist, ''[^,]+'', 1, LEVEL))
                                                                                                             FROM (SELECT :cancellationIds AS vlist FROM dual)
                                                                                                           CONNECT BY regexp_substr(vlist, ''[^,]+'', 1, LEVEL) IS NOT NULL)';
    

    顺便问一下,你为什么要使用动态SQL?所有操作都可以使用简单的SQL语句执行。