Listing 1

CREATE PROCEDURE employee_insert (
  a_emp_id integer,
  a_manager_id integer,
  a_emp_fname char(20),
  a_emp_lname char(20),
  a_dept integer,
  a_street char(40),
  a_city char(20),
  a_state char(16),
  a_zip_code char(10),
  a_phone char(10),
  a_status char(2),
  a_ss_number char(11),
  a_salary numeric(20,3),
  a_start_date date,
  a_termination_date date,
  a_birth_date date,
  a_bene_health_ins char(2),
  a_bene_life_ins char(2),
  a_bene_day_care char(2),
  a_sex char(2)
 )
BEGIN
	insert into employee (
      emp_id,
      manager_id,
	 emp_fname,
      emp_lname,
      dept_id,
      street,
      city,
      state,
      zip_code,
      phone,
      status,
      ss_number,
      salary,
      start_date,
      termination_date,
      birth_date,
      bene_health_ins,
      bene_life_ins,
      bene_day_care,
      sex
  ) values (
      a_emp_id,
      a_manager_id,
      a_emp_fname,
      a_emp_lname,
      a_dept_id,
      a_street,
      a_city,
      a_state,
      a_zip_code,
      a_phone,
      a_status,
      a_ss_number,
      a_salary,
      a_start_date,
      a_termination_date,
      a_birth_date,
      a_bene_health_ins,
      a_bene_life_ins,
      a_bene_day_care,
      a_sex
  ) ;
END ;

Listing 2

ALTER PROCEDURE asademo."employee_update"(
  a_emp_id integer,
  a_manager_id_new integer,
  a_manager_id_old integer,
  a_emp_fname_new char(20),
  a_emp_fname_old char(20),
  a_emp_lname_new char(20),
  a_emp_lname_old char(20),
  a_dept_new integer,
  a_dept_old integer,
  a_street_new char(40),
  a_street_old char(40),
  a_city_new char(20),
  a_city_old char(20),
  a_state_new char(16),
  a_state_old char(16),
  a_zip_code_new char(10),
  a_zip_code_old char(10),
  a_phone_new char(10),
  a_phone_old char(10),
  a_status_new char(2),
  a_status_old char(2),
  a_ss_number_new char(11),
  a_ss_number_old char(11),
  a_salary_new numeric(20,3),
  a_salary_old numeric(20,3),
  a_start_date_new date,
  a_start_date_old date,
  a_termination_date_new date,
  a_termination_date_old date,
  a_birth_date_new date,
  a_birth_date_old date,
  a_bene_health_ins_new char(2),
  a_bene_health_ins_old char(2),
  a_bene_life_ins_new char(2),
  a_bene_life_ins_old char(2),
  a_bene_day_care_new char(2),
  a_bene_day_care_old char(2),
  a_sex_new char(2),
  a_sex_old char(2)
 )
BEGIN
  update employee
      set manager_id = a_manager_id_new,
         emp_fname = a_emp_fname_new,
         emp_lname = a_emp_lname_new,
         dept_id = a_dept_id_new,
         street = a_street_new,
         city = a_city_new,
         state = a_state_new,
         zip_code = a_zip_code_new,
         phone = a_phone_new,
         status = a_status_new,
         ss_number = a_ss_number_new,
         salary = a_salary_new,
         start_date = a_start_date_new,
         termination_date = a_
         termination_date_new,
         birth_date = a_birth_date_new,
         bene_health_ins = a_bene_health_ins_new,
         bene_life_ins = a_bene_life_ins_new,
         bene_day_care = a_bene_
           day_care_new,
         sex = a_sex_new
      where
          emp_id = a_emp_id and
         manager_id = a_manager_id_old and
         emp_fname = a_emp_fname_old and
         emp_lname = a_emp_lname_old and
         dept_id = a_dept_id_old and
         street = a_street_old and
         city = a_city_old and
         state = a_state_old and
         zip_code = a_zip_code_old and
         phone = a_phone_old and
         status = a_status_old and
         ss_number = a_ss_number_old and
         salary = a_salary_old and
         start_date = a_start_date_old and
        termination_date = a_
            termination_date_old and
         birth_date = a_birth_date_old and
         bene_health_ins = a_bene_
           health_ins_old and
         bene_life_ins = a_bene_
           life_ins_old and
         bene_day_care = a_bene_
          day_care_old and
         sex = a_sex_old ;
END ;