employee_qp.pkb

OReilly Oracle PL SQL Programming第4版源代码

            MIDDLE_INITIAL,
            JOB_ID,
            MANAGER_ID,
            HIRE_DATE,
            SALARY,
            COMMISSION,
            DEPARTMENT_ID,
            EMPNO,
            ENAME,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
        INTO retval
        FROM EMPLOYEE
       WHERE
            LAST_NAME = last_name_in AND
            FIRST_NAME = first_name_in AND
            MIDDLE_INITIAL = middle_initial_in
      ;
      ELSE
      SELECT
            EMPLOYEE_ID,
            LAST_NAME,
            FIRST_NAME,
            MIDDLE_INITIAL,
            JOB_ID,
            MANAGER_ID,
            HIRE_DATE,
            SALARY,
            COMMISSION,
            DEPARTMENT_ID,
            EMPNO,
            ENAME,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
        INTO retval
        FROM EMPLOYEE
       WHERE
            (LAST_NAME = last_name_in OR (LAST_NAME IS NULL AND last_name_in IS NULL)) AND
            (FIRST_NAME = first_name_in OR (FIRST_NAME IS NULL AND first_name_in IS NULL)) AND
            (MIDDLE_INITIAL = middle_initial_in OR (MIDDLE_INITIAL IS NULL AND middle_initial_in IS NULL))
      ;
      END IF;
      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
      WHEN TOO_MANY_ROWS
      THEN
         DECLARE
            l_err_instance_id qd_err_instance_tp.id_t;
         BEGIN
            qd_runtime.register_error ('MULTIPLE-UNIQUE-ROWS'
               ,err_instance_id_out => l_err_instance_id);
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'TABLE'
              ,value_in => 'EMPLOYEE'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'LAST_NAME'
              ,value_in => 'last_name_in'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'FIRST_NAME'
              ,value_in => 'first_name_in'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'MIDDLE_INITIAL'
              ,value_in => 'middle_initial_in'
              ,validate_in => FALSE
              );
            qd_runtime.raise_error_instance (
              err_instance_id_in => l_err_instance_id);
         END;
   END or_I_EMPLOYEE_NAME;

   FUNCTION or_I_EMPLOYEE_NAME_cv (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_rc
   IS
      retval EMPLOYEE_TP.EMPLOYEE_rc;
   BEGIN
      IF
         last_name_in IS NOT NULL AND
         first_name_in IS NOT NULL AND
         middle_initial_in IS NOT NULL
      THEN
      OPEN retval FOR
         SELECT
            EMPLOYEE_ID,
            LAST_NAME,
            FIRST_NAME,
            MIDDLE_INITIAL,
            JOB_ID,
            MANAGER_ID,
            HIRE_DATE,
            SALARY,
            COMMISSION,
            DEPARTMENT_ID,
            EMPNO,
            ENAME,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM EMPLOYEE
          WHERE
            LAST_NAME = last_name_in AND
            FIRST_NAME = first_name_in AND
            MIDDLE_INITIAL = middle_initial_in
             ;
      ELSE
      OPEN retval FOR
         SELECT
            EMPLOYEE_ID,
            LAST_NAME,
            FIRST_NAME,
            MIDDLE_INITIAL,
            JOB_ID,
            MANAGER_ID,
            HIRE_DATE,
            SALARY,
            COMMISSION,
            DEPARTMENT_ID,
            EMPNO,
            ENAME,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM EMPLOYEE
          WHERE
            (LAST_NAME = last_name_in OR (LAST_NAME IS NULL AND last_name_in IS NULL)) AND
            (FIRST_NAME = first_name_in OR (FIRST_NAME IS NULL AND first_name_in IS NULL)) AND
            (MIDDLE_INITIAL = middle_initial_in OR (MIDDLE_INITIAL IS NULL AND middle_initial_in IS NULL))
             ;
      END IF;
      RETURN retval;
   END or_I_EMPLOYEE_NAME_cv;

   FUNCTION pky_I_EMPLOYEE_NAME (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_ID_t
   IS
      retval EMPLOYEE_TP.EMPLOYEE_ID_t;
   BEGIN
      IF
         last_name_in IS NOT NULL AND
         first_name_in IS NOT NULL AND
         middle_initial_in IS NOT NULL
      THEN
      SELECT EMPLOYEE_ID
        INTO retval
        FROM EMPLOYEE
       WHERE
            LAST_NAME = last_name_in AND
            FIRST_NAME = first_name_in AND
            MIDDLE_INITIAL = middle_initial_in
      ;
      ELSE
      SELECT EMPLOYEE_ID
        INTO retval
        FROM EMPLOYEE
       WHERE
            (LAST_NAME = last_name_in OR (LAST_NAME IS NULL AND last_name_in IS NULL)) AND
            (FIRST_NAME = first_name_in OR (FIRST_NAME IS NULL AND first_name_in IS NULL)) AND
            (MIDDLE_INITIAL = middle_initial_in OR (MIDDLE_INITIAL IS NULL AND middle_initial_in IS NULL))
      ;
      END IF;
      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
      WHEN TOO_MANY_ROWS
      THEN
         DECLARE
            l_err_instance_id qd_err_instance_tp.id_t;
         BEGIN
            qd_runtime.register_error ('MULTIPLE-UNIQUE-ROWS'
               ,err_instance_id_out => l_err_instance_id);
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'TABLE'
              ,value_in => 'EMPLOYEE'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'LAST_NAME'
              ,value_in => 'last_name_in'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'FIRST_NAME'
              ,value_in => 'first_name_in'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'MIDDLE_INITIAL'
              ,value_in => 'middle_initial_in'
              ,validate_in => FALSE
              );
            qd_runtime.raise_error_instance (
              err_instance_id_in => l_err_instance_id);
         END;
   END pky_I_EMPLOYEE_NAME;

   -- Number of rows by I_EMPLOYEE_NAME
   FUNCTION num_I_EMPLOYEE_NAME (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      IF
         last_name_in IS NOT NULL AND
         first_name_in IS NOT NULL AND
         middle_initial_in IS NOT NULL
      THEN
      SELECT COUNT(*)
        INTO retval
        FROM EMPLOYEE
       WHERE
            LAST_NAME = last_name_in AND
            FIRST_NAME = first_name_in AND
            MIDDLE_INITIAL = middle_initial_in
      ;
      ELSE
      SELECT COUNT(*)
        INTO retval
        FROM EMPLOYEE
       WHERE
            (LAST_NAME = last_name_in OR (LAST_NAME IS NULL AND last_name_in IS NULL)) AND
            (FIRST_NAME = first_name_in OR (FIRST_NAME IS NULL AND first_name_in IS NULL)) AND
            (MIDDLE_INITIAL = middle_initial_in OR (MIDDLE_INITIAL IS NULL AND middle_initial_in IS NULL))
      ;
      END IF;
      RETURN retval;
   END num_I_EMPLOYEE_NAME;

   FUNCTION ex_I_EMPLOYEE_NAME (
      last_name_in IN EMPLOYEE_TP.LAST_NAME_t,
      first_name_in IN EMPLOYEE_TP.FIRST_NAME_t,
      middle_initial_in IN EMPLOYEE_TP.MIDDLE_INITIAL_t
      )
      RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      SELECT 1 INTO l_dummy
        FROM EMPLOYEE
       WHERE
             LAST_NAME = last_name_in AND
             FIRST_NAME = first_name_in AND
             MIDDLE_INITIAL = middle_initial_in
      ;
      RETURN TRUE;
   EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
             WHEN TOO_MANY_ROWS THEN RETURN TRUE;
   END ex_I_EMPLOYEE_NAME;

   FUNCTION ar_FK_EMP_DEPARTMENT_cv (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_rc
   IS
      retval EMPLOYEE_TP.EMPLOYEE_rc;
   BEGIN
      OPEN retval FOR
         SELECT
            EMPLOYEE_ID,
            LAST_NAME,
            FIRST_NAME,
            MIDDLE_INITIAL,
            JOB_ID,
            MANAGER_ID,
            HIRE_DATE,
            SALARY,
            COMMISSION,
            DEPARTMENT_ID,
            EMPNO,
            ENAME,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM EMPLOYEE
       WHERE
          DEPARTMENT_ID = ar_FK_EMP_DEPARTMENT_cv.department_id_in

         ;
      RETURN retval;
   END ar_FK_EMP_DEPARTMENT_cv;

   FUNCTION in_FK_EMP_DEPARTMENT_cv (
      department_id_in IN VARCHAR2
      )
      RETURN EMPLOYEE_TP.weak_refcur
   IS
      retval EMPLOYEE_TP.weak_refcur;
   BEGIN
      OPEN retval FOR
         'SELECT
            EMPLOYEE_ID,
            LAST_NAME,
            FIRST_NAME,
            MIDDLE_INITIAL,
            JOB_ID,
            MANAGER_ID,
            HIRE_DATE,
            SALARY,
            COMMISSION,
            DEPARTMENT_ID,
            EMPNO,
            ENAME,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM EMPLOYEE
       WHERE
          DEPARTMENT_ID IN (' || department_id_in || ')
             '
         ;
      RETURN retval;
   END in_FK_EMP_DEPARTMENT_cv;

   FUNCTION ar_FK_EMP_DEPARTMENT (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t
      )
      RETURN EMPLOYEE_TP.EMPLOYEE_tc
   IS
      CURSOR allrows_cur
      IS
         SELECT
            EMPLOYEE_ID,
            LAST_NAME,
            FIRST_NAME,
            MIDDLE_INITIAL,
            JOB_ID,
            MANAGER_ID,
            HIRE_DATE,
            SALARY,
            COMMISSION,
            DEPARTMENT_ID,
            EMPNO,
            ENAME,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM EMPLOYEE
          WHERE
             DEPARTMENT_ID = ar_FK_EMP_DEPARTMENT.department_id_in

         ;
      l_rows PLS_INTEGER;
      retval EMPLOYEE_TP.EMPLOYEE_tc;
   BEGIN
      OPEN allrows_cur;
      FETCH allrows_cur BULK COLLECT INTO retval;
      RETURN retval;
   END ar_FK_EMP_DEPARTMENT;

   PROCEDURE ar_FK_EMP_DEPARTMENT (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t,
      employee_id_out OUT EMPLOYEE_TP.EMPLOYEE_ID_cc,
      last_name_out OUT EMPLOYEE_TP.LAST_NAME_cc,
      first_name_out OUT EMPLOYEE_TP.FIRST_NAME_cc,
      middle_initial_out OUT EMPLOYEE_TP.MIDDLE_INITIAL_cc,
      job_id_out OUT EMPLOYEE_TP.JOB_ID_cc,
      manager_id_out OUT EMPLOYEE_TP.MANAGER_ID_cc,
      hire_date_out OUT EMPLOYEE_TP.HIRE_DATE_cc,
      salary_out OUT EMPLOYEE_TP.SALARY_cc,
      commission_out OUT EMPLOYEE_TP.COMMISSION_cc,
      department_id_out OUT EMPLOYEE_TP.DEPARTMENT_ID_cc,
      empno_out OUT EMPLOYEE_TP.EMPNO_cc,
      ename_out OUT EMPLOYEE_TP.ENAME_cc,
      created_by_out OUT EMPLOYEE_TP.CREATED_BY_cc,
      created_on_out OUT EMPLOYEE_TP.CREATED_ON_cc,
      changed_by_out OUT EMPLOYEE_TP.CHANGED_BY_cc,
      changed_on_out OUT EMPLOYEE_TP.CHANGED_ON_cc
      )
   IS
   BEGIN
      SELECT
            EMPLOYEE_ID,
            LAST_NAME,
            FIRST_NAME,
            MIDDLE_INITIAL,
            JOB_ID,
            MANAGER_ID,
            HIRE_DATE,
            SALARY,
            COMMISSION,
            DEPARTMENT_ID,
            EMPNO,
            ENAME,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
        BULK COLLECT INTO
            employee_id_out,
            last_name_out,
            first_name_out,
            middle_initial_out,
            job_id_out,
            manager_id_out,
            hire_date_out,
            salary_out,
            commission_out,
            department_id_out,
            empno_out,
            ename_out,
            created_by_out,
            created_on_out,
            changed_by_out,
            changed_on_out
        FROM EMPLOYEE
       WHERE
             DEPARTMENT_ID = ar_FK_EMP_DEPARTMENT.department_id_in

      ;
   END ar_FK_EMP_DEPARTMENT;

   -- Number of rows by FK_EMP_DEPARTMENT
   FUNCTION num_FK_EMP_DEPARTMENT (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t
      )
      RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      SELECT COUNT(*)
        INTO retval
        FROM EMPLOYEE
       WHERE
             DEPARTMENT_ID = department_id_in
      ;
      RETURN retval;
   END num_FK_EMP_DEPARTMENT;

   FUNCTION ex_FK_EMP_DEPARTMENT (
      department_id_in IN EMPLOYEE_TP.DEPARTMENT_ID_t
      )
      RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      SELECT 1 INTO l_dummy
        FROM EMPLOYEE
       WHERE
             DEPARTMENT_ID = department_id_in
      ;
      RETURN TRUE;
   EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
             WHEN TOO_MANY_ROWS THEN RETURN TRUE;