Oracle:游标补充
显示游标
显式游标在使用时，应该遵循声明、打开、访问、关闭的步骤。
- 声明游标
declare cursor cu_emp is
select ename from emp;
declare cursor cu_emp_ename_sal is
select ename,sal from emp;
ename varchar2(20);
sal number(7,2);
- 声明带参数的游标
declare cursor cu_emp(deptno in number,maxsal in number) is
select * from emp
where deptno=deptno and sal>=maxsal;
- 使用变量获取游标信息
set serverout on;
declare cursor cu_emp_empno_ename is
select empno,ename from emp;
emp_no emp.empno%type;
ename_name emp.ename%type;
begin
open cu_emp_empno_ename;
fetch cu_emp_empno_ename into emp_no,ename_name;
while cu_emp_empno_ename %found
LOOP
dbms_output.put_line(emp_no||':'||ename_name);
fetch cu_emp_empno_ename into emp_no,ename_name;
end loop;
close cu_emp_empno_ename;
end;
/
- 利用行类型获取游标信息
set serverout on;
declare
cursor cu_emp is select * from emp;
employee emp%rowtype;
begin
open cu_emp;
fetch cu_emp into employee;
while cu_emp%found loop
dbms_output.put_line(employee.empno||':'||employee.ename);
fetch cu_emp into employee;
end loop;
close cu_emp;
end;
/
- 案例：查询员工信息
declare
cursor cu_emp is select * from emp;
employee emp%rowtype;
begin
open cu_emp;
loop
fetch cu_emp into employee;
exit when cu_emp%notfound;
dbms_output.put_line(employee.empno||':'||employee.ename);
end loop;
close cu_emp;
end;
/
- 带参数的游标
declare
cursor cu_emp(deptno in number,maxsal in number) is
select * from emp
where deptno=deptno and sal>=maxsal;
employee emp%rowtype;
begin
--注意光标的打开方式
open cu_emp(10,3000);
loop
fetch cu_emp into employee;
exit when cu_emp%notfound;
dbms_output.put_line(employee.empno||':'||employee.ename||','||employee.sal);
end loop;
close cu_emp;
end;
/
隐式游标
显示游标通常使用declare命令来声明游标；而隐式游标则无须declare命令即可直接使用；隐士游标不能直接被用户控制和使用——即不能执行打开(open),获取游标数据(fetch),关闭(close)等。
隐式游标有两种：
使用Oracle预定义的名为SQL的隐式游标；
使用cursor for loop进行循环的隐式游标。
sql隐式游标
Oracle为每一个PL/SQL的会话都定义了一个名为sql的游标变量。
可以在PL/SQL developer中直接调用该变量。
- 直接调用
begin
--%rowcount游标的记录数
if sql%rowcount>0 then
dbms_output.put_line('sql游标变量的rowcount属性大于0');
end if;
end;
/
- 自动更新游标变量
begin
update emp set deptno=deptno+10-10;
dbms_output.put_line('共更新了'||sql%rowcount||'条记录');
end;
/
sql变量是被Oracle自动声明的，但是并不能被用户控制。sql游标并不能使用
fetch命令进行显示操作。sql隐式变量只能用于更新、删除等操作之后的属性信息获取。
cursor for 游标
利用该游标，用户可以像使用普通循环语句一样来循环处理select语句所获得
的每一条记录。
begin
for employee in (select * from emp) loop
dbms_output.put_line(employee.empno||':'||employee.ename);
end loop;
end;
/
利用for循环可以处理select命令获得的结果集；利用sql隐式游标可以处理update、delete等操作的结果，二者相互配合可以实现与显式游标相同的所有功能。
结论
隐式游标和显式游标都可以实现对结果集的操作，但是，相比之下，隐式游标不需要用户控制游标的声明、打开、获取和关闭，因此用户可以利用更少的代码实现同样的功能。而且，隐式游标的执行速度更快，在对游标的显式控制要求不高时，应尽量选择隐式游标
游标的4个常用属性
| 标记 | 描述 |
|---|---|
| found | 用于标识当前游标从结果中获取记录时，是否成功捕获了记录； |
| not found | 与上面相反，当不能成功获取记录时，将返回true,否则返回false; |
| rowcount | 用于返回当前记录已经获得了多少条记录； |
| isopen | 判断游标是否打开 |
注：前三个属性，均被游标的fetch动作更新；
案例: 四种属性监测：
declare
cursor cu_emp is select * from emp;
employee emp%rowtype;
begin
if cu_emp%isopen then
dbms_output.put_line('声明游标后，游标已经打开');
else
dbms_output.put_line('声明变量后，游标尚未打开');
end if;
open cu_emp;
if cu_emp%isopen then
dbms_output.put_line('执行OPen命令后，游标已经打开');
else
dbms_output.put_line('执行open命令后，游标尚未打开');
end if;
fetch cu_emp into employee;
dbms_output.put_line('第一次执行Fetch命令后，游标的rowcount属性值是：'|| cu_emp%rowcount);
if cu_emp%found then
dbms_output.put_line('first 执行OPen后，游标的Found属性值为：True');
else
dbms_output.put_line('first 执行OPen后，游标的notFound属性值为：True');
end if;
dbms_output.put_line('--------------------------------------');
LOOP
if cu_emp%found then
dbms_output.put_line('循环执行...,游标的Rowcount属性值为：'|| cu_emp%rowcount);
fetch cu_emp into employee;
else
--dbms_output.put_line('游标的notFound属性值为：'|| cu_emp%found);
dbms_output.put_line('循环完毕...,游标的Rowcount属性值为：'||cu_emp%rowcount);
exit;
end if;
end loop;
dbms_output.put_line('-----------------');
close cu_emp;
if cu_emp%isopen then
dbms_output.put_line('执行close命令后，游标仍然打开');
else
dbms_output.put_line('执行close命令后，游标已经关闭');
end if;
end;
/
结果：
声明变量后，游标尚未打开
执行OPen命令后，游标已经打开
第一次执行Fetch命令后，游标的rowcount属性值是：1
first 执行OPen后，游标的Found属性值为：True
--------------------------------------
循环执行...,游标的Rowcount属性值为：1
循环执行...,游标的Rowcount属性值为：2
循环执行...,游标的Rowcount属性值为：3
循环执行...,游标的Rowcount属性值为：4
循环执行...,游标的Rowcount属性值为：5
循环执行...,游标的Rowcount属性值为：6
循环执行...,游标的Rowcount属性值为：7
循环执行...,游标的Rowcount属性值为：8
循环执行...,游标的Rowcount属性值为：9
循环执行...,游标的Rowcount属性值为：10
循环执行...,游标的Rowcount属性值为：11
循环执行...,游标的Rowcount属性值为：12
循环执行...,游标的Rowcount属性值为：13
循环执行...,游标的Rowcount属性值为：14
循环完毕...,游标的Rowcount属性值为：14
-----------------
执行close命令后，游标已经关闭
PL/SQL 过程已成功完成。
动态游标
无论显式游标还是隐式游标，都具有一个特点，即游标在打开时，其定义已经确定。在整个程序的运行过程中，游标定义不能进行更改。因此显式游标和隐式游标被称为静态游标。为了增加游标的灵活性，Oracle提供了另外一种游标——动态游标，即其定义在游标声明时没有设定，在打开时，可以进行动态修改
强类型动态游标
强类型动态游标是指当游标声明时，虽未设定其查询定义，但是已经指定了游标的返回类型。游标的返回类型可以是Oracle内置类型，也可以是自定义类型声明一个强类型游标首先自定义一个ref cursor的游标类型，然后利用该自定义类型，声明一个游标变量.
案例：利用强类型动态游标打印员工信息
create or replace procedure printEmployee(maxsal in number)
as
begin
declare
type employee_type is record(
empno number,
ename varchar2(10),
sal number(7,2)
);
type emp_type is ref cursor return employee_type;
--因为emp_type引用到了employee_type，所以，必须首先声明employee_type，然后声明emp_type，二者顺序不可颠倒；
cu_emp emp_type;--游标集类型
employee employee_type;--结果集类型
begin
if maxsal<=3000 then
open cu_emp for select empno,ename,sal from emp where sal<=3000;
else
open cu_emp for select empno,ename,sal from emp where sal<=maxsal;
end if;
fetch cu_emp into employee;
while cu_emp%found loop
dbms_output.put_line(employee.empno||':'||employee.ename||'的薪水是：---->>>'||employee.sal);
fetch cu_emp into employee;
end loop;
close cu_emp;
end;
end;
/
调用
set serverout on;
begin
printEmployee(2000);
end;
/
-- 结果：
7369:SMITH的薪水是：---->>>1856.92
7521:WARD的薪水是：---->>>2415.77
7654:MARTIN的薪水是：---->>>2415.77
7844:TURNER的薪水是：---->>>2781.79
7876:ADAMS的薪水是：---->>>2196.15
7900:JAMES的薪水是：---->>>1976.54
7934:MILLER的薪水是：---->>>2488.97
弱类型动态游标
众所周知，编程语言有强类型和弱类型之分，例如，VB、JavaScript为弱类型，在JavaScript中使用var关键字即可声明一个变量，该变量既可以存储字符串也可以存储数字；而Java、C等属于强类型语句，变量类型在声明时确定，而且一旦确定将不能改变。弱类型动态游标的概念与此类似，在声明游标时不使用return关键字指定游标的返回类型，那么在以后的程序中，可以对其使用不同的返回类型。
案例：
create or replace procedure printStudentsByFlag(in_flag in varchar2) as
begin
declare
type name_type is record(
id number,
name varchar2(20)
);
type age_type is record(
id number,
age number
);
type students_type is ref cursor;
-- 声明一个name_type类型的、名为name的变量
name name_type;
-- 声明一个age_type类型的、名为age的变量
age age_type;
-- 声明一个名为students、类型为students_type的游标变量
students students_type;
begin
if upper(in_flag)='NAME' then
-- 在打开游标时，将游标的定义设定为获取student_id和student_name的值
open students for
select student_id, student_name from students;
fetch students into name;
while students%found loop
dbms_output.put_line(name.id || '号学生的姓名是：' || name.name);
fetch students into name;
end loop;
elsif upper(in_flag)='AGE' then
open students for
select student_id, student_age from students;
fetch students into age;
while students%found loop
dbms_output.put_line(age.id || '号学生的年龄是：' || age.age);
fetch students into age;
end loop;
end if;
if students%isopen then
close students;
end if;
end;
end printStudentsByFlag;
通过以上范例可知，强类型动态游标在使用时，必须声明其类型，在以后的使用过程中，虽然游标的定义可以修改，但是返回值类型是一定的。而弱类型则无须声明返回值类型，但在使用过程中，必须保证每次用于获取记录的类型都能够正确接收来自游标的数据，因此，也存在着一定的风险。应尽量避免使用弱类型游标
常见问题
1.什么是静态游标？静态游标主要有哪几类？
答：静态游标是指游标声明时，已经指定了游标的定义，并且不会进行更改。静态游标主要分为显式游标和隐式游标两类，隐式游标又有sql隐式游标和cursor for游标两种。
2.什么是动态游标？动态游标又有哪几类？
答：动态游标是指游标声明时，并未指定游标的定义，当实际使用时，才指定游标的定义。因此，可以灵活地根据不同的条件，来获得不同的结果集合。动态游标分为强类型动态游标和弱类型动态游标两类。
3.强类型动态游标和弱类型动态游标的区别是什么？
答：强类型动态游标在游标声明时，虽然未指定游标的定义，但是指定了游标返回的结果集合的记录类型；而弱类型动态游标在声明时则不指定游标返回的记录类型。
