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}