employee_qp.pkb

OReilly Oracle PL SQL Programming第4版源代码

   END ex_FK_EMP_DEPARTMENT;

   FUNCTION ar_FK_EMP_JOB_cv (
      job_id_in IN EMPLOYEE_TP.JOB_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
          JOB_ID = ar_FK_EMP_JOB_cv.job_id_in

         ;
      RETURN retval;
   END ar_FK_EMP_JOB_cv;

   FUNCTION in_FK_EMP_JOB_cv (
      job_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
          JOB_ID IN (' || job_id_in || ')
             '
         ;
      RETURN retval;
   END in_FK_EMP_JOB_cv;

   FUNCTION ar_FK_EMP_JOB (
      job_id_in IN EMPLOYEE_TP.JOB_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
             JOB_ID = ar_FK_EMP_JOB.job_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_JOB;

   PROCEDURE ar_FK_EMP_JOB (
      job_id_in IN EMPLOYEE_TP.JOB_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
             JOB_ID = ar_FK_EMP_JOB.job_id_in

      ;
   END ar_FK_EMP_JOB;

   -- Number of rows by FK_EMP_JOB
   FUNCTION num_FK_EMP_JOB (
      job_id_in IN EMPLOYEE_TP.JOB_ID_t
      )
      RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      SELECT COUNT(*)
        INTO retval
        FROM EMPLOYEE
       WHERE
             JOB_ID = job_id_in
      ;
      RETURN retval;
   END num_FK_EMP_JOB;

   FUNCTION ex_FK_EMP_JOB (
      job_id_in IN EMPLOYEE_TP.JOB_ID_t
      )
      RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      SELECT 1 INTO l_dummy
        FROM EMPLOYEE
       WHERE
             JOB_ID = job_id_in
      ;
      RETURN TRUE;
   EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
             WHEN TOO_MANY_ROWS THEN RETURN TRUE;
   END ex_FK_EMP_JOB;

   FUNCTION ar_FK_EMP_MANAGER_cv (
      manager_id_in IN EMPLOYEE_TP.MANAGER_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
          MANAGER_ID = ar_FK_EMP_MANAGER_cv.manager_id_in

         ;
      RETURN retval;
   END ar_FK_EMP_MANAGER_cv;

   FUNCTION in_FK_EMP_MANAGER_cv (
      manager_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
          MANAGER_ID IN (' || manager_id_in || ')
             '
         ;
      RETURN retval;
   END in_FK_EMP_MANAGER_cv;

   FUNCTION ar_FK_EMP_MANAGER (
      manager_id_in IN EMPLOYEE_TP.MANAGER_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
             MANAGER_ID = ar_FK_EMP_MANAGER.manager_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_MANAGER;

   PROCEDURE ar_FK_EMP_MANAGER (
      manager_id_in IN EMPLOYEE_TP.MANAGER_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
             MANAGER_ID = ar_FK_EMP_MANAGER.manager_id_in

      ;
   END ar_FK_EMP_MANAGER;

   -- Number of rows by FK_EMP_MANAGER
   FUNCTION num_FK_EMP_MANAGER (
      manager_id_in IN EMPLOYEE_TP.MANAGER_ID_t
      )
      RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      SELECT COUNT(*)
        INTO retval
        FROM EMPLOYEE
       WHERE
             MANAGER_ID = manager_id_in
      ;
      RETURN retval;
   END num_FK_EMP_MANAGER;

   FUNCTION ex_FK_EMP_MANAGER (
      manager_id_in IN EMPLOYEE_TP.MANAGER_ID_t
      )
      RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      SELECT 1 INTO l_dummy
        FROM EMPLOYEE
       WHERE
             MANAGER_ID = manager_id_in
      ;
      RETURN TRUE;
   EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
             WHEN TOO_MANY_ROWS THEN RETURN TRUE;
   END ex_FK_EMP_MANAGER;

    -- Number of rows in table
   FUNCTION tabcount (
      where_clause_in IN VARCHAR2 := NULL)
      RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      IF where_clause_in IS NULL
      THEN
         SELECT COUNT(*) INTO retval FROM EMPLOYEE;
      ELSE
         EXECUTE IMMEDIATE
            'SELECT COUNT(*) FROM EMPLOYEE
              WHERE ' || where_clause_in
            INTO retval;
      END IF;
      RETURN retval;
   END tabcount;

   -- Number of rows by primary key
   FUNCTION pkycount (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
      )
   RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      SELECT COUNT(*)
        INTO retval
        FROM EMPLOYEE
       WHERE
             EMPLOYEE_ID = employee_id_in
      ;
      RETURN retval;
   END pkycount;

   -- Number of rows in table
   FUNCTION ex_EMPLOYEE (
      where_clause_in IN VARCHAR2 := NULL)
      RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      IF where_clause_in IS NULL
      THEN
         SELECT 1 INTO l_dummy FROM EMPLOYEE;
      ELSE
         EXECUTE IMMEDIATE
            'SELECT 1 FROM EMPLOYEE
              WHERE ' || where_clause_in
            INTO l_dummy;
      END IF;
      RETURN TRUE;
   EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
             WHEN TOO_MANY_ROWS THEN RETURN TRUE;
   END ex_EMPLOYEE;

    -- Number of rows by primary key
   FUNCTION ex_pky (
      employee_id_in IN EMPLOYEE_TP.EMPLOYEE_ID_t
      )
   RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      SELECT 1
        INTO l_dummy
        FROM EMPLOYEE
       WHERE
             EMPLOYEE_ID = employee_id_in
      ;
      RETURN TRUE;
   EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
             WHEN TOO_MANY_ROWS THEN RETURN TRUE;
   END ex_pky;
BEGIN
   NULL;
END EMPLOYEE_QP;
/