PL/SQL复习七 游标

 

游标： 指向结果集的指针，他提供一种机制，可以对结果集进行逐行处理

类型： 隐式游标   显式游标

查看隐式游标：output(sql%rowcount);

显式游标：oracle提供的一套语法，让你创建游标、使用游标

用法：

declare

  --1.定义游标(即定义指向的结果集)

  cursor emp_cursor is select ename, sal from emp where deptno = 10;

  v_ename emp.ename%type;

  v_sal emp.sal%type;

begin

  open emp_cursor;--2.打开游标

  loop

    fetch emp_cursor into v_ename, v_sal;--3.提取数据

    exit when emp_cursor%notfound;

    --在这里面做事情

    dbms_output.put_line(v_ename || ': ' || v_sal);

  end loop;

  close emp_cursor;

end;

/

游标属性：

1.%isopen 确定游标是否打开  if cl%isopen then...

2.%found 是否提取到数据

  loop

     fetch cl into var1,var2;

     if cl%found then ...

  end loop;

3.%notfound 与found相反 exit when c1%notfound

4.%rowcount 返回当前位置已经提取到的实际行数

  loop

    fetch c1 into my_ename, my_deptno;

    if c1%rowcount > 10 then ...

  end loop;

---------------------------------------------------------------------------------------------------------------------

批量提取游标的结果集：

declare

  cursor emp_cursor is select ename from emp where deptno = 10;

  type ename_table_type is table of emp.ename%type;

  ename_table ename_table_type;

begin

  open emp_cursor;

  --批量提取

  fetch emp_cursor bulk collect into ename_table;

  for i in 1..ename_table.count loop

    dbms_output.put_line(ename_table(i));

  end loop;

  close emp_cursor;

end;

/

提取一部分：fetch emp_cursor bulk collect into ename_table limit 5;

机遇游标定义记录变量：

cursor emp_cursor is select ename,sal from emp where deptno = 10;

emp_record emp_cursor%rowtype;

--------------------------------------------------------------------------------------------

参数游标： 用于select确定，参数不确定的情况，定义参数数据类型的时候不能指定长度

cursor emp_cursor(no number) is select ename from emp where deptno=no;

打开:open emp_cursor(10);

------------------------------------------------------------------------------------

使用游标更新、删除数据：

for update:加行几所

for update of:对于多表联查的情况加of限定要锁的表

current of: 在执行更新和删除的时候使用它表明执行的当前行

            cursor emp_cursor i select ename,sal from emp for update of emp;

nowait: 不等待: cursor emp_cursor i select ename,sal from emp for update nowait;

 

declare

  cursor emp_cursor i select ename,sal from emp for update;

  v_ename emp.ename%type;

  v_sal emp.sal%type;

begin

  open emp_cursor;

    loop

      fetch emp_cursor into v_ename, v_sal;

      exit when emp_cursor%notfound;

      if v_sal < 2000 then

        update emp set sal = sal + 100 where current of emp_cursor;

    end loop;

  close emp_cursor;

end;

/

-------------------------------------------------------------------------------------------------

游标for循环：不需要打开游标、提取数据、关闭游标

declare

  cursor emp_cursor is select ename,sal from emp;

begin

  for e in emp_cursor loop

    dbms_output.put_line('第'||emp_cursor%rowcount ||'个雇员：'||e.ename);

  end loop;

end;

/

for循环直接打开子查询：

begin

  for e in (select ename, sal from emp) loop

    dbms_output.put_line(e.ename);

  end loop;

end;

/

-----------------------------------------------------------------------------------------

游标变量：

declare

  type emp_cursor_type is ref cursor;--定义游标变量类型

  emp_cursor emp_cursor_type;--定义游标变量

  emp_record emp%rowtype;

begin

  open emp_cursor for select * from emp where deptno = 10;--打开游标

  loop

    fetch emp_cursor into emp_record;

    exit when emp_cursor%notfound;

      dbms_output.put_line('第'||emp_cursor%rowcount || '个雇员：' || emp_record.ename);

  end loop;

  close emp_cursor;--关闭游标

end;

/

return子句：定义返回类型

declare

  type emp_record_type is record(

    name varchar2(10),salary number(6,2)

  );

  type emp_cursor_type is ref cursor return emp_record_type;

  emp_cursor emp_cursor_type;

  emp_record emp_record_type;

begin

  open emp_cursor for select ename,sal from emp where deptno = 10;--打开游标

  loop

    fetch emp_cursor into emp_record;

    exit when emp_cursor%notfound;

      dbms_output.put_line('第'||emp_cursor%rowcount || '个雇员：' || emp_record.name);

  end loop;

  close emp_cursor;--关闭游标

end;

/

cursor:用于返回嵌套游标

declare

  type emp_cursor_type is ref cursor;

  cursor dept_cursor(no number) is select a.dname, cursor(select ename,sal from emp

      where deptno = a.deptno) from dept a where a.deptno = no;

  empcur emp_cursor_type;

  v_dname dept.dname%type;

  v_ename emp.ename%type;

  v_sal emp.sal%type;

begin

  open dept_cursor(&no);

  loop

    fetch dept_cursor into v_dname, empcur;

    exit when dept_cursor%notfound;

    dbms_output.put_line('部门名：'||v_dname);

    loop

      fetch empcur into v_ename,v_sal;

      exit when empcur%notfound;

      dbms_output.put_line('雇员名：'||v_ename ||',工资：' ||v_sal);

    end loop;

  end loop;

  close dept_cursor;

end;

/