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

REGIONS_SELECT

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

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

Описание

(Нет описания для процедуры REGIONS_SELECT)

Определение

CREATE PROCEDURE REGIONS_SELECT(
    PARENTREGIONID INTEGER,
    REGIONTYPEID INTEGER)
RETURNS (
    REGIONID INTEGER,
    REGIONLEVEL INTEGER,
    PARENTID INTEGER,
    REGIONTYPE INTEGER,
    REGIONNAME VARCHAR(100),
    REGIONFULLNAME VARCHAR(255),
    REGIONADMINNANE VARCHAR(255),
    COMMENT VARCHAR(255))
AS
declare variable REGIONNAMES varchar(255);
begin
  for
    select REGIONS.REGIONID, 0, REGIONS.PARENTID, REGIONS.REGIONTYPE,
        REGIONS.REGIONNAME, REGIONS.REGIONFULLNAME, REGIONS.COMMENT
      from REGIONS
      where ((REGIONS.PARENTID = :PARENTREGIONID)
             or ((:PARENTREGIONID is null) and (REGIONS.PARENTID is null)))
        and (coalesce(REGIONS.REGIONTYPE,0) <= coalesce(:REGIONTYPEID,100))
    order by coalesce(REGIONS.REGIONTYPE,10), REGIONS.REGIONNAME
    into :REGIONID, :REGIONLEVEL, :PARENTID, :REGIONTYPE,
         :REGIONNAME, :REGIONFULLNAME, :COMMENT

  do begin
    REGIONADMINNANE = REGIONFULLNAME;
    REGIONNAMES = REGIONFULLNAME;

    suspend;

    for
      select *
        from REGIONS_SELECT(:REGIONID, :REGIONTYPEID)
      into :REGIONID, :REGIONLEVEL, :PARENTID, :REGIONTYPE,
           :REGIONNAME, :REGIONFULLNAME, :REGIONADMINNANE, :COMMENT

    do begin
      REGIONADMINNANE = :REGIONADMINNANE || ' ' || REGIONNAMES;
      if (REGIONTYPE is null) then
        REGIONLEVEL = REGIONLEVEL + 10;
      else REGIONLEVEL = REGIONLEVEL + 1;

      suspend;
    end
  end
end