PL SQL procedure don't work (probably cursor fault)

Question

I'm starting with PL/SQL, this is my first Procedure and i've trouble to make it work.

i've problem (think) with cursor. If you look my code are four comments; i've tested these four Update, but only one work! and i'm sure that two SELECT work, because if i send wrong parameter in input the select don't give a result.

of the four comments, I want that works:

UPDATE partecipa
  SET punti= somma
  WHERE ( (nomesquadra= nomesquadr) AND
          (nometorneo= nometorn));

If there are other errors (also logical) please tell me. I would like to improve.

Thank you all for the answers

my procedure:

create or replace PROCEDURE calcola_giorn (giornata IN INTEGER) IS
  -- si tenga presente che in realtà giornata=idPartita

  somma NUMBER;
  idcal NUMBER;
  nometorn VARCHAR2(100);
  idformaz NUMBER;
  nomesquadr VARCHAR2(100);

  CURSOR formazioni_di_giornata IS
    SELECT id, nomesquadra FROM formazione where idpartita= giornata;

  CURSOR giocatori_di_giornata IS
    SELECT votogiocatore FROM schiera WHERE idformazione= idformaz;
Begin
  SELECT idcalendario
    INTO idcal
    FROM partita
    WHERE id= giornata;

  SELECT nometorneo
    INTO nometorn
    FROM calendario
    WHERE id= idcal;

  FOR tupla_formazione IN formazioni_di_giornata LOOP
    somma:=0;

    FETCH formazioni_di_giornata INTO idformaz, nomesquadr;

    FOR tupla_giocatore IN giocatori_di_giornata LOOP
      somma:= somma + tupla_giocatore.votogiocatore;
      /*DON'T WORK*/-- UPDATE partecipa SET punti= 123;
    END LOOP;

    /*WORK*/-- UPDATE partecipa SET punti= 12;

    /*DON'T WORK*/-- UPDATE partecipa SET punti= 123 WHERE ( (nomesquadra= nomesquadr) AND (nometorneo= nometorn));

    /*DON'T WORK*/-- UPDATE partecipa SET punti= somma WHERE ( (nomesquadra= nomesquadr) AND (nometorneo= nometorn));
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001, 'An error was encountered - ' ||
                                    SQLCODE||' -ERROR- '||SQLERRM);
END;

Answer 1

I recommend you rewrite your procedure:

create or replace PROCEDURE calcola_giorn (giornata IN INTEGER) IS
  -- si tenga presente che in realtà giornata=idPartita

  somma NUMBER;
  nometorn VARCHAR2(100);
  nOuter_cursor_rows_fetched  NUMBER := 0;
Begin
  SELECT c.NOMETORNEO
    INTO nometorn
    FROM PARTITA p
    INNER JOIN CALENDARIO c
      ON c.ID = p.IDCALENDARIO
    WHERE p.ID = giornata;

  FOR tupla_formazione IN (SELECT id, nomesquadra
                             FROM formazione
                             where idpartita = giornata)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Inside outer loop, FORMAZIONE.ID=' ||
                         tupla_formazione.ID);

    nOuter_cursor_rows_fetched := nOuter_cursor_rows_fetched + 1;
    somma := 0;

    FOR tupla_giocatore IN (SELECT votogiocatore
                              FROM schiera
                              WHERE idformazione = tupla_formazione.nomesquadra)
    LOOP
      somma := somma + tupla_giocatore.votogiocatore;

      -- The following statement will set PUNTI to 123 on every row in
      -- PARTECIPA - are you sure this is what you wanted to do?

      UPDATE partecipa SET punti= 123;
    END LOOP;

    -- The following statement will set PUNTI to 12 on every row in
    -- PARTECIPA - are you sure this is what you wanted to do?

    UPDATE partecipa SET punti= 12;

    UPDATE partecipa
      SET punti = 123
      WHERE nomesquadra= tuplafomazione.nomesquadra AND
            nometorneo = nometorn;

    UPDATE partecipa
      SET punti = somma
      WHERE nomesquadra = tupla_formazione.nomesquadr AND
            nometorneo = nometorn;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('# rows fetched by outer cursor=' ||
                       nOuter_cursor_rows_fetched);
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001, 'Error: SQLCODE=' ||
                                    SQLCODE||' SQLERRM='''||SQLERRM || '''');
    ROLLBACK;
    RAISE;
END calcola_giorn;

I believe that @Allan must be right and that the outer loop (for tupla_formazione) is never being entered. The DBMS_OUTPUT.PUT_LINE at the top of this loop is there to demonstrate this. If you don't see any of these lines printed you might need to consider looking at the values which are input to the procedure.

Share and enjoy.

Answer 2

A for loop that invokes a cursor automatically fetches the row once for each loop. The fetch you added inside the loop isn't allowed.

The fact that you're not seeing an ORA-01001: invalid cursor error indicates that you're never getting to the interior of the loop: the formazioni_di_giornata cursor must not be returning any rows.

---

As an aside, I'd strongly suggest getting rid of the exception clause you're using. All it's doing is adding garbage to the error message.