Creación de Objetos de Esquema y Carga de Datos

En esta sección se muestra cómo crear las tablas, editar vistas, disparadores y secuencias para la aplicación, cómo cargar datos en las tablas y cómo otorgar privilegios en estos objetos de esquema a los usuarios que los necesitan.

Pasos para crear los objetos de esquema y cargar los datos:

Conectarse a Oracle Database como app_data de usuario.

Para obtener instrucciones, consulte "Conexión a Oracle Database desde SQL*Plus" o "Conexión a Oracle Database desde SQL Developer".
Cree las tablas, con todas las restricciones necesarias, excepto la restricción de clave ajena que debe agregar después de cargar los datos.
Cree las vistas de edición.
Cree los disparadores.
Cree las secuencias.
Cargue los datos en las tablas.
Agregue la limitación de clave ajena.

Creación de las tablas

En esta sección se muestra cómo crear las tablas para la aplicación, con todas las restricciones necesarias, excepto una, que debe agregar después de cargar los datos.

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

En el siguiente procedimiento, puede introducir las sentencias en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear las tablas con la herramienta Create Table de SQL Developer.

Pasos para crear las tablas:

Create jobs#, que almacena información sobre los trabajos de la compañía (una fila para cada trabajo):

 CREATE TABLE jobs#
 ( job_id      VARCHAR2(10)
               CONSTRAINT jobs_pk PRIMARY KEY,
   job_title   VARCHAR2(35)
               CONSTRAINT jobs_job_title_not_null NOT NULL,
   min_salary  NUMBER(6)
               CONSTRAINT jobs_min_salary_not_null NOT NULL,
   max_salary  NUMBER(6)
               CONSTRAINT jobs_max_salary_not_null NOT NULL
 )
 /

Cree departments#, que almacena información sobre los departamentos de la compañía (una fila para cada departamento):

 CREATE TABLE departments#
 ( department_id    NUMBER(4)
                   CONSTRAINT departments_pk PRIMARY KEY,
   department_name  VARCHAR2(30)
                   CONSTRAINT department_name_not_null NOT NULL
                   CONSTRAINT department_name_unique UNIQUE,
   manager_id       NUMBER(6)
 )
 /

Create employees#, que almacena información sobre los empleados de la compañía (una fila para cada empleado):

 CREATE TABLE employees#
 ( employee_id     NUMBER(6)
                   CONSTRAINT employees_pk PRIMARY KEY,
   first_name      VARCHAR2(20)
                   CONSTRAINT emp_first_name_not_null NOT NULL,
   last_name       VARCHAR2(25)
                   CONSTRAINT emp_last_name_not_null NOT NULL,
   email_addr      VARCHAR2(25)
                   CONSTRAINT emp_email_addr_not_null NOT NULL,
   hire_date       DATE
                   DEFAULT TRUNC(SYSDATE)
                   CONSTRAINT emp_hire_date_not_null NOT NULL
                   CONSTRAINT emp_hire_date_check
                     CHECK(TRUNC(hire_date) = hire_date),
   country_code    VARCHAR2(5)
                   CONSTRAINT emp_country_code_not_null NOT NULL,
   phone_number    VARCHAR2(20)
                   CONSTRAINT emp_phone_number_not_null NOT NULL,

   job_id          CONSTRAINT emp_job_id_not_null NOT NULL
                   CONSTRAINT emp_jobs_fk REFERENCES jobs#,
   job_start_date  DATE
                   CONSTRAINT emp_job_start_date_not_null NOT NULL,
                   CONSTRAINT emp_job_start_date_check
                     CHECK(TRUNC(JOB_START_DATE) = job_start_date),
   salary          NUMBER(6)
                   CONSTRAINT emp_salary_not_null NOT NULL,

   manager_id      CONSTRAINT emp_mgr_to_empno_fk REFERENCES employees#,

   department_id   CONSTRAINT emp_to_dept_fk REFERENCES departments#
 )
 /

Las razones de las restricciones de la REF son las siguientes:

Un empleado debe tener un trabajo existente. Es decir, los valores de la columna employees#.job_id también deben ser valores de la columna jobs#.job_id.
Un empleado debe tener un mánager que también sea empleado. Es decir, los valores de la columna employees#.manager_id también deben ser valores de la columna employees#.employee_id.
Un empleado debe trabajar en un departamento existente. Es decir, los valores de la columna employees#.department_id también deben ser valores de la columna departments#.department_id.

Además, el gerente de un empleado debe ser el gerente del departamento en el que trabaja el empleado. Es decir, los valores de la columna employees#.manager_id también deben ser valores de la columna departments#.manager_id. Sin embargo, no ha podido especificar la restricción necesaria al crear departments#, porque employees# aún no existía. Por lo tanto, debe agregar una restricción de clave ajena a departments# más adelante (consulte "Adición de la Restricción de Clave Ajena").

Cree job_history#, que almacena el historial de puestos de cada empleado de la compañía (una fila para cada puesto ocupado por el empleado):

 CREATE TABLE job_history#
 ( employee_id  CONSTRAINT job_hist_to_employees_fk REFERENCES employees#,
   job_id       CONSTRAINT job_hist_to_jobs_fk REFERENCES jobs#,
   start_date   DATE
               CONSTRAINT job_hist_start_date_not_null NOT NULL,
   end_date     DATE
               CONSTRAINT job_hist_end_date_not_null NOT NULL,
   department_id
             CONSTRAINT job_hist_to_departments_fk REFERENCES departments#
             CONSTRAINT job_hist_dept_id_not_null NOT NULL,
             CONSTRAINT job_history_pk PRIMARY KEY(employee_id,start_date),
             CONSTRAINT job_history_date_check CHECK( start_date < end_date )
 )
 /

Los motivos de las restricciones de referencia son que el empleado, el puesto y el departamento deben existir:

Los valores de la columna job_history#.employee_id también deben ser valores de la columna employees#.employee_id.
Los valores de la columna job_history#.job_id también deben ser valores de la columna jobs#.job_id.
Los valores de la columna job_history#.department_id también deben ser valores de la columna departments#.department_id.

Consulte también: "Creación de Tablas"

Creación de Vistas de Edición

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

Para crear las vistas de edición, utilice las siguientes sentencias (en cualquier orden). Puede introducir las sentencias en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear las vistas de edición con la herramienta SQL Developer Create View.

CREATE OR REPLACE EDITIONING VIEW jobs AS SELECT * FROM jobs#
/
CREATE OR REPLACE EDITIONING VIEW departments AS SELECT * FROM departments#
/
CREATE OR REPLACE EDITIONING VIEW employees AS SELECT * FROM employees#
/
CREATE OR REPLACE EDITIONING VIEW job_history AS SELECT * FROM job_history#
/

Nota: La aplicación siempre debe hacer referencia a las tablas base a través de las vistas de edición. De lo contrario, las vistas de edición no cubren las tablas y no puede utilizar EBR para actualizar la aplicación terminada cuando está en uso.

Consulte además:

"Creación de vistas"
Oracle Database Development Guide para obtener información general sobre la edición de vistas
Oracle Database Development Guide para obtener información sobre la preparación de una aplicación para utilizar vistas de edición

Creación de los disparadores

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

Los disparadores de la aplicación aplican las siguientes reglas de negocio:

Un empleado con el puesto j debe tener un salario entre los salarios mínimo y máximo para el puesto j.
Si un empleado con el trabajo j tiene salarios s, no puede cambiar el salario mínimo de j a un valor mayor que s ni el salario máximo de j a un valor menor que s. (Para ello, los datos existentes no serían válidos).

Consulte también: Uso de disparadores, para obtener información sobre los disparadores

Creación del Disparador para Aplicar la Primera Regla de Negocio

La primera regla de negocio es: un empleado con el puesto j debe tener un salario entre los salarios mínimo y máximo para el puesto j.

Esta regla se puede violar cuando se inserta una nueva fila en la tabla employees o cuando se actualiza la columna salary o job_id de la tabla employees.

Para aplicar la regla, cree el siguiente disparador en la vista de edición de empleados. Puede introducir la sentencia CREATE TRIGGER en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear el disparador con la herramienta SQL Developer Create Trigger.

CREATE OR REPLACE TRIGGER employees_aiufer
AFTER INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW
DECLARE
  l_cnt NUMBER;
BEGIN
  LOCK TABLE jobs IN SHARE MODE;  -- Ensure that jobs does not change
                                  -- during the following query.
  SELECT COUNT(*) INTO l_cnt
  FROM jobs
  WHERE job_id = :NEW.job_id
  AND :NEW.salary BETWEEN min_salary AND max_salary;

  IF (l_cnt<>1) THEN
    RAISE_APPLICATION_ERROR( -20002,
      CASE
        WHEN :new.job_id = :old.job_id
        THEN 'Salary modification invalid'
        ELSE 'Job reassignment puts salary out of range'
      END );
  END IF;
END;
/

LOCK TABLE jobs IN SHARE MODE impide que otros usuarios cambien los trabajos de tabla mientras el disparador realiza la consulta. La prevención de cambios en los trabajos durante la consulta es necesaria porque las lecturas sin bloqueo evitan que el disparador "vea" los cambios que otros usuarios realizan en los trabajos mientras el disparador cambia de empleados (y evitan que esos usuarios "vean" los cambios que el disparador realiza en los empleados).

Otra forma de evitar cambios en los trabajos durante la consulta es incluir la cláusula FOR UPDATE en la sentencia SELECT. Sin embargo, SELECT FOR UPDATE restringe la simultaneidad más que los trabajos LOCK TABLE IN SHARE MODE.

LOCK TABLE jobs IN SHARE MODE impide que otros usuarios cambien los trabajos, pero no bloquean los trabajos en modo de recurso compartido. Los cambios en los puestos de trabajo probablemente serán mucho más raros que los cambios en los empleados. Por lo tanto, el bloqueo de trabajos en modo de recurso compartido proporciona más simultaneidad que el bloqueo de una sola fila de trabajos en modo exclusivo.

Consulte además:

Oracle Database Development Guide para obtener información sobre el bloqueo de tablas en SHARE MODE
Referencia de lenguaje PL/SQL de Oracle Database para obtener información sobre SELECT FOR UPDATE
"Creación de Disparadores"
"Tutorial: Show How the employees_pkg Subprograms Work" para ver cómo funciona el disparador employees_aiufer

Creación del Disparador para Aplicar la Segunda Regla de Negocio

La segunda regla de negocio es: si un empleado con el puesto j tiene salarios s, no puede cambiar el salario mínimo de j a un valor mayor que s o el salario máximo de j a un valor menor que s. (Para ello, los datos existentes no serían válidos).

Esta regla se puede violar cuando se actualiza la columna min_salary o max_salary de la tabla de puestos.

Para aplicar la regla, cree el siguiente disparador en los trabajos de vista de edición. Puede introducir la sentencia CREATE TRIGGER en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear el disparador con la herramienta SQL Developer Create Trigger.

CREATE OR REPLACE TRIGGER jobs_aufer
AFTER UPDATE OF min_salary, max_salary ON jobs FOR EACH ROW
WHEN (NEW.min_salary > OLD.min_salary OR NEW.max_salary < OLD.max_salary)
DECLARE
  l_cnt NUMBER;
BEGIN
  LOCK TABLE employees IN SHARE MODE;

  SELECT COUNT(*) INTO l_cnt
  FROM employees
  WHERE job_id = :NEW.job_id
  AND salary NOT BETWEEN :NEW.min_salary and :NEW.max_salary;

  IF (l_cnt>0) THEN
    RAISE_APPLICATION_ERROR( -20001,
      'Salary update would violate ' || l_cnt || ' existing employee records' );
  END IF;
END;
/

LOCK TABLE employees IN SHARE MODE impide que otros usuarios cambien los empleados de la tabla mientras el disparador realiza la consulta. Es necesario evitar cambios en los empleados durante la consulta porque las lecturas sin bloqueo impiden que el disparador "ve" los cambios que otros usuarios realizan en los empleados mientras el disparador cambia de trabajo (y evitan que esos usuarios "vean" los cambios que el disparador realiza en los trabajos).

Para este disparador, SELECT FOR UPDATE no es una alternativa a LOCK TABLE IN SHARE MODE. Mientras intenta cambiar el rango salarial de este trabajo, este disparador debe impedir que otros usuarios cambien un salario para que esté fuera del nuevo rango. Por lo tanto, el disparador debe bloquear todas las filas de la tabla employees que tengan este job_id y bloquear todas las filas que alguien pueda actualizar para que tengan este job_id.

Una alternativa a LOCK TABLE employees IN SHARE MODE es utilizar el paquete DBMS_LOCK para crear un bloqueo con nombre con el nombre de job_id y, a continuación, utilizar disparadores en las tablas employees y jobs para utilizar este bloqueo con nombre a fin de evitar actualizaciones simultáneas. Sin embargo, el uso de DBMS_LOCK y varios disparadores afecta negativamente al rendimiento del tiempo de ejecución.

Otra alternativa a LOCK TABLE employees IN SHARE MODE es crear un disparador en la tabla employees que, para cada fila cambiada de empleados, bloquee la fila de trabajo correspondiente en los trabajos. Sin embargo, este enfoque provoca un trabajo excesivo en las actualizaciones de la tabla employees, que son frecuentes.

LOCK TABLE employees IN SHARE MODE es más sencillo que las alternativas anteriores, y los cambios en la tabla de trabajos son raros y es probable que se produzcan en el momento del mantenimiento de la aplicación, al bloquear la tabla no molestan a los usuarios.

Consulte además:

Oracle Database Development Guide para obtener información sobre el bloqueo de tablas con SHARE MODE
Oracle Database PL/SQL Packages and Types Reference para obtener información sobre el paquete DBMS_LOCK
"Creación de Disparadores"
"Tutorial: Showing How the admin_pkg Subprograms Work" (Tutorial: Cómo funcionan los subprogramas admin_pkg)

Creación de secuencias

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

Para crear las secuencias que generan claves primarias únicas para los nuevos departamentos y empleados, utilice las siguientes sentencias (en cualquier orden). Puede introducir las sentencias en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede crear las secuencias con la herramienta Create Sequence de SQL Developer.

CREATE SEQUENCE employees_sequence START WITH 210;
CREATE SEQUENCE departments_sequence START WITH 275;

Para evitar conflictos con los datos que cargará de las tablas del esquema de ejemplo HR, los números iniciales de employees_sequence y departments_sequence deben superar los valores máximos de employees.employee_id y departments.department_id, respectivamente. Después de "Cargar los Datos", esta consulta muestra los siguientes valores máximos:

SELECT MAX(e.employee_id), MAX(d.department_id)
FROM employees e, departments d;

Resultado:

MAX(E.EMPLOYEE_ID) MAX(D.DEPARTMENT_ID)
------------------ --------------------
               206                  270

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

Carga de los datos

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

Cargue las tablas de la aplicación con datos de las tablas del esquema de ejemplo HR.

Nota: El siguiente procedimiento hace referencia a las tablas de la aplicación a través de sus vistas de edición.

En el siguiente procedimiento, puede introducir las sentencias en SQL*Plus o en la hoja de trabajo de SQL Developer.

Para cargar datos en las tablas:

Carga de trabajos con datos de la tabla HR.JOBS:

 INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
 SELECT job_id, job_title, min_salary, max_salary
   FROM HR.JOBS
 /

Resultado:

 19 rows created.

Carga de departamentos con datos de la tabla HR.DEPARTMENTS:

 INSERT INTO departments (department_id, department_name, manager_id)
 SELECT department_id, department_name, manager_id
   FROM HR.DEPARTMENTS
 /

Resultado:

 27 rows created.

Cargar empleados con datos de las tablas HR.EMPLOYEES y HR.JOB_HISTORY, mediante expresiones CASE y funciones SQL buscadas para obtener employees.country_code y employees.phone_number de las funciones HR.phone_number y SQL, y una subconsulta escalar para obtener employees.job_start_date de HR.JOB_HISTORY:

 INSERT INTO employees (employee_id, first_name, last_name, email_addr,
   hire_date, country_code, phone_number, job_id, job_start_date, salary,
   manager_id, department_id)
 SELECT employee_id, first_name, last_name, email, hire_date,
   CASE WHEN phone_number LIKE '011.%'
     THEN '+' || SUBSTR( phone_number, INSTR( phone_number, '.' )+1,
       INSTR( phone_number, '.', 1, 2 ) -  INSTR( phone_number, '.' ) - 1 )
     ELSE '+1'
   END country_code,
   CASE WHEN phone_number LIKE '011.%'
     THEN SUBSTR( phone_number, INSTR(phone_number, '.', 1, 2 )+1 )
     ELSE phone_number
   END phone_number,
   job_id,
   NVL( (SELECT MAX(end_date+1)
         FROM HR.JOB_HISTORY jh
         WHERE jh.employee_id = employees.employee_id), hire_date),
   salary, manager_id, department_id
   FROM HR.EMPLOYEES
 /

Resultado:

 107 rows created.

Nota: La sentencia INSERT anterior arranca el disparador creado en "Creating the Trigger to Enforce the First Business Rule".

Cargar job_history con datos de la tabla HR.JOB_HISTORY:

 INSERT INTO job_history (employee_id, job_id, start_date, end_date,
   department_id)
 SELECT employee_id, job_id, start_date, end_date, department_id
   FROM HR.JOB_HISTORY
 /

Resultado:

 10 rows created.

Confirme los cambios:
```
 COMMIT;
```

Consulte además:

"Acerca de la Sentencia INSERT"
"Acerca de HR de Esquema de Ejemplo"
"Uso de Expresiones CASE en Consultas"
"Uso de Funciones Relacionadas con NULL en Consultas" para obtener información sobre la función NVL
Consulte Oracle Database SQL Language Reference para obtener información sobre las funciones SQL.

Adición de la restricción de clave ajena

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

Ahora que los departamentos y empleados de las tablas contienen datos, agregue una restricción de clave ajena con la siguiente sentencia ALTER TABLE. Puede introducir la sentencia en SQL*Plus o en la hoja de trabajo de SQL Developer. También puede agregar la restricción con la herramienta SQL Developer Add Foreign Key.

ALTER TABLE departments#
ADD CONSTRAINT dept_to_emp_fk
FOREIGN KEY(manager_id) REFERENCES employees#;

Si agrega esta restricción de clave ajena antes de que los departamentos# y employees# contengan datos, obtendrá este error al intentar cargar cualquiera de ellos con datos:

ORA-02291: integrity constraint (APP_DATA.JOB_HIST_TO_DEPT_FK) violated - parent key not found

Tutorial: "Tutorial: Adición de Restricciones a Tablas Existentes"

Asignación de Privilegios en Objetos de Esquema a Usuarios

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

Para otorgar privilegios a los usuarios, utilice la sentencia SQL GRANT. Puede introducir las sentencias GRANT en SQL*Plus o en la hoja de trabajo de SQL Developer.

Otorgue a app_code solo los privilegios que necesita para crear employees_pkg:

GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app_code;
GRANT SELECT ON departments TO app_code;
GRANT SELECT ON jobs TO app_code;
GRANT SELECT, INSERT on job_history TO app_code;
GRANT SELECT ON employees_sequence TO app_code;

Otorgue a app_admin solo los privilegios que necesita para crear admin_pkg:

GRANT SELECT, INSERT, UPDATE, DELETE ON jobs TO app_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON departments TO app_admin;
GRANT SELECT ON employees_sequence TO app_admin;
GRANT SELECT ON departments_sequence TO app_admin;

Consulte también: Referencia de lenguaje SQL de Oracle Database para obtener información sobre la sentencia GRANT