oracle select 变量_Oracle中游标cursor的基本介绍和使用

oracle游标cursor的定义

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

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_testbegin    --无需显式打开游标    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_selectissql_select varchar(400);sql_row tablesp%rowtype;type cur_select is ref cursor; --声明一个动态游标类型，名字叫cur_select，因为游标不是类型所以要声明一个动态游标类型curs cur_select;  --声明一个动态游标变量，名字叫cursbegin  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的存储过程。

---

本文来源网络整理，不代表本站观点。分享本文章目的在于学习，如有侵权，请邮件告知删除，本站将在收到删除告知信息的情况下，24小时内删除，并给予道歉，谢谢支持！