Oracle数据库：异常处理

1、使用select into语句读取employees的一行，使用异常处理处理no_data_found和two_many_rows的系统预定义异常

select * from employees;
declare
 item employees%rowtype;
begin 
	SELECT *
		into item
		from employees
		where salary=&input;
		dbms_output.put_line(item.first_name||' '||item.last_name);
EXCEPTION
	when no_data_found
	then
		dbms_output.put_line('[err]NO_DATA_FOUND');
	when too_many_rows
	then
		dbms_output.put_line('[err]too_many_rows');
	when others
	then 
        dbms_output.put_line('[err]others');
end;

2、使用嵌套异常端处理，循环读取若干个id号的员工，使得其中存在不存在员工号。输出对应员工的first_name和last_name,不存在的员工输出“not exists such empolyee”。

select * from employees;
declare
 item employees%rowtype;
begin 
    for i in 95..120 loop
        begin
            SELECT * into item from employees where employee_id=i;
            dbms_output.put_line(item.employee_id||' '||item.first_name||' '||item.last_name);
            EXCEPTION 
                     WHEN no_data_found THEN 
                     DBMS_OUTPUT.PUT_LINE('not exists such empolyee');
                     CONTINUE;
        end;
    end loop;
end;

3、写一个 处理ora-01400 （不能插入空值）的系统异常的示例程序和异常处理

declare
insert_excep exception;
pragma exception_init(insert_excep, -01400);
begin
insert into employees values (50, null,null,null,null,null,null,null,null,null,null);
exception
when insert_excep then
dbms_output.put_line('INSERT OPERATION FAILED');
dbms_output.put_line(SQLERRM);
end;

4、使用SQLCODE,SQLERRM,获得异常的编号和错误信息，并输出异常的编号和错误信息

declare
    emp Employees%rowtype;
    error_code number;
    error_message varchar2(255);
begin
    select * into emp from Employees
    where employee_id =95;
    dbms_output.put_line('95 ''s salary is : ' || emp.salary); 
    exception
    when no_data_found then
        error_code := SQLCODE;
        error_message := SQLERRM;
        dbms_output.put_line(error_code || ' ' || error_message);
end;

5、自定义一个异常，抛出这个异常并处理

declare
    invalid_department exception;
    name varchar2(20) :='name';
    deptno number :=&deptno;
begin
    update employees set employee_id=name where department_id=deptno;
    if sql%notfound then
        raise invalid_department;
    end if;
    exception
    when invalid_department then
    dbms_output.put_line('No such department');
end;

6、使用raise_application_error抛出一个应用异常

declare
    v_id Employees.employee_id%type:=&v;
    e_name exception;
    pragma exception_init(e_name, -20999);
begin
    delete from employees
    where employee_id =v_id;
    if sql % notfound then
        raise_application_error(-20999, 'This is not a valid employee_id');
    end if;
exception
    when e_name then
    dbms_output.put_line('The employee_id '||v_id||' not exists, Please choose again');
end;