Cannot write if or select above begin on a procedure

Question

Im building a procedure for a class proyect where i have to invent some procedure related to my country football league. Right now im developing a procedure to show the results of a match, with the trainer, players, cards and goals after introducing the league round and a team. The thing is, i cant set the date of the round to a variable i created with an if due to a expected one of the following error on the if structure ( i have created just two rounds so that is the reason there are only two conditions), and i cant select data from tables into the other variables i have created for storing the data that doesnt need to be on the cursor due to being unique for each match, the referees names and the trainers names, but there is also another "expecting" error. Any help? Thank you in advance! PS: The rest of the code is still on development so there could be some incomplete parts. Arbitro stands for referee, entrenador stands for trainer or manager, nombre stands for name and partido stands for match Error:

create or replace PROCEDURE COMPROBARPARTIDO(JORNADA IN NUMBER, EQUIPO IN VARCHAR2) AS 

FECHA DATE;
ELOCAL VARCHAR2(30);
EVISITANTE VARCHAR2(30);
ARBIP VARCHAR2(30);
ASISTENTE VARCHAR2(30);
IF JORNADA = 1 THEN
FECHA:=4-03-2021;
ELSIF JORNADA = 2 THEN
FECHA:=13-03-2021;
ELSE
    DBMS_OUTPUT.PUT_LINE('ERROR');
END IF;

SELECT APR.NOMBRE INTO ARBIP, ASS.NOMBRE INTO ASISTENTE, ENL.NOMBRE INTO ELOCAL, ENV.NOMBRE INTO EVISITANTE
FROM  PARTIDO_JUGADOR PA
INNER JOIN PARTIDO P
ON PA.ID_PARTIDO = P.ID
INNER JOIN EQUIPO EL 
ON P.ID_LOCAL =EL.ID
INNER JOIN EQUIPO EV
ON P.ID_VISITANTE = EV.ID
INNER JOIN ENTRENADOR ENL
ON EL.ID_ENTRENADOR = ENL.ID
INNER JOIN ENTRENADOR ENV
ON EV.ID_ENTRENADOR = ENV.ID
INNER JOIN PARTIDO_ARBITRO PAR
ON P.ID = PAR.ID_PARTIDO
INNER JOIN ARBITRO APR
ON PAR.ID_ARBITROC = APR.ID
INNER JOIN ARBITRO ASS
ON PAR.ID_ARBITROA = ASS.ID
WHERE P.FECHA = FECHA AND (EQUIPO =EV.NOMBRE OR EQUIPO = EL.NOMBRE);


CURSOR C1 IS
SELECT J.NOMBRE, J.APELLIDO, EQ.NOMBRE
FROM PARTIDO P
INNER JOIN PARTIDO_JUGADOR PJ
ON P.ID = PARTIDO_JUGADOR.ID_PARTIDO
INNER JOIN JUGADOR J
ON PJ.ID_JUGADOR = J.ID
INNER JOIN EQUIPO EQ
ON J.ID_EQUIPO = EQ.ID
ORDER BY EQ.ID, J.COD_POSICION ;

BEGIN
  NULL;
END COMPROBARPARTIDO;

Re-read your notes about **basic** PL/SQL procedure's structure. Your code looks as if you grabbed bunch of statements from a bucket with a shovel and threw them into your editor, hoping that it'll compile. Well, it won't. Complete mess. — Littlefoot, May 31 '21 at 17:41
Sorry if it isnt understandable enough but i have already created another 2 procedures and i havent seen any similar problem. Im just asking about the if and the first select, it is my second month learning this language so sorry if it looks like a mess — Wazabi, May 31 '21 at 17:46
No problem about that, we were all beginners once. Your "problem" has nothing to do with `if` itself (apart from the fact that dates should be handled as dates, not as "strings" (which aren't enclosed into single quotes). Please, re-read what I said previously, especially the 1st statement. — Littlefoot, May 31 '21 at 17:50

score 1 · Accepted Answer · answered May 31 '21 at 17:58

About the structure I commented about:

create procedure as
  <declaration section>
begin
  <executable section>
end;

You put

executable statements (if) into declaration section, then
there's select that follows (invalid ... there should be only one into keyword), and
surprise! - a cursor declaration (?). BTW, you "declared" it and never used it
refer to dates as dates; either by using to_date with appropriate format mask, or use date literal (as I did)

Code that might compile (can't tell, don't have your tables); see if it helps.

CREATE OR replace PROCEDURE comprobarpartido(
  jornada  IN  NUMBER,
  equipo   IN  VARCHAR2
)
AS
  fecha       DATE;
  elocal      VARCHAR2(30);
  evisitante  VARCHAR2(30);
  arbip       VARCHAR2(30);
  asistente   VARCHAR2(30);

  CURSOR c1 IS
  SELECT j.nombre,
         j.apellido,
         eq.nombre
  FROM partido p
  INNER JOIN partido_jugador  pj ON p.id = partido_jugador.id_partido
  INNER JOIN jugador          j ON pj.id_jugador = j.id
  INNER JOIN equipo           eq ON j.id_equipo = eq.id
  ORDER BY eq.id,
           j.cod_posicion;

BEGIN 
  IF jornada = 1 THEN
     fecha := DATE '2021-03-04';
  ELSIF jornada = 2 THEN
     fecha := DATE '2021-03-13';
  ELSE
     dbms_output.put_line('ERROR');
  END IF;
  
  SELECT APR.NOMBRE, ASS.NOMBRE, ENL.NOMBRE, ENV.NOMBRE
    INTO ARBIP     , ASISTENTE , ELOCAL    , EVISITANTE
  FROM partido_jugador pa
  INNER JOIN partido          p   ON pa.id_partido = p.id
  INNER JOIN equipo           el  ON p.id_local = el.id
  INNER JOIN equipo           ev  ON p.id_visitante = ev.id
  INNER JOIN entrenador       enl ON el.id_entrenador = enl.id
  INNER JOIN entrenador       env ON ev.id_entrenador = env.id
  INNER JOIN partido_arbitro  par ON p.id = par.id_partido
  INNER JOIN arbitro          apr ON par.id_arbitroc = apr.id
  INNER JOIN arbitro          ass ON par.id_arbitroa = ass.id
  WHERE p.fecha = fecha
    AND (   equipo = ev.nombre
         OR equipo = el.nombre
        ); 
END comprobarpartido;

All solved! Thank you so much for the knowledge!! – Wazabi May 31 '21 at 21:58 — Wazabi, May 31 '21 at 21:58

score 0 · Answer 2 · answered May 31 '21 at 19:01

0

To set declaration values according to conditions, you can use case. For example:

fecha date := 
    case jornada
        when 1 then date '2021-03-04'
        when 2 then date '2021-03-13'
    end;

answered May 31 '21 at 19:01

William Robertson

15,273
4
38
44

Cannot write if or select above begin on a procedure

2 Answers2