Oracle Where Current Of

In Oracle, if you wish to update or delete records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement. The where current of statement allows you to update or delete the record that was last fetched by the cursor.

Oracle Where Current Of Syntax

To retrieve the rows from declared cursor in Oracle, use the following syntax:

Syntax

UPDATE table_name
  SET set_clause
  WHERE CURRENT OF cursor_name;

Parameters:

table_name: The table name.

set_clause: To populate the cursor result set.

cursor_name: The cursor name.

Oracle Where Current Of example

The following Oracle example shows how to use the WHERE CURRENT OF statement:

Example

CREATE OR REPLACE Function SearchCustomer
  ( cust_name IN varchar2 )
  RETURN varchar2
IS
   adder varchar2;

   CURSOR cur1
   IS
     SELECT address
     FROM tblCustomerLoan
     WHERE firstName = cust_name
	 FOR UPDATE of cellNo;

BEGIN

   OPEN cur1;
   FETCH cur1 INTO adder;
   
    if cur1%notfound then
      adder := 'Not found';

   else
      UPDATE tblCustomerLoan
        SET cellNo = '8464545454'
        WHERE CURRENT OF cur1;

      COMMIT;

   end if;
   
   CLOSE cur1;

RETURN adder;
END;

Note:

In the above example, shows how to use the WHERE CURRENT OF statement.

« Previous