CODEWITHSUNDEEP
oracle-databaseplsql
NicoRiff
NicoRiff

Reputation: 4883

Cursor in Oracle PL/SQL

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!

Upvotes: 2

Views: 374

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

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;

Upvotes: 2

hol
hol

Reputation: 8423

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;

Upvotes: 3

Related Questions