PLSQL_（4）游标（二）

三、游标for循环
 游标for循环是为简化游标使用过程而专门设计的。使用游标for循环检索游标时，游标的打开、数据的提取、
数据是否检索到的判断与游标的关闭都是oracle系统自动进行的。在pl/sql程序中使用for循环，过程清晰，
简化了对游标的处理。当使用游标开发pl/sql应用程序时，为了简化程序代码，建议大家使用游标for循环。

1.语句格式一：先在定义部分定义游标，然后在游标for循环中引用该游标。

 for record_name in cursor_name loop  
  statement1;          
  statement2;
 end loop;
 --curosr_name是已经定义的游标名，record_name是oracle系统隐含定义的记录变量名。

当使用游标for循环时，在执行循环体内语句之前，oracle系统会自动打开游标，并且随着循环的进行，
每次提取一行数据，oracle系统会自动判断数据是否提取完毕，提取完毕便自动退出循环并关闭游标。

declare
 v_specialty students.specialty%type;
 cursor students_cur
 is
  select name, dob
   from students
   where specialty = v_specialty;
begin
 v_specialty := '%specialty';
 dbms_output.put_line('序号 学生 出生日期');
 for students_record in students_cur loop
  dbms_output.put_line(students_cur%rowcount||' '||students_record.name
   ||' '||students_record.dob);
 end loop;
end;

 2.语句格式二：在for循环中直接使用子查询，隐式定义游标。

 for record_name in subquery loop
  statement1;
  statement2;
 end loop;
 --subquery是形成隐式定义游标的子查询；record_name是oracle系统隐式定义的记录变量名。

当使用游标for循环时，在执行循环体内语句之前，oracle系统会自动打开游标，并且随着循环的进行，
每次提取一行数据，oracle系统会自动判断数据是否提取完毕，提取完毕便自动退出循环并关闭游标。
由于是隐式定义游标（游标未指定名字），因此使用语句格式二的游标for循环，在pl/sql程序代码中，
不能显式使用游标属性。

declare
 v_specialty students.specialty%type;
 /*cursor students_cur
  select name, dob
   from students
   where specialty = v_specialty;*/
begin
 v_specialty := '&specialty';
 dbms_output.put_line('学生姓名 出生日期');
 for students_record in
  (select name, dob from students where specialty = v_specialty) loop
  dbms_output.put_line(students_record.name || ' ' || students_record.dob);
 end loop;
end;

 

四、游标的复杂应用
 游标的复杂应用包括在游标中使用参数、定义使用游标变量及游标表达式。
1.参数游标
 在定义与使用游标时，可以带有参数，以便将参数传递给游标并在pl/sql代码中使用。
当使用不同参数值打开游标时，可以产生不同的结果集。
语法格式：

cursor cursor_name(para_name1 datetype[, para_name2 datetype]...)
  is select_statements;

注意：使用游标参数时，只能指定参数的类型，而不能指定参数的长度、精度、刻度。因此
para_name称为。形参个数可以是一个，也可以是多个。
打开参数游标的语句格式：

 open cursor_name[value1 [,value2]...];

 参数值value可以是常量或已经赋值的变量。value被称为实参。

set serveroutput on
declare
 v_dob students.dob%type;
 v_name students.name%type;
 cursor students_cur(v_specialty students.specialty%type)
 is
  select name, dob
    from students
   where specialty = v_specialty;
begin
  open students_cur('机电工程');
  fetch students_cur into v_sname, v_dob;
  while students_cur%found loop
    dbms_output.put_line(v_sname||''|| v_dob);
    fetch students_cur into v_sname, v_dob;
  end loop;
end;

set serverouput on
declare
 v_tname teachers.name%type;
 v_wage teachers.wage%type;
 cursor teachers_cur(t_title varchar2, t_wage number)
 is
  select name, wage
   from teachers
   where title = t_title
     and wage > t_wage;
begin
  open teachers_cur('副教授', 2000);
  fetch teachers_cur into v_tname, v_wage;
  while teachers_cur%found loop
33     dbms_output.put_line(v_tname ||''|| v_wage);
    fetch teachers_cur into v_tname, v_wage;
35   end loop;
  close teachers_cur;
end;

2.游标变量
 游标是指定的某个查询的结果集所在内存位置的指针，是静态的。
在某一时刻，游标变量是指向某个查询的结果集所在内存位置的指针；
而在另一时刻，游标变量又可能是指向另一个查询的结果集所在的内存位置的指针。
因此，游标变量是动态的，它与游标的关系就像一般的常量和变量之前的关系一样。
游标变量在打开时，可以取得不同的游标值，从而提高了pl/sql程序的灵活性。
（1）定义游标变量
 定义游标变量需要两个步骤，首先定义游标类型，然后定义具有游标类型的变量。
语法格式：

 type ref_type_name is ref cursor [return return_type]; --定义游标类型
 --ref_type_name指定游标变量使用的数据类型，可选子句return由return_type指定返回结果的数据类型，
--并且该数据类型必须是记录类型。
 cursor_variable ref_type_name;   --定义游标变量
 --cursor_variable用于指定游标变量名，ref_type_name用于指定游标变量使用的数据类型。

（2）打开游标
 为了使用游标变量，需要通过打开游标变量语句，为其指定某个查询的结果集所在
内存位置的指针，即为游标变量赋值。
语法格式：

 open cursor_variable for select_statement;

（3）读取游标变量对应的数据
 为了处理游标变量对应的数据，需要使用fetch语句提取游标变量对应的数据。
语法格式：
 

fetch cursor_variable into variable1, variable2, ...;

（4）关闭游标变量
 在游标变量所对应的游标数据处理完毕之后，便可以关闭游标变量了。
游标变量关闭后，自动释放游标变量对应的结果集所在内存空间。
语法格式：

close cursor_variable;
set serveroutput on
declare
 type students_cur is ref cursor;
 stuCursor students_cur;
 students_record students%rowtype;
begin
 if not stuCursor%isOpen then
  open StuCursor for select * from students;
  end if
 dbms_output.put_line('学生姓名 出生日期');
 loop
  fetch stuCursor into students_record;
  exit when StuCursor%notfound;
  dbms_output.put_line(students_record.name || '' || students_record.dob);
 end loop;
 close StuCursor;
end;

--使用return子句

set serveroutput on
declare
 type studetns_record is record(
  stuName varchar2(10),
  StuDOB date
 );
 stuRecord students_record;
 type students_cur is ref cursor return students_record;
 stuCursor students_cur;
begin
 if not stuCursor%isOpen then
  open stuCursor for 
   select name,dob 
     from students;
 end if;
 idbms_output.put_line('学生姓名 出生日期');
 loop
  fetch stuCursor into stuRecord;
  exit when stuCursor%notFound;
  dbms_output.put_line(students_record.name || '' || students_record.dob);
 end loop;
 close stuCursor;
end;

3.游标表达式
 游标表达式在游标的select语句内部使用，构成嵌套游标，其返回类型为ref cursor。
语法格式：

cursor(subquery);

 在pl/sql代码中使用游标表达式，可以处理多表间的关联数据。游标表达式只能用于显示游标，
而不能用于隐式游标。

set serveroutput on 
declare
 v_tname teachers.name%type;
 v_title teachers.title%type;
 v_dname departments.department_name%type;
 type cursor_type is ref cursor;
 cursor departments_cur(dept_id number) is
  select d.department_name, 
      cursor(select name, title 
           from teachers 
           where department_id = d.department_id)
   from departments d 
   where d.department_id = dept_id;
 teachers_cur cursor_type;
begin
 open departments_cur('101');
 loop
  fetch departments_cur into v_dname, teachers_cur;
  exit when departments_cur%notfound;
  dbms_output.put_line('系部名称：' || v_dname);
  dbms_output.put_line('老师姓名  职称');
  loop
   fetch teachers_cur into v_tname, v_title;
   exit when teachers_cur%notfound;
   dbms_output.put_line(v_tname||''|| v_title);
  end loop;
 end loop;
end;