iBase.ru Forum

Код: Выделить всё

CREATE PROCEDURE BUILD_CONTREE(
    IOBJ_ID INTEGER,
    ISTATE1 INTEGER,
    ISTATE2 INTEGER)
RETURNS (
    CONTREE_ID INTEGER,
    PARENT_ID INTEGER,
    OBJ_ID INTEGER,
    RTYPE_ID INTEGER,
    LNAME VARCHAR(250),
    CODE VARCHAR(5),
    ORDERINT INTEGER,
    STATE INTEGER)
AS
DECLARE VARIABLE VNOREC_ID INTEGER;
DECLARE VARIABLE VCONTREE_ID INTEGER;
DECLARE VARIABLE VRTYPE_ID INTEGER;
BEGIN
  VNOREC_ID=0;
  FOR SELECT CONTREE_ID, PARENT_ID, OBJ_ID, RTYPE_ID, LNAME, CODE, ORDERINT, STATE FROM CONTREE
  WHERE OBJ_ID=:IOBJ_ID AND (RTYPE_ID=1 OR RTYPE_ID=2 OR RTYPE_ID=3) AND STATE=1
  ORDER BY ORDERINT
  INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
  DO BEGIN
    SUSPEND;
    VCONTREE_ID = :CONTREE_ID;
    VRTYPE_ID   = :RTYPE_ID + 3;

    IF (NOT EXISTS (SELECT * FROM CONTREE
                    WHERE PARENT_ID=:CONTREE_ID AND (STATE=:ISTATE1 OR STATE=:ISTATE2)
                   )) THEN
      BEGIN
        VNOREC_ID = :VNOREC_ID - 1;
        FOR SELECT :VNOREC_ID, :VCONTREE_ID, :OBJ_ID, :VRTYPE_ID, '', '<...>', 1, :STATE FROM ZX
        INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
        DO SUSPEND;
      END

    FOR SELECT CONTREE_ID, PARENT_ID, OBJ_ID, RTYPE_ID, LNAME, CODE, ORDERINT, STATE FROM CONTREE
    WHERE PARENT_ID=:CONTREE_ID AND (STATE=:ISTATE1 OR STATE=:ISTATE2)
    ORDER BY ORDERINT
    INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
    DO BEGIN
      SUSPEND;
        IF (NOT EXISTS (SELECT * FROM CONTREE
                        WHERE PARENT_ID=:CONTREE_ID AND (STATE=:ISTATE1 OR STATE=:ISTATE2)
                       )) THEN
          BEGIN
            VCONTREE_ID = :CONTREE_ID;
            VNOREC_ID = :VNOREC_ID - 1;
            FOR SELECT :VNOREC_ID, :VCONTREE_ID, :OBJ_ID, 7, '', '<...>', 1, :STATE FROM ZX
            INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
            DO SUSPEND;
          END

      FOR SELECT CONTREE_ID, PARENT_ID, OBJ_ID, RTYPE_ID, LNAME, CODE, ORDERINT, STATE FROM CONTREE
      WHERE PARENT_ID=:CONTREE_ID AND (STATE=:ISTATE1 OR STATE=:ISTATE2)
      ORDER BY FMAIN DESC, ORDERINT
      INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
      DO SUSPEND;
    END

  END
END