-- This SQL module provides the SQL procedures needed by the -- dixie_application.ada program. -- The module illustrates how to use SQL module language to use a cursor -- to fetch and update rows in the database. -------------------------------------------------------------------------------- -- Header Information Section -------------------------------------------------------------------------------- module dixie_conc_interface -- module name language ada -- language of calling program authorization dixie_db -- provides default db handle -------------------------------------------------------------------------------- -- Declare Statements Section -------------------------------------------------------------------------------- declare schema filename 'dixie_db' -- Declaration of the database not using -- the CDD and using a logical name declare emplrow cursor for select employee_id, last_name, first_name, address, weight from employees where employee_id = input_employee_id -------------------------------------------------------------------------------- -- Procedure Section -------------------------------------------------------------------------------- -- This procedure uses the executable form for starting a transaction procedure set_transaction sqlcode; set transaction read write; -- This procedure opens the cursor that has been declared for the employees table procedure open_employees_cursor input_employee_id char(5) sqlcode; open emplrow; -- This procedure fetches the data from the opened emplrow cursor procedure fetch_employees output_employee_id char(5) output_last_name char(10) output_first_name char(10) output_address char(30) output_weight real sqlcode; fetch emplrow into output_employee_id, output_last_name, output_first_name, output_address, output_weight; -- This procedure updates the salary_history table procedure insert_sal_hist_row input_employee_id char(5) input_salary real input_merit_pts int merit_ind smallint sqlcode; insert into salary_hist values (input_employee_id, input_salary, input_merit_pts merit_ind); -- This procedure updates the job_hist table procedure update_job_hist input_employee_id char(5) sqlcode; update job_hist set years_emplyd = years_emplyd + 1 where employee_id = input_employee_id; -- This procedure commits the transaction procedure commit_transaction sqlcode; commit; -- This procedure rolls back the transaction procedure rollback_transaction sqlcode; rollback; -- This procedure closes the emplrow cursor procedure close_emp_cursor sqlcode; close emplrow;