I was asked to create a function or procedure to return the football team who scored the most goals in a competition.

CREATE OR REPLACE PROCEDURE EQUIPO_GOLEADOR 
AS
BEGIN
    SELECT * FROM PAISES P JOIN SELECCIONES S ON P.ID_PAIS = S.ID_PAIS WHERE GOLES_FAVOR =(SELECT MAX(GOLES_FAVOR) FROM SELECCIONES);
END;

I tried this but I received the error PLS-00428: an INTO clause is expected in this SELECT statement

CodePudding user response：

You didn't post tables' description so we can't tell what column exactly you need; I'll guess it is team (you'll know better and use that column name):

CREATE OR REPLACE PROCEDURE EQUIPO_GOLEADOR 
AS
  l_team selecciones.team%type;
BEGIN
    SELECT s.team
    into l_team
    FROM PAISES P JOIN SELECCIONES S ON P.ID_PAIS = S.ID_PAIS 
    WHERE GOLES_FAVOR =(SELECT MAX(GOLES_FAVOR) FROM SELECCIONES);
END;

It'll fail if query doesn't return anything (not very likely, I guess) or if two teams scored the same MAX number of goals; in that case, it depends - what do you want to return? One (if so, which one?); all of them (if so, in what format? As a collection? Just display them on the screen (use a loop, then)?) etc. etc.

But, generally speaking, that INTO clause is what Oracle was complaining about.

CodePudding user response：

If you are using a SQL SELECT statement within an anonymous block, a function, a procedure, etc (in PL/SQL - between the BEGIN and the END keywords) you must select INTO something so that PL/SQL can utilize a variable to hold your result from the query. It is important to note here that if you are selecting multiple columns, you must specify multiple variables or a record to insert the results of your query into.

for example:

SELECT 1 
INTO v_dummy 
FROM dual;

SELECT 1, 2 
INTO v_dummy, v_dummy2 
FROM dual;

It is also worth pointing out that if your SELECT <columns> FROM.... will return multiple rows, PL/SQL will throw an error. You should only expect to retrieve 1 row of data from a SELECT INTO.