本文共 4389 字,大约阅读时间需要 14 分钟。
在DMSQL 程序中使用 SELECT…INTO 语句将查询结果存放到变量中进行处理的方法只能返回一条记录,否则就会产生 TOO_MANY_ROWS 错误。为了解决这个问题,DMSQL 程序引入了游标,允许程序对多行数据进行逐条处理。
游标分为静态游标、动态游标以及引用游标,相当于DMSQL程序中的一个指针,可以通过游标指定的位置,来对于结果集中的数据进行操作。静态游标可以进行DML操作或select … into等操作;动态游标只能指定查询语句进行使用。 DMSQL程序中的每个游标都有%FOUND、%NOTFOUND、%ISOPEN 和%ROWCOUNT 四个属性,这四个属性的意义如下: %FOUND:语句是否修改或查询到了记录,是返回 TRUE,否则返回 FALSE; %NOTFOUND:语句是否未能成功修改或查询到记录,是返回 TRUE,否则返回 FALSE; %ISOPEN:游标是否打开。是返回 TRUE,否返回 FALSE; %ROWCOUNT:DML 语句执行影响的行数,或 SELECT…INTO 语句返回的行数。静态游标分为隐式游标和显式游标,静态游标是只读游标,在编译时就能确定静态游标的作用,如UPDATE、DELETE、INSERT等,在使用静态游标的过程中,这个游标的作用不会发生变化,只能执行编译时所定义的SQL操作。
1.创建测试表 创建表person_test,插入几条数据用于游标的使用:create table person_test( id int primary key, name varchar(20) not null, sex varchar(10) check (sex in ('男','女')), birth date);insert into person_test values(1,'Tom','男','1999-01-01');insert into person_test values(2,'Mike','男','1998-01-01');insert into person_test values(3,'Mary','女','1998-10-10');insert into person_test values(4,'John','男','1997-01-01');insert into person_test values(5,'Kiko','女','1997-10-10');
2.隐式游标
隐式游标的名称为“SQL”,隐式游标无需用户进行定义,每当用户在 DMSQL 程序中执行一个 DML 语句或者 SELECT …INTO 语句时,DMSQL 程序都会自动声明一个隐式游标并管理这个游标。例如,将表person_test中id为2的姓名改为’张三’:begin update person_test set name='张三' where id = 2; if sql%notfound then print '此人不存在'; else print '已修改'; end if;end;
执行结果如图:
3.显式游标 显式游标指向一个查询语句执行后的结果集区域。当需要处理返回多条记录的查询时,应显式地定义游标以处理结果集的每一行。 使用显式游标一般包括四个步骤:例如,通过显示游标进行person_test表中数据的查询:
declare v_name varchar(20); v_sex varchar(10); --定义显示游标 cursor v_test is select name,sex from person_test;begin --打开游标 open v_test; loop --通过fetch拨动游标 fetch v_test into v_name,v_sex; --查询到结果集结尾后退出 exit when v_test%notfound; print '姓名:'||v_name||' '||'性别:'||v_sex; end loop; --关闭游标 close v_test;end;
执行结果如图:
上面的方法是在loop循环中使用fetch语句将每一条数据记录赋给变量进行处理,除此之外,还可以使用 FETCH…BULK COLLECTINTO 可以将查询结果批量地、一次性地赋给集合变量,例如:declare --定义变量类型 type v_test is record (v_name varchar(20),v_sex varchar(10)); type v_type is table of v_test index by int; v1 v_type; --定义游标 c1 cursor is select name,sex from person_test;begin --打开游标 open c1; --通过游标将结果集一次性赋给集合变量 fetch c1 bulk collect into v1; --关闭游标 close c1; --通过for循环将集合变量中的数据逐条输出 for i in 1..v1.count loop print '姓名:'||v1(i).v_name||' '||'性别:'||v1(i).v_sex; end loop;end;
执行结果如图:
动态游标在声明部分只是先声明一个游标类型的变量,并不指定其关
联的查询语句,在执行部分打开游标时才指定查询语句,例如:declare v_name varchar(20); v_sex varchar(10); v_birth date; --定义游标 v_test cursor;begin --打开动态游标,指定查询语句 open v_test for select name,sex,birth from person_test; loop fetch v_test into v_name,v_sex,v_birth; --查询完结果集后退出 exit when v_test%notfound; print '姓名:'||v_name||' '||'性别:'||v_sex||' '||'出生日期:'||v_birth; --查询结果记录超过4条后退出 exit when v_test%rowcount = 4; end loop; --关闭游标 close v_test;end;
执行结果如图:
在动态游标关联的查询语句中还可以带有参数,参数以“?”指定,多个参数之间使用"and"或者"or"连接,同时在打开游标语句中使用 USING 子句指定参数,且参数的个数和类型与语句中的“?”必须一一匹配,例如:declare v_name varchar(20); v_sex varchar(10); v_birth date; --定义游标 v_test cursor;begin --打开动态游标,指定查询语句以及查询条件 open v_test for 'select name,sex,birth from person_test where sex=?' using '女'; loop fetch v_test into v_name,v_sex,v_birth; --查询完结果集后退出 exit when v_test%notfound; print '姓名:'||v_name||' '||'性别:'||v_sex||' '||'出生日期:'||v_birth; end loop; --关闭游标 close v_test;end;
执行结果如图:
游标变量不是真正的游标对象,而是指向游标对象的一个指针,因此是一种引用类型,也可以称为引用游标。
引用游标有以下几个特点:例如使用引用游标在子程序中传递结果集:
declare --定义游标类型 type v_type is ref cursor return person_test%rowtype; --定义游标 v1 v_type; --存储过程以传入自定义类型的游标为参数 procedure proce_test(v_pro in v_type) is person person_test%rowtype; begin --loop循环输出结果集数据 loop fetch v_pro into person; exit when v_pro%notfound; print '姓名:'||person.name||' 出生日期:'||person.birth; end loop; end;begin --打开游标 open v1 for select * from person_test; --使用存储过程 proce_test(v1); --关闭游标 close v1;end;
执行结果如图:
转载地址:http://flmen.baihongyu.com/