PL/SQL Block Finding number of suppliers for each nation-CodePudding

I'm still new to PLSQL and am currently using TPCH Dataset to practice. I have been trying this for a while not but I can't seem to wrap my head around it and could use some advice. A rough overview of the dataset here.

Here is my code so far

DECLARE
countNationkey number (5);

BEGIN
    FOR QRow IN (   SELECT r_name, n_name, s_nationkey, r_regionkey, count(s_nationkey) INTO countNationkey
            FROM region, nation, supplier
            WHERE r_regionkey = n_regionkey
            AND n_nationkey = s_nationkey
            GROUP BY r_name, n_name, s_nationkey, r_regionkey
            HAVING count(s_nationkey) > 130 
            ORDER BY r_name )

    LOOP
            dbms_output.put_line( rpad('R_NAME', 15) || rpad('N_NAME', 15) || 
                            rpad('COUNT(S_NATIONKEY)', 20) || chr(10) );
            dbms_output.put_line('----------------------------------------------------------'); 
        dbms_output.put_line( rpad(QRow.r_name, 15) || rpad(QRow.n_name, 15) || rpad(countNationkey, 15) );
    END LOOP;
END;

However, when I tried just the select query, I got an error

    SELECT r_name, n_name, s_nationkey, r_regionkey, count(s_nationkey) INTO countNationkey
                                                                         *
ERROR at line 1:
ORA-00905: missing keyword

If I remove the INTO countNationkey, I'm aware that countNationkey has no value thus I will get the output shown below.

R_NAME         N_NAME         COUNT(S_NATIONKEY)  

----------------------------------------------------------
ASIA           CHINA
R_NAME         N_NAME         COUNT(S_NATIONKEY)  

----------------------------------------------------------
ASIA           INDONESIA
R_NAME         N_NAME         COUNT(S_NATIONKEY)  

----------------------------------------------------------
EUROPE         GERMANY
R_NAME         N_NAME         COUNT(S_NATIONKEY)  

----------------------------------------------------------
MIDDLE EAST    SAUDI ARABIA

This is the expected outcome that I want

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

Could really use some help! Thanks in advance!

CodePudding user response：

Just remove INTO. It is required in PL/SQL, but not when select is part of a cursor (in your case, that's a cursor FOR loop).

Also, you'd then reference countNationkey with cursor variable's name (QROW.countNationkey), which also means that you don't need a local variable.

So:

BEGIN
   FOR QRow IN (  SELECT r_name,
                         n_name,
                         s_nationkey,
                         r_regionkey,
                         COUNT (s_nationkey) countNationkey
                    FROM region, nation, supplier
                   WHERE     r_regionkey = n_regionkey
                         AND n_nationkey = s_nationkey
                GROUP BY r_name,
                         n_name,
                         s_nationkey,
                         r_regionkey
                  HAVING COUNT (s_nationkey) > 130
                ORDER BY r_name)
   LOOP
      DBMS_OUTPUT.put_line (
            RPAD ('R_NAME', 15)
         || RPAD ('N_NAME', 15)
         || RPAD ('COUNT(S_NATIONKEY)', 20)
         || CHR (10));
      DBMS_OUTPUT.put_line (
         '----------------------------------------------------------');
      DBMS_OUTPUT.put_line (
            RPAD (QRow.r_name, 15)
         || RPAD (QRow.n_name, 15)
         || RPAD (QROW.countNationkey, 15));
   END LOOP;
END;