什么是游标？

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。

显示游标的处理：

显示游标的四个属性：

• %FOUND：该属性用于检测游标结果集是否存在数据，如果存在数据，返回TRUE。
• %NOTFOUND：该属性用于检测游标结果集是否不存在数据，如果不存在数据，返回TRUE。
• %ISOPEN：该属性用于检测游标是否已经打开，如果已经打开返回TRUE。
• %ROWCOUNT：该属性用于返回已提取的实际行数。

游标的FOR循环

当使用游标for循环时，Oracle会隐含地打开游标，提取数据并关闭游标。

参数游标：参数游标是指带有参数的游标。通过使用参数游标，使用不同参数值可以生成不同的游标结果集。

隐式游标的处理

• 显示游标是由用户自定义的显示创建的游标，主要是用于对查询语句的处理。
• 隐式游标是由系统隐含创建的游标，主要用于对非查询语句，如修改、删除等操作，则由Oracle系统自动地位这些操作设置游标并创建其工作区。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由Oracle系统自动完成，无需用户进行处理。
• 隐式游标的名字为SQL，这是由Oracle系统定义的。

DML操作和单行SELECT语句会使用隐式游标，它们是：

• 插入操作：insert
• 更新操作：update
• 删除操作：delete
• 单行查询操作：select...into

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。注意：通过SQL游标名总是只能访问前一个DML操作或单行select操作的游标属性。

隐式游标的属性：

• SQL%FOUND
• SQL%NOTFOUND
• SQL%ISOPEN
• SQL%ROWCOUNT

--根据用户输入的员工号，更新指定员工的工资，比如工资涨100
--隐式游标
BEGIN
  UPDATE empnew SET sal = sal + 100 WHERE empno = &no;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.put_line('成功修改员工的工资');
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line('修改员工工资失败!');
    ROLLBACK;
  END IF;
END;

SELECT * FROM empnew;

使用游标修改或删除数据 

为了对正在处理（查询）的行不被另外的用户改动，Oracle提供一个FOR UPDATE子句来对所选择的行进行锁定。

如果创建的游标需要执行更新或删除的操作，必须带有FOR UPDATE子句。FOR UPDATE子句会将游标提取出来的数据进行行级锁定，这样在本会话更新期间，其他用户的会话就不能对当前游标中的数据行进行更新操作。

--按职工的职称涨工资，总裁涨1000元，经理涨500元，其他员工涨300元。
--1：用显示游标的常规方式实现业务需求
DECLARE
  --定义游标
  CURSOR empnew_cursor IS SELECT empno,job FROM empnew;
  v_empno empnew.empno%TYPE;
  v_job   empnew.job%TYPE;
BEGIN
  --打开游标
  OPEN empnew_cursor;
  --提取数据
  LOOP
    FETCH empnew_cursor INTO v_empno,v_job;
    IF v_job='PRESIDENT' THEN
      UPDATE empnew SET sal = sal + 1000 WHERE empno = v_empno;
    ELSIF v_job='MANAGER' THEN
      UPDATE empnew SET sal = sal + 500 WHERE empno = v_empno;
    ELSE
      UPDATE empnew SET sal = sal + 300 WHERE empno = v_empno;
    END IF;
    EXIT WHEN empnew_cursor%NOTFOUND;
  END LOOP;
  COMMIT;
  --关闭游标
  CLOSE empnew_cursor;
END;

--2：用游标FOR循环的方式实现业务需求
DECLARE
  --定义游标
  CURSOR empnew_cursor IS SELECT empno,job FROM empnew; 
BEGIN
  FOR empnew_record IN empnew_cursor LOOP    
      DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);      
      IF empnew_record.job = 'PRESIDENT' THEN
        UPDATE empnew SET sal = sal + 1000 WHERE empno = empnew_record.empno;
      ELSIF empnew_record.job = 'MANAGER' THEN
        UPDATE empnew SET sal = sal + 500 WHERE empno = empnew_record.empno;
      ELSE
        UPDATE empnew SET sal = sal + 300 WHERE empno = empnew_record.empno;
      END IF;
  END LOOP;
  --COMMIT;
END;

select * from empnew for update;


--3：使用游标添加或删除数据时，定义游标时利用FOR UPDATE 子句可以将游标提取出来的数据进行行级锁定
DECLARE
  --定义游标
  CURSOR empnew_cursor IS SELECT empno,job FROM empnew FOR UPDATE; 
BEGIN
  FOR empnew_record IN empnew_cursor LOOP    
      DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);      
      IF empnew_record.job = 'PRESIDENT' THEN
        UPDATE empnew SET sal = sal + 1000 WHERE CURRENT OF empnew_cursor;
      ELSIF empnew_record.job = 'MANAGER' THEN
        UPDATE empnew SET sal = sal + 500 WHERE CURRENT OF empnew_cursor;
      ELSE
        UPDATE empnew SET sal = sal + 300 WHERE CURRENT OF empnew_cursor;
      END IF;
  END LOOP;
  COMMIT;
END;

SELECT * FROM empnew;

NOWAIT：用于指定不等待锁，如果发现所操作的数据行已经锁定，将不会等待，立即返回。

当游标子查询涉及到多张表时，如果在特定表上加行共享锁，那么需要使用OF子句。

--FOR UPDATE NOWAIT 不等待锁，如发现所操作的数据行已经锁定，将不会等待，立即返回
DECLARE
  --定义游标
  CURSOR empnew_cursor IS SELECT empno,job FROM empnew FOR UPDATE NOWAIT; 
BEGIN
  FOR empnew_record IN empnew_cursor LOOP    
      DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);      
      IF empnew_record.job = 'PRESIDENT' THEN
        UPDATE empnew SET sal = sal + 1000 WHERE CURRENT OF empnew_cursor;
      ELSIF empnew_record.job = 'MANAGER' THEN
        UPDATE empnew SET sal = sal + 500 WHERE CURRENT OF empnew_cursor;
      ELSE
        UPDATE empnew SET sal = sal + 300 WHERE CURRENT OF empnew_cursor;
      END IF;
  END LOOP;
  COMMIT;
END;

SELECT * FROM empnew;

--使用OF子句在特定表上加行共享锁 
DECLARE
       CURSOR empnew_cursor IS
              SELECT d.dname dname,e.ename ename 
              FROM empnew e join dept d on e.deptno = d.deptno
              WHERE e.deptno = &deptno
              FOR UPDATE OF e.deptno;
BEGIN
        FOR empnew_record IN empnew_cursor LOOP
            DBMS_OUTPUT.PUT_LINE('部门名称：'||empnew_record.dname||'员工名：'||empnew_record.ename);
            DELETE FROM empnew WHERE CURRENT OF empnew_cursor;
        END LOOP;
        COMMIT;
END;
              
SELECT * FROM empnew where deptno = 20;