PL/SQL connumdrum

Creamy Goodness

my exams are looming and just trying to do some exam papers here and have come across a question i'm having difficulty with.

i've attached an ERD schema below.

basically the question is this...

Write a PL/SQL procedure to take a borrower Id and an ISBN and issue number and add a book loan, giving the loan date as today, leaving the return date as null. Assume that there is already a ‘before insert’ trigger on the loan table to prevent the borrower from borrowing more than the maximum number of books. If the borrower or issue number doesn’t exist, write an appropriate error to the serveroutput.

right so it's easy enough to get a loan inserted into the Loan table without error checking.

here's what i've got so far

DECLARE

    --these three var's are the ones to store the input 
    --from keyboard from the user.
    
    b_id    Loan.BorrowerId%TYPE:=b_id;
    isbn    Loan.ISBN%TYPE:=isbn;
    iss_no  Loan.IssueNo%TYPE:=iss_no;
    
    --exceptions
    bid_not_found    exception;

    
BEGIN

    --obviously this here is where i'd check to see if the borrower id that is
    --inputted from the user is in the borrower table, if not i would raise
    --that bid_not_fount exception which i can write no problems.

    --i'd have to do the same for the issue number as well.
    
    
    INSERT INTO Loan VALUES(isbn, iss_no, SYSDATE, b_id, NULL);
    
    COMMIT;


    EXCEPTION
            
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No data was found.');
            ROLLBACK WORK;

        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Unexpected error occurred.');
                        ROLLBACK WORK;


END;

i don't know how to effective loop through the borrowerId's in the borrower table to check it against the borrowerId that is inputted from the user's keyboard.

anyone have any hints on how i'd go about doing this?

Webmonkey

I know absolutely nothing about that code but since SQL isn't procedural you can just do a query to check if it exists?

Something like

if (SELECT count(*) FROM Borrower WHERE BorrowerID = 'b_id') == 1
then throw exception?

Obviously that code does nothing on any language, but i'am sure you can work out from there.

Tom Dunne

I haven't looked at this in detail, but here are some minor points:

The code above is an anonymous block of PL/SQL as opposed to a procedure. Not sure if you are aware of the difference, but I would put a procedure declaration at the start, with the input parameters b_id, ISBN and issue_no.

Then you can check if b_id exists in the Borrower table. If it does, then do the insert, otherwise throw the exception.

Creamy Goodness

Webmonkey wrote: »

I know absolutely nothing about that code but since SQL isn't procedural you can just do a query to check if it exists?

Something like

if (SELECT count(*) FROM Borrower WHERE BorrowerID = 'b_id') == 1
then throw exception?

Obviously that code does nothing on any language, but i'am sure you can work out from there.

ahh ya legend, this has put me on to the right track.

tom dunne wrote: »

I haven't looked at this in detail, but here are some minor points:

The code above is an anonymous block of PL/SQL as opposed to a procedure. Not sure if you are aware of the difference, but I would put a procedure declaration at the start, with the input parameters b_id, ISBN and issue_no.

Then you can check if b_id exists in the Borrower table. If it does, then do the insert, otherwise throw the exception.

erm oops i guess when a lecturer says to always read the question fully, i guess that part didn't get through to me. didn't see that it had to be a procedure, all's changed now.

thanks guys for your help.

Webmonkey

No Probs, only glad I was of some help