oracle中游标cursor的基本介绍和使用

作者: Arvin Chen 分类: oracle 来源: Break易站(www.breakyizhan.com)

oracle游标cursor的定义

游标是由英文cursor直接翻译过来的,它的定义和理解其实很简单:游标cursor是SQL的内存工作区,由系统或用户定义为变量,就是一个临时的数据存储区域。游标cursor的作用是临时存储从数据库中提取的数据块。在某些情况下,需要将数据从磁盘上存储的表中传输到计算机的内存中进行处理,最后将显示处理结果或最终将其写回数据库。使用游标cursor将提高数据处理的速度,否则频繁的磁盘数据交换将降低效率。而游标是经常用在动态SQL中绑定变量,可查看oracle绑定变量的定义和使用方法总结 - Break易站的第五部分。

oracle游标cursor的类型和常用属性

游标Cursor类型包含三种: 隐式游标Cursor,显式游标Cursor和Ref Cursor(动态游标Cursor)。

游标有下面的几种常用属性:

属性 描述
%FOUND 如果DML语句在更新后或DQL找到结果后影响了数据,则返回true。否则,它返回false。
%NOTFOUND 如果DML语句在更新后影响数据或DQL找到结果,则返回false。否则,它返回true。
%ISOPEN 如果游标已打开,则返回true,否则返回false。
%ROWCOUNT 返回执行DML之后受影响的行数。

隐式游标Cursor

实际上,当我们在PLSQL中执行非查询(或返回单条记录查询)语句时,例如更新、删除、插入等,ORACLE系统将自动为这些操作设置游标并创建其工作区,并且游标的名称是SQL,由ORACLE系统定义。

对于隐式游标的操作,ORACLE系统将自动完成、值和关闭操作,而无需用户处理。

PLSQL Management 隐式游标,当查询开始时,隐式游标打开,当查询结束时,隐式游标自动关闭。

用户只能使用隐式游标的相关属性来完成相应的操作。在隐式游标的工作区中,存储的数据是新处理的SQL语句、中包含的数据,该语句独立于用户定义的显示光标。这里需要指出的是:关于隐式游标的属性操作,必须在提交commit之前。

PLSQL Management在update语句使用之后,会多一步commit操作,就是在commit之前,oracle就使用了隐式游标。

而在循环语句中,返回多条记录查询的时候,oracle也使用了隐式游标,例如:

Set Serveroutput on;  
  
begin  
    update t_contract_master set liability_state = 1 where policy_code = '123456789';  
      
    if SQL%Found then  
       dbms_output.put_line('the Policy is updated successfully.');  
       commit;  
    else  
      dbms_output.put_line('the policy is updated failed.');  
    end if;    
end;   
/  

显式游标Cursor

当查询返回多行时,需要显式游标,并且用户无法使用select into语句。显式游标在PL/SQL块的声明部分中声明,在执行部分或异常处理部分中打开,获取数据并关闭。为了在这里声明,我们所说的游标通常是指显式游标,并且需要声明该显式游标。
游标的声明,打开,关闭,从游标中提取数据

-- 声明光标
CURSOR cursor_name IS select_statement;
--打开游标
OPEN cursor_name;
--关闭游标
CLOSE cursor_name;
--从游标中提取数据
--使用FETCH命令从游标中获取一行数据。提取每个数据后,游标指向结果集的下一行。
--语法如下:
FETCH cursor_name INTO variable[variable,..]

例子:

set serveroutput on;
declare
    cursor c is select * from ljb_test; --1.声明游标的时候Oracle不会从数据库中取数据
    v_test c%rowtype;
begin
    open c;         --2.打开游标,此时从数据库中取数据,并把结果集放在内存中
        fetch c into v_test;    --3.获取数据,fetch的时候游标自动往下移动一格
        dbms_output.put_line(v_test.name);

        fetch c into v_test;
        dbms_output.put_line(v_test.name);
    close c;        --4.关闭游标,清掉内存。成对编程 
end;
/

遍历结果集

 

如果要遍历整个测试表,显然需要经过循环。
通常,如果遵循游标的循环遍历,则应遵循以下步骤:
1,打开游标
2,开始循环
3,从游标中取值
4,检查返回的行
5,处理数据
6,关闭循环
7,关闭游标
实际上,我们实际上可以通过使用循环和循环来实现。
但是,对于循环并不需要太复杂,这里我们将重点放在循环上。
如上所述,PLSQL中有三种循环。应该指出的是,使用游标遍历时最简单,最稳定的是针对循环,但仍将简要介绍另外两个循环。如下:

for循环遍历游标

FOR 循环游标被声明为普通游标,但不需要显式打开,关闭,获取数据,存在测试数据,定义存储数据的变量等。
对于循环,是推荐使用循环遍历的最简单且最不容易出错的方法。

set serveroutput on;
declare
    cursor c is select * from ljb_test;
    --无需在此声明变量v_test
begin
    --无需显式打开游标
    for v_test in c loop
    --无需显式fetch
        dbms_output.put_line(c%rowcount||'--'||v_test.name);
    end loop;
    --无需显式关闭游标
end;
/

while遍历循环游标

declare
    cursor c is select * from ljb_test; --声明游标的时候Oracle不会从数据库中取数据
    v_test c%rowtype;
begin
    open c;         --打开游标,此时从数据库中取数据,并把结果集放在内存中

        fetch c into v_test;    --获取数据,fetch的时候游标自动往下移动一格
        while c%found loop
            dbms_output.put_line(c%rowcount||'--'||v_test.name);
            fetch c into v_test; 
        end loop;
    close c;        --关闭游标,清掉内存。成对编程 
end;
/

do..while循环遍历游标

declare
    cursor c is select * from ljb_test; --声明游标的时候Oracle不会从数据库中取数据
    v_test c%rowtype;
begin
    open c;
    loop
        fetch c into v_test;
            exit when(c%notfound);
            dbms_output.put_line(c%rowcount||'--'||v_test.name);  --如果顺序反了,就会最后一条记录打印两次
    end loop;
    close c;       --关闭游标,清掉内存。成对编程 
end;
/

含参游标

与函数类似,我们可以将参数传递给游标并在查询中使用它们。

CURSOR cursor_name[(parameter[,parameter],...)] 
IS select_statement;

参数定义如下:

Parameter_name [IN] data_type[{:=|DEFAULT} value]

应该注意的是,游标只能接受传递的值,而不能接受该值。该参数仅定义数据类型,没有大小。

declare
    cursor c(v_dep ljb_test.dep%type, v_salary ljb_test.salary%type) 
        is select * from ljb_test where dep = v_dep and salary = v_salary;
begin
    for v_temp in c(3,4000) loop
        dbms_output.put_line(v_temp.name);
    end loop;
end;
/

可更新的游标

declare 
    cursor c is select * from ljb_test for update;  --添加for update即可
begin
    for v_temp in c loop
        if(v_temp.salary<3500) then
            update ljb_test set salary = salary * 2 where current of c; --更新条件
        elsif(v_temp = 5000) then
            delete from ljb_test where current of c;    --更新条件
        end if;
    end loop;
    commit;
end;
/

动态游标Cursor

语法:

type 动态游标类型名 is ref cursor;  ---- 声明一个动态游标类型,紫色填写一样内容

游标名 动态游标类型; ---- 声明一个动态游标类型的变量,这个变量就是动态游标类型的,也就是动态游标

open 游标名 for SQL语句; ---- 打开游标,并且把SQL语句和游标关联起来

close 游标名;

例子:

--创建过程
create or replace procedure proc_select
is
sql_select varchar(400);
sql_row tablesp%rowtype;
type cur_select is ref cursor; --声明一个动态游标类型,名字叫cur_select,因为游标不是类型所以要声明一个动态游标类型
curs cur_select;  --声明一个动态游标变量,名字叫curs
begin
  sql_select:='select * from tablesp'; -- 需要执行的SQL语句
  open curs for sql_select;    -- 打开游标,并且SQL执行结果存放到curs中
  loop
    fetch curs into sql_row;  -- 把curs中的一条记录赋值为 sql_row
    dbms_output.put_line(sql_row.tid||'-'||sql_row.tname||'-'||sql_row.tage);
    exit when curs%notfound;  -- 退出循环
  end loop;
  close curs;
end;

而我们的游标也多用于oracle的存储过程,关于oracle存储过程的创建可以查看:oracle存储过程的创建 - Break易站

  •   本文标题:oracle中游标cursor的基本介绍和使用 - Break易站
    转载请保留页面地址:https://www.breakyizhan.com/oracle/15711.html
      微信返利机器人
      免费:淘宝,京东,拼多多优惠券
      腾讯,爱奇艺,优酷的VIP视频免费解析,免费看
      即刻扫描二维码,添加微信机器人!

    发表笔记

    电子邮件地址不会被公开。 必填项已用*标注