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 are 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.
For readability, the BASIC exclamation point (!) is used as an end-of-line comment indicator.
Sample Application
10 !
! Program: Process_Expenses
! Purpose: Main entry point to process department and employee expenses.
!
exec sql include sqlca
! The department table
exec sql declare dept table &
(name char(12) not null, &
totsales money not null, &
employees smallint not null)
! The employee table
exec sql declare employee table &
(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)
! "State-of-Limbo" for employees who lose their department
exec sql declare toberesolved table &
(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)
print 'Entering application to process expenses.'
open "expenses.log" for output as file #1
call Init_Db
call Process_Depts
call End_Db
close #1
print 'Successful completion of application.'
end ! of Process_Expenses
!
! Subroutine: Init_Db
! Purpose: Initialize the database. Connect to the database,
! and abort if an error. Before processing employees create the table for
! employees who lose their department,"toberesolved".
! Parameters: None.
!
100 sub Init_Db
exec sql include sqlca
exec sql whenever sqlerror stop
exec sql connect personnel
print #1, '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 sub ! of Init_Db
!
! Subroutine: End_Db
! Purpose: Commit the multi-statement transaction and disconnect
! from the database.
! Parameters: None.
!
200 sub End_Db
exec sql include sqlca
exec sql commit
exec sql disconnect
end sub ! of End_Db
!
! Subroutine: 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 table).
! If an employee was terminated, then update the department's employee
! counter.
! Parameters: None
!
300 sub Process_Depts
exec sql include sqlca
exec sql begin declare section
record department
string dname = 12
real totsales
word employees
end record
declare department dept
declare word emps_term ! Employees terminated
declare string loc_dname ! For parameter passing
exec sql end declare section
! Minimum sales of department
declare real constant MIN_DEPT_SALES = 50000.00
! Was the dept deleted?
declare byte deleted_dept ! Was the dept declared?
declare string dept_format ! Formatting value
exec sql declare deptcsr cursor for &
select name, totsales, employees &
from dept &
for direct update of name, employees
! All errors from this point on close down the application
exec sql whenever sqlerror call Close_Down
exec sql whenever not found goto CloseDCsr ! Close deptcsr
exec sql open deptcsr
while (sqlcode = 0)
exec sql fetch deptcsr into :dept
! Did the department reach minimum sales?
if (dept::totsales \ MIN_DEPT_SALES) then
exec sql delete from dept &
where current of deptcsr
deleted_dept = 1
dept_format = ' -- DISSOLVED --'
else
deleted_dept = 0
dept_format = ' '
end if
! Log what we have just done
print #1, 'Department: ' + (dept::dname) &
+ ', Total Sales: ';
print #1 using '$$####.##', dept::totsales;
print #1, dept_format
! Now process each employee in the department
loc_dname = dept::dname
call Process_Employees(loc_dname, deleted_dept, emps_term)
! If some employees were terminated, record this fact
if (emps_term > 0 and deleted_dept = 0) then
exec sql update dept &
set employees = :dept::employees - :emps_term &
where current of deptcsr
end if
next
exec sql whenever not found continue
CloseDCsr: EXEC SQL CLOSE deptcsr
end sub ! of Process_Depts
!
! Subroutine: Process_Employees
! Purpose: Scan through all the employees for a particular
! department. Based on given conditions the employee
! may be terminated, or given 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: loc_dname - Name of current department
! deleted_dept - Is current department being dissolved?
! emps_term - Set locally to record how many employees
! were terminated for the current department.
!
400 sub Process_Employees(string loc_dname, byte deleted_dept, &
integer emps_term)
exec sql include sqlca
exec sql begin declare section
record employee ! Corresponds to "employee" table
string ename = 20
word age
long idno
string hired = 25
real salary
long hired_since_85
end record
declare employee emp
declare real constant SALARY_REDUC = 0.95
exec sql end declare section
! Minimum employee salary
declare real constant MIN_EMP_SALARY = 14000.00
declare integer constant NEARLY_RETIRED = 58
declare string title ! Formatting values
declare string description
! Note the use of the Ingres function to find out who was 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 = :loc_dname &
for direct update of name, salary
! All errors from this point on close down the application
exec sql whenever sqlerror call Close_Down
exec sql whenever not found goto CloseECsr ! Close empcsr
exec sql open empcsr
emps_term = 0
while (sqlcode = 0)
exec sql fetch empcsr into :emp
if (emp::hired_since_85 > 0) then
exec sql delete from employee &
where current of empcsr
title = 'Terminated: '
description = 'Reason: Hired since 85.'
emps_term = emps_term +1
else
if (emp::salary > MIN_EMP_SALARY) then
! Reduce salary if not nearly retired
if (emp::age < NEARLY_RETIRED) then
exec sql update employee &
set salary = salary * :SALARY_REDUC &
where current of empcsr
title = 'Reduction: '
description = 'Reason: Salary.'
else
! Do not reduce salary
title = 'No Changes: '
description = 'Reason: Retiring.'
end if
else
! Leave employee alone
title = 'No Changes: '
description = 'Reason: Salary.'
end if
end if
! Was employee's department dissolved?
if (deleted_dept = 1) then
exec sql insert into toberesolved &
select * &
from employee &
where idno = :emp::idno
exec sql delete from employee &
where current of empcsr
end if
! Log the employee's information
print #1, ' ' + title;
print #1, str$(emp::idno);
print #1, ', ' + (emp::ename) + ', ';
print #1, str$(emp::age) + ', ';
print #1 using '$$####.##', emp::salary;
print #1, '; ' + description
next
exec sql whenever not found continue
CloseEcsr: exec sql close empcsr
end sub ! of Process_Employees
!
! Subroutine: Close_Down
! Purpose: Error handler called any time after Init_Db was 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 too.
! Parameters: None
!
500 sub Close_Down
exec sql include sqlca
exec sql begin declare section
declare string errbuf
exec sql end declare section
exec sql whenever sqlerror continue ! Turn off error handling
exec sql inquire_sql(:errbuf = errortext)
print 'Closed down because of database error:'
print errbuf
close #1
exec sql rollback
exec sql disconnect
stop
end sub ! of Close_Down