Oracle系列之游标

涉及到表的处理请参看原表结构与数据  Oracle建表插数据等等

游标：

1、目的
解决“ select * ”返回空、多行记录问题
但凡select，就可能多行结果集，也就需要用游标
2、原理
多行记录放内存中，游标指向第一条
比如，培训：
书桌 —— 库 —— 一大堆书
书包 —— 内存 —— 三本书
上课 —— 游标 —— 一本一本取
3、特点
快
一条一条取，逐行处理

set serveroutput on;--开启显示内容

cursor for循环，显示所有的雇员号

declare
cursor c1 is
select pk_Employee_ID from tb_Employee;
begin
for i in c1 loop
dbms_output.put_line(i.pk_Employee_ID);
end loop;
end;
/

等效于下面这段

declare
begin
for i in (select pk_Employee_ID from tb_Employee) loop
dbms_output.put_line(i.pk_Employee_ID);
end loop;
end;
/

open cursor，显示所有的雇员号

declare
v_empno tb_Employee.pk_Employee_ID%type;
cursor c1 is
select pk_Employee_ID from tb_Employee;
begin
open c1;
loop
fetch c1 into v_empno; 
exit when c1%notfound;
dbms_output.put_line(v_empno);
end loop;
close c1;
end;
/

等效于下面这段

declare
v_empno tb_Employee.pk_Employee_ID%type;
cursor c1 is
select pk_Employee_ID from tb_Employee;
begin
open c1;
while c1%isopen loop
fetch c1 into v_empno; 
if c1%notfound then
close c1;
end if;
dbms_output.put_line(v_empno);
end loop;
end;
/

简单的显示游标，根据部门号得出部门中雇员的信息

declare
cursor cur is
select *from tb_Employee where deptno = 10;
v_emp cur%rowtype;
begin
if not cur%isopen then
open cur;
end if;
loop
fetch cur into v_emp;
exit when cur%notfound;
dbms_output.put_line('雇员名是：'||v_emp.ename||'工资是:'||v_emp.sal);
end loop;
close cur;
exception
when others then 
if cur%isopen then 
close cur;
end if;
end;
/

显示游标及游标for循环，查看对应部门的员工号

declare
    cursor emp_cursor is 
    select pk_Employee_ID from tb_Employee
    where deptno = 30;
begin
    for emp_record in emp_cursor
        loop
            dbms_output.put_line(emp_record.pk_Employee_ID);
        end loop;
end;
/

隐式游标，查看返回的行数

declare
empno tb_Employee.pk_Employee_ID%type;
begin
select pk_Employee_ID into empno -- 对于DML之select into
from tb_Employee
where pk_Employee_ID = 7788;
if sql%found then
dbms_output.put_line('有一行记录'); -- 用sql%found、sql%notfound判断是否返回一行记录
end if;
exception
when no_data_found then
dbms_output.put_line('查询返回空行'); -- 用no_data_found判断是否返回空行记录
when too_many_rows then
dbms_output.put_line('查询返回多行'); -- 用too_many_rows判断是否返回多行记录
end;
/

declare
empno tb_Employee.pk_Employee_ID%type;
begin
select pk_Employee_ID into empno
from tb_Employee
where pk_Employee_ID = 7788;
if sql%rowcount > 0 then
dbms_output.put_line('从表中选择了'||sql%rowcount||'行');
else
dbms_output.put_line('从表中未选择行');
end if;
end;
/

--常见异常
--CURSOR_ALREADY_OPEN
--DUP_VAL_ON_INDEX
--INVALID_CURSOR
--INVALID_NUMBER，当输入的数据有误时，例如类型有错，或触发该例外
--NO_DATA_FOUND
--TOO_MANY_ROWS，如果返回超过了一行，则会触发该错误
--ZERO_DIVIDE，例如2/0语句时，则会触发该错误
--VALUE_ERROR，执行赋值操作时，如果长度不足以容纳实际数据，则会触发该例外
--其他预定义例外
--LOGIN_DENIED，登录错误，如果账号密码不对应会出现这个错误
--NOT_LOGGED_ON,如果用户没有登录就执行dml操作，就会触发该例外
--STORAGE_ERROR，吐过超出了内存空间或是内存被损坏，就会触发该例外
--TIMEOUT_ON_RESOURCE，如果Oracle在等待资源时，出现了超时就会触发该例外
--非预定义例外
--自定义例外

动态游标，输入部门号，显示该部门所有员工姓名和他的工资

declare 
type fj_emp_cursor is ref cursor;--定义游标类型fj_emp_cursor
test_cursor fj_emp_cursor;--定义游标变量
v_ename tb_Employee.ename%type;--定义变量
v_sal tb_Employee.sal%type;
begin
open test_cursor for select ename,sal from tb_Employee where deptno=&no;--执行，把test_cursor和一个select结合
loop--循环取出
fetch test_cursor into v_ename,v_sal;
dbms_output.put_line('雇员名是：'||v_ename||',工资是：'||v_sal);
--判断是否test_cursor是否为空
exit when test_cursor%notfound;
end loop;
end;
/

根据输入选项的不同，分别显示员工表、部门表的所有信息

declare
type cur_type is ref cursor; -- 弱类型游标
cur cur_type;
v_emp tb_Employee%rowtype;
v_dept tb_Department%rowtype;
selection varchar2(1) := upper('&选项');
begin
if selection = 'E' then
open cur for
'select * from tb_Employee';
loop
fetch cur into v_emp;
exit when cur%notfound;
dbms_output.put_line(v_emp.ename||':'||v_emp.sal);
end loop;
close cur;
elsif selection = 'newtype' then
open cur for
'select * from tb_Department';
loop
fetch cur into v_dept;
exit when cur%notfound;
dbms_output.put_line(v_dept.pk_Department_ID||':'||v_dept.dname);
end loop;
close cur;
else
null;
end if;
end;
/

在上面的基础上，如果某个雇员的工资低于3000元，就增加1000元

declare
cursor aaa is
select sal from tb_Employee
where sal < 3000 for update;
cursor bbb is
select * from tb_Employee;
begin
for v_sal in aaa
loop
update tb_Employee
set sal = sal + 1000
where current of aaa;
end loop;
for v_emp in bbb
loop
dbms_output.put_line(v_emp.ename||':'||v_emp.sal);
end loop;
end;
/

用强类型游标显示雇员姓名和工资

declare
type record_type is record(name varchar2(10), sal number); -- 记录类型
type cur_type is ref cursor return record_type; -- 强类型游标，将来必须返回record_type类型的结果集
emp_record record_type; -- 记录类型变量
emp_cur cur_type; -- 强类型游标变量
begin
open emp_cur for
select ename, sal from tb_Employee; -- 动态SQL两边的单引号可以省略，必须返回record_type类型的结果集
loop
fetch emp_cur into emp_record; -- 把record_type类型的结果集向record_type类型的变量中填充
exit when emp_cur%notfound;
dbms_output.put_line(emp_record.name||':'||emp_record.sal);
end loop;
close emp_cur;
end;
/