CHECK_TRAIN_RIDE

CREATE OR REPLACE
FUNCTION checkTrainRide (
    v_idTrainRide traincrew.idtrainride%TYPE
    ) RETURN BOOLEAN AS

  s BOOLEAN;
  g BOOLEAN;
  d BOOLEAN;
  sCounter INT;
  myCoachCounter INT;
BEGIN

s := FALSE;
g := FALSE;
d := FALSE;
sCounter := 0;

  FOR cur IN (SELECT
                staff.position
              FROM
                Staff
                  INNER JOIN
                  TrainCrew ON traincrew.idstaff = staff.idstaff
                  WHERE traincrew.idTrainRide = v_idTrainRide) LOOP

  IF(cur.position = 's') THEN
    s := TRUE;
    sCounter := sCounter + 1;
  END IF;

  IF(cur.position = 'd') THEN
    d := TRUE;
  END IF;

  IF(cur.position = 'g') THEN
    g := TRUE;

  END IF;
  END LOOP;

  SELECT
    train.coachCount
  INTO
    myCoachCounter
  FROM
    Train
    INNER JOIN
      TrainRide ON TrainRide.idTrain = Train.idTrain
      WHERE TrainRide.idTrainRide = v_idTrainRide;

  IF (s = TRUE AND d = TRUE AND g = TRUE) THEN
    IF(sCounter = myCoachCounter) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
      END IF;
  ELSE
    RETURN FALSE;
  END IF;
END;