Database Systems - Stored Procedures and Stored Functions
Background
You are to create tables to store product and customer data.
You will create a number of stored procedures and functions (SPFs) to insert / update / delete / query data.
These SPFs will be called from
- Additional stored procedures that can be executed from anonymous blocks via SQL Developer
- A host application written in VB or C#
Some SPFs may modify data in multiple rows in multiple tables
This will require you to demonstrate the use of handling database transactions. Some SPFs may require data to be passed / returned using cursors
PART 1. Basic STORED PROCEDURES / FUNCTIONS and SQL DEVELOPER testing
Part 1.1. Create these stored procedures/function using SQL Developer
Test Strategy.
Write an anonymous block to test these stored procedures/functions.
PART 2. Cursors and SQL DEVELOPER testing
Part 2.1. Create these stored procedures/function using SQL Developer
Part 2.2.
Copy and paste the stored procedure/function code above into the file named Ass1_SQLCode.sql
Part 2.3.
Execute the following block of code in SQL Developer. (Change the student id on line 2)
PART 3. Check Constraints and SQL DEVELOPER testing
Part 3.1. Create these stored procedures/function using SQL Developer
Part 3.2.
Copy and paste the stored procedure/function code above into the file named Ass1_ Code.sql
Part 3.3.
Execute the following block of code in SQL Developer. (Change the student id on line 2)
PART 4. Complex Sale in SQL Developer
Part 4.1. Create these stored procedures/function using SQL Developer
Part 4.2.
Add the statement DELETE FROM SALE; to the top of the section named "TEST DELETION OF EXISTING DATA"
Add the following statements to TEST PART 4 section of the script
PART 5. Delete Complex Sale in SQL Developer
Part 5.1. Create these stored procedures/function using SQL Developer
Part 5.2.
Remove the statement DELETE FROM SALE; from the section named "TEST DELETION OF EXISTING DATA"
Replace it with the statement DELETE_ALL_SALES_VIASQLDEV;
PART 6. Custom-made exceptions for attempted deletion of child rows
Part 6.1. Create the Delete Customer and Delete Product procedures
Part 6.2.
Add the following statements to TEST PART 6 section of the script
PART 7. Visual Basic STORED PROCEDURES / FUNCTIONS and SQL DEVELOPER testing
Part 7.1. Create these stored procedures/function using SQL Developer
Create a VB or C# host application that calls stored procedures and functions from part 1.
Details:
Create a button (or menu item if you want to create a menu) for various requirements.
PART 8. Visual Basic / Cursors / Packages
Part 8.1. Create these stored procedures/function using SQL Developer
Modify the VB or C# application so that there are two additional buttons or menu options to retrieve all customer and product data.
PART 9. Visual Basic - remaining functionality
Part 9.1. Create these stored procedures/function using SQL Developer
Modify the VB or C# application so that there are additional buttons or menu options to retrieve carry out all other functionality that you have created in parts 4, 5 & 6.
Attachment:- Stored Procedures and Stored Functions.rar