Oracle Transactions and exception handling

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