-- Copyright © Oracle Corporation 1995.  All Rights Reserved.
-- This SQL module provides the SQL procedures needed by the
-- SQL$TERMINATE 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		SQL_TERMINATE         -- Module name
LANGUAGE	PASCAL		      -- Language of calling program
AUTHORIZATION	SQL_SAMPLE            -- Provides default db handle
PARAMETER COLONS                      -- Parameters are prefixed by colons
-------------------------------------------------------------------------------
-- DECLARE Statements Section
-------------------------------------------------------------------------------
DECLARE ALIAS FOR                       -- Declaration of the database
	FILENAME 'personnel'     	-- not using the CDD.

DECLARE EMPLROW CURSOR FOR
    SELECT EMPLOYEE_ID, STATUS_CODE FROM EMPLOYEES
    WHERE EMPLOYEE_ID = :P_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_CURSOR
    SQLCODE
    :P_EMPLOYEE_ID	CHAR(5);

    OPEN EMPLROW;

-- This procedure FETCHes the data from the opened cursor
PROCEDURE FETCH_EMPLOYEES
    SQLCODE
    :P_EMPLOYEE_ID	CHAR(5)
    :P_STATUS_CODE	CHAR(1);

    FETCH EMPLROW INTO :P_EMPLOYEE_ID, :P_STATUS_CODE;

-- This procedure converts date.
PROCEDURE GET_DATE
    SQLCODE
    :P_JOB_END		DATE
    :END_DATE_TIME	CHAR(16);
    SELECT CAST(:END_DATE_TIME AS DATE VMS)
         INTO :P_JOB_END FROM
                RDB$DATABASE  LIMIT TO 1 ROW;


-- This procedure UPDATEs the EMPLOYEES table
PROCEDURE UPDATE_EMPLOYEES
    SQLCODE;

    UPDATE EMPLOYEES
    SET STATUS_CODE = '0'
    WHERE CURRENT OF EMPLROW;


-- This procedure UPDATEs the JOB_HISTORY table
PROCEDURE UPDATE_JOBHIST
    SQLCODE
    :P_EMPLOYEE_ID	CHAR(5)
    :P_JOB_END		DATE;

    UPDATE JOB_HISTORY
    SET JOB_END = :P_JOB_END
    WHERE EMPLOYEE_ID = :P_EMPLOYEE_ID
      AND JOB_END IS NULL;


-- This procedure UPDATEs the SALARY_HISTORY table
PROCEDURE UPDATE_SALHIST
    SQLCODE
    :P_EMPLOYEE_ID	CHAR(5)
    :P_JOB_END		DATE;

    UPDATE SALARY_HISTORY
    SET SALARY_END = :P_JOB_END
    WHERE EMPLOYEE_ID = :P_EMPLOYEE_ID
      AND SALARY_END IS NULL;
    
-- This procedure commits the transaction.
PROCEDURE COMMIT_TRANSACTION
    SQLCODE;

    COMMIT;


-- This procedure rolls back the transaction.

PROCEDURE ROLLBACK_TRANSACTION
    SQLCODE;

    ROLLBACK;