最近对oracle 中的游标有了新的认识所以写出来给大家分享分享。
在通过select 语句查询时，返回的结果通常是多行记录组成的集合。这对于程序设计语言而言，并不能够处理以集合形式返回的数据，为此，sql提供了游标机制。游标充当指针的作用，使应用程序设计的语言一次只能处理查询结果的一行。在oracle中可以使用显式和隐式两种游标。
同时对于PL/SQL程序中所发出的dml 和 select 语句，oracle 都会自动的声明“隐式游标”。为了处理select 语句返回一组记录，需要在PL/SQL 程序中声明和处理“显式游标”。
隐式游标
在执行sql语句时，oracle服务器会自动的创建一个隐式的游标。这个游标是内存中处理该语句的工作区域，其中存储了执行sql语句的结果。通过游标的属性可知道sql的执行结果，以及游标的状态信息。
游标的主要属性：
%Found boolean 布尔型，如果sql影响了一行，则为true 否则为false;
%NotFound boolean 布尔型 ，与Found相反;
%IsOpen boolean 布尔型，当游标已达开始返回true 否则返回false;
%RowCount 数字型属性返回sql影响的行数。
注：当使用隐式游标的属性时，属性前要加上sql，因为oracle在创建隐式游标时，默认的游标名为sql。
Boolean 布尔类型的值不能用dnms_output.putline()输出
ü 隐式游标的使用
SQL> begin
2 update emp
3 set sal=800
4 where empno=1;
5 if sql%notfound then
6 dbms_output.put_line('未更新记录');
7 else
8 dbms_output.put_line('更新'|| sql%rowcount||'条');
9 end if;
10 end;/
注：游标的属性信息总是反应最新的sql 语句处理结果。
SQL> declare
2 empr emp%rowtype;
3
4 begin
5 update emp
6 set sal=800
7 where empno=1;
8 if sql%notfound then
9 dbms_output.put_line('未更新记录');
10 else
11 dbms_output.put_line('更新'|| sql%rowcount||'条');
12 end if;
13 select * into empr from emp where empno=1;
14 dbms_output.put_line('更新'|| sql%rowcount||'条');
15 end
16 ;
17 /
更新1条
更新1条
PL/SQL procedure successfully completed
SQL>
ü 另一种隐式游标cursor(光标)
Cursor for loop
begin
for emps in (select empno,ename,job,hiredate,sal,comm,deptno from emp)
loop dbms_output.put_line(emps.empno||emps.ename||emps.job||emps.hiredate||emps.sal||emps.comm);
end loop;
end ;
使用 cursor for loop 遍历查询的结果集
显式游标
显式游标是在PL/SQL程序中使用包含SELECT语句来声明的游标。如果需要处理从数据库中检索的一组记录，则可以使用显式游标。使用先是游标处理数据需要四个PL/SQL步骤：
ü 声明游标
声明游标就是通过定义游标的名称、游标特征来声明游标，以及打开游标后就可用来调用查询语句。使用游标之前必须先声明游标。声明游标的语法如下：
Cursor 游标名字[(参数)[参数]…] [return 返回的类型] is 查询的语句
Declare
Cursor empc (empno_p number default 1) is
Select * from emp where empno=empno_p;
注： number 不能有长度 否则报错
ü 打开游标
打开游标就是执行声明游标时所指定的查询语句。游标必须声明后才能打开。打开游标也就是调用游标中的select语句。
Open 游标名称；
例如打开上面的的游标
Open empc;
ü 检索数据
检索数据就是从检索到的结果集中获取数据保存到变量中，以便在程序中进行处理。检索数据就是使用fetch语句找出结果集的单行病从中提取单个值传给主变量。
Fetch的语法：
Fetch 游标名字 into 主变量名称；
游标中包含一个指针，他将自动记录由fetch返回的下一行，最初的它设置为从查询的第一行。因此第一次执行fetch 语句时，他将检索第一航中的数据保存到变量中。在随后的每执行一个fetch 语句时，将指针移动到结果集的下一行。可以再循环中用fetch语句，这样每一次循环都会从表中读取一行数据，然后进行相同逻辑的处理。如果游标中没有剩余记录时，那么属性%found返回false，循环也就随之结束。
ü 关闭游标
关闭游标就是不能再从查询结果中检索数据。
Close 游标名称;
例如关闭上面的游标
Close empc;
ü 完整案例
declare
cursor empc (empnop number default 1)
is select * from emp where empno=empnop;//声明现实的游标
type empt is record(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type );记录类型
empr empt;声明记录类型
begin
open empc(2);
loop
fetch empc into empr; 游标检索数据
exit when empc%notfound;
end loop;
dbms_output.put_line(empr.ename||empr.hiredate);
close empc;
end ;
注：使用select * from table 表时 其数据库中的表的结果的顺序和声明记录类型声明的顺序要相同 否则会报出如下错误
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
数据表中列的顺序
SQL> declare
2 cursor empc (empnop number default 1)
3 is select * from emp where empno=empnop;
4 type empt is record(
5 empno emp.empno%type,
6 ename emp.ename%type,
7 job emp.job%type,
8 hiredate emp.hiredate%type,
9 mgr emp.mgr%type,
10 sal emp.sal%type,
11 comm emp.comm%type,
12 deptno emp.deptno%type );
13 empr empt;
14 begin
15 open empc(2);
16 loop
17 fetch empc into empr;
18 exit when empc%notfound;
19 end loop;
20 dbms_output.put_line(empr.ename||empr.hiredate);
21
22 close empc;
23 end ;
24 /
declare
cursor empc (empnop number default 1)
is select * from emp where empno=empnop;
type empt is record(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
hiredate emp.hiredate%type,
mgr emp.mgr%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type );
empr empt;
begin
open empc(2);
loop
fetch empc into empr;
exit when empc%notfound;
end loop;
dbms_output.put_line(empr.ename||empr.hiredate);
close empc;
end ;
ORA-06550: 第 18 行, 第 17 列:
PLS-00386: 发现 FETCH 游标和 INTO 变量之间的 'EMPR' 的类型不匹配
ORA-06550: 第 18 行, 第 1 列:
PL/SQL: SQL Statement ignored
最新评论