 |
|
Oracle Concepts by Burleson
Consulting |
Declare and Use a Cursor
From the your task is to use the sample
database along with an anonymous PL/SQL code block in order to display
each book_title in the computer book_type, and compare it’s sales to
the average sales for other computer books.
The completed PL/SQL will produce the
following output:
"Dos For Dummies" sold 11580 copies, above the
average by 10431 copies.
"Unix For Experts" sold 1400 copies, above the
average by 251 copies.
"Oracle 10g Sql Tuning" sold 1100 copies,
below the average by -49 copies.
"The Fall Of Microsoft" sold 4500 copies,
above the average by 3351 copies.
"Windows Sucks" sold 18200 copies, above the
average by 17051 copies.
To solve this problem, you must complete the
following steps. Remember to run and de-bug your PL/SQL after
completing each step.
Step 1 – Clone the PL/SQL that you used in
Assignment 1, and save it in your c:\Burleson directory as
cursor_plsql.sql
Step 2 – Define a variable named sales_rec of
the cursor type:
DECLARE
sales_rec sales_cursor%rowtype;
Step 3 – Create a cursor to select all book_titles, and the sum
of sales for all computer books.
DECLARE
cursor my_cursor is select book_title, sum(quantity) . . . . . ;
Step 4 – Open your cursor and display the book
titles:
open c1;
loop
fetch
c1 into sales_rec;
exit when c1%NOTFOUND;
dbms_output.put_line(‘Title is ‘||sales_rec.book_title);
Step 5 – Add a variable to hold the difference
between the sales for the book and the average sales for all computer
books:
DECLARE
sales_diff sales.quantity%rowtype
. . .
BEGIN
sales_dif := xxx – xxx;
Step 7 – Add the appropriate IF logic to change the display
when your sales_dif variable is greater than zero:
if diff >
0
then
dbms_output.put_line(‘above average sales’);
else
dbms_output.put_line(‘below average sales);
end if;
ANSWER:
set
serveroutput on
DECLARE
v_avg_sales sales.quantity%type;
diff sales.quantity%type;
cursor c1 is
select
initcap(book_title) book_title,
sum(quantity) tot_sales
from
book
natural join
sales
where
book_type = 'computer'
group by
book_title;
sales_rec c1%rowtype;
BEGIN
select
avg(quantity)
into
v_avg_sales
from
book
natural join
sales
where
book_type = 'computer';
open c1;
loop
fetch
c1 into sales_rec;
exit when c1%NOTFOUND;
diff :=
sales_rec.tot_sales - v_avg_sales;
if diff > 0
then
dbms_output.put_line(
'"'||sales_rec.book_title||'" sold '||
sales_rec.tot_sales||' copies, above the average by '||
diff||' copies.'
);
else
dbms_output.put_line(
'"'||sales_rec.book_title||'" sold '||
sales_rec.tot_sales||' copies, below the average by '||
diff||' 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.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
