oracle记录类型和游标结合,Oracle系列之游标

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

游标：

1、目的

解决“ select * ”返回空、多行记录问题

但凡select，就可能多行结果集，也就需要用游标

2、原理

多行记录放内存中，游标指向第一条

比如，培训：

书桌 —— 库 —— 一大堆书

书包 —— 内存 —— 三本书

上课 —— 游标 —— 一本一本取

3、特点

快

一条一条取，逐行处理

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

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

declare

cursor c1 is

select pk_Employee_ID fromtb_Employee;begin

for i inc1 loop

dbms_output.put_line(i.pk_Employee_ID);endloop;end;/

等效于下面这段

declare

begin

for i in (select pk_Employee_ID fromtb_Employee) loop

dbms_output.put_line(i.pk_Employee_ID);endloop;end;/

open cursor，显示所有的雇员号

declarev_empno tb_Employee.pk_Employee_ID%type;cursor c1 is

select pk_Employee_ID fromtb_Employee;begin

openc1;

loopfetch c1 intov_empno;exit when c1%notfound;

dbms_output.put_line(v_empno);endloop;closec1;end;/

等效于下面这段

declarev_empno tb_Employee.pk_Employee_ID%type;cursor c1 is

select pk_Employee_ID fromtb_Employee;begin

openc1;while c1%isopen loopfetch c1 intov_empno;if c1%notfound then

closec1;end if;

dbms_output.put_line(v_empno);endloop;end;/

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

declare

cursor cur is

select *from tb_Employee where deptno = 10;

v_emp cur%rowtype;begin

if not cur%isopen then

opencur;end if;

loopfetch cur intov_emp;exit when cur%notfound;

dbms_output.put_line('雇员名是：'||v_emp.ename||'工资是:'||v_emp.sal);endloop;closecur;

exceptionwhen others then

if cur%isopen then

closecur;end if;end;/

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

declare

cursor emp_cursor is

select pk_Employee_ID fromtb_Employeewhere deptno = 30;begin

for emp_record inemp_cursor

loop

dbms_output.put_line(emp_record.pk_Employee_ID);endloop;end;/

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

declareempno tb_Employee.pk_Employee_ID%type;begin

select pk_Employee_ID into empno --对于DML之select into

fromtb_Employeewhere pk_Employee_ID = 7788;if sql%found thendbms_output.put_line('有一行记录'); --用sql%found、sql%notfound判断是否返回一行记录

end if;

exceptionwhen no_data_found thendbms_output.put_line('查询返回空行'); --用no_data_found判断是否返回空行记录

when too_many_rows thendbms_output.put_line('查询返回多行'); --用too_many_rows判断是否返回多行记录

end;/

declareempno tb_Employee.pk_Employee_ID%type;begin

select pk_Employee_ID intoempnofromtb_Employeewhere pk_Employee_ID = 7788;if sql%rowcount > 0 thendbms_output.put_line('从表中选择了'||sql%rowcount||'行');elsedbms_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在等待资源时，出现了超时就会触发该例外

--非预定义例外

--自定义例外

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

declaretype fj_emp_cursoris 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 intov_ename,v_sal;

dbms_output.put_line('雇员名是：'||v_ename||',工资是：'||v_sal);--判断是否test_cursor是否为空

exit when test_cursor%notfound;endloop;end;/

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

declaretype cur_typeis ref cursor; --弱类型游标

cur cur_type;

v_emp tb_Employee%rowtype;

v_dept tb_Department%rowtype;

selectionvarchar2(1) := upper('&选项');begin

if selection = 'E' then

open cur for

'select * from tb_Employee';

loopfetch cur intov_emp;exit when cur%notfound;

dbms_output.put_line(v_emp.ename||':'||v_emp.sal);endloop;closecur;

elsif selection= 'newtype' then

open cur for

'select * from tb_Department';

loopfetch cur intov_dept;exit when cur%notfound;

dbms_output.put_line(v_dept.pk_Department_ID||':'||v_dept.dname);endloop;closecur;else

null;end if;end;/

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

declare

cursor aaa is

select sal fromtb_Employeewhere sal < 3000 for update;cursor bbb is

select * fromtb_Employee;begin

for v_sal inaaa

loopupdatetb_Employeeset sal = sal + 1000

where current ofaaa;endloop;for v_emp inbbb

loop

dbms_output.put_line(v_emp.ename||':'||v_emp.sal);endloop;end;/

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

declaretype record_typeis 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类型的结果集

loopfetch emp_cur into emp_record; --把record_type类型的结果集向record_type类型的变量中填充

exit when emp_cur%notfound;

dbms_output.put_line(emp_record.name||':'||emp_record.sal);endloop;closeemp_cur;end;/