PL SQL

Vista previa del material en texto

Clase 6
BASE DE DATOS
FAC.DE INGENIERIA - UNJu
PL/SQL (Procedural Language Structured query language ) 
 Es 1 extensión de SQL q agrega construcciones propias de leng. procedimentales 
obteniéndose como resultado 1leng.estructural+ poderoso q SQL.
 Características
 La unidad de programación utilizada x PL/SQL es el bloque. 
 Todos los programas de PL/SQL están conformados x bloques. 
 Típicamente, c/bloque lleva a cabo 1acción lógica en el programa. 
 Un bloque tendrá siempre la siguiente estructura:
DECLARE //Sección declarativa: variables, tipos, y subprogramas //de uso 
local 
BEGIN //Sección ejecutable: las instrucciones procedimentales, y de SQL 
//aparecen aquí. Es la única sección obligatoria en el bloque. 
EXCEPTION //Sección de manejo de excepciones. Las rutinas de manejo de 
errores //aparecen aquí END;
Nota: Solo se requiere la sección ejecutable. Lo demás es opcional.
Bloque PL/SQL (continuación) 
 Las únicas instrucciones SQL permitidas en un bloque PL/SQL son INSERT, UPDATE, 
DELETE y SELECT, además de algunas instrucciones para manipulación de datos, 
e instrucciones para control de transacciones. 
 Otras instrucciones de SQL como DROP, CREATE o ALTER no son permitidas. 
 Se permite el uso de comentarios estilo C (/* . . .*/). PL/SQL no es case sensitive
por lo que no hay distinción entre nombres con mayúsculas y minúsculas. 
 En la sección de declaraciones, se indican las variables que serán usadas dentro 
del bloque y sus tipos. Por ejemplo: 
DECLARE 
miVar VARCHAR(20); 
precio NUMBER(6,2); 
Bloque PL/SQL (continuación) 
 Es posible q se desee q el tipo de 1variable coincida con el tipo usado p/1 
columna de 1tabla determinada, x ej.: 
DECLARE miVAr miTabla.Columna%TYPE; 
Con lo cual la variable miVAr tiene el mismo tipo q la columna de la tabla miTabla.
 Es posible inicializar las variables, mediante el operador :=. Además, mediante el 
uso del mismo operador es posible hacer asignaciones en el cuerpo del 
programa. X ej.: 
DECLARE 
precio NUMBER := 300; 
BEGIN 
precio := precio + 150; 
END; 
. run 
 Nota: La ejecución de este bloque no tendrá ningún efecto, ya q no hace 
cambios sobre la BD.
PL/SQL: Sentencias condicionales
 Se pueden usar sentencias condicionales dentro de los bloques de PL/SQL. Una 
sentencia condicional típica es de la forma:
IF (condicion) 
THEN (lista de acciones) 
ELSE (lista de acciones) 
END IF; 
Se puede hacer el uso de varios casos de condición, mediante: 
IF . . . THEN . . . 
ELSIF . . . THEN . . . 
ELSIF . . . THEN . . . 
.. 
ELSE . . . 
END IF; 
 Nota: En ambos casos, la cláusula ELSE es opcional.
PL/SQL: Ciclos
 También se pueden tener ciclos o lazos dentro de los bloques de 
PL/SQL. Hay varias formas:
a) LOOP 
lista_de_instrucciones
END LOOP; 
En donde alguna de las instrucciones debe ser: EXIT WHEN condicion;
Con lo que el lazo terminará cuando la condición sea verdadera. 
b) WHILE (condicion) LOOP 
lista_de_instrucciones
END LOOP;
El ciclo solo se inicia si la condición es verdadera en principio.
Es posible que el programa nunca entre en el ciclo. 
Usando la instrucción LOOP se garantiza q siempre se ejecute el cuerpo del ciclo 
al menos 1 vez. 
PL/SQL: Ciclos (continuación)
c) FOR i IN a..b LOOP 
lista_de_instrucciones
END LOOP; 
Los ciclos se ejecutan 1 nro.predeterminado de veces.
En este caso i es una variable de uso local, por lo que no es necesario que sea 
declarada, y puede ser usada dentro del lazo, mientras que a y b son constantes. 
PL/SQL: Procedimientos Almacenados
“1proced.almacenado es 1 conj. de instrucc.
en PL/SQL, q puede ser llamado 
usando el nombre q se le ha asignado.” 
Sintaxis: CREATE [OR REPLACE] PROCEDURE name [(param [IN|OUT|IN OUT|] 
datatype) . . .] [IS|AS] pl/sql_subprogram
OR REPLACE sobreescribe 1 proced.existente. Si se omite, y el proced.existe, se produce 1error. 
IN, OUT, IN OUT indican si el parámetro es de entrada, salida o ambos. 
Ej.de creación de un procedimiento: 
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; 
Este proced.actualiza la(s) tupla(s) con nro.de cuenta igual al parámetro acc_no con 1 
incremento de amount en el campo balance de dicha cuenta. 
P/eliminar o borrar 1procedimiento almacenado, se usa: SQL> DROP PROCEDURE name; 
PL/SQL: Cursor
 El conj.de filas resultantes de 1 consulta con SELECT 
puede devolver ninguna, 1 o varias filas, 
dependiendo de la condición de la consulta.
P/poder procesar individualmente c/fila de la 
consulta se define 1cursor, q es 1área de trabajo de 
memoria, q contiene los datos de las filas de la tabla consultada x la sentencia SELECT.
 Pasos:
1- Definir el cursor, especificando la lista de parámetros con sus tipos de datos y 
estableciendo la consulta a realizar con la sentencia SELECT. 
2- Abrir el cursor p/inicializarlo, siendo éste el momento en q se realiza la consulta. 
3- Leer 1 fila del cursor, pasando sus datos a las variables locales definidas a tal efecto. 
4- Repetir el proceso fila a fila hasta llegar a la última. 
5- Cerrar el cursor 1vez q se terminó de procesar su última fila. 
PL/SQL Cursor: Ejemplo
 Objetivo: Consultar las ventas de 1 fecha dada 
ordenadas de mayor a menor. 
 Nombre: cventas. 
 Parámetros: cfecha, variable que contiene la fecha a consultar. 
 Código de definición del cursor: 
Nombre Parámetro 
DECLARE CURSOR cventas (cfecha DATE)
IS 
SELECT artículo, valor 
FROM ventas 
WHERE fecha = cfecha
ORDER BY valor DESC
} Consulta
PL/SQL Cursor: Ejemplo (continuación)
PROCEDURE VENTAS5 (xfecha DATE) is
BEGIN 
DECLARE CURSOR cventas (cfecha DATE) 
IS SELECT articulo,valor
FROM ventas 
WHERE fecha=cfecha
ORDER BY valor DESC; 
xarticulo ventas.articulo%TYPE; 
xvalor ventas.valor%TYPE; 
BEGIN 
OPEN cventas(xfecha); 
FOR i IN 1..5 LOOP 
FETCH cventas INTO xarticulo,xvalor; 
EXIT WHEN cventas%NOTFOUND;
INSERT INTO ventamayor VALUES (xfecha,xarticulo,xvalor);
COMMIT; 
END LOOP; 
CLOSE cventas; 
END; 
END; 
El proced.VENTAS
muestra cómo usar 
el cursor cventas
con el fin de 
registrar en la tabla 
VENTAMAYOR las 5 
mayores ventas en 
1 fecha dada. 
P/llamar al proced.ventas en 1fecha 
dada, se puede escribir, x ej.: 
ventas(to_date('15/11/95','DD/MM/YY') 
o ventas(sysdate). 
PL/SQL: Trigger
En 1 BD es un evento o proced. q se ejecuta cuando se cumple 1condición 
establecida al realizar 1operación de inserción, actualización o borrado. 
Usos:
Mejorar la administración BD: no se necesita contar con el us.q ejecute la sent.SQL). 
 Cuando los datos de 1tabla son generados desde otros proced. y se necesita controlar 
los valores q toman algunos campos determinados de dicha tabla. 
 Duplicar los contenidos de 1 tabla automáticamente y en tiempo real. 
 Implementar complejas restricciones sobre los valores q pueden tomar los campos de 
1 tabla, es decir, cuando los CONSTRAINTS de la tabla son insuficientes. 
Controlar las modificaciones de los valores de los campos de una tabla (auditorías). 
 Incrementar automáticamente los valores de 1campo. 
 Realizar actualizaciones de 1tabla en cascada. 
Modificar campos/registros de 1 tabla q 1usuario no puede modificar directamente. 
Mantener la integridad de la BD (llevando a cabo cualquier acción necesaria p/ello). 
 Llamar a otros proced. y disparar otros triggers(no admiten parámetros y no pueden 
ser invocados desde otros procedimientos PLSQL). 
PL/SQL: Trigger (continuación)
Componentes
La estructura básica está compuesta por:
1) Llamada de activación: es la sentencia q "dispara" 
el código a ejecutar.
2) Restricción: es la condición necesariap/ejecutar el código. 
Esta restricción puede ser de tipo condicional o de tipo nulidad
3) Acción a ejecutar: es la secuencia de instrucciones a ejecutar 1 vez q se han 
cumplido las condiciones iniciales.
Características de Triggers
a) Se almacenan en la tabla catálogo del sistema como parte de las
propiedades de una tabla.
b) Pueden actuar antes o después de q se realice 1inserción, 1borrado o 1
actualización de 1registro de la tabla involucrada. 
c) Se pueden definir triggers diferentes p/c/tipo de evento (INSERT, UPDATE, 
DELETE) pero lo + lógico es crear 1único trigger p/todos los eventos y usar 
1sentencia IF para distinguir q acción realizar dependiendo del evento.
PL/SQL: Trigger (continuación)
 Tipos:
a) Row Triggers (o Triggers de fila): son aquellos q se 
ejecutan n-veces si se llama n-veces desde la tabla asociada al trigger
b) Statement Triggers (o Triggers de secuencia): son aquellos q sin importar la 
cantidad de veces q se cumpla con la condición, su ejecución es única.
 Sintaxis
CREATE OR REPLACE TRIGGER nombre-del-trigger
[BEFORE/AFTER] 
[INSERT/DELETE/UPDATE/UPDATE OF lista-columnas] 
[ORDER integer] ON nombre-tabla 
[REFERENCING [OLD AS nombre-antiguo][NEW AS nombre-nuevo]] 
[FOR EACH ROW/FOR EACH STATEMENT] 
[WHEN {condiciones}] 
{Bloque estándar de sentencias PL/SQL... BEGIN, EXCEPTION} 
Con UPDATE OF se ejecuta sólo cuando se modifica alguna de las columnas de la lista y 
con UPDATE con cualquiera de las columnas de la tabla se modifique 
ORDER determina el orden en q se 
ejecutan los triggers del mismo tipo (INSERT, 
DELETE o UPDATE) q se disparan al mismo 
tiempo sobre1misma tabla
REFERENCING permite referirnos con el nombre q se indica, x 
defecto los nombres son new p/el nuevo valor y old p/el antiguo
WHEN determina q el trigger PLSQL se dispare sólo p/los registros q cumplen la condición de la cláusula. 
PL/SQL: Trigger (ejemplos)
 Ingresar 1 pedido de algún prod.cuando la cant.en el 
almacén sea inferior a 1valor dado.
BEFORE UPDATE ON tabla_almacen
FOR ALL records 
IF NEW.producto < 100 THEN 
INSERT INTO tabla_pedidos(producto) VALUES (1000); 
END IF; 
END;
Si se inserta/actualiza el reg.de 1empleado de manera q su salario sea mayor a 15000$, el 
trigger actualizará dicho salario a 15000$ independiente del salario insertado/modificado
CREATE TRIGGER tr1_empleados 
BEFORE INSERT OR UPDATE OF salario
ORDER 1 ON empleados
FOR EACH ROW 
WHEN (:new.salario > 15000); 
BEGIN 
UPDATE empleados
SET salario = 15000 
WHERE empleado_id = :new.empleado_id; 
END;