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
Oracle 11 G new features associated with this release:- Enhanced ILM  - Information Lifecycle Management (ILM) has been around for the almost 10 years, but Oracle has made

Anatomy of a Table: Figure shows the terminology used in SQL to refer to parts of the structure of a table. As you can see, SQL has no official terms for its counterpa

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

%ISOPEN The %ISOPEN yields TRUE if its cursor or cursor variable is open; or else, the %ISOPEN yields FALSE. In the illustration, you use the %ISOPEN to select an action:

Question 1 . Compare SQL and PL/SQL Question 2 . Write a database trigger to implement the following check condition                          Given the following table

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Bulk Fetching The illustration below shows that you can bulk-fetch from a cursor into one or more collections: DECLARE TYPE NameTab IS TABLE OF emp.ename%TYPE; TYPE S

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to