Using rollback - , PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Using rollback -

Pl sql code review, PL SQL Code Review HEADER ELEMENTS File Name ...

PL SQL Code Review HEADER ELEMENTS File Name Clear, meaningful and descriptive about main objective of the file. Multiple words are joined using underscores which adh

Initializing records, Initializing Records The illustration below show...

Initializing Records The illustration below shows that you can initialize a record in its type definition. Whenever you declare a record of the type TimeRec, its 3 fields supp

Declarations in sql-pl/sql, Declarations in SQL Your program stores value...

Declarations in SQL Your program stores values in the variables and constants. As the program executes, the value of the variables can change, but the values constants cannot.

Sql outer join, SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT...

SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Rollback behavior - bulk bind performance improvement, Rollback Behavior ...

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Using not null-declarations in sql, Using NOT NULL Besides assigning an ...

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Using aggregation on nested tables - sql, Using Aggregation on Nested Table...

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

Product-specific packages, Product-specific Packages The Oracle and dif...

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Sql operators, SQL Operators The PL/SQL uses all the SQL set, compariso...

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators.  1. Comparison Opera

Merge two data sources to sql azure database, Project Description: I hav...

Project Description: I have two types of data sources. One that is a list in SharePoint and another that is an access desktop database. The access desktop database is fairly com

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd