oracle 游标以及游标变量

ORACLE 游标

一. PL/SQL 是用游标来管理 SQL 的 SELECT 语句的 . 游标是为了处理这些语句而分配的一大块内存 . 它提供了对一个结果集进行逐行处理的能力 , 可看作是一种特殊的指针 . 它与某个查询结果集相关联 , 可以指向结果集的任意位置 , 以便对指定位置的数据进行处理 . 使用它可以在查询数据的同时对数据进行处理 .

二. 游标的分类 : 静态游标和动态游标

   1. 在说明游标分类之前, 先说明一下PL/SQL 的两个操作, 游标属性, 变量属性

SET SERVEROUTPUT ON ;-- 此语句用于开启打印输出功能

DMBS_OUTPUT.PUT_LINE();-- 打印输出并换行.

%FOUND: 判断当前游标是否指向有效的一条行记录. 是则返回TRUE, 否则返回FALSE

%NOTFOUND: 与%FOUND 恰好相反.

%ISOPEN: 判断游标是否打开, 打开则返回TRUE, 否则返回FALSE.

%ROWCOUNT: 判断当前游标在所指向的结果集中提取的行数. 并非所有总记录数

%TYPE: 声明变量的类型与表中的某列的数据类型完全一致.%TYPE 声明有两个好处: 一是你不必知道某列的确切数据类型二是当某列的数据类型改变时不用修改变量的数据类型.

%ROWTYPE: 声明变量的数据类型与表中的行记录数据类型一致. 对于自定义的记录, 则必须声明自己的域.

  2. 静态游标:

静态游标又可以分为两类:

1) 隐式游标

隐式游标的特点:

- 隐式游标是由PL/SQL 来管理的, 即不需要声明游标语句, 也不需要OPEN,FETCH,CLOSE 操作

- 隐式游标中必须要有select cur_name into [ 变量名或其他数据类型]. 此句完成OPEN,FETCH,CLOSE 操作.

- 隐式游标只能返回一行记录, 如果无符合条件的记录将会出现NO_DATA_FOUND 异常. 如果出现多条记录将出现TOO_MANY_ROWS 异常.

- 隐式游标只能用SQL% 判断其游标属性

- 对于任何位置使用SQL%ISOPEN 结果都是FALSE, 隐式游标由PL/SQL 管理

- 对于在隐式游标位置前使用SQL%FOUND 或SQL%NOTFOUND, SQL%ROWCOUNT 结果值是NULL( 不确定值)

示例表:

表名:test

字段名	数据类型	可否为空
id( 序号)	number	N
name( 姓名)	varchar2(20)	N
zym( 专业名)	varchar(40)	Y
cj( 成绩)	number	Y
logdate( 记录日期)	date	Y

EG1: 验证隐式游标的特性

  set serveroutput on;-- 开启输出

declare

  no test.id%type;-- 声明变量no 的数据类型同test 表的id 列一致

  ename test.name%type;

  begin

  if sql%isopen then         -- 判断游标是否打开

  dbms_output.put_line(' cursor is openning');

  else

  dbms_output.put_line('cursor is closed');

  end if;

if sql%notfound then – 判断游标是否指向有效的行

  dbms_output.put_line('no value');

  else

  dbms_output.put_line(no||' '||ename);

  end if;

dbms_output.put_line(sql%rowcount);

dbms_output.put_line('---------------');

  select id ,name into no ,ename from test where cj=90;-- 隐式游标必-- 须使用INTO

  dbms_output.put_line(sql%rowcount);

  if sql%isopen then

  dbms_output.put_line(' cursor is openning');

  else

  dbms_output.put_line('cursor is closed');

  end if;

  if sql%notfound then

  dbms_output.put_line('no value');

  else

  dbms_output.put_line(no||' '||ename);

  end if;

  exception

  when no_data_found then

     dbms_output.put_line('no value');

when too_many_rows then

      dbms_output.put_line('too many rows');

  end;

执行结果为:

cursor is closed

--------------------

1

cursor is closed

1001 qbnajj

去掉where 条件时的结果:

cursor is closed

--------------------

too many rows

EG2: 使用%ROWTYPE

declare

  rowtype test%rowtype;

  begin

  select * into rowtype from test where cj=90;

  dbms_output.put_line(rowtype.name||' '||rowtype.cj);

  exception

  when no_data_found then

     dbms_output.put_line('no value');

  when too_many_rows then

      dbms_output.put_line('too many rows');

  end;

执行结果:

qbnajj 90

EG3: 自定义RECORD 数据类型

declare

  type r_type is record(name test.name%type ,cj test.cj%type);

  re_type r_type;

  begin

  select name,cj into re_type from test where cj=90;

  dbms_output.put_line(re_type.name||' '||re_type.cj);

  exception

  when no_data_found then

     dbms_output.put_line('no value');

  when too_many_rows then

      dbms_output.put_line('too many rows');

  end;

/

执行结果:

qbnajj 90

2) 显示游标

声明语法:

DECLARE

CURSOR cur_name( 参数名 数据类型) is select_satatements ;-- 游标名后-- 可带参数

BEGIN

OPEN cur_name;

FETCH cur_name into [ 变量或者其他数据类型];

-- 可用循环语句迭代输出游标所指向的结果集.

CLOSE cur_name;

END;

显示游标特性:

- 显示游标由用户定义, 并由用户来管理游标, 可返回多行记录.

- 通常使用显示游标需要遵守以下规则

   声明游标 -> 打开游标 -> 读取数据 -> 关闭游标

    但由于FOR IN LOOP 语句包含OPEN,FETCH,CLOSE 操作所以不在此例.

  - 查看游标属性时, 使用cur_name%.

EG:PL/SQL 演示

declare

  no test.id%type;

  ename test.name%type;

  cjs test.cj%type;

  cursor test_cur is

  select id,name from test where cj>=cjs;-- 可通过PL/SQL 绑定变量输-- 入值(&cjs)

  begin

  cjs:=50;

  for t_cur in test_cur

  loop

  no:=t_cur.id;

  ename:=t_cur.name;

  dbms_output.put_line(no||' '||ename);

  end loop;

  end;

执行结果:

chenjunhua 80

qbnajj 90

cjh 52

EG2: 利用带参数的存储过程演示

create or replace procedure test_proce(cjs in test.cj%type)

  as

  no test.id%type;

  ename test.name%type;

  cursor test_cur is

  select id,name from test where cj>=cjs;

  begin

  open test_cur;

  fetch test_cur into no,ename;

  while test_cur%found

  loop

  dbms_output.put_line(no||' '||ename);

  fetch test_cur into no,ename;-- 将游标指向下条记录, 否则为死循环.

  end loop;

  close test_cur;

  end test_proce;

  exec test_proce(50);

执行结果:

chenjunhua 80

qbnajj 90

cjh 52

EG3: 带参数的游标与上面的类似

2. 动态游标(REF CURSOR)

首先 , 游标变量和游标是两个不同的概念 . 与游标相似 , 游标变量是指向多行查游标询的结果集的当前行 . 游标是静态的 , 游标变量是动态的 ,. 同时游标变量并不参与与特定的查询绑定 , 所以可以为任何兼容的查询打开游标变量 , 从而提高灵活性 . 而且 , 还可以将新的值赋予游标变量 , 将它作为参数传递给本地和存储过程 . 游标变量针对每个 PL/SQL 用户都是可用的 , 可以在客户端完全使用游标变量 .ORACLE 服务器同样包含 PL/SQL 引擎 , 可以将游标变量在应用和服务器之间进行传递 .

1. 游标变量 : 声明游标实际上是创建一个指针 , 指针具有数据类型 REF X.REF 是 REFERENCE ,X 是表示类对象 . 因此 , 游标变量具有数据类型 REF  CURSOR.

注 : 游标总是指向相同的查询工作区 , 游标变量能够指向不同的工作区 , 因此游标和游标变量不能互操作 .

3. 定义 REF CURSOR 类型 , 创建游标变量有两个步骤 :

  1) 定义 REF CURSOR 类型

  语法格式 :

  TYPE ref_type_name

  IS

  REF CURSOR [RETURN return_type]

  说明 :

  ref_type_name 是游标变量中使用的类型 ;return_type 必须是一个记录 (record) 或者数据库表中的一行 .(rowtype)

  下面定义 一个 REF CURSOR 类型游标

  DELARE

   TYPE xs_cur

  IS

  REF CURSOR RETURN xs%ROWTYPE;

  注意 :

  REF CURSOR 类型既可以是强类型 , 也可以是弱类型 , 区别是强类型有返回类型 , 弱类型没有 . 如下所示

   DECLARE

            TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;-- 强类型

            TYPE mycur IS REF CURSOR;-- 弱类型

2) 声明这种类型的游标变量 : 一旦定义了 REF CURSOR 类型就可以在 PL/SQL 块或子程序中声明这个游标变量 . 如 :

  DECARE

   TYPE xs_cur REF CURSOR RETURN xs%ROWTYPE;

   xscur xs_cur;

   当然 , 在 RETURN 子句中也可以定义自定义的 RECORD 类型 , 如 :

   DECLARE

           TYPE kc_cj IS RECORD

           (

            kch number (4),

            kcm number(10),

            cj  number(4,2)

);

TYPE kc_cjcur IS REF CURSOR RETURN kc_cj;

此外 , 也可以声明游标变量作为函数和过程的参数 . 例如 :

DECLARE

       TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;

       PROCEDURE open_xs (xscur IN OUT xs_cur)IS

       ......................

3. 控制游标变量

  使用游标变量时 , 要遵循以下步骤 :OPEN-FETCH-CLOSE

  OPEN 语句与多行查询的游标变量相关联 , 它执行查询 , 标志结果集

  语法格式 :

  OPEN {cursor_variable|:host_cursor_variable }FOR

  {

select_statement|dynamic_string[USING bind_argument[,......]]

}

  如 :

  IF NOT xscur%ISOPEN THEN

            OPEN xscur FOR SELECT * FROM xs;

  END IF ;

游标变量同样可以使用游标属性 :%FOUND,%ISOPEN,%ROWTYPE

在使用过程中 , 其他的 OPEN 语句可以为不同的查询打开相同的游标变量 . 因此 , 在重新打开之前 , 不要关闭游标变量 . 可以打开游标 , 并作为参数传递给存储过程 . 如 :

CREATE PACKAGE xs_data AS

...

TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;

RROCEDURE open_xs(xscur IN OUT xs_cur);

END xs_data;

 

CREATE PACKAGE BODY xs_data AS

...

PROCEDURE open_xs(xscur IN OUT xs_cur)

AS

BEGIN

      OPEN xscur FOR SELECT * FROM xs;

END open_xs;

END xs_data;

当声明一个游标变量作为打开游标变量子程序的参数时 , 必须定义 IN OUT 模式 . 也就是说 , 子程序可以将一个打开的游标变量传递给调用者 .