te_employee.pkb

OReilly Oracle PL SQL Programming第4版源代码

              first_name,
              middle_initial
           FROM employee
          WHERE
             employee_id = employee_id_in
      ;
   END;

   FUNCTION i_employee_name$rcv (
      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 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
             last_name = i_employee_name$rcv.last_name_in AND
             first_name = i_employee_name$rcv.first_name_in AND
             middle_initial = i_employee_name$rcv.middle_initial_in
             ;
   END;

   --// For each update column ... //--

   FUNCTION hire_date$val (
      employee_id_in IN employee.employee_id%TYPE
      )
   RETURN employee.hire_date%TYPE
   IS
      CURSOR onecol_cur
      IS
         SELECT hire_date
           FROM employee
          WHERE
             employee_id = employee_id_in
         ;
      retval employee.hire_date%TYPE;
   BEGIN
      OPEN onecol_cur;
      FETCH onecol_cur INTO retval;
      CLOSE onecol_cur;
      RETURN retval;
   END hire_date$val;
   --// For each update column ... //--

   FUNCTION salary$val (
      employee_id_in IN employee.employee_id%TYPE
      )
   RETURN employee.salary%TYPE
   IS
      CURSOR onecol_cur
      IS
         SELECT salary
           FROM employee
          WHERE
             employee_id = employee_id_in
         ;
      retval employee.salary%TYPE;
   BEGIN
      OPEN onecol_cur;
      FETCH onecol_cur INTO retval;
      CLOSE onecol_cur;
      RETURN retval;
   END salary$val;

   --// Count of all rows in table and for each foreign key. //--
   FUNCTION rowcount RETURN INTEGER
   IS
      retval INTEGER;
   BEGIN
      SELECT COUNT(*) INTO retval FROM employee;
      RETURN retval;
   END;

   FUNCTION pkyrowcount (
      employee_id_in IN employee.employee_id%TYPE
      )
      RETURN INTEGER
   IS
      retval INTEGER;
   BEGIN
      SELECT COUNT(*)
        INTO retval
        FROM employee
       WHERE
         employee_id = employee_id_in
         ;
      RETURN retval;
   END;

   FUNCTION emp_dept_lookuprowcount (
      department_id_in IN employee.department_id%TYPE
      )
      RETURN INTEGER
   IS
      retval INTEGER;
   BEGIN
      SELECT COUNT(*) INTO retval
        FROM employee
       WHERE
          department_id = emp_dept_lookuprowcount.department_id_in
          ;
      RETURN retval;
   END;
   FUNCTION emp_job_lookuprowcount (
      job_id_in IN employee.job_id%TYPE
      )
      RETURN INTEGER
   IS
      retval INTEGER;
   BEGIN
      SELECT COUNT(*) INTO retval
        FROM employee
       WHERE
          job_id = emp_job_lookuprowcount.job_id_in
          ;
      RETURN retval;
   END;
   FUNCTION emp_mgr_lookuprowcount (
      manager_id_in IN employee.manager_id%TYPE
      )
      RETURN INTEGER
   IS
      retval INTEGER;
   BEGIN
      SELECT COUNT(*) INTO retval
        FROM employee
       WHERE
          manager_id = emp_mgr_lookuprowcount.manager_id_in
          ;
      RETURN retval;
   END;

   PROCEDURE lookup_fkydescs (
      --// Foreign key columns for emp_dept_lookup --//
      department_id_in IN employee.department_id%TYPE,
      emp_dept_lookup_out OUT te_department.i_department_name_rt,
      --// Foreign key columns for emp_job_lookup --//
      job_id_in IN employee.job_id%TYPE,
      emp_job_lookup_out OUT te_job.i_job_function_rt,
      --// Foreign key columns for emp_mgr_lookup --//
      manager_id_in IN employee.manager_id%TYPE,
      emp_mgr_lookup_out OUT te_employee.i_employee_name_rt,
      record_error BOOLEAN := TRUE
      )
   IS
   BEGIN
      emp_dept_lookup_out :=
         te_department.i_department_name$val (
            department_id_in
            );
      emp_job_lookup_out :=
         te_job.i_job_function$val (
            job_id_in
            );
      emp_mgr_lookup_out :=
         te_employee.i_employee_name$val (
            manager_id_in
            );
   EXCEPTION
      WHEN OTHERS
      THEN
         IF record_error
         THEN
            plvexc.recnstop;
         END IF;
         RAISE;
   END lookup_fkydescs;

   --// Generate the next primary key: single column PKYs only --//
   FUNCTION nextpky RETURN employee.employee_id%TYPE
   IS
      retval employee.employee_id%TYPE;
   BEGIN
      SELECT EMPLOYEE_ID_SEQ.NEXTVAL INTO retval FROM dual;
      RETURN retval;
   END;

--// Check Constraint Validation --//

   --// Check Constraint: DEPARTMENT_ID > 0 AND (salary > 0 OR salary IS NULL) --//
   FUNCTION employee$complex$chk (
      department_id_in IN employee.department_id%TYPE,
      salary_in IN employee.salary%TYPE
      ) RETURN BOOLEAN
   IS
   BEGIN
      RETURN (DEPARTMENT_ID_in > 0 AND (SALARY_in > 0 OR SALARY_in IS NULL));
   END employee$complex$chk;

   --// Check Constraint: DEPARTMENT_ID IS NOT NULL --//
   FUNCTION notnull_department_id$chk (
      department_id_in IN employee.department_id%TYPE
      ) RETURN BOOLEAN
   IS
   BEGIN
      RETURN (DEPARTMENT_ID_in IS NOT NULL);
   END notnull_department_id$chk;

   --// Check Constraint: EMPLOYEE_ID IS NOT NULL --//
   FUNCTION notnull_employee_id$chk (
      employee_id_in IN employee.employee_id%TYPE
      ) RETURN BOOLEAN
   IS
   BEGIN
      RETURN (EMPLOYEE_ID_in IS NOT NULL);
   END notnull_employee_id$chk;

   --// Check Constraint: "HIRE_DATE" IS NOT NULL --//
   FUNCTION sys_c002591$chk (
      hire_date_in IN employee.hire_date%TYPE
      ) RETURN BOOLEAN
   IS
   BEGIN
      RETURN (HIRE_DATE_in IS NOT NULL);
   END sys_c002591$chk;

   --// Check Constraint: "CHANGED_BY" IS NOT NULL --//
   FUNCTION sys_c002594$chk (
      changed_by_in IN employee.changed_by%TYPE
      ) RETURN BOOLEAN
   IS
   BEGIN
      RETURN (CHANGED_BY_in IS NOT NULL);
   END sys_c002594$chk;

   --// Check Constraint: "CHANGED_ON" IS NOT NULL --//
   FUNCTION sys_c002595$chk (
      changed_on_in IN employee.changed_on%TYPE
      ) RETURN BOOLEAN
   IS
   BEGIN
      RETURN (CHANGED_ON_in IS NOT NULL);
   END sys_c002595$chk;

   PROCEDURE validate (
      employee_id_in IN employee.employee_id%TYPE,
      hire_date_in IN employee.hire_date%TYPE,
      salary_in IN employee.salary%TYPE,
      department_id_in IN employee.department_id%TYPE,
      changed_by_in IN employee.changed_by%TYPE,
      changed_on_in IN employee.changed_on%TYPE,
      record_error IN BOOLEAN := TRUE
      )
   IS
   BEGIN
      IF NOT employee$complex$chk (
         department_id_in,
         salary_in
         )
      THEN
         --//** General mechanism! //--
         plvexc.raise (-20000, 'ora-2290: check constraint (employee$complex) failed!');
      END IF;
      IF NOT notnull_department_id$chk (
         department_id_in
         )
      THEN
         plvexc.raise (-20000, 'value of department_id cannot be null.');
      END IF;
      IF NOT notnull_employee_id$chk (
         employee_id_in
         )
      THEN
         plvexc.raise (-20000, 'value of employee_id cannot be null.');
      END IF;
      IF NOT sys_c002591$chk (
         hire_date_in
         )
      THEN
         --//** General mechanism! //--
         plvexc.raise (-20000, 'ora-2290: check constraint (sys_c002591) failed!');
      END IF;
      IF NOT sys_c002594$chk (
         changed_by_in
         )
      THEN
         --//** General mechanism! //--
         plvexc.raise (-20000, 'ora-2290: check constraint (sys_c002594) failed!');
      END IF;
      IF NOT sys_c002595$chk (
         changed_on_in
         )
      THEN
         --//** General mechanism! //--
         plvexc.raise (-20000, 'ora-2290: check constraint (sys_c002595) failed!');
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         IF record_error
         THEN
            plvexc.recnstop;
         END IF;
         RAISE;
   END validate;

   PROCEDURE validate (
      rec_in IN allcols_rt,
      record_error IN BOOLEAN := TRUE
      )
   IS
   BEGIN
      validate (
         rec_in.employee_id,
         rec_in.hire_date,
         rec_in.salary,
         rec_in.department_id,
         rec_in.changed_by,
         rec_in.changed_on,
         record_error
         );
   END validate;
--// Update Processing --//

   PROCEDURE reset$frc IS
   BEGIN
      frcflg := emptyfrc;
   END reset$frc;

   FUNCTION last_name$frc (last_name_in IN employee.last_name%TYPE DEFAULT NULL)
      RETURN employee.last_name%TYPE
   IS
   BEGIN
      frcflg.last_name := c_set;
      RETURN last_name_in;
   END last_name$frc;

   FUNCTION first_name$frc (first_name_in IN employee.first_name%TYPE DEFAULT NULL)
      RETURN employee.first_name%TYPE
   IS
   BEGIN
      frcflg.first_name := c_set;
      RETURN first_name_in;
   END first_name$frc;

   FUNCTION middle_initial$frc (middle_initial_in IN employee.middle_initial%TYPE DEFAULT NULL)
      RETURN employee.middle_initial%TYPE
   IS
   BEGIN
      frcflg.middle_initial := c_set;
      RETURN middle_initial_in;
   END middle_initial$frc;

   FUNCTION job_id$frc (job_id_in IN employee.job_id%TYPE DEFAULT NULL)
      RETURN employee.job_id%TYPE
   IS
   BEGIN
      frcflg.job_id := c_set;
      RETURN job_id_in;
   END job_id$frc;

   FUNCTION manager_id$frc (manager_id_in IN employee.manager_id%TYPE DEFAULT NULL)
      RETURN employee.manager_id%TYPE
   IS
   BEGIN
      frcflg.manager_id := c_set;
      RETURN manager_id_in;
   END manager_id$frc;

   FUNCTION hire_date$frc (hire_date_in IN employee.hire_date%TYPE DEFAULT NULL)
      RETURN employee.hire_date%TYPE
   IS
   BEGIN
      frcflg.hire_date := c_set;
      RETURN hire_date_in;
   END hire_date$frc;

   FUNCTION salary$frc (salary_in IN employee.salary%TYPE DEFAULT NULL)
      RETURN employee.salary%TYPE
   IS
   BEGIN
      frcflg.salary := c_set;
      RETURN salary_in;
   END salary$frc;

   FUNCTION commission$frc (commission_in IN employee.commission%TYPE DEFAULT NULL)
      RETURN employee.commission%TYPE
   IS
   BEGIN
      frcflg.commission := c_set;
      RETURN commission_in;
   END commission$frc;

   FUNCTION department_id$frc (department_id_in IN employee.department_id%TYPE DEFAULT NULL)
      RETURN employee.department_id%TYPE
   IS
   BEGIN
      frcflg.department_id := c_set;
      RETURN department_id_in;
   END department_id$frc;

   FUNCTION changed_by$frc (changed_by_in IN employee.changed_by%TYPE DEFAULT NULL)
      RETURN employee.changed_by%TYPE
   IS
   BEGIN
      frcflg.changed_by := c_set;
      RETURN changed_by_in;
   END changed_by$frc;

   FUNCTION changed_on$frc (changed_on_in IN employee.changed_on%TYPE DEFAULT NULL)
      RETURN employee.changed_on%TYPE
   IS
   BEGIN
      frcflg.changed_on := c_set;
      RETURN changed_on_in;
   END changed_on$frc;

   PROCEDURE upd (
      employee_id_in IN employee.employee_id%TYPE,
      last_name_in IN employee.last_name%TYPE DEFAULT NULL,
      first_name_in IN employee.first_name%TYPE DEFAULT NULL,
      middle_initial_in IN employee.middle_initial%TYPE DEFAULT NULL,
      job_id_in IN employee.job_id%TYPE DEFAULT NULL,
      manager_id_in IN employee.manager_id%TYPE DEFAULT NULL,
      hire_date_in IN employee.hire_date%TYPE DEFAULT NULL,
      salary_in IN employee.salary%TYPE DEFAULT NULL,
      commission_in IN employee.commission%TYPE DEFAULT NULL,
      department_id_in IN employee.department_id%TYPE DEFAULT NULL,
      changed_by_in IN employee.changed_by%TYPE DEFAULT NULL,
      changed_on_in IN employee.changed_on%TYPE DEFAULT NULL,
      rowcount_out OUT INTEGER,
      reset_in IN BOOLEAN DEFAULT TRUE
      )
   IS
   BEGIN
      IF PLVxmn.traceactive ('upd', PLVxmn.l_upd)
      THEN
         PLVxmn.trace ('upd', PLVxmn.l_upd,
            '-employee_id='|| employee_id_in ||
            '-last_name='|| last_name_in ||
            '-first_name='|| first_name_in ||
            '-middle_initial='|| middle_initial_in ||
            '-job_id='|| job_id_in ||
            '-manager_id='|| manager_id_in ||
            '-hire_date='|| hire_date_in ||
            '-salary='|| salary_in ||
            '-commission='|| commission_in ||
            '-department_id='|| department_id_in ||
            '-changed_by='|| changed_by_in ||
            '-changed_on='|| changed_on_in
            , override => TRUE);
      END IF;
      UPDATE employee SET
         last_name = DECODE (frcflg.last_name, c_set, UPPER(last_name_in),
            NVL (UPPER(last_name_in), last_name)),
         first_name = DECODE (frcflg.first_name, c_set, UPPER(first_name_in),
            NVL (UPPER(first_name_in), first_name)),
         middle_initial = DECODE (frcflg.middle_initial, c_set, UPPER(middle_initial_in),
            NVL (UPPER(middle_initial_in), middle_initial)),
         job_id = DECODE (frcflg.job_id, c_set, job_id_in,
            NVL (job_id_in, job_id)),
         manager_id = DECODE (frcflg.manager_id, c_set, manager_id_in,
            NVL (manager_id_in, manager_id)),
         hire_date = DECODE (frcflg.hire_date, c_set, TRUNC(hire_date_in),
            NVL (TRUNC(hire_date_in), hire_date)),
         salary = DECODE (frcflg.salary, c_set, salary_in,
            NVL (salary_in, salary)),
         commission = DECODE (frcflg.commission, c_set, commission_in,
            NVL (commission_in, commission)),
         department_id = DECODE (frcflg.department_id, c_set, department_id_in,
            NVL (department_id_in, department_id)),
         changed_by = DECODE (frcflg.changed_by, c_set, USER,
            NVL (USER, changed_by)),
         changed_on = DECODE (frcflg.changed_on, c_set, SYSDATE,
            NVL (SYSDATE, changed_on))
       WHERE
          employee_id = employee_id_in
         ;
      rowcount_out := SQL%ROWCOUNT;
      IF reset_in THEN reset$frc; END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         plvexc.recnstop;
   END upd;

   --// Record-based Update --//
   PROCEDURE upd (
      rec_in IN allcols_rt,
      rowcount_out OUT INTEGER,
      reset_in IN BOOLEAN DEFAULT TRUE)
   IS
   BEGIN
      upd (
         rec_in.employee_id,
         rec_in.last_name,
         rec_in.first_name,
         rec_in.middle_initial,
         rec_in.job_id,
         rec_in.manager_id,