?
Solved

pl/sql cursor

Posted on 2003-03-01
7
Medium Priority
?
412 Views
Last Modified: 2008-01-09
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
Comment
Question by:j2me_tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 8048323
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
 

Author Comment

by:j2me_tech
ID: 8048606
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
 
LVL 15

Expert Comment

by:andrewst
ID: 8048635
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.

 

Author Comment

by:j2me_tech
ID: 8049215
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
 
LVL 15

Accepted Solution

by:
andrewst earned 200 total points
ID: 8049513
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
 
LVL 17

Expert Comment

by:walterecook
ID: 10322815
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question