CODEWITHSUNDEEP
sqlarraysoracle-databaseloopsstored-procedures
MPaul
MPaul

Reputation: 2733

Looping through an SQL array

I'm trying to loop through a list of schools, and if there isn't already a cross reference between the school_id and the context_id in the association table, insert it.

I'm recieving the following errors:

Erreur(141,3): PL/SQL: SQL Statement ignored
Erreur(141,24): PLS-00321: expression 'IDS' is inappropriate as the left hand side of an assignment statement
Erreur(141,28): PL/SQL: ORA-00904: : invalid identifier
Erreur(143,3): PL/SQL: Statement ignored
Erreur(143,16): PLS-00302: component 'FIRST' must be declared

(first line in code block below is considered 136)

PROCEDURE APPLY_IMPLICITE(P_ID IN TBL_CONTEXTES.ID%TYPE, P_UID IN TBL_ECOLE_CONTEXTES.LAST_UID_MODIFICATION%TYPE)
IS
  TYPE IDS IS TABLE OF TBL_CONTEXTES.ID%TYPE INDEX BY PLS_INTEGER;
  ROW_COUNT NUMBER;
BEGIN
  -- Get the IDs of all the schools
  SELECT ECOLE_ID INTO IDS FROM MV_ECOLES;
  -- Loop through all the schools
  FOR i IN IDS.FIRST..IDS.LAST LOOP
    -- Verify if a row exists
    SELECT COUNT(*) INTO ROW_COUNT FROM TBL_ECOLE_CONTEXTES WHERE ECOLE_ID = IDS(i) AND ID = P_ID;
    -- If no row exist, insert.
    IF ROW_COUNT = 0 THEN
      INSERT INTO TBL_ECOLE_CONTEXTES (ID, ECOLE_ID, LAST_UID_MODIFICATION)
      VALUES (P_ID, IDS(i), P_UID);
    END IF;
  END LOOP;

END APPLY_IMPLICITE;

Upvotes: 0

Views: 132

Answers (1)

D Stanley
D Stanley

Reputation: 152501

You shouldn't need a loop. You can just insert all non-matching rows:

The query would look something like:

INSERT INTO TBL_ECOLE_CONTEXTES
    (ID, ECOLE_ID, LAST_UID_MODIFICATION)
SELECT PID, ECOLE_ID, P_UID 
FROM TBL_ECOLE_CONTEXTES 
WHERE ECOLE_ID NOT IN (SELECT IDS FROM FROM MV_ECOLES WHERE ID = P_ID)

Your table structure isn't completely clear but that should be the general idea.

Upvotes: 1

Related Questions