 |
|
Oracle Tips by Burleson |
Declaring and Using a PL/SQL
Cursor
In this assignment, we have been asked to
create a report showing all individual sales for more than 1,000
copies. We should have enough code snippets that it is not necessary
to provide you with extensive code hints. If you get stuck, use the
PL/SQL cursor loop from tutorial 6 as a template for this assignment.
Step 1- Write the SQL - Our first step is
write a query that summarizes sales, organized by book title, store
and order date (see below). Also note that the display is in
descending order according to the size of the order.
BOOK_TITLE
STORE_NAME ORDER_DATE
SUM_QTY
------------------------------ ------------------------------
---------- --------
pay no taxes
and go to jail eaton books 29-MAR-02
9,900
DOS for
dummies borders 19-MAY-02
8,900
the willow
weeps no more borders 10-MAY-02
8,800
windows
sucks wee bee books 20-MAY-02
8,800
windows
sucks books for dummies 17-MAY-02
8,000
pay no taxes
and go to jail hot wet and sexy books 13-APR-02
5,500
piano
greats specialty bookstore 18-MAY-02
5,500
writers
market hot wet and sexy books 06-MAY-02
5,500
piano
greats hot wet and sexy books 26-APR-02
5,200
managing
stress quagmire books 14-MAY-02
5,000
The zen of
auto repair hot wet and sexy books 25-MAR-02
3,600
managing
stress blue ride booksellers 28-MAR-02
3,300
the fall of
microsoft books for dummies 10-FEB-02
2,000
piano
greats ignoramus and dufus 02-APR-02
1,900
piano
greats eaton books 04-JAN-02
1,020
windows
sucks barnes and Noble 02-JAN-02
1,000
writers
market wee bee books 10-FEB-02
1,000
Step 2 - Place this SQL inside a PL/SQL code block, and display only
those sales that are for more than 1,000 units:
"Pay No
Taxes And Go To Jail" had an order on 29-MAR-02 by Eaton Books for
9900 copies.
"Dos For
Dummies" had an order on 19-MAY-02 by Borders for 8900
copies.
"The Willow
Weeps No More" had an order on 10-MAY-02 by Borders for 8800
copies.
"Windows
Sucks" had an order on 20-MAY-02 by Wee Bee Books for 8800
copies.
"Windows
Sucks" had an order on 17-MAY-02 by Books For Dummies for 8000
copies.
"Pay No
Taxes And Go To Jail" had an order on 13-APR-02 by Hot Wet And Sexy
Books for 5500 copies.
"Writers
Market" had an order on 06-MAY-02 by Hot Wet And Sexy Books for 5500
copies.
"Piano
Greats" had an order on 18-MAY-02 by Specialty Bookstore for 5500
copies.
"Piano
Greats" had an order on 26-APR-02 by Hot Wet And Sexy Books for 5200
copies.
"Managing
Stress" had an order on 14-MAY-02 by Quagmire Books for 5000
copies.
"The Zen Of
Auto Repair" had an order on 25-MAR-02 by Hot Wet And Sexy Books for
3600 copies.
"Managing
Stress" had an order on 28-MAR-02 by Blue Ride Booksellers for 3300
copies.
"The Fall Of
Microsoft" had an order on 10-FEB-02 by Books For Dummies for 2000
copies.
"Piano
Greats" had an order on 02-APR-02 by Ignoramus And Dufus for 1900
copies.
"Piano
Greats" had an order on 04-JAN-02 by Eaton Books for 1020 copies.
Evaluation
This assignment is worth 100 points. Please
submit your work to instructor.
ANSWER:
Instructor note: The student may get 100% by
adding a “HAVING sum(quantity) > 1000” in the SQL or by doing an IF
test in the PL/SQL. This example shows both acceptable methods:
set
serveroutput on size 1000000
DECLARE
v_avg_sales sales.quantity%type;
diff sales.quantity%type;
cursor c1 is
select
book_title,
store_name,
order_date,
sum(quantity) sum_qty
from
book
natural join
sales
natural join
store
group by
book_title,
store_name,
order_date
having
sum(quantity) > 1000
order by
sum(quantity) desc
;
sales_rec
c1%rowtype;
BEGIN
open c1;
loop
fetch
c1 into sales_rec;
exit when c1%NOTFOUND;
if sales_rec.sum_qty > 500
then
dbms_output.put_line(
'"'||initcap(sales_rec.book_title)||'" had an order on '||
sales_rec.order_date||' by '||
initcap(sales_rec.store_name)||' for '||sales_rec.sum_qty||'
copies.'
);
end if;
end loop;
close c1;
END;
/
For more details,
see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology. |
