How to open a cursor a second time in a children loop in PL/SQL?-CodePudding

I need for a project to compare many records between each other but I before did a select inside a loop of the same select but I find it too slow and actually believe I can save up to 2 seconds if I can just save my query result into a cursor and use it but I don't know how I should do it.

FUNCTION RES_DETP
(P_OBJET IN NUMBER
 ,P_DATE_DEBUT IN DATE
 ,P_DATE_FIN IN DATE
 ,P_SOCIETE IN VARCHAR2
) RETURN BOOLEAN
IS
CURSOR LISTE_RESERVATION IS SELECT
    NOM cur_nom,
    PRENOM cur_prenom,
    RESERVATIONS.DATE_DEBUT_PRECIS cur_ddp,
    RESERVATIONS.DATE_FIN_PRECIS cur_dfp,
    FROM CLIENTS,RESERVATIONS,LIGNES_RESERVATIONS
    WHERE
    LIGNES_RESERVATIONS.OBJ_NUMERO = P_OBJET AND
    LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = P_SOCIETE AND
    LIGNES_RESERVATIONS.SOCIETES_ID = P_SOCIETE AND
    RESERVATIONS.SOCIETES_ID = P_SOCIETE AND
    LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
    LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
    CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
    CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
    CLIENTS.SOCIETES_ID = P_SOCIETE AND
    P_DATE_FIN > DATE_DEBUT_PRECIS AND 
    P_DATE_DEBUT < DATE_FIN_PRECIS
    order by RESERVATIONS.DATE_DEBUT_PRECIS;

CUR_RESERVATION     LISTE_RESERVATION%ROWTYPE;
COL_RESERVATION     LISTE_RESERVATION%ROWTYPE;

BEGIN

    OPEN LISTE_RESERVATION;
    LOOP
        FETCH LISTE_RESERVATION INTO CUR_RESERVATION;
        EXIT WHEN LISTE_RESERVATION%NOTFOUND;
        -- I want here to loop LISTE_RESERVATION again to compare each reservation together
    END LOOP;
END;

CodePudding user response：

You cannot; a cursor can only be read once and cannot be rewound so it is impossible.

What you could do open a second cursor inside each iteration of the loop with another DECLARE CURSOR LISTE_RESERVATION2 IS ... BEGIN ... END; block:

FUNCTION RES_DETP
(P_OBJET IN NUMBER
 ,P_DATE_DEBUT IN DATE
 ,P_DATE_FIN IN DATE
 ,P_SOCIETE IN VARCHAR2
) RETURN BOOLEAN
IS
  CURSOR LISTE_RESERVATION IS
    <your_query>;

  CUR_RESERVATION     LISTE_RESERVATION%ROWTYPE;
BEGIN
  OPEN LISTE_RESERVATION;
  LOOP
    FETCH LISTE_RESERVATION INTO CUR_RESERVATION;
    EXIT WHEN LISTE_RESERVATION%NOTFOUND;
    DECLARE
      CURSOR LISTE_RESERVATION2 IS
        <your_query>;

      CUR_RESERVATION2 LISTE_RESERVATION2%ROWTYPE;
    BEGIN
      OPEN LISTE_RESERVATION2;
      LOOP
        FETCH LISTE_RESERVATION2 INTO CUR_RESERVATION;
        EXIT WHEN LISTE_RESERVATION2%NOTFOUND;
        -- compare the two values
      END LOOP;
    END;
  END LOOP;
END;
/

However that is going to be slow.

It would probably be better to generate the query inside a sub-query factoring clause and then CROSS JOIN it with itself:

FUNCTION RES_DETP
(P_OBJET IN NUMBER
 ,P_DATE_DEBUT IN DATE
 ,P_DATE_FIN IN DATE
 ,P_SOCIETE IN VARCHAR2
) RETURN BOOLEAN
IS
CURSOR LISTE_RESERVATION IS
  WITH data (nom, prenom, ddp, dfp) AS (
    SELECT NOM,
           PRENOM,
           r.DATE_DEBUT_PRECIS,
           r.DATE_FIN_PRECIS
    FROM   CLIENTS c
           INNER JOIN RESERVATIONS r
           ON (   c.NUMERO = r.CLI_NUMERO
              AND c.SOCIETES_ID = r.CLI_SOCIETES_ID)
           INNER JOIN LIGNES_RESERVATIONS l
           ON (   l.RES_NUMERO = r.NUMERO
              AND l.RES_SOCIETES_ID = r.SOCIETES_ID)
    WHERE  l.OBJ_NUMERO = P_OBJET
    AND    l.OBJ_SOCIETES_ID = P_SOCIETE
    AND    l.SOCIETES_ID = P_SOCIETE
    AND    r.SOCIETES_ID = P_SOCIETE
    AND    c.SOCIETES_ID = P_SOCIETE
    AND    P_DATE_FIN > r.DATE_DEBUT_PRECIS
    AND    P_DATE_DEBUT < r.DATE_FIN_PRECIS
    order by r.DATE_DEBUT_PRECIS
  )
  SELECT d1.nom    AS nom1,
         d1.prenom AS prenom1,
         d1.ddp    AS ddp1,
         d1.dfp    AS dfp1,
         d2.nom    AS nom2,
         d2.prenom AS prenom2,
         d2.ddp    AS ddp2,
         d2.dfp    AS dfp2
  FROM   data d1
         CROSS JOIN data d2
  -- You can even add filters here
  -- WHERE ...
  ;

  CUR_RESERVATION     LISTE_RESERVATION%ROWTYPE;
BEGIN
  OPEN LISTE_RESERVATION;
  LOOP
    FETCH LISTE_RESERVATION INTO CUR_RESERVATION;
    EXIT WHEN LISTE_RESERVATION%NOTFOUND;
    -- compare the two values
  END LOOP;
END;
/

But what you probably want to do is to not use any cursors or loops at all and do all the comparisons in a single SQL query as it will be much, much faster (since you will not be continually context switching between the PL/SQL and SQL scopes).