Creación del paquete admin_pkg

Esta sección muestra cómo crear el paquete admin_pkg, cómo funcionan sus subprogramas, cómo otorgar el privilegio de ejecución en el paquete al usuario que lo necesita y cómo ese usuario puede llamar a uno de sus subprogramas.

Para crear el paquete admin_pkg:

Conectarse a Oracle Database como app_admin de usuario.

Para obtener instrucciones, consulte "Conexión a Oracle Database desde SQL*Plus" o "Conexión a Oracle Database desde SQL Developer".
Cree los siguientes sinónimos:
```
 CREATE SYNONYM departments FOR app_data.departments;
 CREATE SYNONYM jobs FOR app_data.jobs;
 CREATE SYNONYM departments_sequence FOR app_data.departments_sequence;
```
Puede introducir las sentencias CREATE SYNONYM en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear las tablas con la herramienta SQL Developer Create Synonym.
Creación de la especificación del paquete.
Crear el cuerpo del Paquete.

Consulte además:

Creación de la especificación del paquete para admin_pkg

Nota: Debe estar conectado a Oracle Database como usuario app_admin.

Para crear la especificación de paquete para admin_pkg, la API para administradores de aplicaciones, utilice la siguiente sentencia CREATE PACKAGE. Puede introducir la sentencia en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear el paquete con la herramienta SQL Developer Create Package.

CREATE OR REPLACE PACKAGE admin_pkg
AS
  PROCEDURE update_job
    ( p_job_id      IN jobs.job_id%TYPE,
      p_job_title   IN jobs.job_title%TYPE := NULL,
      p_min_salary  IN jobs.min_salary%TYPE := NULL,
      p_max_salary  IN jobs.max_salary%TYPE := NULL );

  PROCEDURE add_job
    ( p_job_id      IN jobs.job_id%TYPE,
      p_job_title   IN jobs.job_title%TYPE,
      p_min_salary  IN jobs.min_salary%TYPE,
      p_max_salary  IN jobs.max_salary%TYPE );

  PROCEDURE update_department
    ( p_department_id     IN departments.department_id%TYPE,
      p_department_name   IN departments.department_name%TYPE := NULL,
      p_manager_id        IN departments.manager_id%TYPE := NULL,
      p_update_manager_id IN BOOLEAN := FALSE );

  FUNCTION add_department
    ( p_department_name   IN departments.department_name%TYPE,
      p_manager_id        IN departments.manager_id%TYPE )
    RETURN departments.department_id%TYPE;

END admin_pkg;
/

Consulte además:

"Acerca de la Aplicación"
"Creación y Gestión de Paquetes"
Oracle Database PL/SQL Language Reference para obtener información sobre la sentencia CREATE PACKAGE

Creación del cuerpo del paquete para admin_pkg

Nota: Debe estar conectado a Oracle Database como usuario app_admin.

Para crear el cuerpo del paquete para admin_pkg, la API para administradores de aplicaciones, utilice la siguiente sentencia CREATE PACKAGE BODY. Puede introducir la sentencia en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear el paquete con la herramienta SQL Developer Create Body.

CREATE OR REPLACE PACKAGE BODY admin_pkg
AS
  PROCEDURE update_job
    ( p_job_id      IN jobs.job_id%TYPE,
      p_job_title   IN jobs.job_title%TYPE := NULL,
      p_min_salary  IN jobs.min_salary%TYPE := NULL,
      p_max_salary  IN jobs.max_salary%TYPE := NULL )
  IS
  BEGIN
    UPDATE jobs
    SET job_title  = NVL( p_job_title, job_title ),
        min_salary = NVL( p_min_salary, min_salary ),
        max_salary = NVL( p_max_salary, max_salary )
    WHERE job_id = p_job_id;
  END update_job;

  PROCEDURE add_job
    ( p_job_id      IN jobs.job_id%TYPE,
      p_job_title   IN jobs.job_title%TYPE,
      p_min_salary  IN jobs.min_salary%TYPE,
      p_max_salary  IN jobs.max_salary%TYPE )
  IS
  BEGIN
    INSERT INTO jobs ( job_id, job_title, min_salary, max_salary )
    VALUES ( p_job_id, p_job_title, p_min_salary, p_max_salary );
  END add_job;

  PROCEDURE update_department
    ( p_department_id     IN departments.department_id%TYPE,
      p_department_name   IN departments.department_name%TYPE := NULL,
      p_manager_id        IN departments.manager_id%TYPE := NULL,
      p_update_manager_id IN BOOLEAN := FALSE )
  IS
  BEGIN
    IF ( p_update_manager_id ) THEN
      UPDATE departments
      SET department_name = NVL( p_department_name, department_name ),
          manager_id = p_manager_id
      WHERE department_id = p_department_id;
    ELSE
      UPDATE departments
      SET department_name = NVL( p_department_name, department_name )
      WHERE department_id = p_department_id;
    END IF;
  END update_department;

  FUNCTION add_department
    ( p_department_name   IN departments.department_name%TYPE,
      p_manager_id        IN departments.manager_id%TYPE )
    RETURN departments.department_id%TYPE
  IS
    l_department_id departments.department_id%TYPE;
  BEGIN
    INSERT INTO departments ( department_id, department_name, manager_id )
      VALUES ( departments_sequence.NEXTVAL, p_department_name, p_manager_id )
      RETURNING department_id INTO l_department_id;
    RETURN l_department_id;
  END add_department;

END admin_pkg;
/

Consulte además:

"Acerca de la Aplicación"
"Creación y Gestión de Paquetes"
Oracle Database PL/SQL Language Reference para obtener información sobre la sentencia CREATE PACKAGE BODY

Tutorial: Cómo funcionan los subprogramas admin_pkg

Mediante SQL*Plus, este tutorial muestra cómo funcionan los subprogramas del paquete admin_pkg. El tutorial también muestra cómo funciona el trigger jobs_aufer.

Nota: Debe estar conectado a Oracle Database como usuario app_admin desde SQL*Plus.

Pasos para mostrar cómo funcionan los subprogramas admin_pkg:

Muestre la información sobre el trabajo cuyo ID es AD_VP:

 SELECT * FROM jobs WHERE job_id = 'AD_VP';

Resultado:

 JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

 ---------- ----------------------------------- ---------- ----------
 AD_VP      Administration Vice President            15000      30000

Aumente el salario máximo de este puesto y vuelva a mostrar la información sobre él:

 EXEC admin_pkg.update_job( 'AD_VP', p_max_salary => 31000 );
 SELECT * FROM jobs WHERE job_id = 'AD_VP';

Resultado:

 JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

 ---------- ----------------------------------- ---------- ----------
 AD_VP      Administration Vice President            15000      31000

Muestre la información sobre el puesto cuyo ID es IT_PROG:

 SELECT * FROM jobs WHERE job_id = 'IT_PROG';

Resultado:

 JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

 ---------- ----------------------------------- ---------- ----------
 IT_PROG    Programmer                                4000      10000

Intente aumentar el salario máximo para este trabajo:

 EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 );

Resultado (de SQL*Plus):

 SQL> EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 );
 BEGIN admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 ); END;

 *
 ERROR at line 1:
 ORA-20001: Salary update would violate 5 existing employee records
 ORA-06512: at "APP_DATA.JOBS_AUFER", line 12

 ORA-04088: error during execution of trigger 'APP_DATA.JOBS_AUFER'
 ORA-06512: at "APP_ADMIN.ADMIN_PKG", line 10
 ORA-06512: at line 1

Agregue un nuevo trabajo y muestre la información al respecto:

 EXEC admin_pkg.add_job( 'AD_CLERK', 'Administrative Clerk', 3000, 7000 );
 SELECT * FROM jobs WHERE job_id = 'AD_CLERK';

Resultado:

 JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

 ---------- ----------------------------------- ---------- ----------
 AD_CLERK   Administrative Clerk                      3000       7000

Muestre la información sobre el departamento 100:

 SELECT * FROM departments WHERE department_id = 100;

Resultado:

 DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID

 ------------- ------------------------------ ----------
           100 Finance                               108

Cambie el nombre y el mánager del departamento 100 y muestre la información al respecto:

 EXEC admin_pkg.update_department( 100, 'Financial Services' );
 EXEC admin_pkg.update_department( 100, p_manager_id => 111,

   p_update_manager_id => true );
 SELECT * FROM departments WHERE department_id = 100;

Resultado:

 DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID

 ------------- ------------------------------ ----------
           100 Financial Services                    111

Consulte también: "Creación y Gestión de Paquetes"

Asignación del Privilegio de Ejecución a app_admin_user

Nota: Debe estar conectado a Oracle Database como usuario app_admin.

Para otorgar el privilegio de ejecución en el paquete admin_pkg a app_admin_user (administrador de la aplicación), utilice la siguiente sentencia GRANT. Puede introducir la sentencia en SQL*Plus o en la hoja de trabajo de SQL Developer.

GRANT EXECUTE ON admin_pkg TO app_admin_user;

Consulte además:

"Esquemas para la Aplicación"
Consulte Oracle Database SQL Language Reference para obtener información sobre la sentencia GRANT.

Tutorial: Llamando a add_department como app_admin_user

Con SQL*Plus, este tutorial muestra cómo llamar a la función app_admin.admin_pkg.add_department como usuario app_admin_user (administrador de la aplicación) y, a continuación, ver la información sobre el nuevo departamento.

Pasos para llamar al administrador_pkg.add_department como app_admin_user:

Conéctese a Oracle Database como usuario app_admin_user desde SQL*Plus.

Para obtener instrucciones, consulte "Conexión a Oracle Database desde SQL*Plus".

Creación de este sinónimo:

 CREATE SYNONYM admin_pkg FOR app_admin.admin_pkg;

Declare una variable de enlace para el valor de retorno de la función:
```
 VARIABLE n NUMBER
```

Añada un nuevo departamento sin un gerente:

 EXEC :n := admin_pkg.add_department( 'New department', NULL );

Muestre el ID del mánager del nuevo departamento:
```
 PRINT :n
```
Resultado:
```
         N

 ----------
       275
```

Pasos para ver la información sobre el nuevo departamento:

Conectarse a Oracle Database como app_admin de usuario.

Muestre la información sobre el nuevo departamento:

 SELECT * FROM departments WHERE department_name LIKE 'New department%';

Resultado:

 DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID

 ------------- ------------------------------ ----------
           275 New department