Objectives
- Create a custom PL/SQL procedure.
- Use local variables and try setting their value with SELECT ... INTO ...
- Create a custom type and type derived from the table definition (%ROWTYPE).
- Using the cycle in procedure.
Motivation
PL/SQL is a procedural SQL extension implemented in the Oracle database system. It allows us to write the imperative code that is performed by database management system.
Using PL/SQL allows us for example to write part of the application logic directly in the database, which may be important if the process works intensely with tha data. In case it is necessary to handle large volumes of data, the use of PL/SQL allows not to download the data from database system into the application and necessary operations are performed by a compiled (so fast) code directly in the database system.
Our task for this laboratory excercise is to write a procedure for creating an invoice for a customer. Let's say the application which is working with database allows for the user to buy tracks using shopping cart. After that our application would like to call just one procedure with parameters for one customer and list of tracks. Procedure would take those parameters and create (insert) all required records into database.
Instructions
Step 1
If you would try to delete invoice and you would not delete all invoice lines for that invoice, database would not allow such delete as there is foreign key in InvoiceLine to primary key of Invoice table. Tables were created using default behaviour for reference integrity check, which is ON DELETE NO ACTION (restrict mode). This option will not allow deletion of invoice if there are some invoice lines.
Comment
SQLDeveloper occasionally misbehaves or crashes during programming PL/SQL. I recommend writing only one procedure (or trigger) in one SQL Worksheet and for testing you can open always new SQL Worksheet (use context menu over connection icon).
Task 1.1
Write the deleteInvoice procedure, which removes the invoice from the database by specified id, and also in case there are some existing invoice lines. Procedure takes 1 parameter:
- 'invoice' of type int - id of invoice to be deleted.
Comment
In the body of the procedure you just need to write two DELETE statements, the first one deletes the items (lines) on the invoice, the second deletes invoice itselt. Do not forget to call the COMMIT statement in order to confirm the transaction.
Comment
Before finishing of procedure write an output about successful deletion of invoice, e.g.: 'Invoice with id -> 1 removed.'. Use procedure DBMS_OUTPUT.PUT_LINE.
For verification you can manually insert a new invoice into the database and then delete it by calling deleteInvoice procedure. You can call the procedure in two ways, directly calling it by:
execute deleteInvoice(413);
or within an anonymous PL/SQL block:
BEGIN
deleteInvoice(413);
END;
Step 2
When we have a deletion procedure ready, we can start preparing the database for the procedure to create invoice. We will begin by creating a procedure for connecting invoice with track in order to create new invoice lines. Such procedure will be later used for newly created invoice.
Task 2.1
Write addTrackToInvoice procedure which will be used to insert purchased tracks on the invoice (insert new entry to the InvoiceLine table). Procedure takes 3 parameters:
- 'invoice' of type int - id of invoice to which new lines are added,
- 'boughtTrack' of type int - id of track, which customer wants to buy, and
- 'trackQuantity' of type int - quantity of bought tracks.
The unit price, which must be included in InvoiceLine table record can be find in Track table (therefore invoice line should use actual unit price of selected track at the current time).
Comment
In order to store unit price value declare variable trackUnitPrice and in the body of procedure initialize it using 'SELECT ... INTO trackUnitPrice FROM ...', napr.:
INTO pocet
FROM track
WHERE name like 'M%';
Keep in mind that unitPrice may contain decimal places - we do not want to round out the value.
invoiceLineId of new record set as next succeeding number for the highest existing invoiceLineId.
Comment
Similarly, you need to select actual maximum invoiceLineId in InvoiceLine table, which should be used to increment in order to get new invoiceLineId.
Comment
Once you have made a new entry to InvoiceLine table write to output a report about new record, e.g.: "Inserted new invoiceLine (invoiceLineId -> 2243, invoiceId -> 324, trackId -> 329, unitPrice -> ,99, quantity -> 2)" for execution "execute addTrackToInvoice(324, 329, 2);".
Step 3
In the next role, we will finish our work on database with creation of procedure for inserting new invoice with all invoice lines. However, within the given procedure we will need the following type (it empties current buffer and thus force creation of new type - use the separator if you just want to write new procedure immediately after creation of new type and you want to run it as script):
CREATE OR REPLACE TYPE listOfTracksType IS VARRAY(10) OF INT;
Type listOfTracksType is an array of up to ten digits. We will use this type to specify the track ids, which are purchased with invoice.
Comment
Running the previously specified code will create the type in the database.
Comment
By using the VARRAY type, we must specify the maximum number of tracks that we are able to process (now we have specified limit of 10 tracks). In PL/SQL there are associative array and nested tables which don't have maximum limit. We can use them if you don't know exact maximum size of collection. If you want to do more, you can try to rewrite this task by using such type (see Collections). However, the use of an associative array requires a package declaration.
Task 3.1
Write buyTracks procedure to create an invoice for given shopping cart. Procedure takes 2 parameters:
- 'custId' of type int - id of buyer (customer), and
- 'boughtTracks' of type listOfTracksType - listo of ids of bought tracks.
In addition to the newInvoiceId variable for storing new invoiceId (same as during the previous task by incrementing the maximum id of invoiceId) you will need variable for storing customer address, which you will then use as the invoice address in the invoice table.
Comment
In this case, you have several options. You can create 5 variables, one for the address, city, state, country and postal code. However, it will be more convenient to load the entire customer record into the variable and then use only required data. Therefore, use only one customerData variable, which will be declared as %ROWTYPE (variable will be a RECORD type; defined by the type of one record from Customer table). You can access all data using the object notation - e.g.: 'customerData.address'.
After getting all the required information insert into Invoice table new record, with InvoiceDate being the current system date and invoice address will be copied actual customer address.
Comment
If you did not delete the total column in previous tasks, you must also calculate value for it.
Comment
Again, for verification ath the end of procedure write to output the report about successful insertion to database (output also values of each column of record).
For verification try to run this anonymous PL/SQL block:
DECLARE
tracks listOfTracksType := listOfTracksType();
BEGIN
tracks.extend(4);
tracks(1) := 3429;
tracks(2) := 3346;
tracks(3) := 335;
tracks(4) := 338;
buyTracks(59, tracks);
END;
It will create new invoice for customer Puja Srivastava with tracks: 'The Return', 'Ji Yeon', 'My Love', and 'I Can't Quit You Baby'.
Step 4
However, using procedure we have not yet created records in the InvoiceLine table, only records in Invoice table.
Task 4.1
Edit buyTracks procedure so that it also creates the necessary records in the InvoiceLine table with foreign keys to invoice record.
Comment
To insert a track on an invoice, use the addTrackToInvoice procedure you wrote in previous tasks. Just iterate in FOR cycle through all the identifiers of the purchased tracks and for each one call addTrackToInvoice with the ID of the newly created invoice, track and quantity is defined statically to one.
Comment
You can get the number of elements in the VARRAY variable by calling 'boughtTracks.count'. To get element on a particular position you can use 'boughtTracks(counter)', where the counter is the index number (starts with 1).
By calling the deleteInvoice procedure delete lastly added invoice and once again execute block:
DECLARE
tracks listOfTracksType := listOfTracksType();
BEGIN
tracks.extend(4);
tracks(1) := 3429;
tracks(2) := 3346;
tracks(3) := 335;
tracks(4) := 338;
buyTracks(59, tracks);
END;
Task 4.2
Use appropriate SELECT to verify that new invoice with all the tracks have been successfuly added to the database.
Additional Tasks
Task A.1
Chinook
Find the longest video in the database (with just one select).
Task A.2
Chinook
Modify the created procedure to ensure the integrity of the database.
- modify the addtracktoinvoice procedure so that, if its parameters refer to a non-existing invoice, or song, it throws a new exception (create custom type of exceptions)
- edit the buyTracks procedure so that if some call to addTrackToInvoice procedure throws exception, the procedure prints the output error and cancels any changes made to the database so far (that is, if it already created a new invoice and assigned it some tracks and during adding of next track there is an exception all previous changes will be reverted and database will be in state as was at the beginning), and throws the exception further
Comment
Remember that if you call COMMIT in each call to addTrackToInvoice, ROLLBACK will not help you.