DAIS_4_2nd

CREATE OR REPLACE
PROCEDURE PocetJizd
AS
CURSOR cursStaff IS SELECT * FROM Staff;
CURSOR cursT IS SELECT * FROM Train;
CURSOR cursTC IS SELECT * FROM TrainCrew;

counter INT;
maxCounter INT;
maxID INT;

BEGIN
maxCounter := 0;
  FOR cStaff IN cursStaff LOOP
    SELECT COUNT(*) INTO counter FROM TrainCrew WHERE idStaff = cStaff.idStaff;
  DBMS_OUTPUT.PUT_LINE('NAME: ' || cStaff.fname || ' LNAME: ' || cStaff.lname || ' COUNTER: ' || counter);
  IF  (counter > maxCounter) THEN
    maxCounter := counter;
    maxId := cStaff.idStaff;
  END IF;
  END LOOP;

  FOR cTC IN cursTC LOOP
    IF (cTC.idStaff = maxID) THEN
      FOR cT IN cursT LOOP
      DBMS_OUTPUT.PUT_LINE('S_FROM: ' || cT.idStation || ' S_TO: ' || cT.idStation1);
      END LOOP;
    END IF;
  END LOOP;
END;