CODEWITHSUNDEEP
sqlplsql
koda
koda

Reputation: 49

PL/SQL Procedure Output

I'm having trouble correctly displaying the results of my procedure. When I execute the procedure that I created. The output should be 'FALSE' since DonorID: 303 has IDStatus = 20 and Paymonths = 0. However, I am getting 'TRUE.' Can someone please help?

CREATE OR replace PROCEDURE ddpay_sp (donorid IN NUMBER,
                                      completed_pledge OUT BOOLEAN)
IS
  CURSOR donor_cur IS
    SELECT idstatus,
           paymonths
    FROM   dd_pledge
    WHERE  iddonor = donorid;

p_idstatus  NUMBER(2);
p_paymonths NUMBER(3);
BEGIN
  OPEN donor_cur;
  LOOP
    FETCH donor_cur
    INTO  p_idstatus,
          p_paymonths;
    EXIT WHEN donor_cur%NOTFOUND;
    IF p_idstatus = 20 AND p_paymonths = 0 THEN
      completed_pledge := FALSE;
    ELSE
      completed_pledge := TRUE;
      EXIT;
    END IF;
  END LOOP;
  CLOSE donor_cur;
END ddpay_sp;

DECLARE
  status BOOLEAN;
BEGIN
  ddpay_sp(303, status);
  IF status = TRUE THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;

Upvotes: 0

Views: 73

Answers (1)

Andrew Sayer
Andrew Sayer

Reputation: 2336

If you have multiple rows in your table that match iddonor = donorid, Oracle will loop through them until it finds one that doesn't match your p_idstatus = 20 AND p_paymonths = 0 condition and then exit from the loop with true, it will only return 'FALSE' if they all match on the second condition. If you have no rows that match iddonor = donorid, then your check will show 'FALSE' Demo

You need to be clear what should happen when there are multiple rows in dd_pledge which have the input iddonor value. E.g. if you wish for it to return false if there are any matching rows which have idstatus=20 and paymonths=0 then you could make the loop exit on this condition instead:

CREATE OR replace PROCEDURE ddpay_sp (donorid IN NUMBER,
                                      completed_pledge OUT BOOLEAN)
IS
  CURSOR donor_cur IS
    SELECT idstatus,
           paymonths
    FROM   dd_pledge
    WHERE  iddonor = donorid;

p_idstatus  NUMBER(2);
p_paymonths NUMBER(3);
BEGIN
  
  OPEN donor_cur;
  LOOP
    FETCH donor_cur
    INTO  p_idstatus,
          p_paymonths;
    EXIT WHEN donor_cur%NOTFOUND;
    IF p_idstatus = 20 AND p_paymonths = 0 THEN
      completed_pledge := FALSE;
      EXIT;
    ELSE
      completed_pledge := TRUE;
    END IF;
  END LOOP;
  CLOSE donor_cur;
END ddpay_sp;
/

Demo showing the difference due to exit placement

Upvotes: 1

Related Questions