03异常错误处理

异常错误处理

1.编写一段PL/SQL程序,根据输入的员工编号参数,查询并打印员工的姓名,并定义异常处理

declare

v_empno emp.empno%type := &empno;

v_ename emp.ename%type;

begin

select ename into v_ename from emp where empno = v_empno;

dbms_output.put_line('empno: ' || v_empno || ' --- ' || 'ename: ' || v_ename);

exception

when no_data_found then

dbms_output.put_line(Sqlcode || ' --- ' || SQLERRM);

when others then

dbms_output.put_line(Sqlcode || ' --- ' || SQLERRM); end;

2.自定义一个异常,根据参数员工编号,查询该员工的姓名,所在部门名称,在本公司服务年限,工资等信息,如果工资小于3000,抛出异常提示信息"smith先生在ACCOUNTING部门白吃了2.3333年的咸盐";

declare

v_empno emp.empno%type := &empno;

Type emp_info_type is record (

v_ename emp.ename%type,

v_dname dept.dname%type,

v_service number(2),

v_sal emp.sal%type

);

v_emp_info emp_info_type;

myexception exception;

begin

select

ename,dname,trunc(months_between(sysdate,hiredate)/12),sal into v_emp_info from emp,dept where emp.deptno=dept.deptno and empno = v_empno;

if v_emp_info.v_sal<3000then

raise myexception;

end if;

exception

when myexception then

dbms_output.put_line('smith先生在ACCOUNTING部门白吃了2.3333年的咸盐');

dbms_output.put_line(SQLCODE ||' --- '|| SQLERRM);

when no_data_found then

dbms_output.put_line(Sqlcode || ' --- ' || SQLERRM);

when others then

dbms_output.put_line(Sqlcode || ' --- ' || SQLERRM);

end;