TAREA 9: TRIGGERS

Existen varios tipos de disparadores, dependiendo del tipo de transacción de disparo y el nivel en el que se ejecuta el disparador (trigger):

1.- Disparadores de nivel de fila: se ejecutan una vez para cada fila afectada por una instrucción DML. Los disparadores de nivel de fila se crean utilizando la cláusula for each row en el comando create trigger.

2.- Disparadores de nivel de instrucción: se ejecutan una vez para cada intrucción DML. Por ejemplo, si una única intrucción INSERT inserta 500 filas en una tabla un disparador de nivel de instrucción para dicha tabla sólo se ejecutará una vez. Los disparadores de nivel de instrucción son el tipo predeterminado que se crea con el comando create trigger.

3.- Disparadores Before y After: puesto que los disparadores son ejecutados por sucesos, puede establecerse que se produzcan inmediatamente antes (before) o después (after) de dichos sucesos.

4.- Disparadores Instead Of: puede utilizar INSTEAD OF para indicar a Oracle lo que tiene que hacer en lugar de realizar las acciones que invoca el disparador. Por ejemplo, podría usar un disparador INSTEAD OF en una vista para gestionar las inserciones en una tabla o para actualizar múltiples tablas que son parte de una vista.

5.- Disparadores de esquema: puede crear disparadores sobre operaciones en el nivel de esquema tales como create table, alter table, drop table, audit, rename, truncate y revoke. Puede incluso crear disparadores para impedir que los usuarios eliminen sus propias tablas. En su mayor parte, los disparadores de nivel de esquema proporcionan dos capacidades: impedir operaciones DDL y proporcionar una seguridad adicional que controle las operaciones DDL cuando éstar se producen.

6.- Disparadores en nivel de base de datos: puede crear disparadores que se activen al producirse sucesos de la base de datos, incluyendo errores, inicios de sesión, conexiones y desconexiones. Puede utilizar este tipo de disparador para automatizar el mantenimiento de la base de datos o las acciones de auditoría.

Ejemplo:

Creamos un Trigger que se ejecute cada vez que en la tabla departments sea modificado el manager_id, osea cuando cambia el encargado del area de ese departamento, y se guarda un historial de los cambios de encargado del departamento y cuando fue designado a ese cargo.
Ademas se crea la tabla en la que se guardarán los datos del historial.

CREATE OR REPLACE TABLE MANAGER_HISTORY(
DEPARTMENT_ID NUMBER(4)
CONSTRAINT MAN_HIST_DEP_FK REFERENCES
departments(department_id)
,MANAGER_ID NUMBER(6)
CONSTRAINT MAN_HIST_MAN_FK REFERENCES
employees ON DELETE SET NULL
,DATE_UPDATE DATE
CONSTRAINT MAN_DATE_UPDATE NOT NULL)
;

TRIGGER CAMBIO_MANAGER_DEPT
AFTER UPDATE OF MANAGER_ID ON DEPARTMENTS
REFERENCING OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO MANAGER_HISTORY(DEPARTMENT_ID,
MANAGER_ID, DATE_UPDATE) VALUES (:OLD.DEPARTMENT_ID,
:OLD.MANAGER_ID, SYSDATE);
END;



REFERENCIA: http://www.ajpdsoft.com/modules.php?name=News&file=article&sid=178

Tarea 7 Cursor

Profesor, subimos la tarea 7 realizada por nuestro grupo.

Cursores Explícitos
Los cursores explícitos se utilizan para realizar consultas SELECT que pueden devolver cero filas o mas de una, aunque en también se utilizan para consultas de una fila por su rapidez en la ejecución.

/*Crear un cursor que obtenga todos contactos de los dueños de propiedades en 'Santiago' y la direccion de la propiedad.
*/

BEGIN

IF NOT C1_CTO_DUENO%ISOPEN THEN
OPEN C1_CTO_DUENO('Santiago');
END IF;
FETCH C1_CTO_DUENO INTO v_nom, v_apel, v_tel, v_dir;
DBMS_OUTPUT.PUT_LINE('El Sr(a): '||TRIM(v_nom)||' '||TRIM(v_apel)||' '||v_tel||' es dueño de la propiedad ubicada: '||v_dir);
LOOP
FETCH C1_CTO_DUENO INTO v_nom, v_apel, v_tel, v_dir;
EXIT WHEN C1_CTO_DUENO%NOTFOUND;
END LOOP;
CLOSE C1_CTO_DUENO;
END;

Referencias bibliograficas:

http://www.mundoracle.com/cursores.html?Pg=sql_plsql_13.htm
http://www.desarrolloweb.com/articulos/cursores-pl-sql-I.html

Tarea 6 - Procedimientos Almacenado

El siguiente tema se trata sobre procedimientos almacenados que utilicen las siguientes instrucciones:
· Varray
· Record
· Table
· instruccion case
· %type
· %Rowtype

VARRAYS
Un varray se manipula de forma muy similar a las tablas de PL, pero se implementa de forma diferente. Los elementos en el varray se almacenan comenzando en el índice 1 hasta la longitud máxima declarada en el tipo varray.

La sintaxis:

TYPE <nombre_tipo> IS VARRAY (<tamaño_maximo>) OF <tipo_elementos>;


Ejemplo:
DECLARE

/* Declaramos el tipo VARRAY de cuatro elementos VARCHAR2*/
TYPE t_cadena IS VARRAY(4) OF VARCHAR2(50);

/* Asignamos los valores con un constructor */
v_lista t_cadena:= t_cadena('Gerardo', 'Francisco', ,'','');

BEGIN
v_lista(3) := 'Miguel';
v_lista(4) := 'Marco';

END;


RECORD
Es una estructura de datos en PL/SQL, almacenados en campos, cada uno de los cuales tiene su propio nombre y tipo y que se tratan como una sola unidad lógica.
Los campos de un registro pueden ser inicializados y pueden ser definidos como NOT NULL. Aquellos campos que no sean inicializados explícitamente, se inicializarán a NULL.

La sintaxis:

TYPE <nombre> IS RECORD
(
campo <tipo_datos> [NULL | NOT NULL]
[,<tipo_datos>...]
);

Ejemplo:
DECLARE
TYPE baseDeDatos IS RECORD
(
CODIGO VARCHAR2(50),
DESCRIPCION VARCHAR2(50),
PROFESOR VARCHAR2(20)
);

 /* Declara una variable identificada por baseDeDatos de tipo ASIGNATURA. Esto significa que la variable baseDeDatos tendrá los campos CODIGO, DESCRIPCION y PROFESOR. */

baseDeDatos ASIGNATURA;

BEGIN 

 /* Asignamos valores a los campos de la variable. */ 

 baseDeDatos.CODIGO:= 'ACI800'; 

 baseDeDatos.DESCRIPCION := 'PL SQL';

 baseDeDatos.PROFESOR := 'Erwin Fischer';

 END;


TABLE
Las tablas de PL/SQL son tipos de datos que nos permiten almacenar varios valores del mismo tipo de datos.
Una tabla PL/SQL :
· Es similar a un array
· Tiene dos componenetes: Un índice de tipo BINARY_INTEGER que permite acceder a los elementos en la tabla PL/SQL y una columna de escalares o registros que contiene los valores de la tabla PL/SQL
· Puede incrementar su tamaño dinámicamente.

La sintaxis:

TYPE <nombre_tipo_tabla> IS TABLE OF

 <tipo_datos> [NOT NULL]

 INDEX BY BINARY_INTEGER ;


Ejemplo:

DECLARE

 /* Definimos el tipo PAISES como tabla PL/SQL */ 

 TYPE PAISES IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;

 /* Declaramos una variable del tipo PAISES */

 tPAISES PAISES;

 BEGIN

 tPAISES(1) := 1;

 tPAISES(2) := 2;

 tPAISES(3) := 3;

 END;


INSTRUCCIONES CASE
La estructura condicional CASE permite evaluar una expresion y devolver un valor u otro.

La sintaxis:
CASE <expresion>

      WHEN <valor_expresion> THEN <valor_devuelto>

      WHEN <valor_expresion> THEN <valor_devuelto>

   ELSE <valor_devuelto> -- Valor por defecto 

 END

Ejemplo: 

DECLARE

docente varchar(100),

asignatura varchar(20)

SET asignatura = 'SQL' 

SET docente = (CASE

                     WHEN asignatura = 'SQL SERVER' THEN 'Maoma el profe'

                     WHEN asignatura = 'PL SQL' THEN 'Bueno el Profe'

                     ELSE 'Buscando Profe'

 END)
PRINT docente


%TYPE y %ROWTYPE
Atributos de tipo. Un atributo de tipo PL/SQL es un modificador que puede ser usado para obtener información de un objeto de la base de datos. El atributo %TYPE permite conocer el tipo de una variable, constante o campo de la base de datos. El atributo%ROWTYPE permite obtener los tipos de todos los campos de una tabla de la base de datos, de una vista o de un cursor.

La sintaxis %TYPE :
tipo_campo | variable%TYPE
customers.name%type

La sintaxis %ROWTYPE :
{Cursor_name | cursor_variable_name | nombre_tabla}% ROWTYPE

Ejemplo:

CREATE PROCEDURE simple_procedure IS

 v_location VARCHAR2(15) := ’Granada’;
PI CONSTANT NUMBER := 3.1416;
v_nombre tabla_empleados.nombre%TYPE;

 reg_datos micursor%ROWTYPE;
BEGIN

 /*Parte de ejecucion*/
EXCEPTION 

 /*Parte de excepciones*/
END;


Referencias:
http://www.devjoker.com/contenidos/catss/62/Tipo-VARRAY.aspx
http://www.tutorialspoint.com/plsql/plsql_arrays.htm
http://sql-plsql.blogspot.com/2007/05/oracle-plsql-varrays.html

Creación de una función (número Par o Impar)

Creación de la función

Creamos la función de la siguiente forma

create or replace function esImpar (l_numero IN INTEGER)
RETURN VARCHAR2 IS
l_par VARCHAR2(20);
begin
if l_numero>0 then
    if mod(l_numero,2)=0 then
      l_par:='par';
    else
      l_par:='impar';
    end if;
      return (l_par);
else
   return  'El número debe ser mayor a Cero';
end if;

end;

Ahora ejecutamos la función

declare
l_mensaje VARCHAR2(100);
begin
l_mensaje:=esImpar(3);
DBMS_OUTPUT.PUT_LINE(l_mensaje);
end;

Tarea 4 - Secuencias, Indices y Sinonimos

--Generamos las sequencias requeridas para las tablas.
--Sequence para Factura
CREATE SEQUENCE FacturaSeq
  MINVALUE 1
  MAXVALUE 99999
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

--Sequence para Detalle
CREATE SEQUENCE DetalleSeq
  MINVALUE 1
  MAXVALUE 99999
  START WITH 1
  INCREMENT BY 1
  CACHE 10;

--Creamos las tablas
CREATE TABLE Factura (
FacturaId NUMBER(5) not null constraint factura_pk primary key,
Folio NUMBER (5) not null,
RutCliente VARCHAR2(10) not null,
FechaEmision date);


CREATE TABLE FacturaDetalle (
DetalleId number(5) not null constraint detalle_pk primary key,
FacturaId number(5) not null constraint FacturaDetalle_fk REFERENCES Factura(FacturaId),
CodigoBarraProducto varchar2(13) not null,
Cantidad number(2) not null,
PrecioUnitario number(5) not null);

/*
Algo necesario para saber sobre indices
Un índice único garantiza que no haya dos filas de una tabla con valores duplicados en lacolumna que define el índice.
Una clave de índice único sólo puede apuntar a una fila de latabla.

En un índice no único, una clave sencilla puede tener múltiples filas asociadas con ella.
*/
--En la tabla factura crearemos 2 indices
Create index FacturaFolioIdx on Factura(Folio);

Create index FacturaRutFechaIdx on Factura(RutCliente, FechaEmision);

--Insertamos datos
INSERT INTO Factura(FacturaId, Folio, RutCliente, FechaEmision)
VALUES (FacturaSeq.NEXTVAL, 250,'78000250', to_date('20140420','YYYY-MM-DD'));


INSERT INTO FacturaDetalle(DetalleId, FacturaId, CodigoBarraProducto, Cantidad, PrecioUnitario)
VALUES (DetalleSeq.NEXTVAL, 1,'781234567891', 1, 1500);

INSERT INTO FacturaDetalle(DetalleId, FacturaId, CodigoBarraProducto, Cantidad, PrecioUnitario)
VALUES (DetalleSeq.NEXTVAL, 1,'781234567892', 2, 2500);

INSERT INTO FacturaDetalle(DetalleId, FacturaId, CodigoBarraProducto, Cantidad, PrecioUnitario)
VALUES (DetalleSeq.NEXTVAL, 1,'781234567893', 3, 3000);


/*Un sinónimo es un nombre alternativo que identifica un tabla en la base de datos.
Con un sinónimo se pretende normalmente simplicar el nombre original de la tabla,
aunque tambien se suelen utilizar para evitar tener que escribir el nombre del propietario de la tabla.
*/
--ahora crearemos un sinonimo para la tabla detalle
Create SYNONYM Detalle
FOR FacturaDetalle;

Select * from Detalle;

Tarea 3 - Practica de Vistas

Tarea 3 - Practica de Vistas.


Creación vista simple,seleccionando los empleados pertenecientes al departamento, numero 40.y concatenando Nombre y apellido.

CREATE view programadores 
(Departamento,Nombre,Mail)
As SELECT departamento_id,nombre_empleado||' '|| apellido_empleado,email_empleado
from EMPLEADO 

where departamento_id = 40;


*********************************************************************************************
Creación de vista Compleja.
CREATE view Personal_x_departamento 
(Departamento,Cantidad)
As SELECT nombre_departamento, count (empleado_id)
from EMPLEADO e
inner join DEPARTAMENTO d on(e.departamento_id= d.departamento_id)

GROUP by d.NOMBRE_DEPARTAMENTO;



********************************************************************************************
Vistas con restricciones

CREATE view programadores_check 
(Departamento,Nombre,Mail)
As SELECT departamento_id,nombre_empleado||' '|| apellido_empleado,email_empleado
from EMPLEADO 
where departamento_id = 40

with check option constraint programadores_ck;


*********************************************************************************************

Eliminar vista


drop view programadores ;

*********************************************************************************************

Intentar Modificar

update programadores_check set departamento=('20');

Error:
update programadores_check set departamento=('20')
Informe de error -
Error SQL: ORA-01402: view WITH CHECK OPTION where-clause violation

01402. 00000 -  "view WITH CHECK OPTION where-clause violation"

Tarea 2 - Comando ALTER TABLE

El comando ALTER TABLE permite modificar la definición de una tabla. En específico permite cambiar tipos de datos de un campo, las restricciones, los largos de un dato, agregar columnas, eliminar columnas, entre otros.


El código para realizar esto es el siguiente:


Sintaxis:

ALTER TABLE [nombre_tabla] ADD [nombre_columna] [definición_columna];

Ejemplo:
Utilizando el compilador online sqlfiddle, realizaremos primero la creación del schema y luego lo modificaremos (importante, utilice el motor Oracle 11g R2)


Creación del Schema o tabla:

CREATE TABLE departamento(
 departamento_id number(4) NOT NULL CONSTRAINT pk_departamento_id PRIMARY KEY,
 nombre_departamento varchar2(30) NOT NULL
);

Alter table , sintaxis

  ALTER TABLE departamento ADD num_empleados number(3)

  ADD CONSTRAINT ck_num_emp CHECK (num_empleado > 0);

En el caso anterior modificamos la tabla para agregar una columna que permita guardar el números de empleados y además valide que este sea mayor a 0 


Eliminemos una columna

Sintaxis: 

ALTER TABLE departamento DROP COLUMN num_empleados;

Podemos validar lo anterior en el siguiente enlace
http://sqlfiddle.com/#!4/4b72de 

Creacion_Tablas_Trabajo1

/*==============================================================*/
/* BORRADO DE TABLAS */
/*==============================================================*/

drop table departamento cascade constraints;

drop table empleado cascade constraints;


/*==============================================================*/
/* Tabla: departamento */
/*==============================================================*/

create table departamento(
departamento_id number(4) not null constraint pk_departamento_id Primary key,
nombre_departamento varchar2(30) not null
);

/*==============================================================*/
/* Tabla: empleado */
/*==============================================================*/

create table empleado(
empleado_id number(4) not null constraint pk_empleado_id Primary key,
nombre_empleado varchar2(30) not null,
apellido_empleado varchar2(30) not null,
email_empleado varchar2(30) not null constraint unik_empleado_mail unique,
departamento_id number(4) not null constraint ref_emp_depto references departamento (departamento_id)
);

/*==============================================================*/
/*= P o b l a m i e n t o d e t a b l a s =*/
/*==============================================================*/

/*==============================================================*/
/* datos: departamento */
/*==============================================================*/
insert into departamento values(10,'Gerencia');
insert into departamento values(20,'Administracion');
insert into departamento values(30,'Produccion');
insert into departamento values(40,'Developer');
insert into departamento values(50,'RRHH');

/*==============================================================*/
/* datos: empleado */
/*==============================================================*/
insert into empleado values(101,'Marco','De la Rivera','m_delarivera@yahoo.es',40);
insert into empleado values(102,'Fancisco','Chavez','francisco.chavez@outlook.cl',10);
insert into empleado values(103,'Gerardo','Gutierrez','ggmiquel@yahoo.com',40);
insert into empleado values(104,'Miguel Angel','Avendano','migaven@gmail.com',20);
insert into empleado values(105,'Nicol','Pantoja','nicoledaphne@gmail.com',50);