department_qp.pkb

OReilly Oracle PL SQL Programming第4版源代码

            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
        INTO retval
        FROM DEPARTMENT
       WHERE
            NAME = name_in
      ;
      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 => 'DEPARTMENT'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'NAME'
              ,value_in => 'name_in'
              ,validate_in => FALSE
              );
            qd_runtime.raise_error_instance (
              err_instance_id_in => l_err_instance_id);
         END;
   END or_I_DEPARTMENT_NAME;

   FUNCTION or_I_DEPARTMENT_NAME_cv (
      name_in IN DEPARTMENT_TP.NAME_t
      )
      RETURN DEPARTMENT_TP.DEPARTMENT_rc
   IS
      retval DEPARTMENT_TP.DEPARTMENT_rc;
   BEGIN
      OPEN retval FOR
         SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT
          WHERE
            NAME = name_in
             ;
      RETURN retval;
   END or_I_DEPARTMENT_NAME_cv;

   FUNCTION pky_I_DEPARTMENT_NAME (
      name_in IN DEPARTMENT_TP.NAME_t
      )
      RETURN DEPARTMENT_TP.DEPARTMENT_ID_t
   IS
      retval DEPARTMENT_TP.DEPARTMENT_ID_t;
   BEGIN
      SELECT DEPARTMENT_ID
        INTO retval
        FROM DEPARTMENT
       WHERE
            NAME = name_in
      ;
      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 => 'DEPARTMENT'
              ,validate_in => FALSE
              );
            qd_runtime.add_context (
               err_instance_id_in => l_err_instance_id
              ,NAME_IN => 'NAME'
              ,value_in => 'name_in'
              ,validate_in => FALSE
              );
            qd_runtime.raise_error_instance (
              err_instance_id_in => l_err_instance_id);
         END;
   END pky_I_DEPARTMENT_NAME;

   -- Number of rows by I_DEPARTMENT_NAME
   FUNCTION num_I_DEPARTMENT_NAME (
      name_in IN DEPARTMENT_TP.NAME_t
      )
      RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      SELECT COUNT(*)
        INTO retval
        FROM DEPARTMENT
       WHERE
            NAME = name_in
      ;
      RETURN retval;
   END num_I_DEPARTMENT_NAME;

   FUNCTION ex_I_DEPARTMENT_NAME (
      name_in IN DEPARTMENT_TP.NAME_t
      )
      RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      SELECT 1 INTO l_dummy
        FROM DEPARTMENT
       WHERE
             NAME = name_in
      ;
      RETURN TRUE;
   EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
             WHEN TOO_MANY_ROWS THEN RETURN TRUE;
   END ex_I_DEPARTMENT_NAME;

   FUNCTION ar_FK_DEPT_LOC_cv (
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t
      )
      RETURN DEPARTMENT_TP.DEPARTMENT_rc
   IS
      retval DEPARTMENT_TP.DEPARTMENT_rc;
   BEGIN
      OPEN retval FOR
         SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT
       WHERE
          LOC_ID = ar_FK_DEPT_LOC_cv.loc_id_in

         ;
      RETURN retval;
   END ar_FK_DEPT_LOC_cv;

   FUNCTION in_FK_DEPT_LOC_cv (
      loc_id_in IN VARCHAR2
      )
      RETURN DEPARTMENT_TP.weak_refcur
   IS
      retval DEPARTMENT_TP.weak_refcur;
   BEGIN
      OPEN retval FOR
         'SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT
       WHERE
          LOC_ID IN (' || loc_id_in || ')
             '
         ;
      RETURN retval;
   END in_FK_DEPT_LOC_cv;

   FUNCTION ar_FK_DEPT_LOC (
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t
      )
      RETURN DEPARTMENT_TP.DEPARTMENT_tc
   IS
      CURSOR allrows_cur
      IS
         SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
           FROM DEPARTMENT
          WHERE
             LOC_ID = ar_FK_DEPT_LOC.loc_id_in

         ;
      l_rows PLS_INTEGER;
      retval DEPARTMENT_TP.DEPARTMENT_tc;
   BEGIN
      OPEN allrows_cur;
      FETCH allrows_cur BULK COLLECT INTO retval;
      RETURN retval;
   END ar_FK_DEPT_LOC;

   PROCEDURE ar_FK_DEPT_LOC (
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t,
      department_id_out OUT DEPARTMENT_TP.DEPARTMENT_ID_cc,
      name_out OUT DEPARTMENT_TP.NAME_cc,
      loc_id_out OUT DEPARTMENT_TP.LOC_ID_cc,
      created_by_out OUT DEPARTMENT_TP.CREATED_BY_cc,
      created_on_out OUT DEPARTMENT_TP.CREATED_ON_cc,
      changed_by_out OUT DEPARTMENT_TP.CHANGED_BY_cc,
      changed_on_out OUT DEPARTMENT_TP.CHANGED_ON_cc
      )
   IS
   BEGIN
      SELECT
            DEPARTMENT_ID,
            NAME,
            LOC_ID,
            CREATED_BY,
            CREATED_ON,
            CHANGED_BY,
            CHANGED_ON
        BULK COLLECT INTO
            department_id_out,
            name_out,
            loc_id_out,
            created_by_out,
            created_on_out,
            changed_by_out,
            changed_on_out
        FROM DEPARTMENT
       WHERE
             LOC_ID = ar_FK_DEPT_LOC.loc_id_in

      ;
   END ar_FK_DEPT_LOC;

   -- Number of rows by FK_DEPT_LOC
   FUNCTION num_FK_DEPT_LOC (
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t
      )
      RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      SELECT COUNT(*)
        INTO retval
        FROM DEPARTMENT
       WHERE
             LOC_ID = loc_id_in
      ;
      RETURN retval;
   END num_FK_DEPT_LOC;

   FUNCTION ex_FK_DEPT_LOC (
      loc_id_in IN DEPARTMENT_TP.LOC_ID_t
      )
      RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      SELECT 1 INTO l_dummy
        FROM DEPARTMENT
       WHERE
             LOC_ID = loc_id_in
      ;
      RETURN TRUE;
   EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
             WHEN TOO_MANY_ROWS THEN RETURN TRUE;
   END ex_FK_DEPT_LOC;

    -- 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 DEPARTMENT;
      ELSE
         EXECUTE IMMEDIATE
            'SELECT COUNT(*) FROM DEPARTMENT
              WHERE ' || where_clause_in
            INTO retval;
      END IF;
      RETURN retval;
   END tabcount;

   -- Number of rows by primary key
   FUNCTION pkycount (
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t
      )
   RETURN PLS_INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      SELECT COUNT(*)
        INTO retval
        FROM DEPARTMENT
       WHERE
             DEPARTMENT_ID = department_id_in
      ;
      RETURN retval;
   END pkycount;

   -- Number of rows in table
   FUNCTION ex_DEPARTMENT (
      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 DEPARTMENT;
      ELSE
         EXECUTE IMMEDIATE
            'SELECT 1 FROM DEPARTMENT
              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_DEPARTMENT;

    -- Number of rows by primary key
   FUNCTION ex_pky (
      department_id_in IN DEPARTMENT_TP.DEPARTMENT_ID_t
      )
   RETURN BOOLEAN
   IS
      l_dummy PLS_INTEGER;
   BEGIN
      SELECT 1
        INTO l_dummy
        FROM DEPARTMENT
       WHERE
             DEPARTMENT_ID = department_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 DEPARTMENT_QP;
/