• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

pl/sql cursor

to all those experts out there.i need some detail explaination in FOR UPDATE CLAUSE & WHERE CURRENT OF clauses.can u tell me the purpose 2 use them.i knew it's for lock table, but can explain in more detaily?how 2 get some answer realli soon.
0
j2me_tech
Asked:
j2me_tech
  • 3
  • 2
1 Solution
 
andrewstCommented:
You can use FOR UPDATE to lock the rows you select to prevent another user from updating them until you release the lock via COMMIT or ROLLBACK.

You can also specify the columns that you will be updating, but this is optional and doesn't really do anything:

... FOR UPDATE OF ename, sal;

When you select for update, you will be prevented from obtaining the lock if another user already has a lock on the same record.  By default, Oracle will wait indefinitely (more or less) until the lock is obtained.  You can modify this behaviour by adding one of the following clauses after the FOR UPDATE clause:

NOWAIT

WAIT <number of seconds>

This will cause Oracle to raise an error (unable to obtain lock) either immediately (NOWAIT) or after the specified number of seconds (WAIT).

WHERE CURRENT OF can be used with FOR UPDATE with cursors.  It is used as the WHERE clause of an UPDATE or DELETE statement, and it means "the row just fetched from the cursor".  For example:

DECLARE
  CURSOR c IS
    SELECT ename, sal
    FROM emp
    FOR UPDATE OF sal NOWAIT;
BEGIN
  FOR r in c LOOP
    UPDATE emp
    SET    sal = sal*1.1
    WHERE CURRENT OF c;
  END LOOP;
END;

It is really just a shorthand equivalent to this:

DECLARE
  CURSOR c IS
    SELECT ename, sal, ROWID row_id
    FROM emp
    FOR UPDATE OF sal NOWAIT;
BEGIN
  FOR r in c LOOP
    UPDATE emp
    SET    sal = sal*1.1
    WHERE ROWID = r.row_id;
  END LOOP;
END;
0
 
j2me_techAuthor Commented:
okie..my codin is like below..and it popup some error message:

coding:
DECLARE
   CURSOR action_cursor IS
      SELECT * FROM action
      FOR UPDATE;
BEGIN
   FOR action_record IN action_cursor LOOP
      IF action_record.oper_type = 'U' THEN
         UPDATE accounts
         SET bal = action_record.new_value
         WHERE account_id = action_record.account_id
         WHERE CURRENT OF action_cursor;
         IF SQL%NOTFOUND THEN
            INSERT INTO accounts
            VALUES(action_record.account_id,action_record.new_value);
         END IF;
      END IF;
   END LOOP;
END;

and the error is:
ERROR at line 11:
ORA-06550: line 11, column 10:
PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:
. ( * @ % & - + ; / mod rem return RETURNING_
an exponent (**) and or ||
ORA-06550: line 12, column 10:
PLS-00103: Encountered the symbol "IF"
ORA-06550: line 16, column 11:
PLS-00103: Encountered the symbol "IF" when expecting one of the following:
; <an identifier> <a double-quoted delimited-identifier>
delete exists prior <a single-quoted SQL string>
0
 
andrewstCommented:
You have 2 WHEREs in your UPDATE:

    UPDATE accounts
        SET bal = action_record.new_value
        WHERE account_id = action_record.account_id
        WHERE CURRENT OF action_cursor;

It should be either:

    UPDATE accounts
        SET bal = action_record.new_value
        WHERE CURRENT OF action_cursor;

or:

    UPDATE accounts
        SET bal = action_record.new_value
        WHERE account_id = action_record.account_id;

The WHERE CURRENT OF version is the most efficient.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
j2me_techAuthor Commented:
i thought i follow exactly whut u said andrewst.but there's still error.

coding:
DECLARE
   CURSOR action_cursor IS
      SELECT *
      FROM ACTION
      FOR UPDATE;
BEGIN
   FOR action_record IN action_cursor LOOP
      /* Perform update on accounts table */
      IF action_record.oper_type = 'U' THEN
         UPDATE accounts
         SET bal = action_record.new_value
         WHERE CURRENT OF action_cursor;
            /* Perform insert if row does not exist */
            IF SQL%NOTFOUND THEN
               INSERT INTO accounts
               VALUES( action_record.account_id, action_record.new_value );
            END IF;
      /* Perform insert on accounts table */
      ELSIF action_record.oper_type = 'I' THEN
         BEGIN
            INSERT INTO accounts
            VALUES( action_record.account_id, action_record.new_value );
         EXCEPTION
            /* Perform update if row already exists */
            WHEN DUP_VAL_ON_INDEX THEN
               UPDATE accounts
               SET bal = action_record.new_value
               WHERE CURRENT OF action_cursor;
         END;
      END IF;
   END LOOP;
END;

error:
DECLARE
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 10
0
 
andrewstCommented:
Right, I hadn't noticed before - you are not updating the table that you locked in the cursor, so WHERE CURRENT OF is not appropriate.  "WHERE CURRENT OF" means "that very same record that we locked by selecting it".

So you will have to use the standard WHERE clause:

UPDATE accounts
SET bal = action_record.new_value
WHERE account_id = action_record.account_id;
0
 
walterecookCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: andrewst {http:#8049513}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now