te_employee.pkb

OReilly Oracle PL SQL Programming第4版源代码

   END;

   PROCEDURE close_emp_job_lookup_name_cur
   IS BEGIN
      IF emp_job_lookup_name_cur%ISOPEN
      THEN
         CLOSE emp_job_lookup_name_cur;
      END IF;
   END;

   PROCEDURE close_emp_mgr_lookup_name_cur
   IS BEGIN
      IF emp_mgr_lookup_name_cur%ISOPEN
      THEN
         CLOSE emp_mgr_lookup_name_cur;
      END IF;
   END;

   PROCEDURE close_allforpky_cur
   IS BEGIN
      IF allforpky_cur%ISOPEN
      THEN
         CLOSE allforpky_cur;
      END IF;
   END;

   PROCEDURE close_allbypky_cur
   IS BEGIN
      IF allbypky_cur%ISOPEN
      THEN
         CLOSE allbypky_cur;
      END IF;
   END;

   PROCEDURE close_emp_dept_lookup_all_cur
   IS BEGIN
      IF emp_dept_lookup_all_cur%ISOPEN
      THEN
         CLOSE emp_dept_lookup_all_cur;
      END IF;
   END;

   PROCEDURE close_emp_job_lookup_all_cur
   IS BEGIN
      IF emp_job_lookup_all_cur%ISOPEN
      THEN
         CLOSE emp_job_lookup_all_cur;
      END IF;
   END;

   PROCEDURE close_emp_mgr_lookup_all_cur
   IS BEGIN
      IF emp_mgr_lookup_all_cur%ISOPEN
      THEN
         CLOSE emp_mgr_lookup_all_cur;
      END IF;
   END;

   PROCEDURE closeall
   IS
   BEGIN
      close_compforpky_cur;
      close_compbypky_cur;
      close_emp_dept_lookup_comp_cur;
      close_emp_job_lookup_comp_cur;
      close_emp_mgr_lookup_comp_cur;
      close_nameforpky_cur;
      close_namebypky_cur;
      close_emp_dept_lookup_name_cur;
      close_emp_job_lookup_name_cur;
      close_emp_mgr_lookup_name_cur;
      close_allforpky_cur;
      close_allbypky_cur;
      close_emp_dept_lookup_all_cur;
      close_emp_job_lookup_all_cur;
      close_emp_mgr_lookup_all_cur;
   END;

--// Functions returning Cursor Variables for each cursor //--

   --// All columns for all rows //--
   FUNCTION allbypky$cv RETURN employee_cvt
   IS
      retval employee_cvt;
   BEGIN
      OPEN retval FOR
         SELECT
             employee_id,
             last_name,
             first_name,
             middle_initial,
             job_id,
             manager_id,
             hire_date,
             salary,
             commission,
             department_id,
             changed_by,
             changed_on
           FROM employee
          ORDER BY
            employee_id
         ;
      RETURN retval;
   END;

   --// All columns for one row //--
   FUNCTION allforpky$cv (
      employee_id_in IN employee.employee_id%TYPE
      )
   RETURN employee_cvt
   IS
      retval employee_cvt;
   BEGIN
      OPEN retval FOR
         SELECT
             employee_id,
             last_name,
             first_name,
             middle_initial,
             job_id,
             manager_id,
             hire_date,
             salary,
             commission,
             department_id,
             changed_by,
             changed_on
           FROM employee
          WHERE
            employee_id = allforpky$cv.employee_id_in
         ;
      RETURN retval;
   END;

   --// For each foreign key.... //--
   FUNCTION emp_dept_lookup_all$cv (
      department_id_in IN employee.department_id%TYPE
      )
   RETURN employee_cvt
   IS
      retval employee_cvt;
   BEGIN
      OPEN retval FOR
         SELECT
             employee_id,
             last_name,
             first_name,
             middle_initial,
             job_id,
             manager_id,
             hire_date,
             salary,
             commission,
             department_id,
             changed_by,
             changed_on
           FROM employee
          WHERE
             department_id = emp_dept_lookup_all$cv.department_id_in
             ;
      RETURN retval;
   END;

   --// For each foreign key.... //--
   FUNCTION emp_job_lookup_all$cv (
      job_id_in IN employee.job_id%TYPE
      )
   RETURN employee_cvt
   IS
      retval employee_cvt;
   BEGIN
      OPEN retval FOR
         SELECT
             employee_id,
             last_name,
             first_name,
             middle_initial,
             job_id,
             manager_id,
             hire_date,
             salary,
             commission,
             department_id,
             changed_by,
             changed_on
           FROM employee
          WHERE
             job_id = emp_job_lookup_all$cv.job_id_in
             ;
      RETURN retval;
   END;

   --// For each foreign key.... //--
   FUNCTION emp_mgr_lookup_all$cv (
      manager_id_in IN employee.manager_id%TYPE
      )
   RETURN employee_cvt
   IS
      retval employee_cvt;
   BEGIN
      OPEN retval FOR
         SELECT
             employee_id,
             last_name,
             first_name,
             middle_initial,
             job_id,
             manager_id,
             hire_date,
             salary,
             commission,
             department_id,
             changed_by,
             changed_on
           FROM employee
          WHERE
             manager_id = emp_mgr_lookup_all$cv.manager_id_in
             ;
      RETURN retval;
   END;

--// Emulate aggregate-level record operations. //--

   FUNCTION recseq (rec1 IN allcols_rt, rec2 IN allcols_rt)
   RETURN BOOLEAN
   IS
      unequal_records EXCEPTION;
      retval BOOLEAN;
   BEGIN
      retval := rec1.employee_id = rec2.employee_id OR
         (rec1.employee_id IS NULL AND rec2.employee_id IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.last_name = rec2.last_name OR
         (rec1.last_name IS NULL AND rec2.last_name IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.first_name = rec2.first_name OR
         (rec1.first_name IS NULL AND rec2.first_name IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.middle_initial = rec2.middle_initial OR
         (rec1.middle_initial IS NULL AND rec2.middle_initial IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.job_id = rec2.job_id OR
         (rec1.job_id IS NULL AND rec2.job_id IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.manager_id = rec2.manager_id OR
         (rec1.manager_id IS NULL AND rec2.manager_id IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.hire_date = rec2.hire_date OR
         (rec1.hire_date IS NULL AND rec2.hire_date IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.salary = rec2.salary OR
         (rec1.salary IS NULL AND rec2.salary IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.commission = rec2.commission OR
         (rec1.commission IS NULL AND rec2.commission IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.department_id = rec2.department_id OR
         (rec1.department_id IS NULL AND rec2.department_id IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.changed_by = rec2.changed_by OR
         (rec1.changed_by IS NULL AND rec2.changed_by IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      retval := rec1.changed_on = rec2.changed_on OR
         (rec1.changed_on IS NULL AND rec2.changed_on IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      RETURN TRUE;
   EXCEPTION
      WHEN unequal_records THEN RETURN FALSE;
   END;

   FUNCTION recseq (rec1 IN pky_rt, rec2 IN pky_rt)
   RETURN BOOLEAN
   IS
      unequal_records EXCEPTION;
      retval BOOLEAN;
   BEGIN
      retval := rec1.employee_id = rec2.employee_id OR
         (rec1.employee_id IS NULL AND rec2.employee_id IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
      RETURN TRUE;
   EXCEPTION
      WHEN unequal_records THEN RETURN FALSE;
   END;

--// Is the primary key NOT NULL? //--

   FUNCTION isnullpky (
      rec_in IN allcols_rt
      )
   RETURN BOOLEAN
   IS
   BEGIN
      RETURN
         rec_in.employee_id IS NULL
         ;
   END;

   FUNCTION isnullpky (
      rec_in IN pky_rt
      )
   RETURN BOOLEAN
   IS
   BEGIN
      RETURN
         rec_in.employee_id IS NULL
         ;
   END;

--// Query Processing --//

   FUNCTION onerow_internal (
      employee_id_in IN employee.employee_id%TYPE
      )
   RETURN allcols_rt
   IS
      CURSOR onerow_cur
      IS
         SELECT
            employee_id,
            last_name,
            first_name,
            middle_initial,
            job_id,
            manager_id,
            hire_date,
            salary,
            commission,
            department_id,
            changed_by,
            changed_on
           FROM employee
          WHERE
             employee_id = employee_id_in
      ;
      onerow_rec allcols_rt;
   BEGIN
      OPEN onerow_cur;
      FETCH onerow_cur INTO onerow_rec;
      CLOSE onerow_cur;
      RETURN onerow_rec;
   END onerow_internal;

   FUNCTION onerow (
      employee_id_in IN employee.employee_id%TYPE
      )
   RETURN allcols_rt
   IS
      retval allcols_rt;
   BEGIN
      IF loadtab.EXISTS (employee_id_in)
      THEN
         retval := loadtab(employee_id_in);
      ELSE
         retval := onerow_internal (employee_id_in);
         IF retval.employee_id IS NOT NULL
         THEN
            --// Load the data into the table. --//
            loadtab(employee_id_in) := retval;
         END IF;
      END IF;
      RETURN retval;
   END onerow;

   FUNCTION onerow$cv (
      employee_id_in IN employee.employee_id%TYPE
      )
   RETURN employee_cvt
   IS
      retval employee_cvt;
   BEGIN
      OPEN retval FOR
         SELECT
            employee_id,
            last_name,
            first_name,
            middle_initial,
            job_id,
            manager_id,
            hire_date,
            salary,
            commission,
            department_id,
            changed_by,
            changed_on
           FROM employee
          WHERE
             employee_id = employee_id_in
      ;
   END;


   FUNCTION i_employee_name$pky (
      last_name_in IN employee.last_name%TYPE,
      first_name_in IN employee.first_name%TYPE,
      middle_initial_in IN employee.middle_initial%TYPE
      )
      RETURN pky_rt
   IS
      CURSOR getpky_cur
      IS
         SELECT
            employee_id
           FROM employee
          WHERE
            last_name = i_employee_name$pky.last_name_in AND
            first_name = i_employee_name$pky.first_name_in AND
            middle_initial = i_employee_name$pky.middle_initial_in
            ;

      getpky_rec getpky_cur%ROWTYPE;
      retval pky_rt;
   BEGIN
      OPEN getpky_cur;
      FETCH getpky_cur INTO getpky_rec;
      IF getpky_cur%FOUND
      THEN
         retval.employee_id := getpky_rec.employee_id;
      END IF;
      CLOSE getpky_cur;
      RETURN retval;
   END i_employee_name$pky;

   FUNCTION i_employee_name$row (
      last_name_in IN employee.last_name%TYPE,
      first_name_in IN employee.first_name%TYPE,
      middle_initial_in IN employee.middle_initial%TYPE
      )
   RETURN allcols_rt
   IS
      CURSOR onerow_cur
      IS
         SELECT
            employee_id,
            last_name,
            first_name,
            middle_initial,
            job_id,
            manager_id,
            hire_date,
            salary,
            commission,
            department_id,
            changed_by,
            changed_on
           FROM employee
          WHERE
             last_name = i_employee_name$row.last_name_in AND
             first_name = i_employee_name$row.first_name_in AND
             middle_initial = i_employee_name$row.middle_initial_in
             ;
      onerow_rec allcols_rt;
   BEGIN
      OPEN onerow_cur;
      FETCH onerow_cur INTO onerow_rec;
      CLOSE onerow_cur;
      RETURN onerow_rec;
   END i_employee_name$row;

   FUNCTION i_employee_name$val (
      employee_id_in IN employee.employee_id%TYPE
      )
   RETURN i_employee_name_rt
   IS
      v_onerow allcols_rt;
      retval i_employee_name_rt;
   BEGIN
      v_onerow := onerow (
         employee_id_in
         );

      retval.last_name := v_onerow.last_name;
      retval.first_name := v_onerow.first_name;
      retval.middle_initial := v_onerow.middle_initial;

      RETURN retval;
   END i_employee_name$val;

   FUNCTION i_employee_name$pcv (
      last_name_in IN employee.last_name%TYPE,
      first_name_in IN employee.first_name%TYPE,
      middle_initial_in IN employee.middle_initial%TYPE
      )
      RETURN pky_cvt
   IS
      retval pky_cvt;
   BEGIN
      OPEN retval FOR
         SELECT
            employee_id
           FROM employee
          WHERE
            last_name = i_employee_name$pcv.last_name_in AND
            first_name = i_employee_name$pcv.first_name_in AND
            middle_initial = i_employee_name$pcv.middle_initial_in
            ;
   END;

   FUNCTION i_employee_name$vcv (
      employee_id_in IN employee.employee_id%TYPE
      )
   RETURN i_employee_name_cvt
   IS
      retval i_employee_name_cvt;
   BEGIN
      OPEN retval FOR
         SELECT
              last_name,