sql - 触发器未发现任何错误时继续存储过程

Question

我真的是 Oracle 的新手，如果我的触发器没有检测到任何错误，我想知道如何继续运行我的程序。
如果触发器中的这两个异常没有被触发，我只想继续运行我的程序，但是当我运行它时，它会显示这个错误

ERROR at line 1:
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "TEST.TRG_BOOKING_VALIDATION", line 9
ORA-04088: error during execution of trigger 'TEST.TRG_BOOKING_VALIDATION'
ORA-06512: at "TEST.PRC_ADD_BOOKING", line 44
ORA-06512: at line 1

程序

create sequence bookingidvalue minvalue 1 start with 3031;


CREATE OR REPLACE PROCEDURE PRC_ADD_BOOKING(CUST_ID IN NUMBER,PAYMENT_ID IN NUMBER,FRM_DEST IN VARCHAR2,TO_DEST IN VARCHAR2)AS


v_journeystatus VARCHAR2(15) := 'Pending';
v_bookeddate Booking.bookingdate%TYPE;

no_null_on_custID EXCEPTION;
no_null_on_payID EXCEPTION;
no_null_on_FROM_DEST EXCEPTION;
no_null_on_TO_DEST EXCEPTION;
duplicate_error EXCEPTION;


BEGIN

IF CUST_ID < 0
THEN
RAISE no_null_on_custID;
END IF;

IF PAYMENT_ID < 0
THEN
RAISE no_null_on_payID;
END IF;


IF FRM_DEST IS NULL
THEN
RAISE no_null_on_FROM_DEST;
END IF;

IF TO_DEST IS NULL
THEN
RAISE no_null_on_TO_DEST;
END IF;
  
IF FRM_DEST = TO_DEST
THEN
RAISE duplicate_error;
END IF;



INSERT INTO Booking values(bookingidvalue.nextval,sysdate,v_journeystatus,FRM_DEST,TO_DEST,CUST_ID,PAYMENT_ID);
DBMS_OUTPUT.PUT_LINE('Booking has been added');

EXCEPTION
WHEN no_null_on_custID then
DBMS_OUTPUT.PUT_LINE('Invalid Customer ID');

WHEN no_null_on_payID then
DBMS_OUTPUT.PUT_LINE('Invalid Payment ID');

WHEN no_null_on_FROM_DEST then
DBMS_OUTPUT.PUT_LINE('From Destination must be filled');

WHEN no_null_on_TO_DEST then
DBMS_OUTPUT.PUT_LINE('To Destination must be filled');

WHEN duplicate_error then
DBMS_OUTPUT.PUT_LINE('From destination cannot same with To destination');


END;
/

扳机

CREATE OR REPLACE trigger trg_booking_validation
 before insert on booking
 for each row

 declare

 v_journeystatus booking.journeystatus%type;
 v_customerid booking.customerid%type;
 v_paymentid booking.paymentid%type;

BEGIN 

select bo.journeystatus into v_journeystatus
 from booking bo
 where bo.customerid = :new.customerid;

if SQL%FOUND then
 case
  when inserting then
   if(v_journeystatus = 'In Journey')then
    RAISE_APPLICATION_ERROR(-20950,'YOU ARE CURRENTLY IN JOURNEY, PLEASE BOOK AGAIN WHEN YOU REACHED');
   end if;
   if(v_journeystatus = 'Pending')then
    RAISE_APPLICATION_ERROR(-20950,'YOUR PREVIOUS BOOKING IS ALREADY ON PENDING! DRIVER WILL SOON PICK YOU UP, BE PATIENCE!');
   end if;
   if(v_journeystatus = NULL)then
    DBMS_OUTPUT.PUT_LINE('N0 errors');//i want to continue to my procedure here
   end if;
end case;
end if;


END;
/

Answer 1

catch NO_DATA_FOUND触发器中的异常:

CREATE OR REPLACE trigger trg_booking_validation
before insert on booking
  for each row
declare
  v_journeystatus booking.journeystatus%type;
BEGIN 
  select bo.journeystatus
  into   v_journeystatus
  from   booking bo
  where  bo.customerid = :new.customerid;

  if v_journeystatus = 'In Journey' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'YOU ARE CURRENTLY IN JOURNEY, PLEASE BOOK AGAIN WHEN YOU REACHED'
    );
  elsif v_journeystatus = 'Pending' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'YOUR PREVIOUS BOOKING IS ALREADY ON PENDING! DRIVER WILL SOON PICK YOU UP, BE PATIENCE!'
    );
  end if;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

注意:您不需要检查您是否是 INSERTING因为触发器只会在 INSERT 期间触发.

但是，我不确定您是否应该使用 SELECT声明，而不仅仅是检查 :NEW.journeystatus如果您指的是您所在的行，则绑定(bind)变量 INSERT :

CREATE OR REPLACE trigger trg_booking_validation
before insert on booking
  for each row
BEGIN 
  if :NEW.journeystatus = 'In Journey' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'YOU ARE CURRENTLY IN JOURNEY, PLEASE BOOK AGAIN WHEN YOU REACHED'
    );
  elsif :NEW.journeystatus = 'Pending' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'YOUR PREVIOUS BOOKING IS ALREADY ON PENDING! DRIVER WILL SOON PICK YOU UP, BE PATIENCE!'
    );
  end if;
END;
/