stored-proceduresplsqlpls-00103

Cannot write if or select above begin on a procedure

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:enter image description here

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;
Solution

  • About the structure I commented about:

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

    You put

    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;