CODEWITHSUNDEEP
oracle-databaseloopsplsqlcursor
Gamidron
Gamidron

Reputation: 133

SQL cursor fetch duplicate records

So my problem is as follows: I have 3 tables:
Categorie:
- Naam
- Omschrijving

Categorieabonnement:
- MailabonneeID
- CategorieNaam

Mailabonnee:
- ID
- Voornaam
- Achternaam
- Emailadres

First I get the ID in Mailabonnee based on Naam in Categorie. (through Categorieabonnement) Then I want to show those ID's that belong to Naam. This works fine. Now I want to get Voornaam, Achternaam and Emailadres. Here's the code below: 

DECLARE 
mailabonneeID  number;
voornaam       varchar2(20);
achternaam     varchar2(20);
emailadres     varchar2(20);
CURSOR getabonnee IS
SELECT CATEGORIEABONNEMENT.MAILABONNEEID 
FROM CATEGORIE
INNER JOIN CATEGORIEABONNEMENT ON CATEGORIEABONNEMENT.CATEGORIENAAM = CATEGORIE.NAAM
WHERE NAAM = 'Sport';

CURSOR getabonneeinfo IS
SELECT MAILABONNEE.VOORNAAM, MAILABONNEE.ACHTERNAAM, MAILABONNEE.EMAILADRES
FROM CATEGORIEABONNEMENT
INNER JOIN MAILABONNEE ON MAILABONNEE.ID = CATEGORIEABONNEMENT.MAILABONNEEID
WHERE MAILABONNEEID = mailabonneeID;

BEGIN
OPEN getabonnee;

LOOP
  FETCH getabonnee
  INTO  mailabonneeID;
  EXIT WHEN getabonnee%NOTFOUND;
  DBMS_OUTPUT.put_line(mailabonneeID);


OPEN getabonneeinfo;

LOOP
  FETCH getabonneeinfo
  INTO voornaam, achternaam, emailadres;
  EXIT WHEN getabonneeinfo%NOTFOUND;
  DBMS_OUTPUT.put_line(voornaam);
  DBMS_OUTPUT.put_line(achternaam);
  DBMS_OUTPUT.put_line(emailadres);
END LOOP;

CLOSE getabonneeinfo;

END LOOP;

CLOSE getabonnee;
END;
/

At the moment, the output just always shows the first 4 records in Mailabonnee, with the third record showing twice.

Any ideas how to fix this?

The current output is:

1
Jan Janssen [email protected]
Piet Zanden [email protected]
Klaas Vaak [email protected]
Klaas Vaak [email protected]
Fake1 de Faker a.nl
2
Jan Janssen [email protected]
Piet Zanden [email protected]
Klaas Vaak [email protected]
Klaas Vaak [email protected]
Fake1 de Faker a.nl

Though the expected output should be:
1
Jan Janssen [email protected]
2
Piet Zanden [email protected]

Upvotes: 1

Views: 2677

Answers (2)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Your WHERE clause in the second cursor is a tautology (aka 1=1).

Here's a fix (with implicit cursor to save space:)

DECLARE
   CURSOR getabonnee IS
      SELECT categorieabonnement.mailabonneeid, naam
        FROM categorie
       INNER JOIN categorieabonnement
          ON categorieabonnement.categorienaam = categorie.naam
       WHERE naam = 'Sport';

   CURSOR getabonneeinfo(p_id Categorieabonnement.mailabonneeid%TYPE) IS
      SELECT mailabonnee.voornaam, mailabonnee.achternaam, 
             mailabonnee.emailadres
        FROM categorieabonnement
       INNER JOIN mailabonnee
          ON mailabonnee.id = categorieabonnement.mailabonneeid
       WHERE -- mailabonneeid = mailabonneeid <- true=true
             mailabonneeid = p_id;
BEGIN
   FOR cc IN getabonnee LOOP
      DBMS_OUTPUT.put_line(cc.mailabonneeID);

      FOR cc2 IN getabonneeinfo(cc.mailabonneeID) LOOP
         DBMS_OUTPUT.put_line(cc2.voornaam);
         DBMS_OUTPUT.put_line(cc2.achternaam);
         DBMS_OUTPUT.put_line(cc2.emailadres);
      END LOOP;
   END LOOP;
END;

Of course in this case a single cursor (with 3 tables) should have worked!

In any case, your WHERE clause would have worked with variables if only you had used a prefix for your variables so that you don't run into variable shadowing problems.

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

After you exit the first loop mailabonneeID has value NULL, thus the second loop does not return anything.

Do this:

BEGIN
OPEN getabonnee;

LOOP
  FETCH getabonnee
  INTO  mailabonneeID;
  EXIT WHEN getabonnee%NOTFOUND;
  DBMS_OUTPUT.put_line(mailabonneeID);

   OPEN getabonneeinfo;
   LOOP
   FETCH getabonneeinfo
     INTO voornaam, achternaam, emailadres;
     EXIT WHEN getabonneeinfo%NOTFOUND;
     DBMS_OUTPUT.put_line(voornaam);
     DBMS_OUTPUT.put_line(achternaam);
     DBMS_OUTPUT.put_line(emailadres);
   END LOOP;
   CLOSE getabonneeinfo;

END LOOP;

CLOSE getabonnee;
END;

Another generic hint: declare varaibles based on column definition, i.e. in your case:

mailabonneeID  CATEGORIEABONNEMENT.MAILABONNEEID%TYPE;
voornaam       MAILABONNEE.VOORNAAM%TYPE;
achternaam     MAILABONNEE.ACHTERNAAM%TYPE;
emailadres     MAILABONNEE.EMAILADRES%TYPE;

It makes your code more robust against errors and modifications.

Upvotes: 0

Related Questions