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

Keyword, what is the use of declare keyword

what is the use of declare keyword

Disjunction - sql, Disjunction (OR, ∨) Again we have nine rows instead...

Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Pl/sql assignment, 1. a. Write a trigger that fires when a part's price...

1. a. Write a trigger that fires when a part's price is updated. The trigger will write a record into a table called PriceUpdates. The record should contain the information of

Semidifference via not in and a subquery , Semidifference via NOT IN and a ...

Semidifference via NOT IN and a subquery SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' AND StudentId NOT IN (SELECT StudentId FROM IS_ENROLLED_ON WHER

Opening a cursor variable, Opening a Cursor Variable The OPEN-FOR stat...

Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Explicit cursor attributes, Explicit Cursor Attributes The cursor varia...

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Using the collection methods, Using the Collection Methods The collecti...

Using the Collection Methods The collection methods below help to generalize the code and make collections easier to use and also make your applications easier to maintain:

Open-for statement, OPEN-FOR Statement The OPEN-FOR statements execute ...

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Declaring subprograms, Declaring Subprograms   You can declare subprog...

Declaring Subprograms   You can declare subprograms in any PL/SQL subprogram, block, or package. But, you should declare subprograms at the end of the declarative part after a

Example of coalesce operator - sql, Example of COALESCE operator Examp...

Example of COALESCE operator Example: Give the total of marks for each exam (simplified solution) SELECT CourseId, COALESCE ((SELECT SUM (Mark) FROM EXAM_MARK AS EM

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