526互联

Oracle游标

发布时间 2023-04-13 11:14:38作者: ISPEAKER

1.定义

　　游标是一种能够从多条记录中逐一提取每条记录的机制，PL/SQL中有两种游标，静态游标和动态游标，静态游标又分为显式游标和隐式游标。

2. 游标的属性

  • %ISOPEN：判断游标是否被打开，如果游标已经打开，返回真，否则返回假
  • %FOUND：判断游标的指针中是否还有值，如果有值，返回真，否则返回假
  • %NOTFOUND：判断游标的指针中是否没有值，如果没有值，返回真，否则返回假
  • %ROWCOUNT：游标指针指定的行数（最大为结果集的行数）

3. 显式游标 

　　用户在当前块或者包中显式声明的游标，使用SELECT语句指定结果集。

显式游标的使用：

① 声明游标，并指定结果集

cursor cursor_name is select statement;　

　　cursor表明vi是声明的变量是一个游标，关键字is后面紧跟select语句查询出来的结果集。

② 声明传递游标中值的变量

③ 打开游标

　　在从游标中获取数据之前，必须打开游标，否则无法获取数据

open cursor_name;　

④ 使用FETCH从游标中获取数据

fetch cursor_name into variable_name;　

 ⑤ 关闭游标

close cursor_name;　

示例：

　　打印雇员表emp中10部门的员工信息：

declare 
cursor cur1 is select * from emp where deptno = 10;  --声明游标
v_emp emp%rowtype;
begin 
  open cur1;   --打开游标
  fetch cur1 into v_emp;   --获取数据
  dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||
                       v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
  
  fetch cur1 into v_emp;
  dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||
                       v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);

  fetch cur1 into v_emp;
  dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||
                       v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);

  close cur1;
end;

　　上面这个例子的使用前提是要知道游标所定义的结果集有多少行数据，否则尽量不要使用这种写法。缺点：①代码量太多   ②不确定具体的结果集数据行，导致无法确定fetch的数量

想要实现这个功能，可以结合游标的属性和循环。

4. 游标与循环

　　从上面的代码可以看出，几乎都是重复的代码，这时可以利用循环来介绍代码量

① loop循环+静态游标

declare 
cursor cur2 is select * from emp where deptno = 10;
v_emp emp%rowtype;
begin 
  open cur2;
  loop
    fetch cur2 into v_emp; 
    exit when cur2%notfound;  --当游标的指针没有值时，退出循环
    dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||
                       v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);  
   end loop;
   close cur2;
end;　

　　使用循环的时候，一定要注意条件和执行语句的顺序，在这里如果将顺序搞反，也就是将exit when和dbms_output.put_line()的顺序颠倒时，最后一行的数据会被重复打印。

② while循环+静态游标

declare 
cursor cur1 is select * from emp where deptno = 10;
v_emp  emp%rowtype;
begin 
  open cur1;
  fetch cur1 into v_emp;  --由于while条件的原因，游标的指针必须先fetch，才能符合条件进入循环
  while cur1%found loop    
    dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||
                       v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno); 
    fetch cur1 into v_emp; 
  end loop;
  close cur1;
end;  　

③ goto循环+静态游标

declare 
cursor cur1 is select * from emp where deptno = 10;
v_emp emp%rowtype;
begin 
  open cur1;
  fetch cur1 into v_emp;    --由于if条件的原因，需要先fetch，才可以进入循环
  <<circulate>>        --定义goto标签
  dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||       --循环体
                       v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
  fetch cur1 into v_emp; 
  if cur1%found then     --进入循环的条件
    goto circulate;
  end if;
end;

④ for循环代替游标

　　for循环的用法不同于其他三种循环，在for循环中，既不用声明游标，也不用打开关闭游标

begin 
for v_emp in (select * from emp where deptno = 10) loop
   dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||       
                       v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
end loop;
end;

4. 隐式游标

　　当Oracle执行一条非查询的DML语句或者SELECT INTO语句时，都会自动创建一个隐式游标。

　　隐式游标不用声明，不用打开，不用赋值，不用关闭，这都是系统自动完成的。

　　和显式游标，游标的四大属性，同样适用于隐式游标。

隐式游标的属性：

  • SQL%ISOPEN：判断游标是否打开,永远返回假
  • SQL%FOUND：判断游标的指针里是否有值，如果最近一次有结果，返回真，否则假。
  • SQL%NOTFOUND：判断游标的指针里是否没值，如果最近一次没结果，返回真，否则假。
  • SQL%ROWCOUNT：游标的指针指了多少行，返回最近一次从游标读取的数据。

隐式游标的使用：

① 非查询的DML操作

declare 
# 隐式游标不需要声明
v_empno emp.empno%type;
begin 
  v_empno := &empno;
# 一个非查询的DML操作，系统自动创建隐式游标
  delete from emp1 where empno = v_empno;
  if sql%found then 
    dbms_output.put_line('找到员工信息');
    dbms_output.put_line('游标所影响的行数'||sql%rowcount);
  else 
    dbms_output.put_line('未找到员工信息');
  end if;
end;　

② SELECT INTO

　　SELECT INTO作用于将单行结果集放置到变量中。SELECT INTO处理的结果包含两种情况：　

  • 查询结果返回单行，SELECT INTO执行成功
  • 查询结果返回多行，PL/SQL抛出too_many_rows异常
  • 查询结果返回没有行，PL/SQL抛出no_data_found异常
declare 
v_ename emp.ename%type;
begin 
  select ename into v_ename   --注意select into后变量的位置
  from emp
  where empno = &empno;
  if sql%found then 
    dbms_output.put_line('员工姓名为'||v_ename);
  else 
    dbms_output.put_line('没有此员工');
  end if;
exception
  when no_data_found then 
    dbms_output.put_line('no_data_found');
  when too_many_rows then 
    dbms_output.put_line('to_many_rows');
end;

5. 动态游标　

　　动态游标：游标在声明时没有设定，在打开时可以对其进行修改，可以重复使用。动态游标分为强类型游标和弱类型游标。
　　强类型动态游标:在声明变量时使用return关键字定义游标的返回类型
　　弱类型动态游标:在声明变量时不使用return关键字定义游标的返回类型
　　一般动态游标有强类型REF CURSOR RETURN、弱类型REF CURSOR、系统预定义的弱类型SYS_REFCURSOR。

动态游标和静态游标使用的不同:

  • 静态游标的声明部分: cursor 游标名                                                     动态游标: 游标名 动态游标类型
  • 静态游标的数据集部分: 在声明时确认数据集，使用is关键字               动态游标: 在打开游标时确认数据集，使用for关键字
  • 静态游标在使用钱酒已经定义完成，不可修改                                      动态游标的在声明时没有定义,可以在打开时进行修改，可重复使用  
declare 
cur1 sys_refcursor;  --声明一个弱类型动态游标
v_emp emp%rowtype;
v_dept dept%rowtype;
begin 
  open cur1 for select * from emp where deptno = 10;  --打开游标，并指定结果集
  loop
    fetch cur1 into v_emp;
    exit when cur1%notfound;    
    dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||       
                       v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
  end loop;
  close cur1;
  dbms_output.put_line('-------------------------------------');
  
  open cur1 for select * from dept;--动态游标每次打开都是一个新的结果集
  fetch cur1 into v_dept;
  while cur1%found loop
    dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);                       
    fetch cur1 into v_dept;
  end loop;
  close cur1;  --每次打开游标，都要将其关闭
end;