Oracle 游标

游标，是SQL的一个内存工作区，用来存放select的结果集。

游标用来处理数据库检索的多行记录（使用select语句）。利用游标，程序可以逐个的处理和遍历一次索引返回的结果集。

在数据库中，存在两种游标，静态游标（隐式和显式）、ref游标（+游标变量）

示例数据准备

-- 建表
  
create table student(
id varchar2(4),
name varchar2(100), --姓名
sex varchar2(1),  --性别 1 男  2 女  0 未知
score integer default 0
);

select * from student;

-- 插入数据

insert into student (id, name, sex)values ('0001', '大王', '2'); 
insert into student (id, name, sex)values ('0002', '刘一', '1'); 
insert into student (id, name, sex)values ('0003', '陈二', '2');
insert into student (id, name, sex)values ('0004', '张三', '0');
insert into student (id, name, sex)values ('0005', '李四', '1');
insert into student (id, name, sex)values ('0006', '王五', '0');
insert into student (id, name, sex)values ('0007', '赵六', '1');
insert into student (id, name, sex)values ('0008', '孙七', '2');
insert into student (id, name, sex)values ('0009', '周八', '2');
insert into student (id, name, sex)values ('0010', '吴九', '1');
insert into student (id, name, sex)values ('0011', '郑十', '1');
commit;

1. 静态游标

1.1 显示游标（需要明确定义）

显示游标被用于处理返回多行数据的select语句，游标名通过cursor……is语句显示的赋给select语句。

--一、静态游标之显示游标

--For 循环游标 ①--------------------------------------------------------------------------------------
--（1）定义游标
--（2）定义游标变量
--（3）使用for循环来使用这个游标
declare
  --类型定义
  cursor c_student is
    select stu_id, stu_name, sex, credit
      from student
     where stu_name = '大王';
  --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
  c_row c_student%rowtype;
begin
  for c_row in c_student loop
    dbms_output.put_line(c_row.stu_id || '-' || c_row.stu_name || '-' || c_row.sex || '-' || c_row.credit);
  end loop;
end;
--For 循环游标 ①--------------------------------------------------------------------------------------

--For 循环游标 ② 另一种写法--------------------------------------------------------------------------------------
declare
begin
  for c_row in (select stu_id, stu_name, sex, credit
                  from student
                 where stu_name = '大王') loop
    dbms_output.put_line(c_row.stu_id || '-' || c_row.stu_name || '-' || c_row.sex || '-' || c_row.credit);
  end loop;
end;
--For 循环游标 ② 另一种写法--------------------------------------------------------------------------------------

--Fetch游标--------------------------------------------------------------------------------------------
--使用的时候必须要明确的打开和关闭

declare
  --类型定义
  cursor c_student is
    select stu_id, stu_name, sex, credit
      from student
     where stu_name = '大王';
  --定义一个游标变量
  c_row c_student%rowtype;
begin
  open c_student;
  loop
    --提取一行数据到c_row
    fetch c_student into c_row;
    --判读是否提取到值，没取到值就退出
    --取到值c_job%notfound 是false 
    --取不到值c_job%notfound 是true
    exit when c_student%notfound;
    dbms_output.put_line(c_row.stu_id || '-' || c_row.stu_name || '-' || c_row.sex || '-' || c_row.credit);
  end loop;
  --关闭游标
  close c_student;
end;
--Fetch游标--------------------------------------------------------------------------------------------


--1、通过update操作查看游标的属性----------------------------------------------------------------------

--任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。

--%isopen 游标是否打开，始终为false。
--%found SQL语句影响了一行或多行时为true；
--%notfound SQL语句没有影响任何行时为true(常用，没找到为T，就退出)
--%rowcount SQL语句影响的行数；


begin
  update student set stu_name = '修改0001';
  
  if sql%isopen then 
    dbms_output.put_line('Openging');
  else
    dbms_output.put_line('closing');
  end if;
  
  if sql%found then
    dbms_output.put_line('游标指向了有效行'); --判断游标是否指向有效行
  else
    dbms_output.put_line('Sorry');
  end if;
  
  if sql%notfound then
    dbms_output.put_line('Also Sorry');
  else
    dbms_output.put_line('Haha');
  end if;
  
  dbms_output.put_line(sql%rowcount);
  
exception
  when no_data_found then
    dbms_output.put_line('Sorry No data');
  when too_many_rows then
    dbms_output.put_line('Too Many rows');
end;
--1、通过update操作查看游标的属性----------------------------------------------------------------------

--2、通过select操作对比游标的属性--------------------------------------------------------------------

--%TYPE 获取与student表stu_id字段相同的数据类型
--%ROWTYPE 获取与student各个列名、数据类型都相同的记录变量
declare
  studentNumber student.stu_id%TYPE;
  studentName   student.stu_name%TYPE;
begin
  
  if sql%isopen then
    dbms_output.put_line('Cursor is opinging');
  else
    dbms_output.put_line('Cursor is Close');
  end if;
  
  if sql%notfound then
    dbms_output.put_line('No Value');
  else
    dbms_output.put_line(studentNumber);
  end if;
  
  dbms_output.put_line(sql%rowcount);
  dbms_output.put_line('-------------');

  select stu_id, stu_name into studentNumber, studentName from student where stu_id = '0001';
  
  dbms_output.put_line(sql%rowcount);

  if sql%isopen then
    dbms_output.put_line('Cursor is opinging');
  else
    dbms_output.put_line('Cursor is Closing');
  end if;
  
  if sql%notfound then
    dbms_output.put_line('No Value');
  else
    dbms_output.put_line(studentNumber);
  end if;
  
exception
  when no_data_found then
    dbms_output.put_line('No Value');
  when too_many_rows then
    dbms_output.put_line('too many rows');
end;
--2、通过select操作对比游标的属性--------------------------------------------------------------------


--3、测试%ROWTYPE属性---------------------------------------------

declare
  studentTemp student%ROWTYPE;
begin

  select * into studentTemp from student where stu_id = '0001';

  dbms_output.put_line('学号:' || studentTemp.stu_id);
  dbms_output.put_line('姓名:' || studentTemp.stu_name);
  dbms_output.put_line('性别:' || studentTemp.sex);
  dbms_output.put_line('学分:' || studentTemp.credit);

exception
  when no_data_found then
    dbms_output.put_line('No Value');
  when too_many_rows then
    dbms_output.put_line('too many rows');
end;

--3、测试%ROWTYPE属性---------------------------------------------


--4、使用游标和loop循环来显示所有学生信息-----------------------------------------
--游标声明

declare
  cursor stu is
  --select语句
    select stu_id, stu_name from student;
  --指定行指针,这句话应该是指定和csr_dept行类型相同的变量
  c_row stu%rowtype;
begin
  --for循环
  for c_row in stu loop
    dbms_output.put_line('学号:' || c_row.stu_id || ' 姓名：' || c_row.stu_name);
  end loop;
end;

--4、使用游标和loop循环来显示所有学生信息-----------------------------------------

--5、使用游标和while循环来显示所有学生信息（用%found属性）---------------------------

declare
  --游标声明
  cursor csr_TestWhile is
  --select语句
    select stu_id, stu_name from student;
  --指定行指针
  c_row csr_TestWhile%rowtype;
begin
  --打开游标
  open csr_TestWhile;
  --给第一行喂数据
  fetch csr_TestWhile into c_row;
  --测试是否有数据，并执行循环
  while csr_TestWhile%found loop
    dbms_output.put_line('学号:' || c_row.stu_id || ' 姓名：' || c_row.stu_name);
    --给下一行喂数据
    fetch csr_TestWhile into c_row;
  end loop;
  close csr_TestWhile;
end;

--5、使用游标和while循环来显示所有学生信息（用%found属性）---------------------------

--6、使用带参数的表控制输出--------------------------------------------------------------------------------------------
--接收用户输入的学生编号，用for循环和游标，打印出该学生的所有信息(使用循环游标)
--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
--定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]  

declare
  CURSOR c_stu_id(p_stu_id number) is
    select * from student where stu_id = p_stu_id;
  c_row student%rowtype;
begin
  for c_row in c_stu_id(&p_stu_id) loop
    dbms_output.put_line('学号:' || c_row.stu_id || ' 姓名：' || c_row.stu_name || ' 学分：' || c_row.credit);
  end loop;
end;

--6、使用带参数的表控制输出--------------------------------------------------------------------------------------------



--7、使用游标更新学生成绩①----------------------------------------------------------------------------
--(用if实现,创建一个与student表一摸一样的student1表，对student1表进行修改操作),并将更新前后的数据输出出来 
--create table student1 as select * frstudentom ;
        
declare
  cursor csr_Update is
    select * from student1 for update of credit;
  studentInfo  csr_Update%rowtype;
  v_credit student1.credit%TYPE;
begin
  FOR studentInfo IN csr_Update LOOP
    
    IF studentInfo.credit < 60 THEN
      v_credit := 60;
    else v_credit:=studentInfo.credit;
    END IF;
    
    UPDATE student1 SET credit = v_credit WHERE CURRENT OF csr_Update;
    
  END LOOP;
END;

--select * from student1;
--select * from student;
--7、使用游标更新学生成绩①----------------------------------------------------------------------------

--7、使用游标更新学生成绩② 另一种写法----------------------------------------------------------------------------
--(用if实现,创建一个与student表一摸一样的student1表，对student1表进行修改操作),并将更新前后的数据输出出来 
--create table student1 as select * from studentom ;
        
declare
  cursor csr_Update is
    select * from student1;
  studentInfo  csr_Update%rowtype;
  v_stu_id integer;
  v_credit student1.credit%TYPE;
begin
  FOR studentInfo IN csr_Update LOOP
    v_stu_id := studentInfo.stu_id;
    
    IF studentInfo.credit < 60 THEN
      v_credit := 60;
    else v_credit:=studentInfo.credit;
    END IF;
    
    UPDATE student1 SET credit = v_credit WHERE stu_id = v_stu_id;
    
  END LOOP;
END;

--select * from student1;
--select * from student;
--7、使用游标更新学生成绩② 另一种写法----------------------------------------------------------------------------

--8、输出学生编号最小的学生信息----------------------------------------------------
--提示：可以定义一个变量作为计数器控制游标只提取两条数据；
--也可以在声明游标的时候把员工编号最小的两个人查出来放到游标中。
declare
  cursor stu is
  --select语句
    select stu_id, stu_name from student order by stu_id;
  --指定行指针,这句话应该是指定和csr_dept行类型相同的变量
  c_row stu%rowtype;
  v_count integer:=2;
begin
  --for循环
  for c_row in stu loop
    if v_count > 0 then
    dbms_output.put_line('学号:' || c_row.stu_id || ' 姓名：' || c_row.stu_name);
    v_count := v_count-1;
    end if;
  end loop;
end;
--8、输出学生编号最小的学生信息----------------------------------------------------

1.2 隐式游标

DML操作和单行SELECT语句会使用隐式游标。它们是：

▶ 插入操作：INSERT。

▶ 更新操作：UPDATE。

▶ 删除操作：DELETE。

▶ 单行查询操作：SELECT ... INTO ...。

所有隐式游标都被假设只返回一条记录。

使用隐式游标时，用户无需进行声明，打开及关闭游标。PL/SQL隐含的打开，处理、然后关掉游标。 

--二、静态游标之隐式游标

--9、查看update游标状态------------------------------------------------------------------------------
--(用if实现,创建一个与student表一摸一样的student1表，对student1表进行修改操作),并将更新前后的数据输出出来 
--create table student1 as select * from student ;

begin

  update student1 set credit = 300 WHERE stu_id = '0001';

  if sql%found then  
    dbms_output.put_line('表已更新,更新' || SQL%ROWCOUNT || '条记录');  
  else
    dbms_output.put_line('更新0条');  
  end if;

end;

--9、查看update游标状态
------------------------------------------------------------------------------

2. ref游标

游标变量是动态的可以在运行时刻与不同的SQL语句关联,在运行时可以取不同的SQL语句.它可以引用不同的工作区.

声明格式：

TYPE 游标类型 IS REF CURSOR;  --定义一个动态游标

游标名　　游标类型;

游标变量又分为强类型strong(with a return type)和弱类型(with no return type)：

--三、游标之动态游标、ref游标

--10、ref游标返回结果集 弱类型------------------------------------------------------------------

declare
  type refcursor is ref cursor; --ref游标类型  
  infolist     refcursor; --集合  
  v_student    student%rowtype; --行，弱类型可以定义为数据库有的任何一个表的类型
begin
  open infolist for
    select * from student; --全部  
  loop
    fetch infolist into v_student;
    exit when infolist%notfound;
    dbms_output.put_line('学号;：' || v_student.stu_id || ' 姓名：' || v_student.stu_name);
  end loop;
  close infolist;
end;

--10、ref游标返回结果集 弱类型------------------------------------------------------------------


--11、ref游标返回结果集 强类型------------------------------------------------------------------

declare
  type refcursor is ref cursor return student%rowtype; --ref游标类型  
  infolist     refcursor; --集合  
  v_stu_id     student%rowtype; --行 ，强类型只能定义为跟student表数据一样的类型
begin
  open infolist for
    select * from student; --全部  
  loop
    fetch infolist into v_stu_id;
    exit when infolist%notfound;
    dbms_output.put_line('学号;：' || v_stu_id.stu_id);
  end loop;
  close infolist;
end;

--11、ref游标返回结果集 强类型------------------------------------------------------------------


--12、ref游标在存储过程中的使用-------------------------
--游标可以直接打开SQL语句，也可以打开一动态SQL，

create or replace procedure p_cursor_test is
  type refcursor is ref cursor; --定义一个动态游标
  tablename varchar2(200) default 'ess_client';
  v_sql     varchar2(1000);
  v_student    varchar2(15);
  infolist      refcursor; --游标返回的结果集
begin

  --使用连接符拼接成一条完整SQL
  v_sql := 'select stu_name from student';
  --打开游标
  open infolist for v_sql;

  loop
    fetch infolist into v_student;
    exit when infolist%notfound;
    dbms_output.put_line('姓名：' || v_student);
  end loop;
  close infolist;
  commit;
end p_cursor_test;

--12、ref游标在存储过程中的使用------------------------


--13、ref游标在包中的使用-----------------------------------------------------------------------------
--可以使用包的方式定义一个公共ref游标，然后在其他地方调用

--① 创建一个ref游标
create or replace package p_package_cursor_test is
  type refcursor is ref cursor;
end p_package_cursor_test;

--②在存储过程中调用
create or replace procedure p_cursor_test(infolist out p_package_cursor_test.refcursor) is
  v_sql varchar(2000);
begin
  v_sql := 'select * from student';
  open infolist for v_sql;
end p_cursor_test;

--13、ref游标在包中的使用-----------------------------------------------------------------------------