oracle 基本異常的練習及各個錯誤碼

DECLAREv_deptno emp.deptno%TYPE :=&deptno;v_sal    emp.sal%TYPE;v_empno  emp.empno%TYPE; 
BEGINSELECT sal INTO v_sal FROM emp WHERE deptno= v_deptno;IF v_sal<=1500 THEN UPDATE emp SET sal=sal+100 WHERE empno=  v_empno; DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已更新!');     ELSEDBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已經超過規定值!');END IF; 
EXCEPTIONWHEN Dup_val_on_index THEN  DBMS_OUTPUT.PUT_LINE('數據庫中沒有編碼為'||v_deptno||'的員工');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE(SQLCODE||v_deptno||SQLERRM);           ---1422返回多行WHEN NO_DATA_FOUND THEN             DBMS_OUTPUT.PUT_LINE(SQLCODE||v_deptno||SQLERRM);          --100表示NO_DATA_FOUND
END; 
select * from dept            DECLAREv_deptno dept.deptno%TYPE :=&deptno;deptno_remaining EXCEPTION;PRAGMA EXCEPTION_INIT(deptno_remaining, 100);/* -2292 是違反一致性約束的錯誤代碼 */
BEGINDELETE FROM dept WHERE deptno=v_deptno;
EXCEPTIONWHEN deptno_remaining THEN DBMS_OUTPUT.PUT_LINE('違反數據完整性約束!');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);  --sqlcode數據庫返回的操作碼   SQLERRM返回錯誤碼信息     '程序運行錯誤!請使用游標'
END;  DECLAREv_deptno dept.deptno%TYPE :=&deptno;no_result  EXCEPTION;no_result1  EXCEPTION;deptno_remaining EXCEPTION;PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);
BEGIN-- DELETE FROM dept WHERE deptno=v_deptno;UPDATE emp SET sal=sal+100 WHERE deptno=v_deptno;IF SQL%FOUND THENRAISE no_result;elsIF SQL%NOTFOUND THEN  RAISE no_result1;    END IF;EXCEPTIONWHEN no_result THENDBMS_OUTPUT.PUT_LINE(SQLCODE||'這是1'||SQLERRM);WHEN no_result1 THENDBMS_OUTPUT.PUT_LINE(SQLCODE||'這是2'||SQLERRM);    --1表示用戶沒有定義WHEN deptno_remaining THEN DBMS_OUTPUT.PUT_LINE('你的數據更新語句失敗了!');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);       
END;Exceptionwhen osi_general_error thenDBMS_OUTPUT.PUT_LINE(SQLCODE||'這是1'||SQLERRM);when OTHERS THEN              DBMS_OUTPUT.PUT_LINE('你的數據更新語句失敗了!');select* from empdeclarev_empno  emp.empno%type := &empno;v_ename emp.ename%type;   beginselect ename into v_ename from empwhere empno = v_empno;dbms_output.put_line(SQLCODE||'----'||v_ename);       --0表示沒有異常exceptionwhen others thenraise_application_error(-20001 , 'emp'||v_empno||' does not exists');end;