Oracle Transactions and exception handling

Source: Internet
Author: User
Tags savepoint

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.