PL/SQL how to display data from select statement inside procedure?

Question

I am not sure how to display my select statement in my procedure. This is my code inside my procedure:

CREATE OR REPLACE PROCEDURE numberOfSupplier (X INT:=0)
AS  
    rName REGION.R_NAME%TYPE;
    nName NATION.N_NAME%TYPE;
    sNKeyC SUPPLIER.S_NATIONKEY%TYPE;   
BEGIN
FOR rec IN(
    SELECT R.R_NAME, N.N_NAME, COUNT(S.S_NATIONKEY)
    INTO rName, nName, sNKeyC
    FROM REGION R, NATION N, SUPPLIER S
    WHERE R.R_REGIONKEY = N.N_REGIONKEY
    AND S.S_NATIONKEY = N.N_NATIONKEY
    GROUP BY R.R_NAME, N.N_NAME
    HAVING COUNT(S.S_NATIONKEY) > X)
LOOP
    dbms_output.put_line('R_NAME'||rName);
    dbms_output.put_line('N_NAME'||nName);
    dbms_output.put_line('COUNT(S_NATIONKEY)'||sNKeyC);
END LOOP;
END;
/ 

--executing numberOfSupplier
EXECUTE numberOfSupplier(130);

This is what I get, which has no errors but not what I want:

SQL> EXECUTE numberOfSupplier(130);
     R_NAME
     N_NAME
     COUNT(S_NATIONKEY)
     R_NAME
     N_NAME
     COUNT(S_NATIONKEY)
     R_NAME
     N_NAME
     COUNT(S_NATIONKEY)
     R_NAME
     N_NAME
     COUNT(S_NATIONKEY)

What I want to get is this:

    R_NAME            N_NAME            COUNT(S.S_NATIONKEY)
------------------------- ------------------------- --------------------
ASIA              INDONESIA                  131
ASIA              CHINA                      145
MIDDLE EAST       SAUDI ARABIA                   132
EUROPE            GERMANY                    132

I am able to get the above result if I just execute the select statement, however I dont know how to put this select statement in my procedure and get the table above:

SELECT R.R_NAME, N.N_NAME, COUNT(S.S_NATIONKEY)
FROM REGION R, NATION N, SUPPLIER S
WHERE R.R_REGIONKEY = N.N_REGIONKEY
AND S.S_NATIONKEY = N.N_NATIONKEY
GROUP BY R.R_NAME, N.N_NAME
HAVING COUNT(S.S_NATIONKEY) > 130;

Can someone explain to me why and how to fix this. Thankyou very much.

Answer 1

Put headers out of the loop and then, in the loop, concatenate all values you'd want to display. Use RPAD to nicely align values.

Apart from that, you misused FOR loop; you don't select INTO within its select statement, but use cursor variable. I don't have your tables so I used Scott's, for illustration:

SQL> CREATE OR REPLACE PROCEDURE numberOfSupplier (X INT:=0)
  2  AS
  3  BEGIN
  4    dbms_output.put_line(rpad('R_NAME', 15, ' ') ||
  5                         rpad('N_NAME', 15, ' ') ||
  6                        'COUNT(S_NATIONKEY)'
  7                       );
  8    dbms_output.put_line(rpad('-', 14, '-') || ' ' ||
  9                         rpad('-', 14, '-') || ' ' ||
 10                         rpad('-', 14, '-'));
 11  FOR rec IN(
 12      SELECT d.dname R_NAME,
 13             e.ename N_NAME,
 14             COUNT(*) snkeyc
 15      FROM emp e join dept d on e.deptno = d.deptno
 16      where e.deptno = 10
 17      group by d.dname, e.ename
 18     )
 19  loop
 20    dbms_output.put_line(rpad(rec.r_name, 15, ' ') ||
 21                         rpad(rec.n_name, 15, ' ') ||
 22                         rec.sNKeyC
 23                        );
 24  END LOOP;
 25  END;
 26  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec numberofsupplier;
R_NAME         N_NAME         COUNT(S_NATIONKEY)
-------------- -------------- --------------
ACCOUNTING     KING           1
ACCOUNTING     CLARK          1
ACCOUNTING     MILLER         1

PL/SQL procedure successfully completed.

SQL>

Answer 2

Changing your code slightly and using cursors instead must do your job.

create or replace PROCEDURE numberOfSupplier (X INT:=0)
AS
    CURSOR rec IS SELECT R.R_NAME, N.N_NAME, COUNT(S.S_NATIONKEY) counter
    FROM REGION R, NATION N, SUPPLIER S
    WHERE R.R_REGIONKEY = N.N_REGIONKEY
    AND S.S_NATIONKEY = N.N_NATIONKEY
    GROUP BY R.R_NAME, N.N_NAME
    HAVING COUNT(S.S_NATIONKEY) > X; -- cursor to collect all the Objects
BEGIN
    dbms_output.put_line('R_NAME'||CHR(9)||'N_NAME'||CHR(9)||'COUNT(S_NATIONKEY)');
    dbms_output.put_line('--------------------------------------------------');
    FOR rec_obj IN rec LOOP  
        dbms_output.put_line(rec_obj.R_NAME||CHR(9)||CHR(9)||rec_obj.N_NAME||CHR(9)||CHR(9)||rec_obj.counter);
    END LOOP;
EXCEPTION  -- exception handlers begin
    WHEN no_data_found THEN --catches exception when No Data Found
        dbms_output.put_line('No Data Found');
    WHEN TOO_MANY_ROWS THEN -- More than 1 row seleced
    dbms_output.put_line('More than 1 row seleced');
    WHEN OTHERS THEN  -- handles all other errors
        ROLLBACK;
        dbms_output.put_line('I AM HERE!!!'
                               || sqlcode
                               || ' '
                               || sqlerrm);
END;
/