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