爱站程序员基地1、使用select into语句读取employees的一行，使用异常处理处理no_data_found和two_many_rows的系统预定义异常
select * from employees;declareitem employees%rowtype;beginSELECT *into itemfrom employeeswhere salary=&input;dbms_output.put_line(item.first_name||\' \'||item.last_name);EXCEPTIONwhen no_data_foundthendbms_output.put_line(\'[err]NO_DATA_FOUND\');when too_many_rowsthendbms_output.put_line(\'[err]too_many_rows\');when othersthendbms_output.put_line(\'[err]others\');end;
2、使用嵌套异常端处理，循环读取若干个id号的员工，使得其中存在不存在员工号。输出对应员工的first_name和last_name,不存在的员工输出“not exists such empolyee”。
select * from employees;declareitem employees%rowtype;beginfor i in 95..120 loopbeginSELECT * into item from employees where employee_id=i;dbms_output.put_line(item.employee_id||\' \'||item.first_name||\' \'||item.last_name);EXCEPTIONWHEN no_data_found THENDBMS_OUTPUT.PUT_LINE(\'not exists such empolyee\');CONTINUE;end;end loop;end;
3、写一个 处理ora-01400 （不能插入空值）的系统异常的示例程序和异常处理
declareinsert_excep exception;pragma exception_init(insert_excep, -01400);begininsert into employees values (50, null,null,null,null,null,null,null,null,null,null);exceptionwhen insert_excep thendbms_output.put_line(\'INSERT OPERATION FAILED\');dbms_output.put_line(SQLERRM);end;
4、使用SQLCODE,SQLERRM,获得异常的编号和错误信息，并输出异常的编号和错误信息
declareemp Employees%rowtype;error_code number;error_message varchar2(255);beginselect * into emp from Employeeswhere employee_id =95;dbms_output.put_line(\'95 \'\'s salary is : \' || emp.salary);exceptionwhen no_data_found thenerror_code := SQLCODE;error_message := SQLERRM;dbms_output.put_line(error_code || \' \' || error_message);end;
5、自定义一个异常，抛出这个异常并处理
declareinvalid_department exception;name varchar2(20) :=\'name\';deptno number :=&deptno;beginupdate employees set employee_id=name where department_id=deptno;if sql%notfound thenraise invalid_department;end if;exceptionwhen invalid_department thendbms_output.put_line(\'No such department\');end;
6、使用raise_application_error抛出一个应用异常
declarev_id Employees.employee_id%type:=&v;e_name exception;pragma exception_init(e_name, -20999);begindelete from employeeswhere employee_id =v_id;if sql % notfound thenraise_application_error(-20999, \'This is not a valid employee_id\');end if;exceptionwhen e_name thendbms_output.put_line(\'The employee_id \'||v_id||\' not exists, Please choose again\');end;
