Using for update, PL-SQL Programming


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:


CURSOR c1 IS SELECT empno, sal FROM emp

WHERE job = 'SALESMAN' AND comm > sal


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:


CURSOR c1 IS SELECT ename, dname FROM emp, dept

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


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:


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



OPEN c1;




UPDATE emp SET sal = new_sal WHERE CURRENT OF c1;


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
Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

TTITLE and BTITLE are commands in Pl-SQL to control report headings and footers. This Ttitle & Btitle are mainly used on creating SQL*PLUS report. Ttitle is used for toptitle headi

Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

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

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Recursion versus Iteration Dissimilar the iteration, recursion is not crucial to PL/SQL programming. Any problem which can be solved using recursion can be solving using the it

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed