Using commit, PL-SQL Programming

Assignment Help:

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 access the changed data; they see the data as it was before you made the changes.

Consider a simple transaction which transfers money from one bank account to the other. The transaction needs two updates as it debits the first account, and then credits the second. In the illustration below, after crediting the second account, you issue a commit that makes the changes everlasting. Only then do other users see the changes.

BEGIN

...

UPDATE accts SET bal = my_bal - debit

WHERE acctno = 7715;

...

UPDATE accts SET bal = my_bal + credit

WHERE acctno = 7720;

COMMIT WORK;

END;

The COMMIT statements release all row and table locks. It also erases any savepoint marked as the last commit or rollback. The elective keyword WORK has no effect other than to get better readability. The keyword END signals the end of the PL/SQL block, not the end of the transaction. Now as a block can span a multiple transactions, the transaction can cover multiple blocks.

The COMMENT clause specifies a comment to be related with the distributed transaction. If you issue a commit, the changes to each database affected by a distributed transaction are made permanent. Though, if a network or machine fails during a commit, the state of the distributed transaction may be unknown or in doubt. In that situation, the Oracle stores the text specified by the COMMENT in the data dictionary beside with the transaction ID. The text should be a quoted literal up to 50 characters in size.  


Related Discussions:- Using commit

Creating and destroying base tables, Creating and Destroying Base Tables: ...

Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

Exit-when - iterative control, EXIT-WHEN The EXIT-WHEN statement permits...

EXIT-WHEN The EXIT-WHEN statement permits a loop to complete conditionally. Whenever the EXIT statement is encountered, the condition in the WHEN clause is computed. When the co

Data types in sql - integer, Data Types in SQL - Integer INTEGER or  s...

Data Types in SQL - Integer INTEGER or  synonymously INT, for integers within a certain range. SQL additionally has types SMALLINT and BIGINT for certain ranges of integers. T

Initializing objects in pl sql, Initializing Objects: Till you initiali...

Initializing Objects: Till you initialize an object by calling the constructor for its object type, the object is automatically null. That is, the object itself is null, not me

Write a pl/sql anonymous block that accepts an employee id, Based on the EM...

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP

Solve the business problems using sql, Use the MASCOT tables CREDITRS, PORD...

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

Calling constructors in pl sql, Calling Constructors: The Calls to a c...

Calling Constructors: The Calls to a constructor are allowed wherever the function calls are allowed. Similarly to the functions, a constructor is called as a section of an ex

Logical operators-pl/sql expressions , Logical Operators The logical op...

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

Database values-assignments in pl/sql, Database Values You can use the S...

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

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

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