oracle 数据库中的异常和游标管理

异常和游标管理

游标：
用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。

分类：
静态游标：
分为显式游标和隐式游标。
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 <exception_expression> then
…
when <exception_expression> then
…
end;

exception_expression包括：
1.预定义表达式
2.用户定义表达式
3.PRAGMA EXCEPTION_INIT

预定义
declare
l_dept departments%rowtype;
begin
l_dept.department_id:=100;
l_dept.department_name:=’HR’;
insert into departments(department_id, department_name)
values(l_dept.department_id, l_dept.department_name);
Exception
When DUP_VAL_ON_INDEX then
Dbms_output.put_line(‘heihei’);
end;
/

DUP_VAL_ON_INDEX 异常

Exception
When DUP_VAL_ON_INDEX then
Dbms_output.put_line……

自定义：
declare
l_exc exception;
begin
…
raise l_exc;
exception
when l_exc then
…
end;
/

PRAGMA EXCEPTION_INIT

Declare
L_update_text varchar2(100):=
‘update &table_name set &updated_column_name= ‘’:a’’
where &key_column_name=:a’;
begin
execute immediate L_update_text using ‘&updated_column_value’, &key_column_value;
end;
/

Declare
Invalid_column_name exception;
Pragma exception_init(Invalid_column_name,-904);

L_update_text varchar2(100):=
	‘update &table_name set &updated_column_name= ‘’:a’’
		where &key_column_name=:a’;

begin
execute immediate L_update_text using ‘&updated_column_value’, &key_column_value;

exception
when Invalid_column_name then
dbms_output.put_line(‘hehe’);
end;
/

异常传播：
begin
begin
begin
begin
begin
declare
fname employees.first_name%type;
begin
select first_name into fname from employees where 1=2;
–exception
–when NO_DATA_FOUND then
– dbms_output.put_line(‘block 6’);
end;
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘block 5’);
end;
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘block 4’);
end;
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘block 3’);
end;
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘block 2’);
end;
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘block 1’);
end;
/

作用域和可视性：
begin
declare
nested_excp exception;
begin
raise nested_excp;
end;
exception
when nested_excp then
dbms_output.put_line(‘haha’);
end;
/

when others then
放在最后

begin
declare
nested_excp exception;
begin
raise nested_excp;
end;
exception
when others then
dbms_output.put_line(‘haha’);
dbms_output.put_line(sqlcode || ‘ is ’ || sqlerrm);

raise;

end;
/

SQLCODE SQLERRM

declare
l_dept departments%rowtype;
begin
l_dept.department_id:=100;
l_dept.department_name:=’HR’;
insert into departments(department_id, department_name)
values(l_dept.department_id, l_dept.department_name);
Exception
When others then
dbms_output.put_line(sqlcode || ‘ is ’ || sqlerrm);
end;
/
declare
l_dept departments%rowtype;
begin
l_dept.department_id:=100;
l_dept.department_name:=’HR’;
insert into departments(department_id, department_name)
values(l_dept.department_id, l_dept.department_name);
Exception
When others then
Raise_application_error(-20001, ‘error message!’);
End;
/