Previous | Table of Contents | Next

Page 532

Listing 20.6 illustrates the script used to create the Classifications table.

Listing 20.6 L_20_06.SQL—Creating the Classifications Table

SQL> create table classifications
 2  (fk_payroll_number number, classification_date date,
 3   classification varchar(15) not null, wages number(8) default(0),
 4   comments varchar(20), department char(4), department_name varchar(15),
 5   primary key (fk_payroll_number, classification_date),
 6   foreign key (fk_payroll_number) references employees
 7    on delete cascade)
 8  storage (initial 100k next 10k pctincrease 1);

Table created.

SQL>

Listing 20.7 illustrates the script used to create the Eyeglasses table.

Listing 20.7 L_20_07.SQL—Creating the Eyeglasses Table

SQL> create table eyeglasses
 2  (fk_payroll_number number, purchase_date date,
 3   optician varchar2(20) not null, cost number(6,2) default(0) not null,
 4   check_number varchar2(10),
 5   primary key (fk_payroll_number, purchase_date),
 6   foreign key (fk_payroll_number) references employees
 7    on delete cascade)
 8  storage (initial 50k next 10k pctincrease 1);

Table created.

SQL>

Listing 20.8 illustrates the script used to create the Tools table.

Listing 20.8 L_20_08.SQL—Creating the Tools Table

SQL> create table tools
 2  (fk_payroll_number number, purchase_date date,
 3   payroll_deduct char (1) default(`N') check (payroll_deduct in (`N', `Y')),
 4   tool_name varchar(15), tool_cost number (6,2) default(0),
 5   payment number(6,2) default(0),
 6   last_payment_amount number(6,2) default(0), first_payment_date date,
 7   last_payment_date date,
 8   primary key (fk_payroll_number, purchase_date),
 9   foreign key (fk_payroll_number) references employees

Page 533

 10    on delete cascade)
 11  storage (initial 50k next 10k pctincrease 1);

Table created.

SQL>

Listing 20.9 shows the script used to create the Appraisals table.

Listing 20.9 L_20_09.SQL—Creating the Appraisals Table

SQL> create table appraisals
 2  (fk_payroll_number number, appraisal_date date,
 3   primary key (fk_payroll_number, appraisal_date),
 4   foreign key (fk_payroll_number) references employees
 5    on delete cascade)
 6  storage (initial 10k next 10k pctincrease 1);

Table created.

SQL>

The last table created is the Security table. This table will be used to validate security before the form allows records to be inserted, updated, or deleted (see Listing 20.10).

Listing 20.10 L_20_10.SQL—Creating the Security Table

SQL> create table security
 2  (payroll_number  number,
 3   security_option  varchar2(4));

Table created.

SQL>

The last database object to be created is a sequence. The sequence will be used in the Employee system to generate a unique number for new employees (see Listing 20.11).

Listing 20.11 L_20_11.SQL—Creating the Next_payroll_number Sequence

SQL> create sequence next_payroll_number
 2  increment by 1 start with 1;

Sequence created.

SQL>

Page 534

Installment 3—Loading the Data into the Tables

The goal of this installment is to load the data from the original Employee system into the Oracle tables created in Installment 2. The data from the original system was converted into a comma-separated value file called RAWDATA.CSV. The records in this file are not normalized and need some reformatting. In order to accomplish this, the data will be loaded into a temporary table. A PL/SQL program is then executed against the data in the temporary table. This program scrubs the data and places the fields into the normalized tables of the Employee database.

Listing 20.12 contains the Create table file for the temporary load table. The name of the table is "Temptable."

Listing 20.12 L_20_12.SQL—Creating the Temporary Load Table Called Temptable

SQL> create table temptable
 2  (last_name varchar2(15),first_name varchar2(15), absences number(2),
 3  wages number(8,2), street varchar2(20), city varchar2(15), state char(2),
 4  phone char(13), social_security_number char(11), employment_date char(9),
 5  birth_date char(9), current_position varchar2(15), position_date char(9),
 6  department char(4), department_name varchar2(15),
 7  department_1 char(4), historic_position_1 varchar2(15),
 8  position_date_1 char(9), wages_1 number(8), comments_1 varchar2(20),
 9  department_2 char(4), historic_position_2 varchar2(15),
 10  position_date_2 char(9), wages_2 number(8), comments_2 varchar2(20),
 11  department_3 char(4), historic_position_3 varchar2(15),
 12  position_date_3 char(9), wages_3 number(8), comments_3 varchar2(20),
 13  department_4 char(4), historic_position_4 varchar2(15),
 14  position_date_4 char(9), wages_4 number(8), comments_4 varchar2(20),
 15  department_5 char(4), historic_position_5 varchar2(15),
 16  position_date_5 char(9), wages_5 number(8), comments_5 varchar2(20),
 17  department_6 char(4), historic_position_6 varchar2(15),
 18  position_date_6 char(9), wages_6 number(8), comments_6 varchar2(20),
 19  purchase_date_1 char(9), optician_1 varchar2(20),
 20  cost_1 number(5), check_number_1 char(10),
 21  purchase_date_2 char(9), optician_2 varchar2(20),
 22  cost_2 number(5), check_number_2 char(10),
 23  purchase_date_3 char(9), optician_3 varchar2(20),
 24  cost_3 number(5), check_number_3 char(10),
 25  tool_purchase_date_1 char(9), payroll_deduct_1 char(1),
 26  tool_name_1 varchar2(15), tool_cost_1 number(5),
 27  payment_amount_1 number(5), last_payment_amount_1 number(5),
 28  first_payment_date_1 char(9), last_payment_date_1 char(9),
 29  tool_purchase_date_2 char(9), payroll_deduct_2 char(1),
 30  tool_name_2 varchar2(15), tool_cost_2 number(5),
 31  payment_amount_2 number(5), last_payment_amount_2 number(5),
 32  first_payment_date_2 char(9), last_payment_date_2 char(9),
 33  tool_purchase_date_3 char(9), payroll_deduct_3 char(1),

Page 535

 34  tool_name_3 varchar2(15), tool_cost_3 number(5),
 35  payment_amount_3 number(5), last_payment_amount_3 number(5),
 36  first_payment_date_3 char(9), last_payment_date_3 number(5))
 37 storage (initial 100k next 10k pctincrease 1);

Table created.

SQL>

The next step is to load this table with the values from the RAWDATA.CSV load file. This is performed by using SQL*Loader. Listing 20.13 contains the control file used to load the Temptable file. The control file contains two important settings. The field terminated by expression tells Oracle what terminates the field. Because this is a comma-separated value file, the statement must be used. The TRAILING NULLCOLS option must also be used because some of the load records contain null values. This option tells Oracle to load the table's fields with nulls when a value does not exist rather than generating an error. The command on line 2 specifies that the data is on the d drive. This is the assumed CD. Please change this command if it does not match the actual file path.

Listing 20.13 L_20_13.CTL—The Control File Used by SQL*Loader to Populate the Temporary File

load data
infile `d:\rawdata.csv'
into table temptable
replace
fields terminated by "," TRAILING NULLCOLS
 (last_name, first_name, absences, wages, street, city, state,
 phone, social_security_number, employment_date,
 birth_date, current_position, position_date,
 department, department_name, department_1, historic_position_1,
 position_date_1, wages_1, comments_1, department_2, historic_position_2,
 position_date_2, wages_2, comments_2, department_3, historic_position_3,
 position_date_3, wages_3, comments_3, department_4, historic_position_4,
 position_date_4, wages_4, comments_4, department_5, historic_position_5,
 position_date_5, wages_5, comments_5, department_6, historic_position_6,
 position_date_6, wages_6, comments_6,
 purchase_date_1, optician_1,
 cost_1, check_number_1, purchase_date_2, optician_2,
 cost_2, check_number_2, purchase_date_3, optician_3,
 cost_3, check_number_3, tool_purchase_date_1, payroll_deduct_1,
 tool_name_1, tool_cost_1, payment_amount_1, last_payment_amount_1,
 first_payment_date_1, last_payment_date_1,
 tool_purchase_date_2, payroll_deduct_2,
 tool_name_2, tool_cost_2, payment_amount_2, last_payment_amount_2,
 first_payment_date_2, last_payment_date_2,
 tool_purchase_date_3, payroll_deduct_3, tool_name_3, tool_cost_3,
 payment_amount_3, last_payment_amount_3,
first_payment_date_3, last_payment_date_3)

Page 536

The next step is to execute a conversion program that will modify and normalize the values. This program is shown in Listing 20.14. The first part of the program contains the cursors that will be used to retrieve the data for the program. The program uses the Cursor For loop because of its built-in functionality. The developer does not have to define the local variables, fetch statements, and other cursor commands. The program contains a large number of variables. The Cursor For loop dramatically reduces the amount of code that must be written.

The first section of the program populates the Departments table. This table must be populated first since it is the parent table to the Employees table. This is mandated by the foreign key constraint that exists on the fk_department field of the Employees table. The section contains a cursor that retrieves the unique Department values from Temptable. As each value is retrieved, a record is inserted into the Departments table.

The next section populates the Employees, Classifications, Eyeglasses, and Tools tables. The section is controlled by a Cursor For loop that retrieves each record in the load table, Temptable. The physical record from Temptable is changed into logical records. The logical records are inserted into the proper table. Before the records are placed into their tables, a value is generated from the sequence Next_payroll_number. This value is assigned to a local variable (pay_number) to be used to populate the payroll number fields in all of the tables. One payroll_number is generated for each record in Temptable. As the records are inserted into the tables, the Upper function is used to make an alpha character uppercase.

In addition, the historic classification fields do not contain a value for the Department name. The records only contain the department id. The Classifications table contains the Department Name for reasons mentioned earlier. In order to populate the Department Name field, a cursor is opened to retrieve the Department Name value from the Departments table before records are inserted into the Classifications table.

Listing 20.14 L_20_14.SQL—The Data Conversion Program for the Employee System

declare
pay_number   number;
dept      varchar2(4);
dept_name    varchar2(15);
cursor a is select distinct department, department_name
      from temptable;
cursor b is select * from temptable;
cursor c is select department_name from departments
       where department = dept;
begin
delete from appraisals;
delete from tools;
delete from eyeglasses;
delete from classifications;
delete from employees;
delete from departments;

Previous | Table of Contents | Next