189 8069 5689

Oracle专题12之游标

- 什么是游标？

我们提供的服务有：成都网站设计、成都做网站、外贸网站建设、微信公众号开发、网站优化、网站认证、达州ssl等。为上千企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的达州网站制作公司

  • 游标是SQL的一个内存工作区，由系统或者用户以变量的形式定义。
  • 游标的作用就是用于临时存储从数据库中提取的数据块。
  • 通俗的来讲，游标就是一个结果集。
  • 游标的类型分为显式游标和隐式游标。
    Oracle专题12之游标

    1、显式游标处理的四个步骤

  • 显式游标处理的语法：
    1、定义游标：CURSOR cursor_name[(parameter_name datatype)] IS select_statement;
    2、打开游标：OPEN cursor_name
    3、提取数据：FETCH cursor_name INTO variable1[, variable2, ...];
    4、关闭游标：CLOSE cursor_name。
    Oracle专题12之游标

  • 代码演示：查询所有员工的员工号、姓名和职位的信息。 
    DECLARE
    --定义游标
    CURSOR emp_cursor IS SELECT empno, ename, job FROM emp;
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_job emp.job%TYPE;
BEGIN
    --打开游标，执行查询
    OPEN emp_cursor;
    --提取数据
    LOOP
             FETCH emp_cursor INTO v_empno, v_ename, v_job;
             DBMS_OUTPUT.PUT_LINE('员工号：' || v_empno || '，姓名' || v_ename || '，职位' || v_job);
             --什么时候退出循环？%FOUND,%NOTFOUND
             EXIT WHEN emp_cursor%NOTFOUND;  --EXIT WHEN NOT emp_cursor%FOUND;
    END LOOP;
    --关闭游标
    CLOSE emp_cursor;
END;
  • FETCH语句的说明：1、把当前指针指向的记录返回；2、将指针指向下一条记录。

2、显式游标的四个属性

1、%FOUND：该属性用于检测游标结果集是否存在数据，如果存在数据，返回TRUE。
2、%NOTFOUND：该属性用于检测结果集是否存在数据，如果不存在数据，返回TRUE。
3、%ISOPEN：该属性用于检测游标是否已经打开，如果已经打开返回TURE。
4、%ROWCOUNT：该属性用于返回已经提取的实际行数。（如EXIT WHEN emp_cursor%ROWCOUNT=5;）

  • 示例：按职工的职称涨工资，总裁涨1000元，经理涨500元，其他员工涨300元。

    DECLARE
    --定义游标
    CURSOR emp01_cursor IS SELECT empno, job FROM emp01;
    v_empno emp01.empno%TYPE;
    v_job emp01.job%TYPE;
BEGIN
    --打开游标，执行查询
    OPEN emp01_cursor;
    --提取数据
    LOOP
             FETCH emp01_cursor INTO v_empno, v_job;
             IF v_job ='PRESIDENT' THEN
                 UPDATE emp01 SET sal = sal + 1000 WHERE empno = v_empno;
             ELSIF v_job = 'MANAGER' THEN
                 UPDATE emp01 SET sal = sal + 500 WHERE empno = v_empno;  
             ELSE
                 UPDATE emp01 SET sal = sal + 300 WHERE empno = v_empno; 
             END IF;
             --什么时候退出循环？%FOUND,%NOTFOUND
             EXIT WHEN NOT emp01_cursor%FOUND;
    END LOOP;

    COMMIT;
    CLOSE emp01_cursor;
    --关闭游标
END;

    3、游标的FOR循环

    a、游标FOR循环的语法

  • 当使用游标FOR循环时，Oracle会隐含地打开游标，提取数据并关闭游标。
  • 语法如下： 
    FOR record_name IN cursor_name（或者可以使用子查询） LOOP statement; 
END LOOP;
  • 代码演示：查询员工的员工号、姓名和职位。 
    DECLARE 
    CURSOR emp_cursor IS SELECT empno, ename, job FROM emp;
BEGIN
    FOR emp_record IN emp_cursor LOOP 
        DBMS_OUTPUT.put_line('员工号：' || emp_record.empno || '， 姓名' || '，职位' || emp_record.job);
        END LOOP;
END;
    BEGIN
    FOR emp_record IN (SELECT empno, ename, job FROM emp) LOOP 
        DBMS_OUTPUT.put_line('员工号：' || emp_record.empno || '， 姓名' || '，职位' || emp_record.job);
        END LOOP;
END;
  • 示例：按职工的职称涨工资，总裁涨1000元，经理涨500元，其他员工涨300元。 
    DECLARE 
    --定义游标
    CURSOR emp01_cursor IS SELECT empno, job FROM emp01;
BEGIN
    FOR emp01_record IN emp01_cursor LOOP
        DBMS_OUTPUT.put_line(emp01_record.empno || '----' || emp01_record.job);
        IF emp01_record.job = 'PRECIDENT' THEN
            UPDATE emp01 SET sal = sal + 1000 WHERE empno = emp01_record.empno;
        ELSIF emp01_record.job = 'MANAGER' THEN
            UPDATE emp01 SET sal = sal + 500 WHERE empno = emp01_record.empno;
        ELSE
            UPDATE emp01 SET sal = sal + 300 WHERE empno = emp01_record.empno;
        END IF;
    END LOOP;
    COMMIT;
END;

    b、参数游标

  • 参数游标是指带有参数的游标。通过使用参数游标，使用不同参数值可以生成不同的游标结果集。
  • 定义和打开参数游标的语法如下： 
    CURSOR cursor_name (parameter_name datatype) IS select_statement;
OPEN cursor_name (parameter_value);
  • 代码演示：显示10号部门的所有员工。 
    DECLARE 
    CURSOR emp_cursor(dno NUMBER) IS SELECT empno, ename, job FROM emp WHERE deptno = dno;
BEGIN
    FOR emp_record IN emp_cursor(10) LOOP
        DBMS_OUTPUT.put_line('员工号：' || emp_record.empno || '，姓名：' || emp_record.ename || '，职位：' || emp_record.job);
    END LOOP;
END;

    4、隐式游标的处理

    a、显式游标和隐式游标

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

    b、隐式游标的详细说明

  • 如DML操作和单行SELECT语句会使用隐式游标，他们是：
  • 插入操作：INSERT、更新操作：UPDATE、删除操作：DELECT、单行查询操作：SELECT ... INTO。
  • 当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。（需要注意：通过SQL游标名总是只能访问前一个DML操作或者单行SELECT操作的游标属性。）
  • 隐式游标的属性有： 
    SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
SQL%ROWCOUNT
  • 代码演示：根据用户输入的员工号，更新指定员工的工资。（比如说工资涨100） 
    BEGIN
    UPDATE emp01 SET sal = 100 + sal WHERE empno = &n1;
    IF SQL%FOUND THEN
        dbms_output.put_line('成功修改员工的工资');
    ELSE 
        dbms_output.put_line('修改员工工资失败');
        ROLLBACK;
    END IF;
END;

    5、使用游标修改或者删除数据1：对提取出来的数据进行行级锁定

  • 为了对正在处理（查询）的行不被另外的用户改动，oracle提供一个FOR UPDATE子句来对所选择的行进行锁住。
  • 如果当前用户锁定所选择的行，则其他用户不能进行对所选行进行修改，直到当前用户数据提交。
  • 因此如果创建的游标需要执行更新或者删除的操作的时候必须带有FOR UPDATE子句。
  • FOR UPDATE子句会将游标提取出来的数据进行行级锁定，这样在本会话更新期间，其他用户的会话就不能对当前游标中的数据行进行更新操作。
  • 语法格式： 
    CURSOR cursor_name IS select_statement FOR UPDATE [OF column_reference] [NOWAIT];
UPDATE table_name SET column = ... WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;
  • 示例：按职工的职称涨工资，总裁涨1000元，经理涨500元，其他员工涨300元。 
    DECLARE 
        --定义游标
        CURSOR emp01_cursor IS SELECT empno, job FROM emp01 FOR UPDATE;
    BEGIN
        FOR emp01_record IN emp01_cursor LOOP
            DBMS_OUTPUT.put_line(emp01_record.empno || '----' || emp01_record.job);
            IF emp01_record.job = 'PRECIDENT' THEN
                UPDATE emp01 SET sal = sal + 1000 WHERE CURRENT OF emp01_cursor;
            ELSIF emp01_record.job = 'MANAGER' THEN
                UPDATE emp01 SET sal = sal + 500 WHERE CURRENT OF emp01_cursor;
            ELSE
                UPDATE emp01 SET sal = sal + 300 WHERE  CURRENT OF emp01_cursor;
            END IF;
        END LOOP;
        COMMIT;
    END;
  • WHERE CURRENT OF cursor_name子句表示当前游标提取的结果集中对应条件的数据行。

    6、使用游标修改或者删除数据2：死锁状态

  • 当其他用户正在对所选行进行修改，而当前用户也对当前行进行锁定，则会出现死锁状态。（一直等待，无法锁定）

    a、NOWAIT子句

  • 用于指定不等待锁，如果发现所操作的数据行已经锁定，将不会等待，立即返回。（可以避免死锁的状态）

    b、使用of子句在特定表上加行共享锁

  • 当游标子查询涉及到多张表时，如果在特定表上加行共享锁，那么需要使用OF子句。
  • 需求：输入部门号，显示该部门名称以及员工的姓名，并删除该部门下的这些员工。 
    DECLARE
    CURSOR emp01_cursor IS
                 SELECT d.dname dname, e.ename ename 
                 FROM emp01 e join dept d on e.deptno = d.deptno 
                 WHERE e.deptno = &deptno 
                 FOR UPDATE OF e.deptno;
BEGIN
    FOR emp01_record IN emp01_cursor LOOP
        dbms_output.put_line('部门名称：' || emp01_record.dname || '，员工名：' || emp01_record.ename);
        DELETE FROM emp01 WHERE CURRENT OF emp01_cursor;
        END LOOP;
        COMMIT;
END;

文章名称：Oracle专题12之游标
URL地址：http://cdxtjz.cn/article/pojggg.html

其他资讯