plsql学习:cursor游标使用例子(1)

--PL/SQL supports three kinds of records: table-based, cursor-based,and programmer-defined.

/**
    A table-based record is one whose structure is drawn from the list of columns in the table. 
    A cursor-based record is one whose structure matches the elements of a predefined cursor. 
    To create a table-based or cursor-based record, use the %ROWTYPE attribute:
    record_name table_name or cursor_name%ROWTYPE
*/
-----------example A:--------
set serveroutput on
declare 
    vr_student student%rowtype
begin
    select * 
      into vr_student
      from student
      where student_id = 156;
      dbms_output.put_line(vr_student.first_name||'  '||
         vr_student.lase_name||' has an ID of 156');
    exception
      when no_data_found
        then 
          reise_application_error(-2001,'the student '||
              'is not in the database');
end;

----------example B:----------
set serveroutput on 
declare 
    cursor c_zip is
        select * from zipcode;
    vr_zip c_zip%rowtype;

begin
  open c_zip;
  loop
    fetch c_zip into vr_zip;
    exit when c_zip%notfound;
    dbms_output.put_line(vr_zip.zip||'  '||vr_zip.city||'  '||vr_zip.state);
  end loop;
end;

----------example C:---------
set serveroutput on;
declare
    type instructor_info is record
         (first_name instructor.first_name%type,
          last_name instructor.last_name%type,
          sections number);
    rv_instructor instructor_info;
    begin
      select rtrim(i.first_name),rtrim(i.last_name),count(*)
      into rv_instructor
      from instructor i,section s
      where i.instructor_id = s.instructor_id
      and i.instructor_id = 102
      group by i.first_name,i.last_name;
      dbms_output.put_line('instructor, '||rv_instructor.first_name||
         '  '||rv_instructor.last_name||
         ',teaches '||rv_instructor.sections||
         'sections(s)');
exception 
  when no_data_found then
    dbms_output.put_line('there is no such instructor');
end;


#######################################################################################
#
# Explicit Cursor Attributes
#----------------------------------------------------------------------------------
# CURSOR ATTRIBUTE   SYNTAX                             DESCRIPTION
#----------------------------------------------------------------------------------
# A: %NOTFOUND       cursor_name%NOTFOUND   A Boolean attribute that returns TRUE if
#                                                                          the previous FETCH did not return a row
#                                                                          and FALSE if it did.
#----------------------------------------------------------------------------------
# B: %FOUND          cursor_name%FOUND             A Boolean attribute that returns TRUE if
#                                                                          the previous FETCH returned a row and
#                                                                          FALSE if it did not.
#----------------------------------------------------------------------------------
# C:%ROWCOUNT        cursor_name%ROWCOUNT  The number of records fetched from a
#                                                                           cursor at that point in time.
#----------------------------------------------------------------------------------
# D:%ISOPEN          cursor_name%ISOPEN              A Boolean attribute that returns TRUE if
#                                                                           the cursor is open and FALSE if it is not.
#---------------------------------------------------------------------------------- 
#######################################################################################


--example D:-----------
# Cursor attributes can be used with implicit cursors by using the prefix SQL, such as
# SQL%ROWCOUNT.
# If you use SELECT INTO syntax in your PL/SQL block, you will create an implicit cursor.You can
# then use these attributes on the implicit cursor.
set serveroutput on
declare
   v_city zipcode.city%type;
begin
  select city 
  into v_city
  from zipcode
  where zip = 07002;
  if SQL%rowcount = 0;
  then 
    dbms_output.put_line('******');
  else if sql%rowcount = 0
    then 
      dbms_output.put_line('******');
  else 
    dbms_output.put_line('*******');
  end if;
end;

--------example E:-------
declare
   v_sid student.student_id%type;
   cousor c_student is 
      select student_id
      from student
      where student_id < 110;
begin
  open c_student;
  loop
    fetch c_student into v_sid;
    exit when c_student%notfound;
      dbms_output.put_line('student ID:'||v_sid);
  end loop;
  close c_student;
exception
  when others
  then
    if c_student%isopen
    then 
      close c_student;
    end if;
end;


--------------example F:(Nest cusors)-------
set serveroutput on
declare
   v_zip zipcode.zip%Type;
   v_student_flag Char;
   cursor c_zip is
      select zip, city, state
      from zipcode
      where state = 'CT';
   cursor c_student is
      select first_name ,last_name
      from student
      where zip = v_zip;
bebin
   for r_zip in c_zip
   loop
     v_student_flag := 'N';
     v_zip := r_zip.zip;
     dbms_output.put_line(chr(10));
     dbms_output.put_line('students living in'||r_zip.city);
     for r_student in c_student
     loop
       dbms_output.put_line(r_student.first_name||
          '  '||r_student.last_name);
       v_student_flag := 'Y';
     end loop;
     if v_student_flag = 'N'
       then
       dbms_output.put_line('no student for this zipcode');
     end if
   end loop;
end ;

---------example G: using Current Of statement--------
declare
   cursor c_stud_zip is
      select s.student_id ,z.city
      from student s, zipcode z
      where z.city = 'Brookyn'
      and s.zip = z.zip
      for update of phone;
begin
  for c_stud_zip in c_stud_zip
  loop
    dbms_output.put_line(r_stud_zip.student_id);
    update student
       set phone = '718'||Substr(phone,4)
       where current of c_stud_zip;
  end loop;
end;