plsql编程中游标的使用

 

游标（Cursor）：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。

 

oracle中显示使用游标一般要包含以下5个步骤：

• 声明一些变量以便存储从游标返回的值。
• 声明游标，并指定查询。
• 打开游标。
• 遍历游标并取得数据。
• 关闭游标

 

表结构及数据如下：

-- Create table
create table EXCHANGETIME
(
  ID          NUMBER(18) default 0 not null,
  SYSTEM_TYPE CHAR(1) default ' ' not null,
  TIME_KIND   NUMBER(10) default 0 not null,
  TIME_NAME   VARCHAR2(16) default ' ' not null,
  BEGIN_TIME  NUMBER(10) default to_number(to_char(sysdate,'hh24miss')) not null,
  END_TIME    NUMBER(10) default to_number(to_char(sysdate,'hh24miss')) not null
)

insert into EXCHANGETIME (ID, SYSTEM_TYPE, TIME_KIND, TIME_NAME, BEGIN_TIME, END_TIME)
values (1, '0', 0, '上午交易时间', 91500, 113000);

insert into EXCHANGETIME (ID, SYSTEM_TYPE, TIME_KIND, TIME_NAME, BEGIN_TIME, END_TIME)
values (2, '0', 1, '下午交易时间', 130000, 150000);

insert into EXCHANGETIME (ID, SYSTEM_TYPE, TIME_KIND, TIME_NAME, BEGIN_TIME, END_TIME)
values (3, '1', 2, '盘后交易时间', 150000, 153000);

 

-- Create table
create table BACKUPINFO
(
  ID         NUMBER(18) default 0 not null,
  INIT_DATE  NUMBER(10) default to_number(to_char(sysdate,'yyyymmdd')) not null,
  TREAT_FLAG VARCHAR2(120) default ' ' not null,
  ERROR_INFO VARCHAR2(4000) default ' ' not null,
  FLAG       CHAR(1) default ' ' not null
)

insert into backupinfo (ID, INIT_DATE, TREAT_FLAG, ERROR_INFO, FLAG)
values (1, 20140923, '1', '343%3r3', '2');

insert into backupinfo (ID, INIT_DATE, TREAT_FLAG, ERROR_INFO, FLAG)
values (2, 19900909, '4', 'fr454', ' ');

insert into backupinfo (ID, INIT_DATE, TREAT_FLAG, ERROR_INFO, FLAG)
values (1, 20140923, '1', '343%3r3', '2');

insert into backupinfo (ID, INIT_DATE, TREAT_FLAG, ERROR_INFO, FLAG)
values (2, 19900909, '4', 'fr454', ' ');

insert into backupinfo (ID, INIT_DATE, TREAT_FLAG, ERROR_INFO, FLAG)
values (1, 20140923, '1', '343%3r3', '2');

insert into backupinfo (ID, INIT_DATE, TREAT_FLAG, ERROR_INFO, FLAG)
values (2, 19900909, '4', 'fr454', ' ');

insert into backupinfo (ID, INIT_DATE, TREAT_FLAG, ERROR_INFO, FLAG)
values (1, 20140923, '1', '343%3r3', '2');

insert into backupinfo (ID, INIT_DATE, TREAT_FLAG, ERROR_INFO, FLAG)
values (2, 19900909, '4', 'fr454', ' ');

 

下面来看几个例子：

1. 完整的示例：

  

declare
  --声明变量（用于存放游标查询出来的值）
  id exchangetime.id%type;
  system_type exchangetime.system_type%type;
  time_kind exchangetime.time_kind%type;
  time_name exchangetime.time_name%type;
  begin_time exchangetime.begin_time%type;
  end_time exchangetime.end_time%type;
  
  --声明游标
  cursor v_eq is select * from exchangetime;
begin
  --打开游标
  open v_eq;
  
  loop
    --从游标中取出每行数据赋给上面定义的变量并打印出来
    fetch v_eq into id, system_type, time_kind, time_name, begin_time, end_time;
    dbms_output.put_line(id || ' ' || system_type|| 'time_kind：' || time_kind || ' time _name：' || time_name || ' ' || begin_time || '-->' || end_time);
    
    exit when v_eq%NOTFOUND;
  end loop;
  
  --关闭游标
  close v_eq;
  
end;
/

输出结果：
1 0time_kind：0 time _name：上午交易时间 91500-->113000
2 0time_kind：1 time _name：下午交易时间 130000-->150000
3 1time_kind：2 time _name：盘后交易时间 150000-->153000
3 1time_kind：2 time _name：盘后交易时间 150000-->153000

 

2. 与for循环联合使用(推荐用法)   

declare
begin
  --mcursor的名字可以随便定义，aa或者bb都行，这样写的好处是不用显示定义游标，打开游标循环取值再关闭游标，很方便。
 for mcursor in (select * from exchangetime) loop
   dbms_output.put_line(mcursor.id || mcursor.time_name);
 end loop;
  
end;
/

结果如下:

  1上午交易时间
  2下午交易时间
  3盘后交易时间

3.使用open ... for ...语句

 1 declare
 2   --定义游标指针并指定返回类型为exchangetime的所有列
 3   type t_cursor is ref cursor  return exchangetime%rowtype;
 4   --定义接收返回值的变量
 5   v_etresult exchangetime%rowtype;
 6   --相当于定义了一个指针变量
 7   v_cursor t_cursor;
 8 begin
 9   --将指针并指向一个游标并打开
10   open v_cursor for select * from exchangetime t where t.id<3;
11   
12   loop
13     -- --指向的游标里面每一行的值赋给接收的变量，并打印出来
14     fetch v_cursor into v_etresult;
15     dbms_output.put_line(v_etresult.time_name);
16     exit when v_cursor%notfound;
17   end loop;
18   --关闭指向的游标
19   close v_cursor;
20   
21   --将指针指向另外一个游标并打开
22    open v_cursor for select * from exchangetime t;
23    
24    loop
25     fetch v_cursor into v_etresult;
26     dbms_output.put_line(v_etresult.time_name|| '--' || v_etresult.system_type);
27     exit when v_cursor%notfound;
28    end loop;
29   
30   
31   --关闭指针指向的游标
32   close v_cursor;
33 end;
34 /

 结果如下：

上午交易时间
下午交易时间
下午交易时间
上午交易时间--0
下午交易时间--0
盘后交易时间--1
盘后交易时间--1

4.无约束游标：前面的游标都有返回类型称为约束游标，约束游标的返回类型必须与游标运行时查询中的列相匹配。无约束游标没有返回类型因此可以运行任何查询。

 

declare
  --定义游标指针
  type t_cursor is ref cursor;
  --定义接收返回值的变量
  v_etresult exchangetime%rowtype;
  v_buresult backupinfo%rowtype;
  --相当于定义了一个指针变量
  v_cursor t_cursor;
begin
  --将指针并指向一个游标并打开
  open v_cursor for select * from exchangetime t where t.id<3;
  
  loop
    --指向的游标里面每一行的值赋给接收的变量，并打印出来
    fetch v_cursor into v_etresult;
    dbms_output.put_line(v_etresult.time_name);
    exit when v_cursor%notfound;
  end loop;
  --关闭指向的游标
  close v_cursor;
  
  --将指针指向另外一个游标并打开
   open v_cursor for select * from backupinfo t;
   
   loop
     --指向的游标里面每一行的值赋给接收的变量，并打印出来
    fetch v_cursor into v_buresult;
    dbms_output.put_line(v_buresult.flag|| '--' || v_buresult.init_date);
    exit when v_cursor%notfound;
   end loop;
  
  
  --关闭指针指向的游标
  close v_cursor;
end;
/

结果如下：
    上午交易时间
    下午交易时间
    下午交易时间
    2--20140923
     --19900909
    2--20140923
      --19900909
    2--20140923
      --19900909
    2--20140923
      --19900909
      --19900909