Oracle数据库01游标

游标

PL/SQL使用select仅可查询一行信息，所以对于多行信息的查询引入了游标。

• 定义：是在内存中开辟的一小块工作区，在其中存放select语句的查询结果。
• 分类：
  • 显示游标：由程序员显示说明及控制，用于从表中取出多行数据，并将多行数据一行一行单独处理。
  • 隐式游标：PL/SQL隐式建立并自动管理这些游标，无需显示定义。

隐式游标

可以使用游标属性从最近执行的SQL语句中获取信息。
用于处理DML语句以及返回单行的查询

• SQL%rowcount：返回最近一条SQl语句所影响到的记录的数目。
• SQL%notfound：
• SQL%found
• SQl%isopen

由于隐式游标没有名字因此只能用SQL代指。

显式游标

由用户显式声明，。查询返回多条记录

• 使用游标时，select语句查询的结果可以是单条记录、多条记录、零条记录
• 游标工作区中，存在着一个指针，初始时指向查询结果首记录
• 使用fetch获取数据（一次仅获取一行，所以要使用循环语句）
• 使用过程：定义游标、打开游标、提取数据、关闭游标（即释放内存）

游标声明

在declare部分声明

cursor cursor_name is
select ...
from ...

打开游标

在可执行语句中执行
打开游标实际是执行了游标对应的select语句，将查询结果检索到了工作区

open cursor_name

提取数据

游标指针只可以向下移动不可以回退

fetch cursor_name into 变量名

关闭游标

close cursor_name

• 属性：
  • %isopen:如果游标是打开的，其值为true。仅当游标处于打开状态时才可以从中取数据。执行fetch操作时可以使用%isopen属性检测一下。
  • %notfound:如果fetch语句没有返回记录，其值为true
  • %found:如果fetch语句有返回记录，其值为true
  • %rowcount：返回迄今为止从游标中取出的记录的数目。（与隐式游标不同）
    在没有fetch之前，%notfound与%found值都为空

1. 使用%isopen属性检测游标是否打开

//使用%isopen属性检测游标是否打开
if not emp_cursor%isopen then
    open emp_cursor;
end if;
loop 
    fetch emp_cursor into ..

2. 简单循环fetch数据

//查询工资大于3000的员工名和工资
declare
    cursor emp_cursor is
    select ename,sal
    from emp
    where sal>3000
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;

3. while循环fetch数据
   %found如果在fetch前执行结果会返回null。因此在while前要先fetch。

begin 
    fetch emp_cursor into v_ename,v_sal;
    while emp_cursor%found loop
        dbms_output.put_line(v_ename||','||v_sal);
        fetch emp_cursor into v_ename,v_sal;
    end loop;
end;

4. for循环fetch数据

• for循环可以更方便的处理显示游标
• 隐式的打开、提取、关闭游标
• 隐式声明记录类型变量

begin 
    for emp_record inemp_cursor loop
        dbms_output.put_line(emp_record.v_ename||','||emp_record.v_sal);
    end loop;
end;

5. 游标名%rowtype得到一个复合类型

delcare 
    emp_record  emp_cursor%rowtype;
begin
    fetch emp_cursor into emp_record;
end;

6. 带参数的游标

declare 
    cursor cur_emp (p_sal number) is
    select ename,sal
    from emp
    where sal>p_sal;
    rec_emp_test    cur_emp%rowtype;
begin
    for rec_emp in cur_emp(2000) loop
         dbms_output.put_line(rec_emp.ename||''||rec_emp.sal);
    end loop;
    
    open cur_emp(3000);
    loop
        fetch cur_emp into rec_emp_test;
        dbms_output.put_line(rec_emp_test.ename||''||rec_emp_test.sal);
        exit when cur_emp%notfound;
    end loop;
end;

使用游标对数据库值进行修改

for update

for update:表示可以使用游标对数据进行更改
nowait：表示不会无限制的等待数据库资源
在事务执行期间了以显式锁定以拒绝访问
在更新或删除时要锁定该行

declare emp_cursor is
    select ename,sal
    from emp
    for update nowait;
begin
    for emp_record in emp_cursor loop
        if(emp_record.ename='SCOTT') then
            update emp set sal=emp_record.sal+300
            where ename='SCOTT'
            exit;
        end if;
    end loop;
end;

where current of子句

更新或删除游标中的当前行数据
首先要在游标中使用for update子句锁定行
使用where current of子句从显示游标中引用当前行

declare 
    cursor sal_cursor is
        select sal
        from emp
        where deptno=30
        for update of sal nowait;
begin
    for emp_record in sal_cursor loop
        update emp
        set sal=emp_record.sal*1.10
        where current of sal_cursor;
    end loop;
end;