explicit cursors gr01 by gegeshandong

VIEWS: 1 PAGES: 3

									EXPLXIT CURSORS

Recap

Anchored declaration:

<variable name> <table name>.<column name>%TYPE;

Data type of the variable called <variable name> will be the same as the data type (because
you typed %TYPE) of >.<column name> column of the table <table name>

--write an anonymous block that will retrieve and print name
--of the recipe with rno=120
--print the cursor attruibute rowcount on screen
--then change names of the recipes for rno between 130 and 150
to
--coffee
--print the cursor atttibute rowcount on screen
declare
   v_name recipe.name%TYPE;--anchored declaration
begin
   select name INTO v_name
   from recipe
   where rno=120;
   dbms_output.put_line('The name of this recipe is '||V_name);
   dbms_output.put_line('The row count is '||sql%rowcount);

  update recipe
  set name='coffee'
  where rno between 130 and 150;
  dbms_output.put_line('The row count is '||sql%rowcount);
  commit;
 end;

REMEMBER:

SELECT INTO STATEMENT WORKS ONLY WHEN exactly one row is returned!!!!!!


declare
  v_name recipe.name%TYPE;--anchored declaration
begin

 select name INTO v_name
  from recipe
  where rno=120;
  dbms_output.put_line('The name of this recipe is '||V_name);
  dbms_output.put_line('The row count is '||sql%rowcount);


 end;

the above query returns exactly one row and the folwwoing output
is produced

The name of this recipe is Chocolate Chip Cookie
The row count is 1

If instead of 120 we write 420, there is no such recipe nin the recipe table and the anonymous
block fails with exception number ora-01403!!!!

If we use

select name INTO v_name
  from recipe
  where rno between 100 and 200;

the above query returns more than one row and the anonymous
block fails again!! With exception number ora-01422


use select into only when the select statement is guaranteed to
return one row!!!
   1- When you use a group function without group by
      Select count(*) from recipe;
      13

   2- When you use the primary key as part of an equality
      condition in the where clause

select name
  from recipe
  where rno=130;



using explicit cursors

--write an anonymous block to list all region names on screen
declare
  cursor rlist is
   select region_id, region_name
   from regions ;
    v_region_id regions.region_id%TYPE;
    v_region_name regions.region_name%TYPE;

begin
  open rlist;
  loop
       fetch rlist INTO v_region_id,v_region_name;
       exit when rlist%NOTFOUND;
       dbms_output.put_line(v_region_id||' '||v_region_name);
  end loop;
  close rlist;
end;

1   Europe
2   Americas
3   Asia
4   Middle East and Africa

								
To top