Exceptions and rollbacks for Oracle
DECLAREDept_no Number(2) := -;BEGIN --Start a transaction INSERT intoDeptVALUES(Dept_no,'Marketing Department','Beijing');--Insert Department record INSERT intoDeptVALUES(Dept_no,'Logistics','Shanghai');--Insert the same numbered department record INSERT intoEmp--Insert Employee record VALUES(7997,'Weir','Sales Staff',NULL, TRUNC (Sysdate), the, -, Dept_no); --Commit a transaction COMMIT; EXCEPTION whenDup_val_on_index Then --abnormal catching of footDbms_output. Put_Line (SQLERRM);--Show Exception Message ROLLBACK;--Rollback Exception
View Code
Ollback will roll back all transactions by default, SavePoint can save points, rollback can roll back to the previous point, reducing data duplication.
DECLAREDept_no Number(2) := -;BEGIN --Start a transactionsavepoint A; INSERT intoDeptVALUES(Dept_no,'Marketing Department','Beijing');--Insert Department recordsavepoint B; INSERT intoEmp--Insert Employee record VALUES(7997,'Weir','Sales Staff',NULL, TRUNC (Sysdate), the, -, Dept_no); SavePoint C; INSERT intoDeptVALUES(Dept_no,'Logistics','Shanghai');--Insert the same numbered department record --Commit a transaction COMMIT; EXCEPTION whenDup_val_on_index Then --abnormal catching of footDbms_output. Put_Line (SQLERRM);--Show Exception Message ROLLBACK toB--Rollback ExceptionEND;
View Code
Processing format for exceptions
format in Plsql block Declare variable Begin code block EXCEPTION When exception name then The specific work to do when the above exception is born. End;
View Code
Common Exception Handling:
SetServeroutput on;Create or Replace procedurePr12 as--define an int variable LiangV_ageinteger; V_namevarchar( -);beginV_age:= the;--setting values for V_name with select--modify into a processSelectName intoV_name fromStudwhereId=1;D Bms_output. Put_Line ('No Errors'); exception whenValue_error ThenSYS. Dbms_output. Put_Line ('Numeric Error'); whenNo_data_found ThenSYS. Dbms_output. Put_Line ('No Data'); whenOthers ThenSYS. Dbms_output. Put_Line (Sqlcode||'you made a mistake.'||SQLERRM); End;execpr12 ();-------------------------------------------Custom Exceptions throw exceptions themselves//*define one's own exception myexception Exception; throw an exception RAISE myexception; Handle your own exceptions: Exception when MyException and then ....*/SetServeroutput on;DeclaremyEx exception;beginDbms_output. Put_Line ('It's true .'); Raise Myex;dbms_output. Put_Line ('no output, exception thrown in front');--Handling ExceptionsException whenMyEx ThenDbms_output. Put_Line ('their own anomalies.'||Sqlcode||' '||sqlerrm); whenOthers ThenDbms_output. Put_Line ('I don't know what's wrong.'||Sqlcode||sqlerrm);END;---Error thrown directlyDeclarebeginDbms_output. Put_Line ('No Errors');--Direct ThrowRaise_application_error (-20000,'A');D Bms_output. Put_Line ('go Okk ....'); exception whenOthers ThenDbms_output. Put_Line (Sqlcode||' '||sqlerrm);End;
View Code
Oracle Transactions and exception handling