Stored Procedures and Stored Functions - Major Assessment Task 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
1 Develop and test SP/SFs to be executed within SQL Developer
2 Develop and test SP/SFs that use cursors executed within SQL Developer
3 Develop and test SP/SFs that utilise check constraints executed within SQL Developer
4 Develop and test SP/SFs that add and retrieve complex sale data and is executed within SQL Developer
5 Develop and test SP/SFs that deletes complex sale data and is executed within SQL Developer
6 Develop and test SP/SFs that Delete Customer and Delete Product executed within SQL Developer
7 Develop and test VB/C# code that calls SP/SFs from Part 1
8 Develop and test VB/C# code to handle cursors from Part 2
9 Develop and test VB/C# code to handle task 4,5,6 functionality
TASK 1
PART 1. Basic STORED PROCEDURES / FUNCTIONS and SQL DEVELOPER testing
Part 1.1. Create these stored procedures/function using SQL Developer
Part 1.2 Create these stored procedures/functions using SQL Developer
Attachment:- Database.rar