Solved: Write a procedure that takes a car registration and returns, PL-SQL Programming

+44 141 628 6080
[email protected]

Write a procedure that takes a car registration and returns

Question 1

Write a function (called most_recent_rental) that returns the most recent date of rental for a particular car (registration) for all completed bookings made for that car. If a rental does not exist for that car registration, the function should return 'NO RENTALS'

Write a second function (called maximum_rental_period) that returns the maximum rental period for a particular car (registration) for all completed bookings made for that car. If the car has only one booking but has not been returned it should return a '-999' for the rental period.

For both the functions, you should check for completed bookings, i.e. miles_in is not null.

Write a procedure that takes a car registration and returns the most recent date of rental and the maximum rental period for that car. The procedure had the following formal parameters

PROCEDURE CAR_DETAIL( REGISTRATION IN VARCHAR2, RECENT_DATE VARCHAR2, RENTAL_PERIOD OUT NUMBER)

Write an anonymous block to call the procedure using an explicit cursor to display the details (registration, most_recent_rental, maximum_rental_period) for all the cars in the i_car table by calling the procedure above. The output should only include cars that have a booking and have been returned.

The CAR_REGSTRATION was last rented on MOST_RECENT_RENTAL and has had the longest rental period of MAXIMUM_RENTAL_PERIOD.

Write ONE SQL statement that displays the registration, model name and cost for each car and calls the two functions to display the most recent date of rental and the maximum rental period for each of the cars.

Display the output of the SQL statement.

Question 2

The car hire company is concerned about possible unauthorised changes to customer details that may compromise the company's security and privacy policies. You will create a database trigger to monitor such changes to some important columns in the I_CUSTOMER table.

1. Create a copy of the I_CUSTOMER table in your own schema by using the following command:
CREATE TABLE MY_CUSTOMER AS SELECT * FROM I_CUSTOMER;

2. Also create a table to store change logs (CUSTOMER_CHANGELOG) by using the following command:

CREATE TABLE CUSTOMER_CHANGELOG

(

cust_no NUMBER(5),

cust_name VARCHAR2(20),

contact VARCHAR2(20),

log_date DATE);

3. Create a database trigger with the following specification:

fires AFTER an UPDATE on MY_CUSTOMER table.
fires if there is any change to cust_name or contact columns of the MY_CUSTOMER table.
fires for each row.
inserts a first row: the affected cust_no column, the OLD values of the cust_name and contact columns and the current date into the table CUSTOMER_CHANGELOG.
inserts a second row: the affected cust_no column, the NEW values of the cust_name and contact columns and the current date into the table CUSTOMER_CHANGELOG.

4. Test the trigger with the following specification:

Go ahead and make a change to MY_CUSTOMER table by issuing the following command:

UPDATE MY_CUSTOMER

SET cust_name = 'Sam Spade', contact = 'Kaptain Krunch'

WHERE cust_no = 8981;

5. Now, select all rows from the CUSTOMER_CHANGELOG table. This should display the old and new values of the changes you made due to firing of the trigger. Output all the rows of CUSTOMER_CHANGELOG table.

Question 3

Create a PL/SQL procedure called report_rental_month which has the needed IN parameters for year and month. The procedure will display to the screen the following format for each car rented during that year/month in the i_booking table. The information needs to be retrieved using an explicit cursor for all rentals in that year for that month.