-- Copyright © Oracle Corporation 1995.  All Rights Reserved.
-- This SQL module provides the SQL procedures needed by the
-- SQL$LOAD_JOBHIST program.
-- The module illustrates how to use SQL module language to load
-- a database.   
--
-- In order to compile the generated Ada package  that lets you use this
-- module with an Ada program, you must supply the /PACKAGE_COMPILATION
-- qualifier on the module language command line.
-------------------------------------------------------------------------------
-- Header Information Section
-------------------------------------------------------------------------------
MODULE		SQL_LOAD_JOBHIST   -- Module name
LANGUAGE	ADA		   -- Language of calling program
AUTHORIZATION	SQL_SAMPLE	   -- Provides default db handle
PARAMETER COLONS                   -- Parameters will use colons
-------------------------------------------------------------------------------
-- DECLARE Statements Section
-------------------------------------------------------------------------------
DECLARE ALIAS FILENAME 'PERSONNEL'     -- Declaration of the database
				       -- not using the CDD and using
				       -- a logical name.

-------------------------------------------------------------------------------
-- Procedure Section
-------------------------------------------------------------------------------

-- This procedure uses the executable statement SET TRANSACTION to     
-- start a transaction.  The EMPLOYEES, JOBS, and DEPARTMENTS tables    
-- are reserved because of constraint checking.                        

PROCEDURE SET_TRANSACTION
    SQLCODE;

    SET TRANSACTION READ WRITE RESERVING
	JOB_HISTORY FOR EXCLUSIVE WRITE,
	EMPLOYEES FOR SHARED READ,
	JOBS FOR SHARED READ,
	DEPARTMENTS FOR SHARED READ;


-- This procedure inserts a row in the JOB_HISTORY table.
-- The list of names in the VALUES clause corresponds to the
-- parameter list for the procedure.  The list of names that
-- follows the INSERT clause names the columns in the table
-- that are to be used for the INSERT operation.

PROCEDURE STORE_JOBHIST		
    SQLCODE
    :P_EMPLOYEE_ID	CHAR(5)
    :P_JOB_CODE		CHAR(4)
    :P_JOB_START        DATE
    :P_JOB_END		DATE
    :P_JOB_END_IND	SMALLINT
    :P_DEPARTMENT_CODE	CHAR(4)
    :P_SUPERVISOR_ID	CHAR(5);

    INSERT INTO JOB_HISTORY
    (EMPLOYEE_ID, JOB_CODE, JOB_START, JOB_END,
     DEPARTMENT_CODE, SUPERVISOR_ID)
    VALUES
    (:P_EMPLOYEE_ID, :P_JOB_CODE, :P_JOB_START,
     :P_JOB_END INDICATOR :P_JOB_END_IND,
     :P_DEPARTMENT_CODE, :P_SUPERVISOR_ID);


-- This procedure commits the transaction.

PROCEDURE COMMIT_TRANSACTION
    SQLCODE;

    COMMIT;


-- This procedure rolls back the transaction.

PROCEDURE ROLLBACK_TRANSACTION
    SQLCODE;

    ROLLBACK;