Abnormal could not judge the stored procedure-CodePudding

Why can't the stored procedure determine borrowing
serial number does not exist?Tests found that can judge whether can borrow books, if there is an exception is thrown 20010
20007 could not judge, but abnormal,,
for help

 CREATE OR REPLACE PROCEDURE yuyue - booking PROCEDURE 
(yy_id READER_RESERVATION RESERVATION_NUM % TYPE, serial number - reservation 
Borrow_id READER. RE_NUM % TYPE, -- -- library card number 
Yy_ISBN BOOKS_INFO. BK_ISBN % TYPE) - books ISBN number 
As 

Nowdate varchar2 (12); - record the current appointment 
Doesn integer; - check the ISBN of the books borrowed from state 
Reader_num READER. RE_NUM % TYPE; - library card number temporary variable 
Reader_num_exist BOOLEAN:=TRUE; - borrowing number exist judgment 
Reservation_error EXCEPTION; - booking error (has not borrowed the book), exception handling 
The BEGIN 
The SELECT RE_NUM INTO reader_num FROM READER - to determine whether a library card number is 
WHERE READER. RE_NUM=borrow_id; 
If reader_num IS NULL 
Then reader_num_exist:=FALSE; 
End the if; 
The SELECT to_char (sysdate, 'yyyy - mm - dd) INTO nowdate FROM dual; - get time 
SELECT count (*) INTO doesn FROM BOOKS_INFO WHERE BK_ISBN=yy_ISBN AND BOOK_BORROWED='no'; - whether the book has been lent out 
IF doesn=0 THEN - IF have been lent out to make the insert 
INSERT INTO READER_RESERVATION 
VALUES (yy_id borrow_id, yy_ISBN to_date (nowdate, 'yyyy - mm - dd)); 
Dbms_output. Put_line (' reservation success); 
The ELSE 
RAISE reservation_error; Abnormal - throw reservation 
END the IF; 
The EXCEPTION 
The WHEN NO_DATA_FOUND THEN ROLLBACK. 
IF (reader_num_exist=FALSE) THEN RAISE_APPLICATION_ERROR (20007, 'ERROR: no library card number). 
END the IF; 
The WHEN reservation_error THEN RAISE_APPLICATION_ERROR (20010, 'ERROR, the bibliography can borrow books, please find'); 
end;

CodePudding user response:

The SELECT RE_NUM INTO reader_num FROM READER - to determine whether a library card number is
WHERE READER. RE_NUM=borrow_id;
Don't check to record thrown exception, don't go below the IF judgment
You can modify for the count (*),=0

CodePudding user response:

Remove the false judgment

The EXCEPTION
The WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (20007, 'ERROR: no library card number).

CodePudding user response:

reference 1st floor sych888 response:

SELECT RE_NUM INTO reader_num FROM READER, to determine whether a library card number is
WHERE READER. RE_NUM=borrow_id;
Don't check to record thrown exception, don't go below the IF judgment
You can modify for the count (*),=0

 SELECT count (*) INTO reader_num FROM READER WHERE READER. RE_NUM=borrow_id; - to determine whether a library card number is 
If reader_num=0 
Then reader_num_exist:=FALSE; 
End the if;

So to change or not to go after the IF,,,,

CodePudding user response:

refer to the second floor jdsnhan response:

remove the false judgment

The EXCEPTION
The WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (20007, 'ERROR: no library card number).

 BEGIN 
SELECT count (*) INTO reader_num FROM READER WHERE READER. RE_NUM=borrow_id; - to determine whether a library card number is 
The 
The following 
The EXCEPTION 
The WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (20007, 'ERROR: no library card number).

Or not to go IF, so, error ORA - 02291, should be no go IF want to plug the wrong data to list directly, can be linked list don't have the figures so don't go in to a mistake

CodePudding user response:

reference 4 floor tanqibi6601 response:

Quote: refer to the second floor jdsnhan response:

Remove the false judgment

The EXCEPTION
The WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (20007, 'ERROR: no library card number).

BEGIN
SELECT count (*) INTO reader_num FROM READER WHERE READER. RE_NUM=borrow_id; - to determine whether a library card number is
The
The following
The EXCEPTION
The WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (20007, 'ERROR: no library card number).

Or not, IF so, error ORA - 02291, should be no go IF want to plug the wrong data to list directly, can be linked list don't have the figures so it won't plug in to the wrong

You can use PL/DEV test function, location tracking, IF don't go, must be your current logic is not in conformity with the conditions