INTERVIEW QUESTIONS ORACLE PL-SQL DETAILS

Question: Explain the usage of WHERE CURRENT OF clause in cursors ?

Answer: WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers Source: CoolInterview.com

Where CURRENT OF clause means
cursor
points to present row of
the cursor
Source: CoolInterview.com

WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Source: CoolInterview.com

This clause use for update & delete most resent row with out help of ROWID and befour update lock the particular row in the base table.FOR UPDATE clause is manditory for this clause. Source: CoolInterview.com

Take an example, Cursor is
Select * from EMP;
and in cursor we write
Update Emp set sal = sal + 100;
that means the fetching loops will execute as many times as rows returned by the cursor and the update will also be done for the same number of times.
Now suppose, If we put a where clause in the update statement, then the result would also not be predictable.
In the third case, we use the WHERE CURRENT OF clause in the where clause, this point out to the current row returned by the cursor. In that case, Oracle differentiate all the rows on the basis of RowID. But what happens if someone changes one row in the table.....that means the rowid would be changed. So we have to manually lock all the rows of that table by writing the FOR UPDATE/DELETE clause.
So In case of WHERE CURRENT OF clause, we should use the FOR UPDATE/DELETE clause also. This is known as the Pessimistic/Manual Locking and this the major difference between the Implicit Cursors and Explicit Cursors. Source: CoolInterview.com

UPDATE and DELETE statements can use a WHERE CURRENT OF clause if they reference a cursor declared FOR UPDATE. This syntax indicates that the UPDATE or DELETE should modify the current row identified by the FOR UPDATE cursor. The syntax is:

[UPDATE | DELETE ] ...
WHERE CURRENT OF cursor_name;By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement. For example:

DECLARE
CURSOR wip_cur IS
SELECT acct_no, enter_date FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec.acct_no, wip_rec.enter_
date);
DELETE FROM wip
WHERE CURRENT OF wip_cur;
END LOOP;
END; Source: CoolInterview.com

If your program has positioned the cussor on a row,u can update its data by using update statemnet with where current clause. Source: CoolInterview.com

Where current of is used to delete or update the current row that is being processed in the cursor loop.


Source: CoolInterview.com

WHERE CURRENT OF CLAUSE is used when referencing the current row from an explicit cursor.
this clause allows to apply updates and deletes to the row currently being addressed,without the need to explicitly reference ROWID.
We must use the FOR UPDATE clause in the cursor query, such thay the rows are locked on open. Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question