Creación del paquete employees_pkg

Esta sección muestra cómo crear el paquete employees_pkg, cómo funcionan sus subprogramas, cómo otorgar el privilegio de ejecución en el paquete a los usuarios que lo necesitan y cómo esos usuarios pueden llamar a uno de sus subprogramas.

Pasos para crear el paquete employees_pkg:

Conectarse a Oracle Database como app_code 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 OR REPLACE SYNONYM employees FOR app_data.employees;
 CREATE OR REPLACE SYNONYM departments FOR app_data.departments;
 CREATE OR REPLACE SYNONYM jobs FOR app_data.jobs;
 CREATE OR REPLACE SYNONYM job_history FOR app_data.job_history;

Puede introducir las sentencias CREATE SYNONYM en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear los sinónimos con la herramienta Crear sinónimo de SQL Developer.

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 employees_pkg

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

Para crear la especificación de paquete para employees_pkg, la API para los mánager, 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 employees_pkg
AS
  PROCEDURE get_employees_in_dept
    ( p_deptno     IN     employees.department_id%TYPE,
      p_result_set IN OUT SYS_REFCURSOR );

  PROCEDURE get_job_history
    ( p_employee_id  IN     employees.department_id%TYPE,
      p_result_set   IN OUT SYS_REFCURSOR );

  PROCEDURE show_employee
    ( p_employee_id  IN     employees.employee_id%TYPE,
      p_result_set   IN OUT SYS_REFCURSOR );

  PROCEDURE update_salary
    ( p_employee_id IN employees.employee_id%TYPE,
      p_new_salary  IN employees.salary%TYPE );

  PROCEDURE change_job
    ( p_employee_id IN employees.employee_id%TYPE,
      p_new_job     IN employees.job_id%TYPE,
      p_new_salary  IN employees.salary%TYPE := NULL,
      p_new_dept    IN employees.department_id%TYPE := NULL );

END employees_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 employees_pkg

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

Para crear el cuerpo del paquete para employees_pkg, la API para los mánager, 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 employees_pkg
AS
  PROCEDURE get_employees_in_dept
    ( p_deptno     IN     employees.department_id%TYPE,
      p_result_set IN OUT SYS_REFCURSOR )
  IS
     l_cursor SYS_REFCURSOR;
  BEGIN
    OPEN p_result_set FOR
      SELECT e.employee_id,
        e.first_name || ' ' || e.last_name name,
        TO_CHAR( e.hire_date, 'Dy Mon ddth, yyyy' ) hire_date,
        j.job_title,
        m.first_name || ' ' || m.last_name manager,
        d.department_name
      FROM employees e INNER JOIN jobs j ON (e.job_id = j.job_id)
        LEFT OUTER JOIN employees m ON (e.manager_id = m.employee_id)
        INNER JOIN departments d ON (e.department_id = d.department_id)
      WHERE e.department_id = p_deptno ;
  END get_employees_in_dept;

  PROCEDURE get_job_history
    ( p_employee_id  IN     employees.department_id%TYPE,
      p_result_set   IN OUT SYS_REFCURSOR )
  IS
  BEGIN
    OPEN p_result_set FOR
      SELECT e.First_name || ' ' || e.last_name name, j.job_title,
        e.job_start_date start_date,
        TO_DATE(NULL) end_date
      FROM employees e INNER JOIN jobs j ON (e.job_id = j.job_id)
      WHERE e.employee_id = p_employee_id
      UNION ALL
      SELECT e.First_name || ' ' || e.last_name name,
        j.job_title,
        jh.start_date,
        jh.end_date
      FROM employees e INNER JOIN job_history jh
        ON (e.employee_id = jh.employee_id)
        INNER JOIN jobs j ON (jh.job_id = j.job_id)
      WHERE e.employee_id = p_employee_id
      ORDER BY start_date DESC;
  END get_job_history;

  PROCEDURE show_employee
    ( p_employee_id  IN     employees.employee_id%TYPE,
      p_result_set   IN OUT sys_refcursor )
  IS
  BEGIN
    OPEN p_result_set FOR
      SELECT *
      FROM (SELECT TO_CHAR(e.employee_id) employee_id,
              e.first_name || ' ' || e.last_name name,
              e.email_addr,
              TO_CHAR(e.hire_date,'dd-mon-yyyy') hire_date,
              e.country_code,
              e.phone_number,
              j.job_title,
              TO_CHAR(e.job_start_date,'dd-mon-yyyy') job_start_date,
              to_char(e.salary) salary,
              m.first_name || ' ' || m.last_name manager,
              d.department_name
            FROM employees e INNER JOIN jobs j on (e.job_id = j.job_id)
              RIGHT OUTER JOIN employees m ON (m.employee_id = e.manager_id)
              INNER JOIN departments d ON (e.department_id = d.department_id)
            WHERE e.employee_id = p_employee_id)
      UNPIVOT (VALUE FOR ATTRIBUTE IN (employee_id, name, email_addr, hire_date,
        country_code, phone_number, job_title, job_start_date, salary, manager,
        department_name) );
  END show_employee;

  PROCEDURE update_salary
    ( p_employee_id IN employees.employee_id%type,
      p_new_salary  IN employees.salary%type )
  IS
  BEGIN
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
  END update_salary;

  PROCEDURE change_job
    ( p_employee_id IN employees.employee_id%TYPE,
      p_new_job     IN employees.job_id%TYPE,
      p_new_salary  IN employees.salary%TYPE := NULL,
      p_new_dept    IN employees.department_id%TYPE := NULL )
  IS
  BEGIN
    INSERT INTO job_history (employee_id, start_date, end_date, job_id,
      department_id)
    SELECT employee_id, job_start_date, TRUNC(SYSDATE), job_id, department_id
      FROM employees
      WHERE employee_id = p_employee_id;

    UPDATE employees
    SET job_id = p_new_job,
      department_id = NVL( p_new_dept, department_id ),
      salary = NVL( p_new_salary, salary ),
      job_start_date = TRUNC(SYSDATE)
    WHERE employee_id = p_employee_id;
  END change_job;
END employees_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 employees_pkg

Con SQL*Plus, este tutorial muestra cómo funcionan los subprogramas del paquete employees_pkg. El tutorial también muestra cómo funcionan el disparador employees_aiufer y la restricción CHECK job_history_date_check.

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

Para utilizar SQL*Plus para mostrar cómo funcionan los subprogramas employees_pkg:

Utilice comandos de formato para mejorar la legibilidad de la salida. Por ejemplo:

 SET LINESIZE 80
 SET RECSEP WRAPPED
 SET RECSEPCHAR "="
 COLUMN NAME FORMAT A15 WORD_WRAPPED
 COLUMN HIRE_DATE FORMAT A20 WORD_WRAPPED
 COLUMN DEPARTMENT_NAME FORMAT A10 WORD_WRAPPED
 COLUMN JOB_TITLE FORMAT A29 WORD_WRAPPED
 COLUMN MANAGER FORMAT A11 WORD_WRAPPED

Declare una variable de enlace para el valor del parámetro de subprograma p_result_set:
```
 VARIABLE c REFCURSOR
```

Mostrar los empleados en el departamento 90:

 EXEC employees_pkg.get_employees_in_dept( 90, :c );
 PRINT c

Resultado:

 EMPLOYEE_ID NAME            HIRE_DATE            JOB_TITLE

 ----------- --------------- -------------------- --------------------------
 MANAGER     DEPARTMENT

 ----------- ----------
         100 Steven King     Tue Jun 17th, 2003   President
             Executive                                                         ===========================================================================
         102 Lex De Haan     Sat Jan 13th, 2001   Administration Vice President
 Steven King Executive
 ===========================================================================
         101 Neena Kochhar   Wed Sep 21st, 2005   Administration Vice President
 Steven King Executive
 ===========================================================================

Muestre el historial de puestos del empleado 101:

 EXEC employees_pkg.get_job_history( 101, :c );
 PRINT c

Resultado:

 NAME            JOB_TITLE                     START_DAT END_DATE

 --------------- ----------------------------- --------- ---------
 Neena Kochhar   Administration Vice President 16-MAR-05
 Neena Kochhar   Accounting Manager            28-OCT-01 15-MAR-05
 Neena Kochhar   Public Accountant             21-SEP-97 27-OCT-01

Muestra información general sobre el empleado 101:

 EXEC employees_pkg.show_employee( 101, :c );
 PRINT c

Resultado:

 ATTRIBUTE       VALUE

 --------------- ----------------------------------------------
 EMPLOYEE_ID     101
 NAME            Neena Kochhar
 EMAIL_ADDR      NKOCHHAR
 HIRE_DATE       21-sep-2005
 COUNTRY_CODE    +1
 PHONE_NUMBER    515.123.4568

 JOB_TITLE       Administration Vice President
 JOB_START_DATE  16-mar-05

 SALARY          17000
 MANAGER         Steven King
 DEPARTMENT_NAME Executive

 11 rows selected.

Muestre la información sobre el vicepresidente de administración de puestos:

 SELECT * FROM jobs WHERE job_title = 'Administration Vice President';

Resultado:

 JOB_ID     JOB_TITLE                     MIN_SALARY MAX_SALARY

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

Trate de darle a la empleada 101 un nuevo salario fuera del rango para su trabajo:

 EXEC employees_pkg.update_salary( 101, 30001 );

Resultado:

 SQL> EXEC employees_pkg.update_salary( 101, 30001 );
 BEGIN employees_pkg.update_salary( 101, 30001 ); END;

 *
 ERROR at line 1:
 ORA-20002: Salary modification invalid
 ORA-06512: at "APP_DATA.EMPLOYEES_AIUFER", line 13

 ORA-04088: error during execution of trigger 'APP_DATA.EMPLOYEES_AIUFER'
 ORA-06512: at "APP_CODE.EMPLOYEES_PKG", line 77
 ORA-06512: at line 1

Proporcione a la empleada 101 un nuevo salario dentro del rango para su trabajo y muestre de nuevo información general sobre ella:

 EXEC employees_pkg.update_salary( 101, 18000 );
 EXEC employees_pkg.show_employee( 101, :c );
 PRINT c

Resultado:

 ATTRIBUTE       VALUE

 --------------- ----------------------------------------------
 EMPLOYEE_ID     101
 NAME            Neena Kochhar
 EMAIL_ADDR      NKOCHHAR
 HIRE_DATE       21-sep-2005
 COUNTRY_CODE    +1
 PHONE_NUMBER    515.123.4568
 JOB_TITLE       Administration Vice President
 JOB_START_DATE  16-mar-05

 SALARY          18000
 MANAGER         Steven King
 DEPARTMENT_NAME Executive

 11 rows selected.

Cambie el trabajo del empleado 101 a su trabajo actual con un salario inferior:

 EXEC employees_pkg.change_job( 101, 'AD_VP', 17500, 90 );

Resultado:

 SQL> exec employees_pkg.change_job( 101, 'AD_VP', 17500, 90 );
 BEGIN employees_pkg.change_job( 101, 'AD_VP', 17500, 80 ); END;

 *
 ERROR at line 1:

 ORA-02290: check constraint (APP_DATA.JOB_HISTORY_DATE_CHECK) violated
 ORA-06512: at "APP_CODE.EMPLOYEES_PKG", line 101
 ORA-06512: at line 1

Muestra información sobre el empleado. (Tenga en cuenta que la sentencia del paso anterior no modificó el salario; es 18000, no 17500).

exec employees_pkg.show_employee( 101, :c );
print c

Resultado:

ATTRIBUTE       VALUE

--------------- ----------------------------------------------
EMPLOYEE_ID     101
NAME            Neena Kochhar
EMAIL_ADDR      NKOCHHAR
HIRE_DATE       21-sep-2005
COUNTRY_CODE    +1
PHONE_NUMBER    515.123.4568
JOB_TITLE       Administration Vice President
JOB_START_DATE  10-mar-2015
SALARY          18000
MANAGER         Steven King
DEPARTMENT_NAME Executive

11 rows selected.

Consulte además:

Guía del usuario y referencia de SQL*Plus para obtener información sobre los comandos de SQL*Plus
"Creación y Gestión de Paquetes"

Asignación del Privilegio de Ejecución a app_user y app_admin_user

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

Para otorgar el privilegio de ejecución en el paquete employees_pkg a app_user (normalmente un gestor) y app_admin_user (un administrador de aplicaciones), utilice las siguientes sentencias GRANT (en cualquier orden). Puede introducir las sentencias en SQL*Plus o en la hoja de trabajo de SQL Developer.

GRANT EXECUTE ON employees_pkg TO app_user;
GRANT EXECUTE ON employees_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 get_job_history como app_user o app_admin_user

Con SQL*Plus, en este tutorial se muestra cómo llamar al subprograma app_code.employees_pkg.get_job_history como el usuario app_user (normalmente un gestor) o app_admin_user (un administrador de aplicaciones).

Pasos para llamar a employees_pkg.get_job_history como app_user o app_admin_user:

Conéctese a Oracle Database como usuario app_user o 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 employees_pkg FOR app_code.employees_pkg;

Muestre el historial de puestos del empleado 101:

 EXEC employees_pkg.get_job_history( 101, :c );
 PRINT c

Resultado:

 NAME            JOB_TITLE                     START_DAT END_DATE

 --------------- ----------------------------- --------- ---------
 Neena Kochhar   Administration Vice President 16-MAR-05 15-MAY-12
 Neena Kochhar   Accounting Manager            28-OCT-01 15-MAR-05
 Neena Kochhar   Public Accountant             21-SEP-97 27-OCT-01