Lab assignment # 5, version 1.1
Coordinator: Pär Douhan,
Lab assignment# 5 - Procedures, functionsand triggers
In this lab you will work with stored procedures, functions and triggers in OraclePLSQL.
Start by erasing the objects in your database schema, from the earlier lab, by the following cut and paste operation.
------Clear your database schema START COPY------
DROP TABLE car;
DROP TABLE car_owner;
------Clear your database schema END COPY------
Task 1
Create a function get_return_date. It is intended to be stored in a variable that is printed on a lease.
Example: One person goes into a hire firm and rents an item in, say 7 days. If it is the 4th of October at 23:59, the function returns: date + hour + minutes, The function returns varchar2, i.e. a string.
SELECT get_return_date(7)
FROM dual;
GET_RETURN_DATE(7)
------
2016-10-11:23:59
Task 2
Create one table and one sequence according to the following specification:
SQL> desc customer
Name Null? Type
------
CUST_ID PRIMARY KEY NOT NULL NUMBER(9)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(30)
Anda sequencewith the name customer_seq.
Task 3
Create a trigger that startsbefore insert on the customer table. The triggershould fix primary key valuesfor the columncust_id. Use the sequence (customer_seq)to generate unique values. Finally,use the trigger to reformat the first - and last name to begin with a capital letter.
Task 4
Create a stored procedurewith the namedo_new_customer. The procedure should be usedfor adding new rows in the customer table. The procedure takes two parameters:first name and last name.
Task 5
Create a table according to the following specification:
SQL> desc customer_log
Name Null? Type
------
WHO VARCHAR2(25)
WHEN DATE
EVENT VARCHAR2(15)
The table is used to log DML events in the table customer
Task 6
Create a trigger that starts after insert, update or deleteon the customer table. The trigger should insert a row in the table customer_log. Who: is the logged in user. When: the date and time. Event: which DML was used? (insert, update or delete)
Task 7
Create a trigger that starts before insert, update or deleteon the customer table. The trigger must ensurethat only the user'BOSS'is authorized to perform DMLoperationsagainst thecustomer table. Create anexceptionthat throws whensomeone other than user BOSStries to perform DML on the customer table. Make sure that an appropriate error code and error message is printed out in the exception handler.
Task 8
Create a function with the name get_distance. This function receives four parametersof the data typenumber. These parameters are actually two coordinates describing spatial information. The first coordinates (y1, x1), and the second coordinates (y2, x2), where y-values indicate the distance in meters from the equator, and the x-values indicate the distance in meters from the Greenwich meridian. The function should return the distance in meters between the two points.
In order to solve the task, you should be familiar with the Pythagorean Theorem. PLSQL Built-in-functions: SQRT(9) = 3, POWER(3,2) = 9.
Correct output, example:
SELECT get_distance(6000000,2000000,6000256,2000658)
FROM dual;
GET_AVSTÅND(6000000,2000000,6000256,2000658)
------
706
In the example above, it is 706 meters as the crow flies between the points P1(6000000,2000000)andP2(6000256,2000658).
Optional task (not necessary for the lab)
A company sends a 4-digit date code in their text files: for example, 1234, where 1 represents the year 2001, and 234 representsthe day No 234 in 2001.
Write a function get_date which is receiving the date code and returnsadate on the form 'YYYY-MM-DD'.
Put the lab report in Learn (Blackboard). Click on the link Assignments.
Pär Douhan:
1(3)