温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Oracle专题12之游标

发布时间:2020-06-21 04:43:15 来源:网络 阅读:818 作者:糖醋白糖 栏目:关系型数据库

- 什么是游标?

  • 游标是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;    
向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI