Oracle笔记之游标

一、游标的概念

游标是一种访问机制,它允许用户访问单独的数据行(对数据逐行进行操作),而非对整个结果行集进行操作.
简而言之：游标就是位于内存中的数据集，加快对数据的操作

使用游标的步骤：

1. 创建游标
2. 打开游标
3. 存取游标
4. 关闭游标

--创建表
SQL> create table stu(id number(5),name varchar(10));
SQL> insert into stu values(1,'zhangsan');
SQL> insert into stu values(2,'lisi');


--游标
declare
  -- 1 声明游标
  cursor cur_stu is select * from stu;
  v_stu cur_stu%rowtype;--声明变量v_stu，和stu和行属性同类型
begin
  -- 2 开启游标
  open cur_stu;

  -- 3 获取数据（一次获取一行）
  fetch cur_stu
    into v_stu;
  dbms_output.put_line(v_stu.id || ' : ' || v_stu.name);

  -- 4 关闭游标
  close cur_stu;

end;

1. %TYPE说明
   为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致，Oracle提供了%TYPE定义方式。当被参照的那个变量的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个变量的数据类型时，就只能采用这种方法定义变量的数据类型。
2. %ROWTYPE说明
   如果一个表有较多的列，使用%ROWTYPE来定义一个表示表中一行记录的变量，比分别使用%TYPE来定义表示表中各个列的变量要简洁得多，并且不容易遗漏、出错。这样会增加程序的可维护性。
   为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，Oracle提供%ROWTYPE定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。
   一行记录可以保存从一个表或游标中查询到的整个数据行的各列数据。一行记录的各个列与表中一行的各个列有相同的名称和数据类型。

二、游标的属性

游标的属性(同样也有 %ROWCOUNT、%NOTFOUND、%ISOPEN 、%FOUND)

属性	返回值类型	作用
sql%isopen	布尔型	判断游标是否 ‘开启’
sql%found	布尔型	判断游标是否 ‘获取’ 到值
sql%notfound	布尔型	判断游标是否 ‘没有获取’ 到值（常用于 “退出循环”）
sql%rowcount	整型	‘当前’ 成功执行的数据行数（非 “总记录数”）

--显示所有的员工姓名
create or replace procedure showEmpEname as
    cursor emp_cur is select ename from emp;
    v_name emp.ename%type;
begin
    open emp_cur;
    fetch emp_cur into v_name;
    while emp_cur%FOUND loop
      dbms_output.put_line('name is: ' || v_name);
      fetch emp_cur into v_name ;
    end loop;  
    close emp_cur;
end;

三、使用 fetch bulk collect into 批量提取数据

fetch bulk collect into 的使用格式是：

 fetch some_cursor bulk collect into col1, col2 limit xxx。

• col1、col2 是声明的集合类型变量，xxx 为每次取数据块的大小（记录数），
• 相当于缓冲区的大小，可以不指定 limit xxx 大小

 
--使用 fetch  bulk collect into 批量提取数据
--type ... is recard :定义一个记录行类型
--type...is table of :定义一个存放记录行类型元素的集合类型即表
declare 
     cursor emp_cur is select ename from emp;
     type emp_name_type is table of emp.ename%type;
     emp_name emp_name_type;
begin
     open emp_cur;
     fetch emp_cur bulk collect into emp_name;
     for i in 1..emp_name.count loop
       dbms_output.put_line('name: ' || emp_name(i));
     end loop;
     close emp_cur;
end;

--用游标方式显示姓名和工资 
create or replace procedure ShowEmpNameSal is
   cursor emp_cursor is select ename,sal from emp;
   v_name emp.ename%type;
   v_sal emp.sal%type;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into v_name,v_sal;
  exit when emp_cursor%NOTFOUND ;
    dbms_output.put_line('Name: ' || v_name || ' Sal: ' || v_sal);
  end loop;
  close emp_cursor;
end;

--用游标方式显示姓名和工资(批量提取) 
create or replace procedure showEmpNameSal is
   cursor emp_cursor is select ename,sal from emp;
   TYPE emp_record_type is record(v_name emp.ename%type,v_sal emp.sal%type);
   TYPE emp_table_type is table of  emp_record_type;
   emp_table emp_table_type;   
begin
   open emp_cursor;
   fetch emp_cursor bulk collect into emp_table;
   for i in 1..emp_table.count loop
     dbms_output.put_line('Name: ' || emp_table(i).v_name || ' Sal: ' || emp_table(i).v_sal);
   end loop;
   close emp_cursor;
end;

--使用基于游标定义的记录变量
--用游标方式显示姓名和工资(批量提取) 
declare 
     cursor emp_cur_ename_sal is select ename,sal from emp;
     type emp_table_ename_sal is table of emp_cur_ename_sal%rowtype;
     emp_table emp_table_ename_sal;
begin
     open emp_cur_ename_sal;
     fetch emp_cur_ename_sal bulk collect into emp_table;
     for i in 1..emp_table.count loop
       dbms_output.put_line('name: ' || emp_table(i).ename || ' sal: ' || emp_table(i).sal);
     end loop;
     close emp_cur_ename_sal;
end;

四、通过游标修改数据

1. 声明时要加for update
2. 更改数据时要加where current of cursor_name

--根据部门编号给低于100的人增加工资
create or replace procedure updateSalByDeptno(dno emp.deptno%type,addSal emp.sal%type) is
    cursor emp_cursor is select sal from emp where deptno = dno for update;
    v_sal emp.sal%type;
begin
    open emp_cursor;
    loop
      fetch emp_cursor into v_sal;
    exit when emp_cursor%NOTFOUND ;
      if v_sal < 100 then
        update emp set sal = v_sal + addSal where current of emp_cursor;
      end if;
    end loop;
    close emp_cursor;
end;  


--删除数据
--删除某个部门的工资低于1000的员工   
create or replace procedure deleteEmpInfoBydeptno(dno emp.deptno%type) is
   cursor emp_cursor is select sal from emp where deptno = dno for update;
   v_sal emp.sal%type;
begin
   open emp_cursor;
   loop
     fetch emp_cursor into v_sal;
   exit when emp_cursor%NOTFOUND ;
     if v_sal < 1000  then
       delete from emp where current of emp_cursor;
     end if;
   end loop;
   close emp_cursor;
end;

--游标for 循环
declare 
  cursor emp_cursor is select empno,ename,sal from emp;
begin
  for emp_record in emp_cursor loop--自动打开,且不用定义变量
    dbms_output.put_line('Name: ' || emp_record.ename || ' Sal: ' || emp_record.sal );
  end loop; --自动关闭
end;
    

--更简洁的写法
begin
  for emp_record in (select ename,sal from emp) loop
    dbms_output.put_line('Name: ' || emp_record.ename || ' Sal: ' || emp_record.sal);
  end loop;
end;