Cursor in Oracle PL/SQL

Question

I´m trying to write a CURSOR to make a task in Oracle. I´m not familiarized with cursors in Oracle. Obviously I´m doing something wrong with this. Can someone tell me what it is?.

DECLARE 
    CURSOR c1 IS
        SELECT BADGE.EMPID
        FROM EVENTS
        INNER JOIN BADGE ON EVENTS.CARDNUM = BADGE.ID
        INNER JOIN EMP ON BADGE.EMPID = EMP.ID
        WHERE EMP.VISITOR = 0
        AND EVENTS.EVENT_TIME_UTC - 0.125 > TO_DATE('20090101 00:00:00', 'RRRRMMDD hh24:mi:ss')
        AND EVENTS.EVENT_TIME_UTC - 0.125 < TO_DATE('20120101 23:59:59', 'RRRRMMDD hh24:mi:ss');
    r1 c1%ROWTYPE;
BEGIN
    FOR r1 IN c1 LOOP
    UPDATE EVENTS
    SET EVENTS.EMPID = r1.EMPID
    END LOOP;
END;

Thanks for your help!

Why are you "obviosuly doing something wrong"? And besides: for this update yo don't need a cursor. You can do that with a single UPDATE statement. For something similar see here: stackoverflow.com/q/16891930/330315 — user330315
– user330315, Commented Jun 3, 2013 at 14:36

hol · Accepted Answer · 2013-06-03 15:44:14Z

3

There is also a way to do this without a cursor but this is what you want to do. The point is that you define the cursor and then loop along it. Recognize the FOR UPDATE

DECLARE 
  CURSOR c1 IS
    SELECT BADGE.EMPID
    FROM EVENTS
    INNER JOIN BADGE ON EVENTS.CARDNUM = BADGE.ID
    INNER JOIN EMP ON BADGE.EMPID = EMP.ID
    WHERE EMP.VISITOR = 0
    AND EVENTS.EVENT_TIME_UTC - 0.125 > TO_DATE('20090101 00:00:00', 'RRRRMMDD hh24:mi:ss')
    AND EVENTS.EVENT_TIME_UTC - 0.125 < TO_DATE('20120101 23:59:59', 'RRRRMMDD hh24:mi:ss')
    FOR UPDATE of events.empid;
BEGIN
  for l in c1 loop 
    UPDATE events
      SET empid = l.empid
      WHERE CURRENT OF c1;
  end loop;
  COMMIT;
end;

edited Jun 3, 2013 at 15:44

answered Jun 3, 2013 at 15:16

hol

8,4615 gold badges37 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Toru Over a year ago

Be careful, "When SELECT FOR UPDATE queries multiple tables, it locks only rows whose columns appear in the FOR UPDATE clause": docs.oracle.com/cd/E11882_01/appdev.112/e25519/…

NicoRiff Over a year ago

Thanks you!, by the way I was missing the ';' inside the loop statement

hol Over a year ago

You are welcome. Now I also see it ... (about the missing semicolon ;)

Farzad Fallah · Accepted Answer · 2013-10-04 12:04:16Z

2

Solution without using implicit cursor (no locking issue):

Begin
  For X in (
        SELECT BADGE.EMPID, EVENTS.CARDNUM FROM EVENTS
        INNER JOIN BADGE ON EVENTS.CARDNUM = BADGE.ID
        INNER JOIN EMP ON BADGE.EMPID = EMP.ID
        WHERE EMP.VISITOR = 0 AND 
              EVENTS.EVENT_TIME_UTC - 0.125 > TO_DATE('20090101 00:00:00', 'RRRRMMDD hh24:mi:ss') AND 
              EVENTS.EVENT_TIME_UTC - 0.125 < TO_DATE('20120101 23:59:59', 'RRRRMMDD hh24:mi:ss')
    )
  Loop
    UPDATE EVENTS
    SET EVENTS.EMPID = X.EMPID
    WHERE EVENTS.CARDNUM = X.CARDNUM;
  END LOOP;
  COMMIT;
END;

edited Oct 4, 2013 at 12:04

Farzad Fallah

6015 silver badges9 bronze badges

answered Jun 3, 2013 at 16:58

Mohsen Heydari

7,2924 gold badges33 silver badges47 bronze badges

2 Comments

Shannon Severance Over a year ago

That is syntactic sugar. There is still a cursor, see ""Cursor FOR LOOP statement" docs.oracle.com/cd/E11882_01/appdev.112/e25519/…

Shannon Severance Over a year ago

And you need a where clause. EMPID is being updated for all rows in the EVENTS table every time.

Collectives™ on Stack Overflow

Cursor in Oracle PL/SQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related