Using for update, PL-SQL Programming

Using FOR UPDATE

If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an exclusive row locks. An illustration is as shown below:

DECLARE

CURSOR c1 IS SELECT empno, sal FROM emp

WHERE job = 'SALESMAN' AND comm > sal

FOR UPDATE NOWAIT;

The FOR UPDATE clause identifies the row which will be updated or deleted, then locks each & every row in the result set. This is helpful when you want to base an update on the existing values in a row. In that situation, you should make sure that the row is not changed by the other user before the update.

The elective keyword NOWAIT tells the Oracle not to wait if the table has been locked by the other user. The Control is immediately returned to your program so that it can do the other work before trying again to obtain the lock. If you omit the keyword NOWAIT, the Oracle waits until the table is available.

All rows are locked when you open the cursor, they are not liked fetched. The rows are unlocked when you commit or roll back the transaction. And hence, you cannot fetch from a

When querying the multiple tables, you can use the FOR UPDATE clause to lock up the row locking to the particular tables. The Rows in a table are locked only if the FOR UPDATE OF the clause refers to the column in that table. For illustration, the following query locks rows in the emp table but not in the dept table:

DECLARE

CURSOR c1 IS SELECT ename, dname FROM emp, dept

WHERE emp.deptno = dept.deptno AND job = 'MANAGER'

FOR UPDATE OF sal;

As the next illustration shows, you use the CURRENT OF clause in an UPDATE or DELETE statement to refer to the newest row fetched from a cursor:

DECLARE

CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE;

...

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO ...

...

UPDATE emp SET sal = new_sal WHERE CURRENT OF c1;

END LOOP;

Posted Date: 10/4/2012 5:20:41 AM | Location : United States







Related Discussions:- Using for update, Assignment Help, Ask Question on Using for update, Get Answer, Expert's Help, Using for update Discussions

Write discussion on Using for update
Your posts are moderated
Related Questions
Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

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

Transaction context As the figure shows, the major transaction shares its context with the nested transactions, but not with the autonomous transactions. Similarly, If one aut

Need to change mysql query to PDO Project Description: I want someone to convert me 1 .php file that includes some sql/mysql stuff to PDO is a very small file. Skills requ

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

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

Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

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

%NOTFOUND The %NOTFOUND is logical, opposite of the %FOUND. The %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE when the final fetch failed to return a row. I