Oracle 笔记06

游标

什么是游标？

Oracle游标是通过关键字CURSOR来定义一组Oracle查询出来的数据集，类似数组一样，把查询的数据集存储在内存中，可以通过游标指向其中一条记录，通过循环游标来达到循环数据集的目的。

游标的类型

• 显式游标：由用户定义、操作，用于处理返回多行数据的SELECT查询
• 隐式游标：由系统自动处理，用于处理DML和返回单行数据的SELECT查询

显式游标

使用步骤：

游标的定义：

-- 游标必须在PL/SQL块的声明部分进行定义
-- 游标定义时可以引用PL/SQL块变量，但必须在游标前定义
-- 定义游标时没有生成数据，只是将定义信息保存到数据字典中
-- 游标定义后，可用 cursor_name%ROWTYPE 定义游标类型变量
CURSOR cursor_name[(paramter1 datatype,..)] IS select_statement;

打开游标：

-- 检查变量的值
-- 执行游标定义时对应的SELECT语句，将查询结果检索到工作区中
-- 游标指针指向第一个元组
-- 游标一旦打开，就无法再次打开，除非先关闭
-- 如果游标定义中的变量值发生变化，只能重新打开游标才起作用
OPEN cursor_name;

检索游标：

-- 使用FETCH 语句前要先打开游标
-- 对游标第一次使用FETCH语句时，游标指针指向第一条记录
-- 使用后，游标指针指向下一条记录
-- 指针只能向下移动，不能回退
-- INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录结构相同
FETCH cursor_name INTO variable_list|record_variable;

关闭游标：

-- 释放工作区内存
CLOSE cursor_name;

示例：根据输入的部门号查询某个部门的员工信息

DECLARE
    v_deptno emp.deptno%TYPE;
    CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno;  --定义游标
    v_emp c_emp%ROWTYPE;
BEGIN
    v_deptno := &x;
    OPEN c_emp;  --打开游标
    LOOP
        FETCH c_emp INTO v_emp;       --检索游标
        EXIT WHEN c_emp%NOTFOUND;     --检索游标
        DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
                             v_emp.ename||' '||
                             v_emp.sal||' '||
                             v_emp.deptno);
    END LOOP;
    CLOSE c_emp;      --关闭游标
END;

显示游标的属性：

循环检索游标：

-------------简单循环
OPEN c_emp;
LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
                         v_emp.ename||' '||
                         v_emp.sal||' '||
                         v_emp.deptno);
END LOOP;
-------------WHILE循环
OPEN c_emp;
FETCH c_emp INTO v_emp;
WHILE c_emp%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
                         v_emp.ename||' '||
                         v_emp.sal||' '||
                         v_emp.deptno);
    FETCH c_emp INTO v_emp;
END LOOP;
CLOSE c_emp;
---------------FOR循环
OPEN c_emp;
FOR v_emp IN c_emp LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
                         v_emp.ename||' '||
                         v_emp.sal||' '||
                         v_emp.deptno);
END LOOP;
CLOSE c_emp;

利用游标更新或删除数据：

-- 1.定义可用于更新的游标
-- 打开游标时相应的表被加锁，其他用户不能对该表进行DML操作
-- 若数据对象已被其他会话加锁，则当前会话挂起
-- 若指定了NOWAIT子句，则不等待，返回错误
-- OF子句用于多表查询时指定某个表加锁，否则默认全部加锁
-- 当用户执行COMMIT或ROLLBACK操作时，数据锁自动被释放
CURSOR cursor_name IS select_statement
   FOR UPDATE [OF column_reference] [NOWAIT];
-- 2.利用游标更新或删除数据
-- 如果该游标没有设置FOR UPDATE子句，则不能进行该操作
UPDATE|DELETE ...
WHERE CURRENT OF cursor_name;

示例：根据员工的部门号提高工资

DECLARE
    CURSOR c_emp IS SELECT * FROM emp FOR UPDATE;
    v_increment NUMBER;
BEGIN
    FOR v_emp IN c_emp LOOP
        CASE v_emp.deptno
            WHEN 10 THEN v_increment:=100;
            WHEN 20 THEN v_increment:=200;
            WHEN 30 THEN v_increment:=300;
        END CASE;
    UPDATE emp SET sal=sal+v_increment
        WHERE CURRENT OF c_emp;
    END LOOP;
COMMIT;
END;

隐式游标

游标定义：

-- 所有的SQL语句都有一个执行的缓冲区
-- 隐式游标就是指向该缓冲区的指针，由系统隐含地打开、处理和关闭
-- 又称SQL游标
-- 主要处理DML语句以及单行的SELECT...INTO语句
-- 没有OPEN FETCH CLOSE 等操作语句

示例：给员工号为1000的员工加100薪资，如果不存在则创建该员工

-- 第一种方法
BEGIN
    UPDATE emp SET sal=sal+100 WHERE empno=1000;
    IF SQL%NOTFOUND THEN
        INSERT INTO emp(empno,sal) VALUES (1000,1600);
    END IF;
END;
-- 第二种方法
BEGIN
    UPDATE emp SET sal=sal+100 WHERE empno=1000;
    IF SQL%ROWCOUNT=0 THEN
        INSERT INTO emp(empno,sal) VALUES (1000,1600);
    END IF;
END;

游标变量

• 游标变量是一个指向多行查询结果集的指针
• 它不与特定的查询绑定，有很大的灵活性
• 可以在打开游标时再定义具体查询内容
• 可以返回不同结构的结果集

定义游标引用类型：

-- 定义游标类型：
TYPE ref_cursor_type_name IS REF CURSOR [RETURN return_type];
-- RETURN子句用于指定定义的游标类型返回结果集的类型，必须是记录类型
-- 如果定义时带有RETURN子句，则该游标变量被称为强游标变量，否则是弱游标变量
-- Oracle 10g中，系统内置了一个游标引用类型，称为SYS_REFCURSOR

声明游标变量

ref_cursor_type_name variable_name;
--例如：
TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;
v_emp emp_cursor_type;

打开游标变量

OPEN cursor_variable FOR select_statement;
--例如：
OPEN v_emp FOR SELECT * FROM emp;

检索游标变量

LOOP
    FETCH cursor_variable INTO variable1,variable2,...;
    EXIT WHEN cursor_variable%NOTFOUND;
    ...
END LOOP;
-- 检索游标变量只能用简单循环或WHILE循环，不能用FOR循环

关闭游标变量

CLOSE cursor_variable;