WorkID%TYPE;
v_ArtistID Artist.ArtistID%TYPE;
v_SalesPrice Transaction.SalesPrice%TYPE;
v_testSalesPrice Transaction.SalesPrice%TYPE;
CURSOR TransactionCursor IS
SELECT SalesPrice
FROM Transaction
WHERE WorkID = v_WorkID
FOR UPDATE OF SalesPrice, CustomerID, PurchaseDate;
BEGIN
/*
Selecting and then looking for NULL does not work
because finding no qualifying records results in
Oracle throwing a NO_DATA_FOUND exception.
So, be ready to catch the exception by creating
an 'anonymous block' with its own EXCEPTION clause.
*/
BEGIN
SELECT CustomerID INTO v_CustomerID
FROM Art_Customer
WHERE Art_Customer.Name = v_CustomerName;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT CustomerSeq.nextval into v_CustomerID from Dual;
INSERT INTO Art_Customer (CustomerID, Name)
VALUES ( v_CustomerID, v_CustomerName );
END;
SELECT ArtistID into v_ArtistID
FROM Artist
WHERE Artist.Name = v_Artist;
SELECT WorkID INTO v_WorkID
FROM Work
WHERE Work.Title = v_Title
AND Work.Copy = v_Copy
AND Work.ArtistID = v_ArtistID;
--We need to use a cursor here, because a work can re-enter the
-- gallery, resulting in multiple records for a given WorkID.
--Look for a Transaction record with a null for SalesPrice:
v_TransactionFound:= FALSE;
FOR Trans_record in TransactionCursor LOOP
IF( Trans_Record.
Pages:
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435