lundi 18 septembre 2017

Looping through a cursor with a condition on an updated field [PLSQL]

I am currently implementing a PL/SQL script, which balances a value in two lists.

Consider this example as background:

  • Rec_1 | 2
  • Rec_2 | 1
  • Rec_3 | 2

  • Rec_A | -1
  • Rec_B | -3
  • Rec_C | -2

I want to loop through all these values one-by-one and settle as much as possible, i.e. that after the first settlement Rec_1 should be 1, Rec_A should be 0. Afterwards, Rec_1 will be settled with Rec_B such that it gets 1, Rec_B gets -2, and so on.

I want to use two cursors to do this, and update the values in its own procedure (or function, if that is necessary), since there is a little more to do than just update this value.

Now, here is my challenge: How do I know which cursor to fetch after a settlement has happened?

Right now, my code for this function looks like this:

PROCEDURE SettleLists (
  ListNegV SYS_REFCURSOR,
  ListPosV SYS_REFCURSOR
) IS
  currentNegV TABLENAME%ROWTYPE;
  currentPosV TABLENAME%ROWTYPE;
BEGIN
  FETCH ListNegV INTO currentNegV;
  FETCH ListPosV INTO currentPosV;
  LOOP
    EXIT WHEN ListNegV%NOTFOUND;
    EXIT WHEN ListPosV%NOTFOUND;
    IF (currentNegV.NUMERICVALUE < 0)
    THEN
      IF (currentPosV.NUMERICVALUE > 0)
      THEN
        Settle(currentPosV, currentNegV);
      ELSE 
        FETCH ListPosV INTO currentPosV;
      END IF;
    ELSE 
      FETCH ListNegV INTO currentNegV;
    END IF;  
  END LOOP;
END;

Inside the settle procedure, there will be an UPDATE on both records. Since the variables and cursor values are not updated, this will produce an infinite loop. I could update the parameter of settle when the record is updated in the database as well, but since I am not used to cursors, you might have a better idea.

I could consider the cursor to be strongly typed, if that makes any difference. If there is a better way than using a cursor, feel free to suggest it.

Finally, I was playing around with SELECT FOR UPDATE and UPDATE WHERE CURRENT OF, but it did not seem to work when passing the cursor to a procedure in between. If anyone has some idea on this, I would also appreciate your help.

Aucun commentaire:

Enregistrer un commentaire