Oracle中游标的使用
游标可以看成是指向一个查询结果集的指针,通过游标的移动逐行提取每一行的记录 可以让开发者一次访问一行结果集,在每条结果集上作操作。游标的作用如下:
(1)指定结果集中特定行的位置;
(2)基于当前的结果集位置检索一行或连续的几行;
(3)在结果集的当前位置修改行中的数据;
(4)以编程的方式访问数据库。
由于程序语言是面向记录的,一组变量一次只能存放一个变量或者一条记录,无法直接接收数据库中的查询结果集,而引入游标就解决了这个问题。
一、游标的分类
游标分为两种类型:隐式游标和显式游标。
1、隐式游标
DML操作(INSERT、UPDATE、DELETE)和单行查询操作(SELECT…INTO…)会使用隐式游标。隐式游标不需要用户自己声明,它由系统自动定义,名称为sql。
可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。但要注意,通过SQL游标名只能访问前一个DML操作或单行SELECT操作的隐式游标属性。所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。游标的属性有四种:标的属性有四种,如下所示。
(1)SQL%ROWCOUNT:代表DML语句成功执行的数据行数 (整型);
(2)SQL%FOUND:值为TRUE代表插入、删除、更新或单行查询操作成功,否则表示操作失败;
(3)SQL%NOTFOUND:与SQL%FOUND属性返回值相反;
(4)SQL%ISOPEN:DML执行过程中为真,结束后为假。
更新失败的例子(没有部门号为21的雇员):
SQL>
begin
update emp set comm=100
where deptno=21;
if sql%found then
dbms_output.put_line('更新成功!');
else
dbms_output.put_line('更新失败!');
end if;
if sql%isopen then
dbms_output.put_line('成功更新了'||sql%rowcount||'条记录');
end;
16 /
更新失败!
成功更新了0条记录
PL/SQL procedure successfully completed.
更新成功的例子:
SQL>
begin
update emp set comm=100
where deptno=20;
if sql%found then
dbms_output.put_line('更新成功!');
else
dbms_output.put_line('更新失败!');
end if;
dbms_output.put_line('成功更新了'||sql%rowcount||'条记录');
end;
16 /
更新成功!
成功更新了5条记录
PL/SQL procedure successfully completed.
2、显式游标
如果要提取多行数据,就要由程序员定义一个显式游标,并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 游标一旦打开,数据就从数据库中传送到游标变量中,然后应用程序再从游标变量中分解出需要的数据,并进行处理。 下面介绍显示游标的使用方法。
二、显示游标的使用
显示游标的使用分为4个步骤:(1)声明游标;(2)打开游标;(3)提取数据;(4)关闭游标。
1、声明游标
声明游标的语法如下:
CURSOR 游标名[(参数1 数据类型[,参数2 数据类型...])]
IS SELECT语句;
说明:
(1)必须在代码块的DECLEAR部分声明游标;
(2)参数是可选部分,所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数,则必须在打开游标时传递相应的实际参数。
(3)SELECT语句是对表或视图的查询语句,甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句,但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
(4)声明游标时并没有执行Select 语句。
2、打开游标
打开游标的语法如下:
OPEN 游标名[(实际参数1[,实际参数2...])];
说明:
(1)必须在代码块的可执行部分打开游标;
(2)打开游标时,执行Select 语句,SELECT语句的查询结果就被传送到了游标工作区。
(3)打开游标后,游标指向结果集头, 而不是第一条记录。
3、提取数据
打开游标之后,就可以在代码块的可执行部分,将游标工作区中的数据取到变量中。格式如下:
FETCH 游标名 INTO 变量名1[,变量名2...];
或
FETCH 游标名 INTO 记录变量;
说明:
(1)游标打开后有一个指针指向数据区,FETCH语句执行一次返回指针所指的一行数据,要返回多行数据可以使用循环语句来实现。可以通过判断游标的%found或%notfound属性的值来控制循环。
(2)第一种格式中变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
(3)第二种格式一次将一行数据取到记录变量中,需要使用%ROWTYPE事先定义记录变量。
4、关闭游标
CLOSE 游标名;
说明:显式游标打开后,必须显式地关闭。游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用。
三、遍历循环游标
1、使用Loop循环
使用Loop循环遍历游标之前,需要先声明并打开游标,使用完毕需要手动关闭游标。
Loop
Fatch 游标名 InTo ....;
Exit When 游标名%NotFound;
End Loop;
2、使用for循环
循环执行时隐式打开游标,自动获取一条记录,并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。格式如下:
For 变量名 In 游标名 Loop
数据处理语句;
End Loop;
四、游标使用举例
1、利用游标显示10号部门所有雇员的姓名、雇佣日期、工资和奖金
(1)使用Loop循环
SQL>
declare
row_emp emp%rowtype;
cursor cur_emp
is select * from emp
where deptno=10; --定义游标变量
begin
open cur_emp; --打开游标
loop
fetch cur_emp into row_emp;
exit when cur_emp%notfound;
dbms_output.put_line('雇员姓名:'||rpad((row_emp.ename),7,' ')||
',雇佣日期:'||row_emp.hiredate||',工资:'||row_emp.sal||
',奖金:'||nvl(row_emp.comm,0));
end loop;
close cur_emp; --关闭游标
end;
/
雇员姓名:MILLER ,雇佣日期:1982-01-23 00:00:00,工资:1300,奖金:100
雇员姓名:CLARK ,雇佣日期:1981-06-09 00:00:00,工资:2450,奖金:0
雇员姓名:KING ,雇佣日期:1981-11-17 00:00:00,工资:5000,奖金:0
PL/SQL procedure successfully completed.
(2)使用for循环
SQL>
begin
for cur_emp in (select * from emp where deptno=10) loop
dbms_output.put_line('雇员姓名:'||rpad((cur_emp.ename),7,' ')||
',雇佣日期:'||cur_emp.hiredate||',工资:'||cur_emp.sal||
',奖金:'||nvl(cur_emp.comm,0));
end loop;
end;
8 /
雇员姓名:MILLER ,雇佣日期:1982-01-23 00:00:00,工资:1300,奖金:100
雇员姓名:CLARK ,雇佣日期:1981-06-09 00:00:00,工资:2450,奖金:0
雇员姓名:KING ,雇佣日期:1981-11-17 00:00:00,工资:5000,奖金:0
PL/SQL procedure successfully completed.
2、显示每个部门中工资最高的前两名员工的姓名、雇佣日期、工资、奖金
SQL>
declare
emp_count number default 1;
begin
for cur_dept in (select * from dept order by deptno) loop
emp_count:=1;
dbms_output.put_line('部门编号:'||cur_dept.deptno||
',部门名称:'||cur_dept.dname);
for cur_emp in (select ename,hiredate,sal,comm
from emp where deptno=cur_dept.deptno
order by sal desc) loop
dbms_output.put_line('===>雇员姓名:'||
rpad((cur_emp.ename),7,' ')||
',雇佣日期:'||cur_emp.hiredate||
',工资:'||cur_emp.sal||
',奖金:'||nvl(cur_emp.comm,0));
exit when emp_count>=2;
emp_count:=emp_count+1;
end loop;
end loop;
end;
21 /
部门编号:10,部门名称:ACCOUNTING
===>雇员姓名:KING ,雇佣日期:1981-11-17 00:00:00,工资:5000,奖金:0
===>雇员姓名:CLARK ,雇佣日期:1981-06-09 00:00:00,工资:2450,奖金:0
部门编号:20,部门名称:RESEARCH
===>雇员姓名:SCOTT ,雇佣日期:1987-04-19 00:00:00,工资:3000,奖金:100
===>雇员姓名:FORD ,雇佣日期:1981-12-03 00:00:00,工资:3000,奖金:100
部门编号:30,部门名称:SALES
===>雇员姓名:BLAKE ,雇佣日期:1981-05-01 00:00:00,工资:2850,奖金:0
===>雇员姓名:ALLEN ,雇佣日期:1981-02-20 00:00:00,工资:1600,奖金:300
部门编号:40,部门名称:OPERATIONS
===>雇员姓名:TOMMY ,雇佣日期:2020-02-03 10:59:11,工资:8000,奖金:1200
===>雇员姓名:MARK DO,雇佣日期:2020-02-13 10:59:11,工资:3000,奖金:0
PL/SQL procedure successfully completed.
五、带参数的游标
如果游标带有参数,则打开游标时需要指定参数,带参数的游标格式如下:
CURSOR 游标名[(参数1 数据类型[,参数2 数据类型...])]
IS SELECT 语句;
例如:
SQL>
declare
row_emp emp%rowtype;
cursor cur_emp(dept_no number) is
select * from emp where deptno=dept_no;
begin
open cur_emp(10);
loop
fetch cur_emp into row_emp;
exit when cur_emp%notfound;
dbms_output.put_line('雇员编号:'||row_emp.empno||
', 姓名:'||row_emp.ename);
end loop;
close cur_emp;
end;
15 /
雇员编号:7934, 姓名:MILLER
雇员编号:7782, 姓名:CLARK
雇员编号:7839, 姓名:KING
PL/SQL procedure successfully completed.
使用for循环:
SQL>
declare
cursor cur_emp(dept_no number) is
select * from emp where deptno=dept_no;
begin
for r in cur_emp(20) loop
dbms_output.put_line('雇员编号:'||r.empno||
', 姓名:'||r.ename);
end loop;
end;
10 /
雇员编号:7788, 姓名:SCOTT
雇员编号:7876, 姓名:ADAMS
雇员编号:7566, 姓名:JONES
雇员编号:7902, 姓名:FORD
雇员编号:7369, 姓名:SMITH
PL/SQL procedure successfully completed.
改变游标的参数:
SQL>
declare
cursor cur_emp(dept_no number) is
select * from emp where deptno=dept_no;
begin
for r in cur_emp(10) loop
dbms_output.put_line('雇员编号:'||r.empno||
', 姓名:'||r.ename);
end loop;
end;
10 /
雇员编号:7934, 姓名:MILLER
雇员编号:7782, 姓名:CLARK
雇员编号:7839, 姓名:KING
PL/SQL procedure successfully completed.
六、动态游标的用法
对于查询结果为一行的SELECT语句,可以用动态生成查询语句字符串的方法,在程序执行阶段临时地生成并执行。
语法如下:
execute immediate 查询语句字符串 into 变量1[,变量2...];
例如:
SQL>
declare
str varchar2(200);
v_ename varchar2(10);
begin
str:='select ename from emp where empno=7788';
execute immediate str into v_ename;
dbms_output.put_line('name:'||v_ename);
end;
/
name:SCOTT
PL/SQL procedure successfully completed.
使用动态游标可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型,然后声明一个游标变量,游标对应的查询语句可以在程序的执行过程中动态地说明。
1、定义动态游标类型
语法如下:
TYPE 游标类型名 REF CURSOR;
2、声明游标变量
语法如下:
游标变量名 游标类型名;
3、打开动态游标
在代码块的执行部分打开一个动态游标。语法如下:
OPEN 游标变量名 FOR 查询语句字符串;
4、动态游标应用举例
查询姓名以某个字母开头的雇员,并把雇员编号和姓名显示出来:
SQL>
declare
type cur_type is ref cursor; --声明一个动态游标
cur_emp cur_type;
row_emp emp%rowtype;
str varchar2(50);
letter char:='M';
begin
str:= 'select * from emp where ename like '''||letter||'%'''; --字符串中如果包含单引号('),用两个单引号代替
open cur_emp for str; --打开动态游标
loop
fetch cur_emp into row_emp;
exit when cur_emp%notfound;
dbms_output.put_line('雇员编号:'||row_emp.empno||
',姓名:'||row_emp.ename);
end loop;
end;
17 /
雇员编号:7934,姓名:MILLER
雇员编号:7654,姓名:MARTIN
雇员编号:8101,姓名:MARK DOWN
PL/SQL procedure successfully completed.