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
Dynamic Ranges The PL/SQL lets you determine the loop range dynamically at run time, as the example below shows: SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_cou

Example of UNWRAP Operator - SQL Example here shows how unwrapping can be done in longhand in SQL. Example: Unwrapping in SQL Letting CONTACT_INFO_WRAPPED denote the res

(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

Question 1 . Compare SQL and PL/SQL Question 2 . Write a database trigger to implement the following check condition                          Given the following table

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

%ROWCOUNT When its cursor or cursor variable is opened, the %ROWCOUNT is zeroed. Before the first fetch, the %ROWCOUNT yields 0. Afterward, it yields the number of rows fetche

%ROWCOUNT The %ROWCOUNT yields the number of rows affected by the INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. The %ROWCOUNT yields zero when a

Using a Host Variable You can declare the cursor variable in the PL/SQL host environment like an OCI or Pro C program. To use the cursor variable, you should pass it as a host

Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw

Package STANDARD The package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available