PL/SQL游标描述

游标示意图：

 

游标的类型分为：隐式游标，显式游标，REF游标。

游标的属性：%FOUND,%NOTFOUND,%ROWCOUNT,%ISOPEN

常见的异常：NO_DATA_FOUND,TOO_MANY_ROWS，其他的为others

隐式游标：在使用DML语句时自动创建，自动打开、关闭游标,其名为SQL；

我们可以使用for循环来对游标进行隐式处理。For循环中变量v_test不需要在声明部分声明，它由PLSQL编译器进行隐式的声明。并且for循环开始的时候，游标也就隐式地打开了。

 

--使用for （该用法是隐式游标） declare cursor user_sor is select * from t_user; begin for row_data in user_sor loop//当前游标的记录被隐式的赋值给变量row_data dbms_output.put_line(row_data.userid); end loop; end;

显式游标：在PL/SQL语句中定义的游标称为显式游标。显式游标处理需如下步骤：

    a) cursor 游标名 is 查询语句（申明游标）；

    b) open 游标名（打开游标）；

    c) fetch 游标名 in--游标的当前记录已被隐式地提取给变量 v_testto 变量名（提取行）；

    d) close 游标名（关闭游标）。 例：学习使用游标，顺便使用%type --使用while循环 declare employee emp.ename %type; salary emp.sal %type; cursor cursor_test is select ename,sal from emp; begin open cursor_test; fetch cursor_test into employee,salary; while cursor_test %found loop dbms_output.put_line(employee||':'||to_char(salary)); fetch cursor_test into employee,salary; end loop; close cursor_test; end; --使用loop循环实现 declare V_ename emp.ename %type; V_sal emp.sal %type; cursor c1 is select ename,sal from emp; begin open c1; loop fetch c1 into V_ename,V_sal; dbms_output.put_line(V_ename||to_char(V_sal)); exit when c1 % notfound; end loop; close c1; end;

 

带参数的显式游标：游标的参数，只能接受传递的值，而不能返回值，参数只定义数据类型，没有大小。可以给参数赋一个缺省值

 

 

DECLARE desig VARCHAR2(20); emp_code VARCHAR2(5); empnm VARCHAR2(20); CURSOR emp_cur(desigparam VARCHAR2 DEFAULT '05') IS SELECT empno, ename FROM employee WHERE designation=desigparam; BEGIN desig:= '&desig';//加入&的作用就是运行的时候会提示输入变量值 OPEN emp_cur(desig);//打开游标的同时传入参数 LOOP FETCH emp_cur INTO emp_code,empnm; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm); END LOOP; CLOSE emp_cur; END;

 

REF游标：用于处理运行时动态执行的SQL语句。创建REF游标需要以下两个步骤：

              a) 申明REF游标类型；

              b) 申明REF游标类型的变量。

  --定义REF游标类型 TYPE REF_CURSOR is REF CURSOR [return userinfo%rowtype];//若有返回值则是强类型，反之为弱类型 --定义游标变量 CUR_REF REF_CURSOR; user_row userinfo%rowtype; begin open CUR_REF for select * from userinfo;//REF游标申明时无需指定查询语句 loop fetch CUR_REF into user_row; exit when CUR_REF%notfound; dbms_output.put_line(user_row.password); end loop; close cur_ref; end;

游标修改和删除操作:

游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用for update选项。

为了对正在处理（查询）的行不被另外的用户改动，oracle提供一个for update子句来对所有选择的行进行锁定，该需求迫使oracle锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。

语法：

Select…from..for update [of column[,column]…][nowait]

如果另一个会话已对活动集中行加了锁，那么select for update操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上nowait子句时，如果这些行真的被另一个会话锁定，则open立即返回并给出：ora-0054：resource busy and acquire with nowait specified。如果使用for update声明游标，则可在delete和update语句中使用where current of cursor_name 子句，修改或删除游标结果集合当前行对应的数据表中数据行。

declare cursor c_thy2 is select * from emp for update of sal; begin for v_record in c_thy2 loop if v_record.sal < 1000 then update emp set sal = 1000, comm = 200 where current of c_thy2; end if; end loop; commit; end;