Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  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

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.