For this laboratory exercise, you will use the same database schema of the Pine Valley Furniture Company. The database schema is provided for you.
Construct the following PL/SQL programs and be able to provide a screenshot of the output.
1. Create a procedure that displays the name of the customer with the highest amount of orders among all customers. Name the procedure PRINT_CUST_PROC. The procedure will also invoke a function called GET_HIGHORDER_FUNC that retrieves the name and total amount ordered of customer with the highest amount of order. Invoke the procedure and view the results.
2. Create a trigger called VALIDATE_DATEHIRED_TRIG to validate the hired date of an employee when a new row is added to the EMPLOYEE table. If the employee hired date is more than current date, display an appropriate message that prompts the user regarding the error and include an exception. The new row will be inserted to the table only if the hired date is valid. Test the trigger by inserting a new row to the EMPLOYEE table with an invalid hired date.
3. Do the same in Item #1 to validate employee's date of birth. This time you create a procedure called VAL_BDAY_PROC that will validate the employee's date of birth. The procedure accepts the date of birth as a parameter. Then create a trigger called VALIDATE_BDAY_TRIG that invokes the VAL_BDAY_PROC. Test the trigger by inserting a new row to the EMPLOYEE table with an invalid birthday.
4. Modify the PRODUCT table by adding a new column named PRODUCT_ONHAND with data type NUMBER(3,0). Add the value 10 in each row of the PRODUCT_ONHAND column. Create a function called GET_QUANTITY_FUNC that retrieves the on hand value of a specific product. Create a procedure called CHECK_ONHAND_PROC that ensures that the quantity ordered by customer is less than or equal to the on hand value of the product. Invoke the GET_QUANTITY_FUNC and CHECK_ONHAND_PROC inside a trigger and name it as CHECK_ONHAND_TRIG. If the quantity ordered by the customer is not greater than the quantity on hand, update the column PRODUCT_ONHAND in the PRODUCT table by subtracting quantity ordered from product on hand. Include exception handling and test the trigger by inserting new rows to the ORDERLINE table with ordered quantity 11 and 5.
5. Create a table called EVENTLOGS with the following attributes:
Column Data type Constraint Description
Eventlog_id Number(3,0) Primary key A sequence of number that identifies the user event logs
User_name Varchar2(20) The name of the user logged in to the database
Date_done Date Date when the user logged in to the database
Time_done Timestamp Time when the user perform an operation to the database
Action_done Varchar2(50) The action performed by the user to the database
Object_name Varchar2(30) The table from which the action was done
Create a sequence called EVENTLOG_ID_SEQ to generate an automatic id number for the
EVENTLOG_ID column whenever the trigger is fired. START WITH 1 INCREMENT BY 1 NO CYCLE.
Create an AFTER trigger that records all the information about the action performed by the user who logged in to the database. Perform at least 10 different DML operations on the PVFC database to test the trigger. Then display the content of the EVENTLOGS table. Sample output:
Eventlog_id User_name Date_done Time_done Action_done Object_name
1 System 6/22/2016 7:00pm Insert ID 101 EMPLOYEE
2 System 6/22/2016 7:22pm Update salary EMPLOYEE
3 HR 6/22/2016 7:30pm Delete ID 10 PRODUCT
Attachment:- Exercise.rar