"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

GET_GRAINSTATE

Входные параметры

Выходные параметры

Описание

Получение состояния зерна по влажности и сорной примеси

Определение

CREATE PROCEDURE GET_GRAINSTATE(
    CROPID INTEGER,
    CLASSID INTEGER,
    HUMIDITY NUMERIC(4,1),
    DIRT NUMERIC(4,1))
RETURNS (
    HUMIDITYSTATE INTEGER,
    DIRTSTATE INTEGER)
AS
begin
  select GRAINSTATE.GRAINSTATEID
    from GRAINSTATE
    where (GRAINSTATE.QUALITYTYPEID = 102)
      and (GRAINSTATE.CROPID = :CROPID) and (GRAINSTATE.CLASSID = :CLASSID) and (GRAINSTATE.GRAINSTATEID <> -1)
      and (:HUMIDITY between GRAINSTATE.LOWERBOUND and GRAINSTATE.UPPERBOUND)
  into :HUMIDITYSTATE;

  if (:HUMIDITYSTATE is null) then
    select GRAINSTATE.GRAINSTATEID
      from GRAINSTATE
      where (GRAINSTATE.QUALITYTYPEID = 102)
        and (GRAINSTATE.CROPID = :CROPID) and (GRAINSTATE.CLASSID is null) and (GRAINSTATE.GRAINSTATEID <> -1)
        and (:HUMIDITY between GRAINSTATE.LOWERBOUND and GRAINSTATE.UPPERBOUND)
    into :HUMIDITYSTATE;

  if (:HUMIDITYSTATE is null) then
    HUMIDITYSTATE = -1;

  select GRAINSTATE.GRAINSTATEID
    from GRAINSTATE
    where (GRAINSTATE.QUALITYTYPEID = 103)
      and (GRAINSTATE.CROPID = :CROPID) and (GRAINSTATE.CLASSID = :CLASSID) and (GRAINSTATE.GRAINSTATEID <> -1)
      and (:DIRT between GRAINSTATE.LOWERBOUND and GRAINSTATE.UPPERBOUND)
  into :DIRTSTATE;

  if (:DIRTSTATE is null) then
    select GRAINSTATE.GRAINSTATEID
      from GRAINSTATE
      where (GRAINSTATE.QUALITYTYPEID = 103)
        and (GRAINSTATE.CROPID = :CROPID) and (GRAINSTATE.CLASSID is null) and (GRAINSTATE.GRAINSTATEID <> -1)
        and (:DIRT between GRAINSTATE.LOWERBOUND and GRAINSTATE.UPPERBOUND)
    into :DIRTSTATE;

  if (:DIRTSTATE is null) then
    DIRTSTATE = -1;

  suspend;
end