5. Embedded QUEL for Ada : Sample Applications : The Department-Employee Master/Detail Application
 
Share this page                  
The Department-Employee Master/Detail Application
This application uses two database tables joined on a specific column. This typical example of a department and its employees demonstrates how to process two tables as a master and a detail.
The program scans through all the departments in a database table, in order to reduce expenses. Department information is stored in program variables. Based on certain criteria, the program updates department and employee records. The conditions for updating the data are the following:
Departments:
If a department has made less than $50,000 in sales, the department is dissolved.
Employees:
If an employee was hired since the start of 1985, the employee is terminated.
If the employee's yearly salary is more than the minimum company wage of $14,000 and the employee is not nearing retirement (over 58 years of age), the employee takes a 5% pay cut.
If the employee's department is dissolved and the employee is not terminated, the employee is moved into a state of limbo (the "toberesolved" database table, described below) to be resolved by a supervisor.
This program uses two cursors in a master/detail fashion. The first cursor is for the Department table, and the second is for the Employee table. The create statements used to create the tables are shown below. The cursors retrieve all the information in their respective tables, some of which is updated. The cursor for the Employee table also retrieves an integer date interval whose value is positive if the employee was hired after January 1, 1985.
Each row that is scanned, both from the Department table and the Employee table, is recorded into the system output file. This file serves as a log of the session and as a simplified report of the updates that were made.
Each section of code is commented for the purpose of the application and also to clarify some of the uses of the EQUEL statements. The program illustrates table creation, multi-query transactions, all cursor statements and direct updates. For purposes of brevity, error handling on data manipulation statements is simply to close down the application.
The following two create statements describe the Employee and Department database tables:
##    create dept
##         (name      = c12,   -- Department name 
##          totsales  = money, -- Total sales 
##          employees = i2)    -- Number of employees

##    create employee
##        (name       = c20,   -- Employee name 
##         age        = i1,    -- Employee age
##         idno       = i4,    -- Unique employee id
##         hired      = date,  -- Date of hire 
##         dept       = c10,   -- Employee department 
##         salary     = money) -- Yearly salary

-- Package:   Long_Float_Text_IO
-- Purpose:   Create text I/O package for LONG_FLOAT so as not to
--            conflict with the default G_FLOAT format. This
--            example assumes that the ACS SET PRAGMA command has
--            been issued for LONG_FLOAT.

with text_io;
package long_float_text_io is new text_io.float_io(long_float);
-- Package:   Trap_Error
-- Procedure: Close_Down
-- Purpose:   To trap Ingres runtime error messages. This
--            package defines the procedure Close_Down which is 
--            called when a Ingres error is returned. The 
--            procedure Close_Down is
--            passed to the runtime system via IIseterr.
--            When Close_Down is called, the error is printed 
--            and the database session is terminated. Any open
--            transactions and cursors are implicitly closed.
-- Parameters:
--             ingerr - Integer containing Ingres 
--             error number.

with text_io;                use text_io;
## with EQUEL;

##      package Trap_Error is

            function Close_Down(ingerr: Integer) return Integer;
            pragma export_function(Close_Down);
            ingres_error: Exception;

##      end Trap_Error;
##      package body Trap_Error is

##          function Close_Down(ingerr: Integer) return Integer is
##                error_text: String(1..200);
##          begin
##            inquire_ingres (error_text = errortext)
##            exit
              put_line("Closing down because of database error:");
              put_line(error_text);
              raise ingres_error;
              return ingerr;
##          end Close_Down;

##      end Trap_Error;
-- I/O utilities
with text_io;                       use text_io;
with integer_text_io;               use integer_text_io;
with short_integer_text_io;         use short_integer_text_io;
with short_short_integer_text_io;   use short_short_integer_text_io;
with float_text_io;                 use float_text_io;
with long_float_text_io;            use long_float_text_io;
with trap_error;                    use trap_error;

## with EQUEL;
-- Procedure: Process_Expenses -- MAIN
-- Purpose:   Main body of the application. Initialize the
--            database, process each department, and terminate 
--            the session.
-- Parameters:
--            None

##      procedure Process_Expenses is

              -- Function: Init_Db
              -- Purpose:  Initialize the database.
              --           Connect to the database, and abort on
              --           error. Before processing departments 
              --           and employees create the table for
              --            employees who lose their department,
              --           "toberesolved".
              --            Initiate the multi-statement
              --           transaction.
              -- Parameters:
              --            None
              -- Returns:
              --            TRUE is initialized, FALSE if error.

##            function Init_Db return Boolean is

##                     create_err: Integer;

##            begin
##                    ingres personnel

                    put_line("Creating ""To_Be_Resolved"" table.");
##                    create toberesolved
##                             (name    = char(20),
##                              age     = integer1,
##                              idno    = integer4,
##                              hired   = date,
##                              dept    = char(10),
##                              salary  = money)

                      -- Was the create successful ?
##                    inquire_ingres (create_err = errorno)
                      if (create_err > 0) then
                       put_line("Fatal error on table creation.");
                          return FALSE;
                      else
                          -- Inform Ingres runtime system 
                          -- about the errorhandler. All errors
                          -- from here on close down the
                          -- application.
                          IIseterr(Close_Down'Address);
##                        begin transaction
                          return TRUE;
                      end if;

##            end Init_Db;
              -- Procedure: End_Db
              -- Purpose:   Commit the multi-statement transaction 
              --            and access to the database.
              -- Parameters:
              --            None

##            procedure End_Db is
##            begin
##                  end transaction
##                  exit
##            end _Db;
              -- Procedure:  Process_Employees
              -- Purpose:    Scan through all the employees for a
              --         particular department. Based on given
              --         conditions the employee may be or take a
              --         salary reduction.
              --          1.  If an employee was hired since 
              --             1985 then the employee is terminated.
              --          2. If the employee's yearly salary is
              --             more than the minimum company wage of
              --             $14,000 and the employee is not close
              --             to retirement (over 58 years of age),
              --             then the employee takes a 5% salary 
              --             reduction.
              --          3. If the employee's department is
              --             dissolved and the employee is not
              --             terminated, then the employee is
              --              moved into the "toberesolved" table.
              -- Parameters:
              --     dept_name    - Name of current department.
              --     deleted_dept - Is department dissolved?
              --     emps_term    - Set locally to record how many
              --                    employees were terminated
              --                     for the current department.

##            procedure Process_Employees
##                             (dept_name:      in String;
##                             deleted_dept:   in Boolean;
##                              emps_term:      in out Integer) is

##             salary_reduc: constant float = 0.95;
##             min_emp_salary: constant float := 14000.00;
##             nearly_retired: constant Short_Short_Integer := 58;

              -- Emp_Rec corresponds to the "employee" table
##            type Emp_Rec is
##                  record
##                           name:           String(1..20);
##                           age:            Short_Short_Integer;
##                                  idno:           Integer;
##                                  hired:          String(1..25);
##                                  salary:         Float;
##                                  hired_since_85: Integer;
##                          end record;
##                  emp: Emp_Rec;

##                no_rows:   Integer;          -- Cursor control

                  title:     String(1..12);    -- Formatting values 
                  descript:  String(1..25);
## begin

        -- Note the use of the Ingres function to find out
        -- who was hired since 1985.

##              range of e is employee

##              declare cursor empcsr for
##             retrieve (e.name, e.age, e.idno, e.hired, e.salary,
##                       res = int4(interval("days",
##                       e.hired-date("01-jan-1985"))))
##                  where e.dept = dept_name
##                  for direct update of (name, salary)

                no_rows := 0;
                emps_term := 0; -- Record how many

##              open cursor empcsr

                while (no_rows = 0) loop

##            retrieve cursor empcsr (emp.name, emp.age, emp.idno,
##                                     emp.hired, emp.salary,
##                                     emp.hired_since_85)
##                inquire_equel (no_rows = endquery)

                  if (no_rows = 0) then

                      -- Terminate if new employee
                      if (emp.hired_since_85 > 0) then

##                        delete cursor empcsr
                          title := "Terminated: ";
                          descript := "Reason: Hired since 1985.";
                           emps_term := emps_term + 1;

                 -- Reduce salary if large and not nearly retired
                      elsif (emp.salary > MIN_EMP_SALARY) then

                           if (emp.age < NEARLY_RETIRED) then
##                                 replace cursor empcsr
##                                    (salary = 
##                                     salary * SALARY_REDUC)
                                   title := "Reduction: ";
                                   descript :=
                                             "Reason: Salary. ";
                           else
                                   -- Do not reduce salary
                                   title := "No Changes: ";
                                 descript := "Reason: Retiring. ";
                           end if;

                      -- Leave employee as is - low salary
                      else

                                   title = "No Changes: ";
                                   descript = "Reason: Salary. ";

                      end if;
                -- Was employee's department dissolved ? 
                if (deleted_dept) then
##                          append to toberesolved (e.all)
##                               where e.idno = emp.idno
##                          delete cursor empcsr
                end if;

                -- Log the employee's information 
                put(" " & title & " ");
                put(emp.idno, 6);
                put(", & emp.name & ", ");
                put(emp.age, 3);
                put(", ");
                put(emp.salary, 8, 2, 0);
                put_line(" ; " & descript);

            end if; -- If a row was retrieved

        end loop;   -- Continue with cursor loop

##      close cursor empcsr

## end Process_Employees;
-- Procedure:   Process_Depts
-- Purpose:     Scan through all the departments, processing
--              each one. If the department has made less
--              than $50,000 in sales, then the department
--              is dissolved. 
--              For each department process all the
--              employees (they may even be moved to another
--              database table).
--              If an employee was terminated, then update
--              the department's employee counter.
-- Parameters:
--              None

## procedure Process_Depts is

            MIN_TOT_SALES: constant := 50000.00;

            -- Dept_Rec corresponds to the "dept" table
##          type Dept_Rec is
##                record
##                       name: String(1..12);
##                       totsales: Long_Float;
##                       employees: Short_Integer;
##                end record;
##          dpt: Dept_Rec;

##       emps_term: Integer := 0;   -- Employees terminated 
         deleted_dept: Boolean;      -- Was the dept deleted? 
         dept_format: String(1..20); -- Formatting value
##       no_rows:     Integer;       -- Cursor control
##    begin
##        range of d is dept

##        declare cursor deptcsr for
##           retrieve (d.name, d.totsales, d.employees)
##           for direct update of (name, employees)
      no_rows := 0;
      emps_term := 0;
##    open cursor deptcsr

      while (no_rows = 0) loop
##          retrieve cursor deptcsr     (dpt.name,
##                                     dpt.totsales,
##                                     dpt.employees)
##          inquire_equel (no_rows = endquery)
            if (no_rows = 0) then

                -- Did the department reach minimum sales?
                if (dpt.totsales < MIN_TOT_SALES) then
##                      delete cursor deptcsr
                        deleted_dept := TRUE;
                        dept_format := " -- DISSOLVED --";
                else
                        deleted_dept := FALSE;
                        dept_format := (1..20 => ' ');
                end if;

                -- Log what we have just done 
                put("Department: " & dpt.name &
                    ", Total Sales: ");
                put(dpt.totsales, 12, 3, 0);
                put_line(dept_format);

                -- Now process each employee in the department 
                Process_Employees(dpt.name,
                                  deleted_dept,
                                  emps_term);

                -- If employees were terminated, record it
                if (emps_term > 0 and not deleted_dept) then
##                       replace cursor deptcsr
##                       (employees = employees - emps_term)
                end if;

            end if;          -- If a row was retrieved

        end loop;            -- Continue with cursor loop

## close cursor deptcsr

## end Process_Depts;
## begin        -- MAIN program

            put_line("Entering application to process expenses.");
              if (Init_Db) then
                   Process_Depts;
                   End_Db;
              end if;
              put_line("Completion of application.");

              exception

                when ingres_error => -- Raised by Close_Down
                 put_line("Contact your database administrator.");

## end Process_Expenses;