PL/SQL Opportunity No. 5
PL/SQL Opportunity No. 5 asks you to create a revised version of the update_item_cost procedure found on page 2 of the handout, “Creating and Referencing a PL/SQL Procedure” in Packet 2. Your procedure should handle two requirements not included in the version of update_item_cost that appears on page 2 of the Creating and Referencing a PL/SQL Procedure” handout.
Requirement 1. Create a user defined exception handler that does not allow a null value to be inserted as the item cost in the pitem table when the user enters a null value for item cost in your equivalent of the p403.sql PL/SQL block that calls the update_item_cost procedure. In other words, instead of entering a null value for the item cost like we did on page 4 of the “Creating and Referencing a PL/SQL Procedure” handout, your exception handler should replace the null cost entered by the user with the original cost for the item. Consider the following example. Instead of your procedure replacing the item cost for item_id 10 with a null value, the original value of 25 should be retained. In addition, a row should be added to the pitem_audit table that displays a message as to what has happened. By the way, if you hard code the 25 into your message, I’m going to return your procedure to you for additional fine tuning.
SQL> start d:\mis4386fall2014\plsqlstuff\p403
Enter value for item: 10
Enter value for cost: ''
PL/SQL procedure successfully completed.
SQL> select * from pitem where item_id = 10;
ITEM_ID ITEM_NAME ITEM_DESC RETAIL_PRICE ITEM_COST CATEGORY
------
10 25
1 row selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
3 rows selected.
Requirement 2. It turns out that the pitem table has duplicate rows. Thus the too_many_rows exception handler needs to be incorporated into your procedure. The too_many_rows exception handler should
- delete the duplicate rows and then allow the row to be inserted by the user via the p403.sql block to be inserted into the pitem table and
- insert a row into the pitem_audit table like the one that appears in the example that follows:
SQL> start d:\mis4386fall2014\plsqlstuff\p403
Enter value for item: 7
Enter value for cost: 77
PL/SQL procedure successfully completed.
SQL> select item_id, item_cost from pitem;
ITEM_ID ITEM_COST
------
1 10
2 10
3 10
4 10
5 10
6 10
8 10 Observe that the two rows for item_id 7 no longer appear.
9 10
10 99
11 10
12 10
13 50
14 10
15 10
16 10
16 10
17 10
18 10
19 10
20 10
7 77
21 rows selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
7 More than one row for this item: both rows deleted, new row inserted
4 rows selected.
I have emailed to each member of the class a file twotables.sql that you can use to create and populate the pitem table and create the pitem_audit table. My guess is that as you are testing the generality of your update_item_cost procedure that you may need to re-run this file each time you begin retesting your procedure.
Pages 3-8 of this opportunity contain 12 test cases. Your procedure should be able to replicate the result of each test. As far as deliverables go, please begin by printing the update_item_cost procedure you are testing, the PL/SQL block you are using in your testing to call the procedure, the original content of the pitem table, and the original content of the pitem_audit table. Follow this by printing the results of each test. Number and highlight each test. In other words, please try to make your output look at least as good as that which appears on pages 3 through 8. Please use the SQL*Plus set echo off command and the set verify off command. It is not necessary for me to see of listing of your equivalent of the p403.sql file for each test case. Incidentally, Test 12 requires the use of another PL/SQL Predefined Exceptions. Incidentally Test 11 illustrates how the entry of character number (e.g. ‘12’) is automatically converted to a number by SQL.
I decided against providing you with the original version of the p401.sql and p403.sql since it should not take to long to recreate these files.
The update_item_cost procedure found on page 2 of the handout
create or replace procedure update_item_cost(itemid integer, fnewcost number) as
fcurcost number(10,2);
missing_cost exception;
begin
select item_cost into fcurcost from pitem
where item_id=itemid;
if fcurcost is null then
raise missing_cost;
else
update pitem set item_cost=fnewcost
where item_id=itemid;
end if;
commit;
exception
when no_data_found then
insert into pitem_audit
values (itemid, 'Invalid Item identified.');
commit;
when missing_cost then
insert into pitem_audit
values (itemid, 'Item Cost is NUll');
commit;
when others then
rollback;
insert into pitem_audit
values (itemid, 'Miscellaneous error.');
commit;
end update_item_cost;
/
SQL> set echo off
SQL> select item_id, item_cost from pitem;
ITEM_ID ITEM_COST
------
1 10
2 10
3 10
4 10
5 10
6 10
7 10
7 10
8 10
9 10
10 10
11 10
12 10
13 10
14 10
15 10
16 10
16 10
17 10
18 10
19 10
20 10
22 rows selected.
SQL> select * from pitem_audit;
no rows selected
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 1
Enter value for item: 10
Enter value for cost: 25
PL/SQL procedure successfully completed.
SQL> select * from pitem where item_id = 10;
ITEM_ID ITEM_NAME ITEM_DESC RETAIL_PRICE ITEM_COST CATEGORY
------
10 25
1 row selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 2
Enter value for item: 50
Enter value for cost: 25
PL/SQL procedure successfully completed.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
1 row selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 3
Enter value for item: ''
Enter value for cost: 25
PL/SQL procedure successfully completed.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
2 rows selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 4
Enter value for item: 10
Enter value for cost: ''
PL/SQL procedure successfully completed.
SQL> select * from pitem where item_id = 10;
ITEM_ID ITEM_NAME ITEM_DESC RETAIL_PRICE ITEM_COST CATEGORY
------
10 25
1 row selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
3 rows selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 5
Enter value for item: 10
Enter value for cost: 99
PL/SQL procedure successfully completed.
SQL> select * from pitem where item_id = 10;
ITEM_ID ITEM_NAME ITEM_DESC RETAIL_PRICE ITEM_COST CATEGORY
------
10 99
1 row selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
3 rows selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 6
Enter value for item: 13
Enter value for cost: 50
PL/SQL procedure successfully completed.
SQL> select * from pitem where item_id > 9 and item_id < 15;
ITEM_ID ITEM_NAME ITEM_DESC RETAIL_PRICE ITEM_COST CATEGORY
------
10 99
11 10
12 10
13 50
14 10
5 rows selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
3 rows selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 7
Enter value for item: 7
Enter value for cost: 77
PL/SQL procedure successfully completed.
SQL> select item_id, item_cost from pitem;
ITEM_ID ITEM_COST
------
1 10
2 10
3 10
4 10
5 10
6 10
8 10
9 10
10 99
11 10
12 10
13 50
14 10
15 10
16 10
16 10
17 10
18 10
19 10
20 10
7 77
21 rows selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
7 More than one row for this item: both rows deleted, new row inserted
4 rows selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 8
Enter value for item: 16
Enter value for cost: 66
PL/SQL procedure successfully completed.
SQL> select item_id, item_cost from pitem;
ITEM_ID ITEM_COST
------
1 10
2 10
3 10
4 10
5 10
6 10
8 10
9 10
10 99
11 10
12 10
13 50
14 10
15 10
17 10
18 10
19 10
20 10
7 77
16 66
20 rows selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
7 More than one row for this item: both rows deleted, new row inserted
16 More than one row for this item: both rows deleted, new row inserted
5 rows selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 9
Enter value for item: 7
Enter value for cost: ''
PL/SQL procedure successfully completed.
SQL> select item_id, item_cost from pitem;
ITEM_ID ITEM_COST
------
1 10
2 10
3 10
4 10
5 10
6 10
8 10
9 10
10 99
11 10
12 10
13 50
14 10
15 10
17 10
18 10
19 10
20 10
7 77
16 66
20 rows selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
7 More than one row for this item: both rows deleted, new row inserted
16 More than one row for this item: both rows deleted, new row inserted
7 Null Cost replaced by original cost of 77
6 rows selected.
SQL> start d:\mis4386fall2014\plsqlstuff\p403Test 10
Enter value for item: 20
Enter value for cost: 200
PL/SQL procedure successfully completed.
SQL> select item_id, item_cost from pitem;
ITEM_ID ITEM_COST
------
1 10
2 10
3 10
4 10
5 10
6 10
8 10
9 10
10 99
11 10
12 10
13 50
14 10
15 10
17 10
18 10
19 10
20 200
7 77
16 66
20 rows selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
50 Invalid Item identifier.
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
7 More than one row for this item: both rows deleted, new row inserted
16 More than one row for this item: both rows deleted, new row inserted
7 Null Cost replaced by original cost of 77
6 rows selected.
SQL> start d:\mis4386fall2014\plsqlbasics\p403Test 11
Enter value for item: '12'
Enter value for cost: 100
PL/SQL procedure successfully completed.
SQL> select item_id, item_cost from pitem;
ITEM_ID ITEM_COST
------
1 10
2 10
3 10
4 10
5 10
6 10
8 10
9 10
10 99
11 10
12 100
13 50
14 10
15 10
17 10
18 10
19 10
20 200
7 77
16 66
20 rows selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
7 More than one row for this item: both rows deleted, new row inserted
50 Invalid Item identifier.
16 More than one row for this item: both rows deleted, new row inserted
7 Null Cost replaced by original cost of 77
6 rows selected.
SQL> start d:\mis4386fall2014\plsqlbasics\p403Test 12
Enter value for item: '1x'
Enter value for cost: 250
PL/SQL procedure successfully completed.
SQL> select item_id, item_cost from pitem;
ITEM_ID ITEM_COST
------
1 10
2 10
3 10
4 10
5 10
6 10
8 10
9 10
10 99
11 10
12 100
13 50
14 10
15 10
17 10
18 10
19 10
20 200
7 77
16 66
20 rows selected.
SQL> select * from pitem_audit;
ITEM_ID MESSAGE
------
Invalid Item identifier.
10 Null Cost replaced by original cost of 25
7 More than one row for this item: both rows deleted, new row inserted
50 Invalid Item identifier.
16 More than one row for this item: both rows deleted, new row inserted
7 Null Cost replaced by original cost of 77
You entered an invalid number
7 rows selected.
1
Caution: Use may find this opportunity addicting.