Description

PL/SQL, Procs, and Funcs  (MIS 325)                                      Nov 2019.v1

Instructions:

·         Do your own work: This is an individual assignment and you must do your own work and create your own SQL statements.  If you are caught cheating on this, you will receive a zero on this assignment and be reported to the Dean of Students.  Also this homework prepares you for the exam coming up so doing the work now will help you learn and do well on when it counts more.

·         What to turn in

o   Clearly separate your code for each question. Save your code into one SQL file with the naming format: LastName_FirstName_UTEid.  Please make sure the lastname and firstname you use matches what is in Canvas.

o   Submit your .sql file on Canvas before the deadline.  Late submissions receive 50% off.  No submissions will be accepted 24 hours after the deadline.

·         The SQL problems below will be based on the DDL script that is posted on the Canvas instructions.  Download that script and run it before you start.

PL/SQL Problems:

1.      Write a script that uses an anonymous block of PL/SQL code to declare a variable called vendor_count and set it to the count of all California vendors in the Vendors table. If the count is greater than 30, the block should display a message that says, “The number of vendoers is greater than 30”. Otherwise, it should say, “The number of vendors is less than or equal to 30”.  Make sure you set the server output to be on before the PL/SQL block of code

2.      Write a script that uses an anonymous block of PL/SQL code that uses a bulk collect to capture a list of all invoice_ids where the balance due is greater than zero i.e. (invoice_total – credit_total – payment) > 0. The rows in this result set should be sorted in ascending sequence invoice_id. Then, the procedure should display a string variable for each invoice_id in the list to include the invoice_id and some explicit text that looks something like this:

"Invoice 3 is not paid”

3.      Write a script that uses an anonymous block of PL/SQL code that attempts to insert a new department named “IT Dept” into the Departments table. If the insert is successful, the procedure should display this message:

1 row was inserted.

If the update is unsuccessful, the procedure should display this message:

Row was not inserted - duplicate entry.

Procedure/Function Problems:

4.      Write a script that creates and calls a stored procedure named insert_department. First, code a statement that creates a procedure that adds a new row to the Department table. To do that, this procedure should have one parameter for the department name since there is a sequence for department_id.  Also handle exceptions generally by rolling back the transaction “when others” occurs.

Code at least two CALL statements that test this procedure. (Note that this table does allow duplicate category names.) 

5.      Write a script that creates and calls a stored function named phone_lookup that retrieves the phone from the Addresses table for a customer. To do that, this function should accept one parameter for the customer_id, and it should return the value of the Addresses.Phone for that customer.

Use the following SELECT statement to test this function.

SELECT first_name, last_name, email_address, phone_lookup(customer_id)

FROM customers_mgs;