PL/SQL异常处理方法(四)
EGIN
add_more_departments('Media',100,1800,'Editing',99,1800);
END;
/
BEGIN
create_more_departments;
END;
执行结果如下：
SQL> BEGIN
2 create_more_departments;
3 END;
4 /
Add Dept: Media
BEGIN
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (HR.DEPT_MGR_FK) - 未找到父项关键字
ORA-06512: 在 "HR.ADD_MORE_DEPARTMENTS", line 6
ORA-06512: 在 "HR.CREATE_MORE_DEPARTMENTS", line 4
ORA-06512: 在 line 2
查询结果：
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
这次没有异常处理，把异常处理去掉了。
这次的执行结果： 两条记录一条记录也没有插入进去。
ex3：
delete from departments where department_id >270;
select department_id,department_name from departments order by 1;
[sql]
CREATE OR REPLACE PROCEDURE add_more_departments(p_name1 VARCHAR2, p_mgr1 NUMBER, p_loc1 NUMBER,p_name2 VARCHAR2, p_mgr2 NUMBER, p_loc2 NUMBER) IS
BEGIN
INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(departments_seq.NEXTVAL,p_name1,p_mgr1,p_loc1);
DBMS_OUTPUT.PUT_LINE('Add Dept: ' || p_name1);
INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(departments_seq.NEXTVAL,p_name2,p_mgr2,p_loc2);
DBMS_OUTPUT.PUT_LINE('Add Dept: ' || p_name2);
END;
/
CREATE OR REPLACE PROCEDURE create_more_departments
IS
BEGIN
add_more_departments('Media',100,1800,'Editing',99,1800);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Errors have happend');
END;
/
BEGIN
create_more_departments;
END;
执行结果：
SQL> BEGIN
2 create_more_departments;
3 END;
4 /
Add Dept: Media
Errors have happend
PL/SQL 过程已成功完成。
查询结果：
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
400 Media
这次把异常处理放在了最外面：调用的最外面：
执行结果如下：
执行成功的第一条语句被成功插入，第二条被捕获。