Using rollback - , PL-SQL Programming

Using ROLLBACK

The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if you made a mistake such as deleting the wrong row from the table, rollbacks restore the original data. Secondly, if you start the transaction that you cannot finish as an exception is raised or the SQL statement fails the rollback return to the starting point to take the correct action and possibly try again.

Consider the illustration shown below, in which you insert information about an employee into 3 various database tables. All 3 tables have a column that hold employee numbers and is constrained by an exclusive index. If an INSERT statement tries to store a duplicate employee number, the predefined exception DUP_VAL_ON_ INDEX is raised. In that situation, you want to undo all the changes, so you issue a rollback in an exception handler.

DECLARE

emp_id INTEGER;

...

BEGIN

SELECT empno, ... INTO emp_id, ... FROM new_emp WHERE ...

...

INSERT INTO emp VALUES (emp_id, ...);

INSERT INTO tax VALUES (emp_id, ...);

INSERT INTO pay VALUES (emp_id, ...);

...

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK;

...

END;

Posted Date: 10/4/2012 5:14:24 AM | Location : United States







Related Discussions:- Using rollback - , Assignment Help, Ask Question on Using rollback - , Get Answer, Expert's Help, Using rollback - Discussions

Write discussion on Using rollback -
Your posts are moderated
Related Questions
Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

Iteration Schemes The bounds of a loop range can be variables, literals, variables, or expressions but must compute to integers. Below are some of the examples. As you can see t

Example of Null operator - NiNo Rule If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is sho

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

LONG and LONG RAW You use the LONG datatype to store the variable-length character strings. The LONG datatype is such as the VARCHAR2 datatype, except that the maximum length o

UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Constants and Variables:   You can declare the constants and variables in the declarative section of any PL/SQL subprogram, block, or package. The Declarations allot the stor