CREATE OR REPLACE  PACKAGE AlexMonitor.PKG_Server
AS
   TYPE cursorref is ref cursor;
   PROCEDURE GetRobotsByServerID(serverID in AlexMonitor.Server.Server_ID%type, out_var out cursorref);
   PROCEDURE GetStationsByServerID(serverID in AlexMonitor.Server.Server_ID%type, out_var out cursorref);
  
 
end PKG_Server;

CREATE OR REPLACE  PACKAGE BODY AlexMonitor.PKG_Server
   AS
   PROCEDURE GetRobotsByServerID(serverID in AlexMonitor.Server.Server_ID%type, out_var out cursorref)
       IS
   BEGIN
  
       OPEN out_var for select
           Robot_ID,
           RT_ID,
           SERVER_ID,
           Robot_Name,
           Robot_IP,
           Robot_Use_State,
           Robot_Port,
           Robot_Expect_Yield,
           Robot_Expect_UPH
        From AlexMonitor.RobotManagement  RM where RM.SERVER_ID = serverID;
     EXCEPTION WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-10000,'No data found, Please change the condition!');
    END GetRobotsByServerID; 
  
   PROCEDURE GetStationsByServerID(serverID in AlexMonitor.Server.Server_ID%type, out_var out cursorref)
       IS
   BEGIN
     
    OPEN out_var for select
        Station_ID,
        Station.Robot_ID,
        TestStation_ID,
        Station_Name,
        Station_IP,
        Station_Mac_Addr,
        Staion_Type
       
     From AlexMonitor.STATION JOIN AlexMonitor.ROBOTMANAGEMENT RM ON STATION.ROBOT_ID = RM.ROBOT_ID  where RM.SERVER_ID = serverID;
    EXCEPTION WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-10000,'No data found, Please change the condition!');
    END GetStationsByServerID; 

END PKG_Server;