/*  Department_Tree.sql  --  Marco Bommeljé, 1999
/
/   This SELECT PROCEDURE shows how to produce a list of
/   a tree structure implemented as a self-association.
/   The Department_Tree procedure returns a list of the
/   organisation's hierarchy from the Department table in
/   the employee database.
/
*/
SET TERM ^;
CREATE PROCEDURE Department_Tree (
    Current_Dept_no             CHAR(3),
    Current_Depth               INTEGER )
RETURNS (
    Dept_No                     CHAR(3),
    Department                  VARCHAR(25),
    Head_Dept                   CHAR(3),
    Mngr_No                     SMALLINT,
    Budget                      NUMERIC(9, 2),
    Location                    VARCHAR(15),
    Phone_No                    VARCHAR(20),
    Depth                       INTEGER )
AS
/*  --------------------------------------------------------
/   SELECT PROCEDURE Department_Tree
/
/   Selects a Department with its sub-departments.
/
/   USAGE EXAMPLE:
/     SELECT * FROM Department_Tree(100, 0) 
/
/   DESCRIPTION:
/     Department_Tree selects the department whose Dept_No is
/     is passed as value of parameter Current_Dept_No and
/     selects it's sub-departments down the organisation's
/     hierarchy. The integer value passed as Current_Depth
/     is used as initial value for the Depth return parameter.
/
/   DATABASE:
/     Employee.gdb (Interbase example database)
/
/   PARAMETERS:
/     Current_Dept_no             CHAR(3)
/     Current_Depth               INTEGER
/
/   RETURNS:
/     Dept_No                     CHAR(3),
/     Department                  VARCHAR(25),
/     Head_Dept                   CHAR(3),
/     Mngr_No                     SMALLINT,
/     Budget                      NUMERIC(9, 2),
/     Location                    VARCHAR(15),
/     Phone_No                    VARCHAR(20),
/     Depth                       INTEGER
/
/   AUTHOR:
/     Marco Bommeljé, 1999
/   --------------------------------------------------------
*/
  DECLARE VARIABLE Sub_Dept_No INTEGER;
BEGIN
  /*  Depth is initialized if parameter value is NULL */
  IF (Current_Depth IS NULL) THEN
    Current_Depth = 0 ;
  /*  Row SELECT retrieves current Department */
  SELECT Dept_No, Department, Head_Dept, Mngr_No, Budget,
      Location, Phone_No, :Current_Depth
    FROM Department
    WHERE Dept_No = :Current_Dept_no
    INTO :Dept_No, :Department, :Head_Dept, :Mngr_No, :Budget,
      :Location, :Phone_No, :Depth ;
  /*  Exit without a result set if current Department does not exist. */
  IF ( Dept_no IS NULL ) THEN
    EXIT;
  /*  Return the current Department row */
  SUSPEND;
  /*  FOR SELECT sub-departments of current Department */
  FOR SELECT Dept_No
    FROM Department
    WHERE Head_Dept = :Current_Dept_no
    ORDER BY Dept_No
    INTO :Sub_Dept_No
  DO BEGIN
    /*  Now recurse ... */
    FOR SELECT Dept_No, Department, Head_Dept, Mngr_No, Budget,
        Location, Phone_No, Depth
      FROM Department_Tree( :Sub_Dept_No, :Current_Depth + 1 )
      INTO :Dept_No, :Department, :Head_Dept, :Mngr_No, :Budget,
        :Location, :Phone_No, :Depth
    DO BEGIN
      SUSPEND ;
    END
  END
END^
SET TERM ;^
COMMIT ;
/*
/		end_of_script
*/