Oracle Assignment using Cursors
First problem: Set up an inventory table that has at least an item number, item name, on hand and
on order. If you have an existing inventory table that has these as a minimum you can use it. Set up
an appropriate key. Set up a second table that has the item number, the date of receipt and the
number received. Set up an appropriate key.
The processing that you want to do involves subtracting from the on order and adding to the on hand. Set
up transactions that will not put the on order into a negative state. You should have one inventory item
where there are no receipts and at couple of inventory item where there are multiple receipts.
Write hte PL/SQL to process this.
Second problem: Using COPIES of the employee and department tables provided by Oracle or using similiar
taples that provide employee, salary, job and dept in one table and dept number and department name in
another table, write the following program. Use the dept table to step through sequentially and bring up the
records with the same department from the employee table. Using an IF statement calcuate a new salary based
on the job (you decide on the criteria). Update each record on the employee file (this is why you should use
copies) with the new salary. In addition, calculate the total salary for each department and create a new
table with the department number, the department name and the salary.
Third problem: Using the payroll tables that you recently designed and implemented do the following.
Modify the project table to have the number of hours that the project has available. Using a cursor step
through the project table and for each project bring up the corresponding transactions that contain the
employee hours spent on the project in another cursor. Sum the hours spent on the project. Then update the
hours the project has available by subtracting the sum.
For example if project 111 has 500 hours available and 125 hours were spent on the project this week,
subtract the 125 from the 500 and put 375 back in the project table.