oracle游标简介

游标：对DML语句的结果集进行处理，分为三个操作：打开游标，操作数据，关闭游标

--【显示游标】可以打开游标，关闭游标，定义游标
--1.使用type创建游标
declare
--定义游标
  cursor cu_student_id_name is
  select student_id, student_name, student_age from students;
  student_id students.student_id%type;
  student_name students.student_name%type; 
  student_age students.student_age%type;
begin
  --使用游标
  open cu_student_id_name;
    fetch cu_student_id_name into student_id, student_name, student_age;
    while cu_student_id_name%found loop
      dbms_output.put_line(student_id||'-'||student_name||'-'||student_age);
      dbms_output.put_line('-------------');
      fetch cu_student_id_name into student_id, student_name, student_age;
    end loop;
  close cu_student_id_name;
end;
/
--结果
1-金瑞-18
-------------
2-钟君-18
-------------
3-王山-18
-------------
4-刘迪-18
-------------
5-钟会-18
-------------
6-张玉-18
-------------
7-柳青-18
-------------
8-胡东-18
-------------
9-商乾-18
-------------
10-周明-18
-------------
PL/SQL procedure successfully completed
--2.使用行类型rowtype创建游标
declare
--定义游标
  cursor cu_student_id_name is
  select * from students;
  student students%rowtype;
begin
  --使用游标
  open cu_student_id_name;
    fetch cu_student_id_name into student;
    while cu_student_id_name%found loop
      dbms_output.put_line(student.student_id||'-'||student.student_name||'-'||student.student_age);
      dbms_output.put_line('-------------');
      fetch cu_student_id_name into student;
    end loop;
  close cu_student_id_name;
end;
/
--结果集
1-金瑞-18
-------------
2-钟君-18
-------------
3-王山-18
-------------
4-刘迪-18
-------------
5-钟会-18
-------------
6-张玉-18
-------------
7-柳青-18
-------------
8-胡东-18
-------------
9-商乾-18
-------------
10-周明-18
-------------
PL/SQL procedure successfully completed
--3.创建带参游标
declare
--定义游标
  cursor cu_student_id_name(minAge in number,maxAge in number) is
  select * from students where student_age>=minAge and student_age<=maxAge;
  student students%rowtype;
begin
  --使用游标
  open cu_student_id_name(20,40);
    fetch cu_student_id_name into student;
    while cu_student_id_name%found loop
      dbms_output.put_line(student.student_id||'-'||student.student_name||'-'||student.student_age);
      dbms_output.put_line('-------------');
      fetch cu_student_id_name into student;
    end loop;
  close cu_student_id_name;
end;
/
--结果
3-王山-21
-------------
4-刘迪-23
-------------
6-张玉-34
-------------
--【隐式游标】用户不能定义游标。分为两种：①Oracle预定义的sql隐式游标。②cursor for loop用于循环的游标
--sql游标：oracle为pl/sql developer的每个会话都预定义了一个sql隐式游标
--演示sql隐式游标1
begin
  if sql %rowcount>0 then
    dbms_output.put_line('sql隐式游标的rowcount大于0');
  end if;
end;
/
--结果 没有结果输出，但执行成功已说明。
PL/SQL procedure successfully completed
--演示rowcount的作用，更新学生表的年龄，rowcount代表的更新的行数
begin
  update students set student_age=18;
  dbms_output.put_line('更新了'||sql%rowcount||'行');
end;
/
--结果
更新了10行
PL/SQL procedure successfully completed
--演示sql隐式游标2显式操作DML，会报错
declare
  student students%rowtype;
begin
    update students set student_age=student_age;
    fetch sql into student;
    while sql%found loop
      dbms_output.put_line(student.student_id||'-'||student.student_name||'-'||student.student_age);
      dbms_output.put_line('-------------');
      fetch sql into student;
    end loop;
end;
/
--结果
ORA-06550: 第 5 行, 第 11 列: 
PLS-00103: 出现符号 "SQL"在需要下列之一时：
 <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
符号 "<an identifier>在 "SQL" 继续之前已插入。
ORA-06550: 第 9 行, 第 13 列: 
PLS-00103: 出现符号 "SQL"在需要下列之一时：
 <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
符号 "<an identifier>在 "SQL" 继续之前已插入。
--循环隐式游标1
begin
  for student in (select * from students) loop
    dbms_output.put_line(student.student_id||'-'||student.student_name||'-'||student.student_age);
  end loop;
end;
--结果
1-金瑞-18
2-钟君-18
3-王山-18
4-刘迪-18
5-钟会-18
6-张玉-18
7-柳青-18
8-胡东-18
9-商乾-18
10-周明-18

--cursor for游标举例2
declare
begin
  for student in (select * from students where student_age>18 order by student_age desc) loop
    dbms_output.put_line(student.student_id||'--'||student.student_name||'--'||student.student_age);
  end loop;
end;
--结果
3--王山--29
4--刘迪--28
1--金瑞--19
PL/SQL procedure successfully completed
PL/SQL procedure successfully completed