7. Embedded SQL for Pascal : 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. 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 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 cursor is for the Employee table. Both tables are described in declare table statements at the start of the program. The cursors retrieve all the information in the 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, from both the Department table and the Employee table, is recorded in an output file. This file serves both 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 Embedded SQL statements. The program illustrates table creation, multi-statement transactions, all cursor statements, direct updates and error handling.
Sample Application
program Departments( input, output );
exec sql include sqlca;

{The department table}
exec sql declare dept table
     (name          char(12)   not null,  {Department name}
      totsales      money      not null,  {Total sales}
      employees     smallint   not null); {Number of employees}

{The employee table}
exec sql declare employee table
     (name          char(20)   not null,  {Employee name}
      age           integer1   not null,  {Employee age}
      idno          integer1   not null,  {Unique employee id}
      hired         date       not null,  {Date of hire}
      dept          char(12)   not null,  {Department of work}
      salary        money      not null); {Yearly salary}

{"State-of-Limbo" for employees who lose their department}
exec sql declare toberesolved table
     (name          char(20)   not null,  {Employee name}
      age           integer1   not null,  {Employee age}
      idno          integer1   not null,  {Unique employee id}
      hired         date       not null,  {Date of hire}
      dept          char(12)   not null,  {Department of work}
      salary        money      not null); {Yearly salary}
label
      exit_program;
exec sql begin declare section;
type
     String12 = varying[12] of Char;
     String20 = varying[20] of Char;
     String25 = varying[25] of Char;
     String200 = varying[200] of Char;
     Short_Short_Integer = [byte] -128 .. 127;
     Short_Integer = [word] -32768 .. 32767;
exec sql end declare section;

{
| 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;
type
    File_type = Text;
var
    log_file: File_type; {Log file to which to write.}

    {
    | Procedure:  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".
    | Parameters: None
    }

procedure Init_Db;
begin
        exec sql whenever sqlerror stop;
        exec sql connect personnel;

        {Create the table.}
         writeln(log_file,
                 'Creating ''To_Be_Resolved'' table.');
         exec sql create table toberesolved
                 (name    char(20) not null,
                        age     integer1 not null,
                        idno    integer  not null,
                        hired   date not null,
                        dept    char(12) not null,
                        salary  money not null);
end; {Init_Db}

{
| Procedure: End_Db
| Purpose:    Commit the multi-statement transaction and
|             end access to the database.
| Parameters: None
}

procedure End_Db;
begin
    exec sql commit;
    exec sql disconnect;
end; {End_Db}

{
| Procedure:  Close_Down
| Purpose:    Error handler called any time after Init_Db has been
|             successfully completed. In all cases, print the
|             cause of the error and abort the transaction,
|             backing out changes. Note that disconnecting
|             from the database will implicitly close any
|             open cursors.
| Parameters: None.
}

procedure Close_Down;
        exec sql begin declare section;
        var
                errbuf: String200;
        exec sql end declare section;
begin
        {Turn off error handling here}
        exec sql whenever sqlerror continue;

        exec sql inquire_sql (:errbuf = ERRORTEXT);
        writeln( 'Closing Down because of database error.' );
        writeln( errbuf );

        exec sql rollback;
        exec sql disconnect;

        goto exit_program;             {no return}
end; {Close_Down}

{
| Procedure: Process_Employees
| Purpose:   Scan through all the employees for a
|            particular department. Based on given
|            conditions, the employee may be terminated or
|            take a salary reduction.
|            1. If an employee was hired since 1985,
|               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), the employee
|               takes a 5% salary reduction.
|            3. If the employee's department is dissolved
|               and the employee is not terminated,
|               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:     Varying[ub] of Char;
         deleted_dept:     Boolean;
         var emps_term: Integer);

    label
        Close_Emp_Csr;
    exec sql begin declare section;
    const
        salary_reduc = 0.95;
    type
        {Emp_Rec corresponds to the "employee" table}
         Emp_Rec = record
            name:       String20;
            age:        Short_Short_Integer;
            idno:       Integer;
            hired:      String25;
            salary:     Real;
            hired_since_85: Integer;
        end;
    var
        erec:  Emp_Rec;
            dname: String12;
    exec sql end declare section;

    const
        min_emp_salary = 14000.00;
        nearly_retired = 58;
    var
        title: String12; {Formatting values}
        descript: String25;

    {
    | Note the use of the INGRES function to find out
    | who has been hired since 1985
    }
    exec sql declare empcsr cursor for
        select name, age, idno, hired, salary,
            int4(interval('days',
                hired-date('01-jan-1985')))
        from employee
        where dept = :dname
        for direct update of name, salary;

begin {Process_Employees}
    dname := dept_name;

    {
    | All errors from this point on close down
    | the application
    }
    exec sql whenever sqlerror call Close_Down;
    exec sql whenever not found goto Close_Emp_Csr;

    exec sql open empcsr;

    emps_term := 0; {Record how many}
    while (sqlca.sqlcode = 0) do
    begin
        exec sql fetch empcsr into :erec;

        if (erec.hired_since_85 > 0) then
            begin
                exec sql delete from employee
                    where current of empcsr;
                title := 'Terminated: ';
                descript := 'Reason: Hired since 1985.';
                emps_term := emps_term + 1;
        end else if (erec.salary > min_emp_salary) then
        begin {Will reduce salary if not nearly retired}
            if (erec.age < nearly_retired) then
            begin
                exec sql update employee
                    set salary =
                        salary * :salary_reduc
                    where current of empcsr;
                title := 'Reduction: ';
                descript := 'Reason: Salary. ';
            end else
            begin
                {Do not reduce salary}
                title := 'No Changes: ';
                descript := 'Reason: Retiring. ';
            end;
        end else {Else leave employee as is}
        begin
            title := 'No Changes: ';
            descript := 'Reason: Salary. ';
        end;

        {Was employee's department dissolved?}
        if (deleted_dept) then
        begin
            exec sql insert into toberesolved
                select *
                from employee
                where idno = :erec.idno;

            exec sql delete from employee
                where current OF empcsr;
        end;

        {Log the employee's information}
        write(log_file, ' ', title, ' ');
        write(log_file, erec.idno:6);
        write(log_file, ', ', erec.name, ', ');
        write(log_file, erec.age:3);
        write(log_file, ', ');
        write(log_file, erec.salary:8:2);
        writeln(log_file, ' ; ', descript);
    end;

Close_Emp_Csr:
    exec sql whenever not found continue;
    exec sql close empcsr;
end;

{
| 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;
    exec sql begin declare section;
    type
        {Dept_Rec corresponds to the "dept" table}
         Dept_Rec = record
                name: String12;
                totsales: Double;
                employees: Short_Integer;
        end;
    var
        dept: Dept_Rec;
        emps_term: Integer; {Employees terminated}
    exec sql end declare section;

    label
        Close_Dept_Csr;
    const
        min_tot_sales = 50000.00;
    var
        deleted_dept: Boolean; {Was the dept deleted?}
        dept_format: String20; {Formatting value}
        exec sql declare deptcsr cursor for
             select name, totsales, employees
             from dept
             for direct update of name, employees;
    begin {Process_Depts}
        emps_term := 0;

        {All errors from this point on close down the application}
        exec sql whenever sqlerror call Close_Down;
        exec sql whenever not found goto Close_Dept_Csr;

        exec sql open deptcsr;

        while (sqlca.sqlcode = 0) do
        begin
            exec sql fetch deptcsr into :dept;

            {Did the department reach minimum sales?}
            if (dept.totsales \ min_tot_sales) then
            begin
                exec sql delete from dept
                    where current of deptcsr;
                deleted_dept := TRUE;
                dept_format := ' -- DISSOLVED --';
            end else
            begin
                deleted_dept := FALSE;
                dept_format := ' ';
            end;

            {Log what we have just done}
            write(log_file,
                'Department: ', dept.name, ', Total Sales: ');
            write(log_file, dept.totsales:12:3);
            writeln(log_file, dept_format);

            {Now process each employee in the department}
            Process_Employees(dept.name,
                deleted_dept, emps_term);

            {If employees were terminated, record this fact}
            if ((emps_term > 0) and (not deleted_dept)) then
            begin
                exec sql update dept
                    set employees = :dept.employees - :emps_term
                    where current of deptcsr;
                end;
            end;

Close_Dept_Csr:
        exec sql whenever not found continue;
        exec sql close deptcsr;
end;                        {Process_Depts}

begin                     {Process_Expenses}
        writeln('Entering application to process expenses.');
        open(file_variable := log_file, file_name := 'expenses.log');
        rewrite( log_file );
        Init_Db;
        Process_Depts;
        End_Db;
        close(log_file);
        writeln('Completion of application.');
end;                     {Process_Expenses}

begin                     {MAIN program}
        Process_Expenses;
exit_program:;
 end. {MAIN}