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 ondeclare     vr_student student%rowtypebegin    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 ondeclare   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 ondeclare   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;