7 short labs in Database

GEIT 3341 DATABASE I LAB 7

GEIT 3341 Database I

Lab 7

Stored Procedures & Triggers

Due Date:

Objective(s) Target CLO(s) Reference To practice writing and calling stored 6 Chapter 8 procedures and functions. To practice creating triggers and making them fire by executing the triggering event.

ID

Name

Section

Stored Procedures:

1. Complete and test the following stored procedure department_locations

that has one input parameter called dno that stores a department number,

and when called, will produce a report showing the location(s) of this

department. Here is an example to illustrate this procedure: (1.5 points)

SQL> exec department_locations(5);

Department Locations

================== Bellaire

Sugarland

Houston

GEIT 3341 DATABASE I LAB 7

create or replace procedure department_locations(dno IN INTEGER) IS

cursor locations_cursor is

begin

dbms_output.put_line(' Department Locations ');

dbms_output.put_line('=================== ');

open locations_cursor;

loop

end loop;

close locations_cursor;

end;

GEIT 3341 DATABASE I LAB 7

2. Write a stored procedure from scratch called show_details that has one

input parameter called in_Ssn that stores a social security number of an

employee, and when called, will produce a report showing the first and last

name of the employee, and the department name that the employee works

in. Here is an example to illustrate this procedure: (1.5 points)

SQL> exec show_details('123456789 ');

FirstName LastName Department Name

================================

John Smith Research

Functions:

3. Write a function called number_of_dependents that accepts a social

security number for some employee and returns the number of dependents

for this employee. (1 points)

GEIT 3341 DATABASE I LAB 7

For testing, write and submit an anonymous block so that the function

displays the number of dependents for the employee like (assuming that the

function was called using SSN=123456789) this: (1 points)

This employee has 3 dependents

Triggers:

1. Use the CREATE TABLE command in Chapter 7 to create the following table called ORDERS:

Column Name Data Type

OrderNo INTEGER

Product VARCHAR(20)

Units INTEGER

Price NUMBER

Total NUMBER

Next, write a statement-level trigger called update_order that will fire

whenever Units and/or Price is inserted/updated. When this happens, the

Total column will be set by trigger as the product of Units and Price. First, test

the trigger by inserting the following row:

INSERT INTO ORDERS(OrderNo,Product,Units,Price) VALUES(1001, 'PC', 2, 999);

(3 points)

GEIT 3341 DATABASE I LAB 7

Check that the Total column for this order is set correctly by the trigger to 1998.

Now, update the order like this:

UPDATE ORDERS SET Units=3 WHERE orderNo=1001;

Verify that the Total column for this order is set correctly by the trigger to 2997.

Finally, update the order like this:

UPDATE ORDERS SET Price=1200 WHERE orderNo=1001;

Verify that the Total column for this order is set correctly by the trigger to 3600.

2. Convert the trigger in the previous question from a statement-level trigger to a row-level trigger. Insert another order and test it as outlined in the previous question.

(2 points)