-- Copyright © Oracle Corporation 1995.  All Rights Reserved.
--------------------------------------------------------------------------
-- This module shows how to use SQL module language when working with a
-- multischema database and using multischema naming conventions. The 
-- C program MSDB.C that this module is linked with uses the sample 
-- multischema database CORPORATE_DATA.
--------------------------------------------------------------------------
-- Header Information Section
--------------------------------------------------------------------------
MODULE          MSDB_MOD          -- Module name
LANGUAGE        C                 -- Language of calling program
CATALOG         ADMINISTRATION    -- Set default catalog 
SCHEMA          PERSONNEL         -- Set default schema 
PARAMETER	COLONS
--------------------------------------------------------------------------
-- DECLARE Statements Section
--------------------------------------------------------------------------
DECLARE ALIAS FILENAME corporate_data   -- Declare the database. 

DECLARE DEPT_CURSOR TABLE CURSOR        -- Declare table cursor for the 
    FOR SELECT                          -- DEPARTMENTS table in the 
      DEPARTMENT_CODE,                  -- ACCOUNTING schema.
      DEPARTMENT_NAME,
      MANAGER_ID FROM               
    ACCOUNTING.DEPARTMENTS 
    ORDER BY DEPARTMENT_CODE            

DECLARE EMP_CURSOR TABLE CURSOR         -- Declare table cursor for the 
    FOR SELECT                          -- EMPLOYEES table in the PERSONNEL
      E.EMPLOYEE_ID,                    -- schema.
      E.LAST_NAME,
      E.FIRST_NAME FROM
    EMPLOYEES E, JOB_HISTORY JH 
    WHERE E.EMPLOYEE_ID = JH.EMPLOYEE_ID
    AND JH.DEPARTMENT_CODE = :DEPT_CODE
    AND JH.JOB_END IS NULL
    ORDER BY E.LAST_NAME
--------------------------------------------------------------------------
-- Procedure Section
--------------------------------------------------------------------------
-- Opens the cursor declared for the DEPARTMENTS table.
PROCEDURE OPEN_DEPT_CURSOR
    SQLCODE;
    OPEN DEPT_CURSOR;
--------------------------------------------------------------------------
-- Opens the cursor declared for the EMPLOYEES table.
PROCEDURE OPEN_EMP_CURSOR
    SQLCODE
    :DEPT_CODE CHAR(4); 
    OPEN EMP_CURSOR;
--------------------------------------------------------------------------
-- Fetches data from the opened cursor for the DEPARTMENTS table.
PROCEDURE FETCH_DEPT_DATA
    SQLCODE
    :DEPT_CODE          CHAR(4)
    :DEPT_NAME          CHAR(20)
    :DEPT_MAN_ID        CHAR(5);

    FETCH DEPT_CURSOR INTO 
                :DEPT_CODE,
                :DEPT_NAME,
		:DEPT_MAN_ID;
--------------------------------------------------------------------------
-- Fetches data from the opened cursor for the EMPLOYEES table.

PROCEDURE FETCH_EMP_DATA
    SQLCODE
    :EMP_ID             CHAR(5)
    :EMP_LAST_NAME      CHAR(20)
    :EMP_FIRST_NAME     CHAR(20);

    FETCH EMP_CURSOR INTO 
		:EMP_ID,
                :EMP_LAST_NAME,
                :EMP_FIRST_NAME;
--------------------------------------------------------------------------
-- Closes the DEPT_CURSOR cursor for the DEPARTMENTS table.
PROCEDURE CLOSE_DEPT_CURSOR
   SQLCODE;
   CLOSE DEPT_CURSOR;
--------------------------------------------------------------------------
-- Closes the EMP_CURSOR cursor for the	EMPLOYEES table.
PROCEDURE CLOSE_EMP_CURSOR
   SQLCODE;
   CLOSE EMP_CURSOR;