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

在运行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;

参数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语句执行。