Bloques anónimos PL/SQL

Empezaremos con los bloques anónimos, caracterizados porque no tienen nombre y se suelen crear y ejecutar desde PL/SQL.

Todo bloque debe acabar en . para que sea almacenado en el buffer SQL. Una vez guardado lo podemos ejecutar con la orden “run”. También podemos guardarlo en un fichero con la siguiente orden:

save nombrefichero [replace]

El replace sólo lo pondremos si el fichero ya esta creado.

Para cargar y ejecutar este bloque anónimo guardado en fichero ejecutaremos la siguiente orden:

start nombrefichero

El start lo podemos cambiar por la @ y nos funcionará igualmente.

Pero también podemos cargarlo sin ejecutarlo con la orden “get” y luego ejecutarlo posteriormente con la orden “run”

Un ejemplo muy sencillo de bloque seria el que nos muestra en pantalla un nombre.

BEGIN

DBMS_OUTPUT.PUT_LINE('nombre');


END;
.

Además en los bloques PL/SQL se pueden utilizar lo que llamamos variables de sustitución, que nos pedirán datos por pantalla antes de ejecutar el bloque. Estas variables tienen que ir antepuestas del & para que funcionen.

Un ejemplo seria un bloque que nos pide el DNI de un usuario y nos muestra su nombre.

DECLARE
Vnom clientes.nombre%TYPE;
BEGIN
select nombre into Vnom from clientes where NIF= '&V_nif';
DBMS_OUTPUT.PUT_LINE (Vnom);
END;
.


ejemplo



SQL> CREATE PROCEDURE credit (acc_no IN NUMBER, amount IN NUMBER)
1> AS BEGIN
2> UPDATE accounts
3> SET balance = balance + amount
4> WHERE account_id = acc_no;
5> END;

Funciones

¿QUÉ ES UNA FUNCIÓN?

Una función es un subprograma que devuelve un valor.

La sintaxis para construir funciones es la siguiente:

CREATE [OR REPLACE]
FUNCTION [( IN ,  IN , ...)]
RETURN 
IS
  result ;
BEGIN

  return(result);
[EXCEPTION]
  -- Sentencias control de excepcion
END [];

El uso de OR REPLACE permite sobreescribir una función existente.
Si se omite, y la función existe, se producirá, un error.
La sintaxis de los parámetros es la misma que en los procedimientos almacenado,
exceptuando que solo pueden ser de entrada.

Ejemplo:

CREATE OR REPLACE FUNCTION fn_Obtener_Precio(p_producto VARCHAR2) RETURN NUMBER IS result NUMBER; BEGIN SELECT PRECIO INTO result FROM PRECIOS_PRODUCTOS WHERE CO_PRODUCTO = p_producto; return(result); EXCEPTION WHEN NO_DATA_FOUND THEN return 0; END ;

Si el sistema nos indica que el la función se ha creado con errores de compilación podemos ver estos errores de compilacion con la orden SHOW ERRORS en SQL *Plus.
Una vez creada y compilada la función podemos ejecutarla de la siguiente forma:

DECLARE
  Valor NUMBER;
BEGIN
 Valor := fn_Obtener_Precio('000100');

END; 

Las funciones pueden utilizarse en sentencias SQL de manipulación de datos (SELECT, UPDATE, INSERT y DELETE):

SELECT CO_PRODUCTO,
      DESCRIPCION,
      fn_Obtener_Precio(CO_PRODUCTO)
FROM PRODUCTOS; 

Trigger

¿QUÉ ES UN TRIGGER?

Un trigger(disparacdor o activador) define una acción que la base de datos siempre debería realizar cuando ocurre
algún tipo de acontecimiento que la afecta.


Se utilizan para:

*mantener la integridad referencial.

*asegurar reglas de negocios complejas y

*auditar cambios en los datos.


Para definir un trigger se debe:

*Especificar las condiciones bajo las que el trigger será ejecutado.

*Especificar las acciones que se realizarán cuando el trigger se ejecute.



Las acciones del trigger se definen mediante bloques de PL/SQL nominados con las secciones:


*Declarativa

*Ejecutable

*Manejo de excepciones

*Los triggers se almacenan en la BD y están asociados a una tabla.

*Pueden afectar a n filas.

*Se ejecutan de manera implícita ante eventos(operación de inserción, modifiación o borrado)

*Se compilan cada vez que se activan.

SINTAXIS DE UN TRIGGER: Esta instrucción nos permite definir un trigger:

CREATE TRIGGER  [NombreEsquema.]NombreTrigger
 ON {tabla | vista } [,...n] ]
 {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]}
 AS sentencia_sql  [;] [,...n ]   

Cuando se INSERTe un pedido, entrará en funcionamiento el trigger ActualizaVentasEmpleado y se ejecutarán las instrucciones que aparecen después de AS, en este caso actualizará (UPDATE) la tabla empleados sumará a las ventas del empleado (ventas) el importe del pedido insertado (inserted.importe), y sólo actualizará el empleado cuyo numemp coincida con el campo rep del pedido insertado (WHERE numemp=inserted.rep).

    -- Ahora comprobamos que funciona
SELECT * FROM empleados WHERE  numemp=108;
INSERT INTO pedidos (numpedido,fechapedido,rep,clie,cant,importe,fab,producto)
          VALUES  (123456789,getdate(),108,2103,10,100,'Aci',41001)
SELECT * FROM empleados WHERE  numemp=108; 

Vemos que al insertar un pedido de 100 € del empleado 108, sus ventas han aumentado en 100€.

ALTER TRIGGER

Permite modificar la definición del desencadenador, no permite cambiar su nombre, para cambiar el nombre de un desencadenador hay que eliminarlo (DROP TRIGGER) y volver a crearlo (CREATE TRIGGER).

    ALTER TRIGGER [NombreEsquema.]NombreTrigger
 ON {tabla|vista}
 {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} [WITH APPEND]
 AS sentencia_sql  [;] [,...n ]   

La sintaxis es similar a la instrucción CREATE TRIGGER.
Ejemplo:

    ALTER TRIGGER  ActualizaVentasEmpleados
ON pedidos FOR INSERT
AS
UPDATE  empleados SET ventas=ventas+inserted.importe
FROM  empleados, inserted
WHERE numemp=inserted.rep AND  inserted.importe IS NOT NULL; 

Hemos modificado el desencadenador para que si el importe del pedido es nulo, no haga nada, no actualice con un valor nulo.

DISABLE TRIGGER

En ocasiones puede ser útil inhabilitar temporalmente un desencadenador sin que por ello suponga eliminarlo, para estos casos podemos utilizar la sentencia DISABLE TRIGGER.

    DISABLE TRIGGER  {[NombreEsquema.]NombreTrigger [,...n] | ALL }
 ON {NombreTablaVista | DATABASE |  ALL SERVER} [;]  

Ejemplo:

    DISABLE TRIGGER  ActualizaVentasEmpleado ON pedidos; 

Deshabilita el desencadenador que hemos creado anteriormente, si después de ejecutar esta sentencia se introduce un nuevo pedido, el empleado correspondiente no se actualizará.
Lo podemos comprobar con:

    SELECT * FROM empleados WHERE  numemp=108;
 INSERT INTO pedidos  (numpedido,fechapedido,rep,clie,cant,importe,fab,producto)
                    VALUES  (123456791,getdate(),108,2103,10,300,'Aci',41001)
 SELECT * FROM empleados WHERE  numemp=108;  

    DISABLE TRIGGER ALL ON pedidos;  

Deshabilita todos los desencadenadores asociados a la tabla pedidos:

    DISABLE TRIGGER ALL ON DATABASE;  

Deshabilita todos los desencadenadores definidos en la base de datos actual,deshabilita todos los desencadenadores definidos en el servidor:

    DISABLE TRIGGER ALL ON ALL SERVER; 

Eliminar un trigger:

DROP TRIGGER nombre_disparador;

Esta instrucción te permite ver los errores de compilacion:

SHOW ERREOS TRIGGER nombre_disparador;