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
Rephrase Conditional Control Statements When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops evaluating the expression as s

Understanding Varrays The Items of type VARRAY are termed as the varrays. They permit you to relate a single identifier with the whole collection. This relationship lets you man

Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

I want to implement heap sort algorithm in pl sql please share the source code for guidance

Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement

Example of GROUP BY and COLLECT Operator Example: Using GROUP BY and COLLECT to obtain C_ER2 SELECT CourseId, CAST ( COLLECT (ROW (StudentId, Mark)) AS ROW (Studen

Effects of NULL in Aggregate Operator - SQL Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be

Hi there, I have the final part of a submission to do, it is a demonstration that takes place tomorrow. I do not have to use previous information, but i have resources that sho