First Quiz on PL/SQL
First problem: Using the code below, explain the output that would be
generated from this PL/SQL code if the identification number was entered as 2222. The
table inven is shown below.
SET SERVEROUTPUT ON
ACCEPT in_itemno PROMPT 'Enter the identification number: '
DECLARE
v_itemno inven.itemno%TYPE;
v_itemname inven.itemname%TYPE;
v_cost inven.cost%TYPE;
v_price inven.price%TYPE;
BEGIN
SELECT itemno, itemname, cost, price
INTO v_itemno, v_itemname, v_cost, v_price
FROM inven
WHERE itemno = &in_itemno;
dbms_output.put_line (v_itemno||' '||v_itemname||' '||v_cost||' '||v_price);
END;
/
SET SERVEROUTPUT OFF
SQL> select * from inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 48 51.99 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
Second problem: Explain the PL/SQL code above, be specific.
Third problem: Using the PL/SQL code below, explain the output that would be
generated if the identification number 2222 was entered and then if the identification
number 3333 was entered. The table being used is the same as inven shown below.
SET VERIFY OFF
DECLARE
v_itemno new_inven.itemno%TYPE :=&input_itemno;
v_price new_inven.price%TYPE;
v_cost new_inven.cost%TYPE;
BEGIN
SELECT price, cost INTO v_price, v_cost
FROM new_inven
WHERE itemno = v_itemno;
IF v_price < v_cost * 1.2 THEN
v_price := v_cost * 1.2;
ELSE
IF v_price > v_cost * 1.3 THEN
v_price := v_cost * 1.3;
END IF;
END IF;
UPDATE new_inven
SET price = v_price
WHERE itemno = v_itemno;
END;
/
SET VERIFY ON
Fourth problem: Explain the PL/SQL code above, be specific.
SQL> select * from inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 48 51.99 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
6789 BAT/BALL 14.99 21.99
7890 Mother Goose 15.25 16.99
10 rows selected.
Fifth problem: Using the code below, explain the output that would be generated.
SQL> select * from looptable;
IDNO ANAME AMT CO
--------- --------------- --------- --
1 Susan Smith 5000 AB
2 John Davis 4000 DG
3 Jane Costa 4500 LK
4 Lawrence Morris 3500 AB
5 Catherine Brown 2000 DG
6 Richard Souza 3500 LK
7 Ann Riley 5000 AB
SQL> desc looptable
Name Null? Type
------------------------------- -------- ----
IDNO NUMBER(3)
ANAME VARCHAR2(15)
AMT NUMBER(4)
CODE CHAR(2)
DECLARE
v_idno looptable.idno%TYPE;
v_aname looptable.aname%TYPE;
v_amt looptable.amt%TYPE;
v_code looptable.code%TYPE;
v_toget looptable.idno%TYPE :=1;
BEGIN
LOOP
SELECT idno, aname, amt, code
INTO v_idno, v_aname, v_amt, v_code
FROM looptable
WHERE idno = v_toget;
IF v_code = 'AB' THEN
IF v_amt > 3000 THEN
v_amt := v_amt + v_amt * .1;
ELSE
v_amt := v_amt + v_amt * .25;
END IF;
ELSE
v_amt := v_amt + 1000;
END IF;
UPDATE looptable
SET amt = v_amt
WHERE idno = v_toget;
v_toget := v_toget + 1;
EXIT WHEN v_toget > 7;
END LOOP;
END;
/
Sixth problem: Explain the PL/SQL code above, be specific.
Seventh problem: Using the code below, explain the output that would be
generated.
DECLARE
v_idno looptable.idno%TYPE;
v_aname looptable.aname%TYPE;
v_amt looptable.amt%TYPE;
v_code looptable.code%TYPE;
v_toget looptable.idno%TYPE :=1;
BEGIN
WHILE v_toget < 8 LOOP
SELECT idno, aname, amt, code
INTO v_idno, v_aname, v_amt, v_code
FROM looptable
WHERE idno = v_toget;
IF v_code = 'AB' THEN
IF v_amt > 3000 THEN
v_amt := v_amt + v_amt * .1;
ELSE
v_amt := v_amt + v_amt * .25;
END IF;
ELSE
IF v_code = 'DG' THEN
IF v_amt < 3000 THEN
v_amt := v_amt + 500;
ELSE
v_amt := v_amt + 1000;
END IF;
ELSE
v_amt := (v_amt + 2000)/2;
END IF;
END IF;
UPDATE looptable
SET amt = v_amt
WHERE idno = v_toget;
v_toget := v_toget + 1;
END LOOP;
END;
/
Eighth problem: Explain the PL/SQL code above, be specific.