Oracle中使用游标

/*游标
目的：为了处理SELECT语句返回多行数据

使用步骤：
1、定义游标
CURSOR cursor_name IS select_statement
2、打开游标
OPEN cursor_name
3、提取数据
FETCH cursor_name INTO variable1,...  --提取一行数据
或
FETCH cursor_name INTO BULK COLLECT collect1,...   --提取多行数据
4、关闭游标
CLOSE  cursor_name
显示游标属性:
使用方法：cursor_name%游标属性
常用游标属性:
%ISOPEN
%FOUND
%NOT FOUND
%ROWCOUNT
*/
DECLARE
  CURSOR emp_cursor
    IS
      SELECT ename,sal FROM emp WHERE deptno=20;
  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;
    dbms_output.put_line('姓名:' || v_ename || '，薪水:' || v_sal);
  END LOOP;
 
  CLOSE emp_cursor;
END;

/*参数游标
CURSOR cursor_name (parameter_name datatype)
  IS
    select_statement
*/
DECLARE
  CURSOR getUser_cursor(cno NUMBER)
    IS
      SELECT ename,sal FROM emp WHERE deptno=cno;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  IF NOT getUser_cursor%ISOPEN THEN
    OPEN getUser_cursor(&NO);
  END IF;
 
  LOOP
    FETCH getUser_cursor INTO v_ename,v_sal;
    EXIT WHEN getUser_cursor%NOTFOUND;
    dbms_output.put_line('姓名:' || v_ename || '，薪水:' || v_sal);
  END LOOP;
 
  CLOSE getUser_cursor;
END;

/*使用游标更新或删除数据
CURSOR cursor_name (parameter_name datatype) IS select_statement
FOR UPDATE [OF column_reference] [NOWAIT];
FOR UPDATE子句用于在游标结果集数据上加共享锁
[OF column_reference]对指定列加锁，如没有[OF column_reference]则全表加锁
[NOWAIT]指立即加锁
注意：在提取了游标数据之后，为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中
引用WHERE CURRENT OF子句，语法如下：
UPDATE table_name SET column_name=... WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;
*/
DECLARE
  CURSOR addSal_cursor
    IS
      SELECT ename,sal FROM emp FOR UPDATE OF sal;  --在sal列上加上共享锁
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE; 
BEGIN
  OPEN addSal_cursor;
 
  LOOP
    FETCH addSal_cursor INTO v_ename,v_sal;
    EXIT WHEN addSal_cursor%NOTFOUND;
    IF v_sal<2500 THEN
      UPDATE emp SET sal=sal+250 WHERE CURRENT OF addSal_cursor;
    END IF;
  END LOOP;
 
  CLOSE addSal_cursor;
END;

SELECT * FROM emp;

/*游标的FOR循环
FOR record_name IN cursor_name LOOP
  statement1;
  ...
END LOOP;
注意：使用游标的FOR循环时，不要显示的打开和关闭游标
*/
DECLARE
  CURSOR showEmp_cursor
    IS
      SELECT ename FROM emp WHERE deptno=&NO;
BEGIN
  FOR emp_name IN showEmp_cursor
  LOOP
    dbms_output.put_line('第' || showEmp_cursor%ROWCOUNT || '个员工' || emp_name.ename);
  END LOOP;
END;

/*在使用游标循环时可以直接在游标FOR循环中使用子查询*/
BEGIN
   FOR emp_name IN (SELECT ename FROM emp WHERE deptno=&NO)
   LOOP
    dbms_output.put_line('员工:' || emp_name.ename);
  END LOOP;
END;