PocetJizd

CREATE OR REPLACE
PROCEDURE PocetJizd
AS
  CURSOR cursStaff IS SELECT s.idstaff, S.fname, S.lname, COUNT(Tr.idStaff) pocet FROM Staff S
    JOIN TrainCrew Tr ON (S.idStaff = Tr.idStaff) GROUP BY s.idstaff, s.fname,s.lname ORDER BY pocet DESC;
  v_max INT := 0; -- PREDPOKLADANY MAX. POCET JAZD
  v_temp_max INT;
  v_top_employ staff.idstaff%TYPE; -- EMPLOYER S NAJVACSIM POCTOM JAZD
  v_staff Staff%ROWTYPE;
  v_from INT;
  v_to INT;
BEGIN
  FOR cRow IN cursStaff
    LOOP
      v_temp_max := cRow.pocet;
      IF (v_temp_max > v_max) THEN
        v_max := v_temp_max;
        v_top_employ := cRow.idStaff; -- ZAPAMATAM SI ID EMPLOYERA S NAJVACSIM POCTOM JAZD
      END IF;
      DBMS_OUTPUT.put_line('NAME: ' || cRow.fname || ' SURNAME: ' || cRow.lname || ' POCET JAZD: ' || cRow.pocet);
  END LOOP;
  /* VYPIS TOP ZAMESTNANCA S JAZDAMI */
    SELECT * INTO v_staff FROM Staff WHERE (idstaff = v_top_employ);
    DBMS_OUTPUT.PUT_LINE('TOP EMPLOY: ');
    DBMS_OUTPUT.PUT_LINE('NAME: ' || v_staff.fname || ' SURNAME: ' || v_staff.lname || ' POCET JAZD: ' || v_max);

    FOR pCrew IN (SELECT * FROM TrainCrew WHERE idStaff = v_top_employ)
      LOOP
          FOR pTrain IN (SELECT t.idstation, t.idstation1 FROM Train T JOIN TrainRide Tr
          ON (t.idtrain = tr.idtrain) WHERE (tr.idtrainride = pCrew.idTrainRide))
            LOOP
              v_from := pTrain.idStation;
              v_to := pTrain.idStation1;
              DBMS_OUTPUT.PUT_LINE('FROM: ' || v_from || ' TO: ' || v_to);
            END LOOP;
      END LOOP;
END;