异常和游标管理
** 游标： ** **
**
用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。
** 分类： ** **
**
静态游标：
分为显式游标和隐式游标。
REF 游标：
是一种引用类型，类似于指针。
** 显式游标： ** **
**
CURSOR 游标名 ( 参数 ) [ 返回值类型 ] IS
Select 语句
生命周期：
1． 打开游标（ OPEN ）：
解析，绑定。。。不会从数据库检索数据
2． 从游标中获取记录 (FETCH INTO) ：
执行查询，返回结果集。通常定义局域变量作为从游标获取数据的缓冲区。
3． 关闭游标 (CLOSE)
完成游标处理，用户不能从游标中获取行。还可以重新打开。
选项：参数和返回类型
set serveroutput on
declare
cursor emp_cur ( p_deptid in number) is
select * from employees where department_id = p_deptid;
l_emp employees%rowtype;
begin
dbms_output.put_line(‘Getting employees from department 30’);
** open emp_cur(30);
**
loop
**fetch emp_cur into l_emp;
**
exit when emp_cur%notfound;
dbms_output.put_line(‘Employee id ‘|| l_emp.employee_id || ‘ is ‘);
dbms_output.put_line(l_emp.first_name || ‘ ‘ || l_emp.last_name);
end loop;
** close emp_cur;
**
dbms_output.put_line(‘Getting employees from department 90’);
open emp_cur(90);
loop
fetch emp_cur into l_emp;
exit when emp_cur%notfound;
dbms_output.put_line(‘Employee id ‘|| l_emp.employee_id || ‘ is ‘);
dbms_output.put_line(l_emp.first_name || ‘ ‘ || l_emp.last_name);
end loop;
close emp_cur;
end;
/
** 隐式游标： ** **
**
不用明确建立游标变量，分两种：
1． 在 PL/SQL 中使用 DML 语言，使用 ORACLE 提供的名为 SQL 的隐示游标
2． CURSOR FOR LOOP ，用于 for loop 语句。
1 举例：
declare
begin
update departments set department_name=department_name;
--where 1=2;
dbms_output.put_line(‘update ‘|| sql%rowcount ||’ records’);
end;
/
2 举例：
declare
begin
for my_dept_rec in ( select department_name, department_id from departments)
loop
dbms_output.put_line(my_dept_rec.department_id || ‘ : ’ || my_dept_rec.department_name);
end loop;
end;
/
** 游标属性： ** **
**
%FOUND ：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND ：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT ：当前时刻已经从游标中获取的记录数量。
%ISOPEN ：是否打开。
Declare
Cursor emps is
Select * from employees where rownum<6 order by 1;
Emp employees%rowtype;
Row number :=1;
Begin
**Open emps;
**
** Fetch emps into emp;
**
Loop
If emps%found then
Dbms_output.put_line(‘Looping over record ‘||row|| ‘ of ‘ || emps%rowcount);
Fetch emps into emp;
Row := row + 1;
Elsif emps%notfound then
Exit; ---exit loop, not IF
End if;
End loop;
**If emps%isopen then
**
** Close emps;
**
** End if;
**
End;
/
** 显式和隐式游标的区别： ** **
**
尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。
**
**
** REF CURSOR ** ** 游标 ** ** :
**
动态游标，在运行的时候才能确定游标使用的查询。分类：
强类型（限制） REF CURSOR ，规定返回类型
弱类型（非限制） REF CURSOR ，不规定返回类型，可以获取任何结果集。
TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]
Declare
Type refcur_t is ref cursor;
Type emp_refcur_t is ref cursor return employee%rowtype;
Begin
Null;
End;
/
** 强类型举例： ** **
**
declare
-- 声明记录类型
type emp_job_rec is record(
employee_id number,
employee_name varchar2(50),
job_title varchar2(30)
);
-- 声明 REF CURSOR ，返回值为该记录类型
type emp_job_refcur_type is ref cursor
return emp_job_rec;
-- 定义 REF CURSOR 游标的变量
emp_refcur emp_job_refcur_type;
emp_job emp_job_rec;
begin
open emp_refcur for
select e.employee_id,
e.first_name || ‘ ’ ||e.last_name “employee_name”,
j.job_title
from employees e, jobs j
where e.job_id = j.job_id and rownum < 11 order by 1;
fetch emp_refcur into emp_job;
while emp_refcur%found loop
dbms_output.put_line(emp_job.employee_name || ‘’’s job is ’);
dbms_output.put_line(emp_job.job_title);
fetch emp_refcur into emp_job;
end loop;
end;
/
** 单独 ** ** select
**
**
**
declare
l_empno emp.EMPLOYEE_ID%type;
-- l_ename emp.ename%type;
begin
select EMPLOYEE_ID
into l_empno
from emp;
--where rownum =1;
dbms_output.put_line(l_empno);
end;
/
使用 INTO 获取值，只能返回一行。
** 错误处理： ** **
**
exception
when
1<exception_expression> then
2
3…
4
5when <exception_expression> then
6
7…
8
9end;
10
11exception_expression 包括：
12
131\. 预定义表达式
14
152\. 用户定义表达式
16
173\. PRAGMA EXCEPTION_INIT
18
19** 预定义 ** **
20
21**
22
23declare
24
25l_dept departments%rowtype;
26
27begin
28
29l_dept.department_id:=100;
30
31l_dept.department_name:=’HR’;
32
33insert into departments(department_id, department_name)
34
35values(l_dept.department_id, l_dept.department_name);
36
37Exception
38
39When DUP_VAL_ON_INDEX then
40
41Dbms_output.put_line(‘heihei’);
42
43end;
44
45/
46
47DUP_VAL_ON_INDEX 异常
48
49Exception
50
51When DUP_VAL_ON_INDEX then
52
53&nb</exception_expression></exception_expression>