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
Identifiers You use identifiers to name the PL/SQL program items and units that include constants, variables, cursors, exceptions, cursor variables, subprograms, and packages.

We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Advantages of Packages The benefits of the Packages are as shown below: Modularity The Packages encapsulate logically associated items, types, and subprograms in the

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Packaging Cursors   You can split a cursor specification from its body for placement in a package. In that way, you can change the cursor body without changing the cursor spec

(a) What are decision support systems, and what role do they play in the business environment? (b) Data warehousing is defined as "a subject-oriented, integrated, non-volatile c

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming

I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax: