Will This Be On The Turing Test?: A closer look at a simple PL/SQL procedure

Please refer to page 569 of Steven Feuerstein's classic "Oracle PL/SQL Programming", the fifth edition. This blog post will focus on the procedure "apply_discount". Before going any further, note the following three important things:
1. To actually create the procedure, the "PROCEDURE" keyword must, obviously, be preceded by the "CREATE" keyword.
2. Two typos exist on line 11: (i) change ":=" to just "=", and (ii) remove the semicolon at the end of the line.
3. The table name "order" is invalid (it raises a "ORA-00903: invalid table name" error), because "order" is a reserved word, so change it to "order_table".

Now let's get down to business.

1. First, create the tables:

CREATE TABLE ORDER_TABLE
( ITEM_ID NUMBER,
ORDER_ID NUMBER,
ITEM_AMOUNT NUMBER);

CREATE TABLE ITEM
( COMPANY_ID NUMBER,
ORDER_ID NUMBER,
SALESPERSON_ID NUMBER);

CREATE TABLE COMPANY
( COMPANY_ID NUMBER,
COMPANY_NAME VARCHAR2(30));

---------------------------------------------

2. Second, populate the tables with some data:

insert into order_table
values (1, 1, 3);
insert into order_table
values (2, 1, 4);
insert into order_table
values (3, 2, 5);

insert into item
values (1, 1, 10.0);
insert into item
values (2, 1, 20.0);
insert into item
values (1, 2, 10.0);
insert into item
values (3, 2, 30.0);
insert into item
values (2, 3, 20.0);

insert into company
values (1, 'bozo_inc');
insert into company
values (2, 'bus_inc');

---------------------------------------------

3. Third, create the procedure

CREATE PROCEDURE apply_discount
(company_id_in IN company.company_id%TYPE, discount_in IN number)
IS
min_discount CONSTANT NUMBER := 0.05;
max_discount CONSTANT NUMBER := .25;
invalid_discount EXCEPTION;
BEGIN
IF discount_in BETWEEN min_discount AND max_discount
THEN
UPDATE item
SET item_amount=item_amount*(1-discount_in)
WHERE EXISTS ( SELECT 'x' FROM order_table
WHERE order_table.order_id=item.order_id
AND order_table.company_id=company_id_in);
IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; END IF;
ELSE
RAISE invalid_discount;
END IF;
EXCEPTION
WHEN invalid_discount
THEN
DBMS_OUTPUT.PUT_LINE('The specified discount ('||discount_in||') is invalid.');
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('No orders in the system for this company: '||
TO_CHAR(company_id_in))
END apply_discount;

For your cutting and pasting convenience, here it is without the line numbers:

CREATE PROCEDURE apply_discount
(company_id_in IN company.company_id%TYPE, discount_in IN number)
IS
min_discount CONSTANT NUMBER := 0.05;
max_discount CONSTANT NUMBER := .25;
invalid_discount EXCEPTION;
BEGIN
IF discount_in BETWEEN min_discount AND max_discount
THEN
UPDATE item
SET item_amount=item_amount*(1-discount_in)
WHERE EXISTS ( SELECT 'x' FROM order_table
WHERE order_table.order_id=item.order_id
AND order_table.company_id=company_id_in);
IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; END IF;
ELSE
RAISE invalid_discount;
END IF;
EXCEPTION
WHEN invalid_discount
THEN
DBMS_OUTPUT.PUT_LINE('The specified discount ('||discount_in||') is invalid.');

WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('No orders in the system for this company: '||TO_CHAR(company_id_in))
END apply_discount;

---------------------------------------------

4. Fourth, check to make sure things are as they should be:

select i.item_id, i.order_id, c.company_name, i.item_amount
from item i join order_table o on (i.order_id = o.order_id)
join company c on (o.company_id = c.company_id)
order by 1;

ITEM_ID ORDER_ID COMPANY_NAME ITEM_AMOUNT
---------- ---------- ------------------------------ -----------
1 1 bozo_inc 10
1 2 bozo_inc 10
2 3 bus_inc 20
2 1 bozo_inc 20
3 2 bozo_inc 30

Notes:
a. According to the data loaded into the tables in Step 2 above, there are two companies, three orders, and five line items spread across those three orders. All of the data is summarized in the select statement directly above.
b. For this data, the "item" table has one line for each line item in each order. Therefore, the item_id field is not unique. If you wanted to (and you should want to!) have a primary key on this table it would be a composite key made up of "item_id" and "order_id". There should ~~probably~~ also be a "qty" field. And a better name for the table would be "line_item".

---------------------------------------------

5. Fifth run the procedure (after making a backup):

create table item_bup as select * from item;
Table created.
execute apply_discount(2, 0.15);
PL/SQL procedure successfully completed.

---------------------------------------------

6. Sixth, compare with results from step Four above:

ITEM_ID ORDER_ID COMPANY_NAME ITEM_AMOUNT
---------- ---------- ------------------------------ -----------
1 1 bozo_inc 10
1 2 bozo_inc 10
2 3 bus_inc 17
2 1 bozo_inc 20
3 2 bozo_inc 30

Notes: The discount was correctly applied to only items ordered by company #2, "bus_inc".

---------------------------------------------

7. Seventh, restore original table and rerun on other company:

drop table item;
create table item as select * from item_bup;
execute apply_discount(1, 0.15);

ITEM_ID ORDER_ID COMPANY_NAME ITEM_AMOUNT
---------- ---------- ------------------------------ -----------
1 1 bozo_inc 8.5
1 2 bozo_inc 8.5
2 3 bus_inc 20
2 1 bozo_inc 17
3 2 bozo_inc 25.5

This time, the discount was applied to items ordered by company #1, but the price for items ordered by company #2 were left unchanged.

---------------------------------------------

8. What's up with that "WHERE EXISTS" statement?

The where statement on line 13 (refer to the procedure in Step 3 above) is unnecessarily convoluted, or is it? The following select statement would achieve the same results, and is more straightforward:

WHERE order_id in
(select order_id
from order_table
where company_id = company_id_in)

But "EXISTS" is famously more efficient than "IN":
http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance/2065374#2065374

Also, "IN" can cause serious problems related to "three-value logic" (ie, NULLs).

Also check out this interesting discussion of IN versus JOIN versus EXISTS:
http://www.gregreda.com/2013/06/03/join-vs-exists-vs-in/

Will This Be On The Turing Test?

Tuesday, October 22, 2013

A closer look at a simple PL/SQL procedure