# PL/SQL
## Introducción
Todo aquello que no tiene SQL se lo da PL/SQL (bucles, if, variables, etc.). Sirve para aplicaciones más complejas.
El objeto básico de PL/SQL es un bloque.
Los bloques están divididos en las siguientes secciones:
* DECLARE
* BEGIN
* EXCEPTION
* END
Existen distintos tipos de componentes en PL/SQL:
* Bloques anónimos
* Procedimientos y funciones
* Triggers
## Infraestructura
Versiones:
* Oracle Express 11g
* Consume menos recursos (4GB de RAM recomendados)
* Oracle Express 18c
* Consume más recursos (8GB de RAM recomendados)
* Más moderno
* Tiene el usuario PDBADMIN
* Cambia la información de la base de datos de conexión (pasa de XE a XEPDB)
Descarga: https://www.oracle.com/es/database/technologies/xe-downloads.html
Oracle iniciará algunos servicios en Windows de manera predeterminada:
* OracleServiceXE: Es la base de datos.
* OracleXETNSListener: Componente que permite las conexiones entre los clientes y la base de datos.
TIP: Si la base de datos solo se va a utilizar de vez en cuando, conviene dejar los servicios en Tipo de inicio --> Manual para arrancarlos cuando se utilicen.
También, tendremos a nuestra disposición SQL*Plus, podemos probar a conectarnos y enumerar la versión:
```
CONNECT SYSTEM
pepito
SELECT * FROM V$VERSION;
```
Oracle es case-sensitive.
## SQL Developer
Oracle SQL Developer es una herramienta totalmente gratuita que permite realizar programas y trabajar contra Oracle de una manera mucho más gráfica de lo que podemos hacer con el SQL*Plus.
Descarga: https://www.oracle.com/tools/downloads/sqldev-downloads.html
JDK es requerido.
Para utilizarlo, basta con arrancar sqldeveloper.exe.
Después, hay que crear una conexión con el usuario system. La opción de Guardar Contraseña puede ser bastante útil. El tipo de conexión será Básico.
**En Oracle 18c, en la configuración de conexión hay que seleccionar en vez de SID, Nombre del Servicio, y establecer el valor: xepdb1.**
Al conectar, en la parte izquierda aparecerá la base de datos (tablas, vistas, etc.) y en la parte derecha una Worksheet para poner comandos.
Se creará una segunda conexión, pero esta vez, con el usuario HR (Human Resources).
El usuario HR por defecto está bloqueado, para desbloquearlo, desde la conexión con SYSTEM lanzamos el siguiente comando:
```
ALTER USER HR ACCOUNT UNLOCK;
ALTER USER HR IDENTIFIED BY hr;
```
Importante: La contraseña que pongamos en el IDENTIFIED BY es case-sensitive.
Las credenciales serán hr:hr, ya podemos crear la nueva conexión.
Al abrir tablas, existe la opción "Models" que devuelve un diagrama entidad-relación.
El icono de la izquierda, lanza el comando en el que te encuentras en ese momento. El icono de la derecha, lanza todos los comandos del Worksheet o script.
Este icono permite crear nuevas Worksheets.
SQL Developer tiene autocompletado, por ejemplo, nos detecta las tablas directamente.
## Primeros pasos en PL/SQL
### Bloques anónimos
Entre el BEGIN y el END; va el código.
NULL no hace nada.
```sql
BEGIN
NULL;
END;
```
Este icono borra la pantalla, para que no se acumulen logs.
Para no perder nuestros scripts PL/SQL tenemos que hacer clic en este icono y guardar en formato .sql.
### Visualizar salida por pantalla con DBMS_OUTPUT
PL/SQL no es un lenguaje de tipo visual, por lo tanto, carece de un comando similar a print, como el que tienen otros lenguajes de programación.
Para solucionarlo, utilizaremos un paquete que nos permite visualizar cosas por pantalla.
`SET SERVEROUTPUT ON` habilita la salida por pantalla.
DBMS_OUTPUT es el paquete y PUT_LINE es el método.
```sql
cl scr
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(100);
DBMS_OUTPUT.PUT_LINE('AAAAA'||' XXXXX');
END;
```
Las cadenas en PL/SQL van con comillas simples.
Para concatenar cadenas de caracteres se utilizan dos tuberías o pipes (||).
Para cambiar el tamaño de la fuente en Oracle SQL Developer:
Truco: Para que en la Salida del Script no se genere siempre un log, podemos poner al principio de nuestro script `cl scr` de la siguiente forma.
### Variables en PL/SQL
Las variables sirven para almacenar valores durante el desarrollo de los programas.
Existen de distintos tipos, se pueden integrar en comandos SQL normales.
Para nombrar una variable:
* Debe comenzar por una letra
* Podemos incluir números o letras
* Pueden tener algunos caracteres especiales como dólar, guión bajo, etc.
* El nombre no puede ser más largo de 30 caracteres
* No debemos usar palabras reservadas.
Las variables se pueden utilizar en todo el bloque PL/SQL pero hay que declararlas previamente en la sección DECLARE.
La forma de crear variables es:
```sql
DECLARE
nombre tipo;
```
Ejemplo real:
```sql
DECLARE
SALARY NUMBER(2);
NAME VARCHAR(2);
BIRTH_DATE DATE;
```
Para poner un valor a una variable se utiliza :=
En la zona BEGIN se utilizan como con cualquier otro lenguaje de programación.
Entre los diferentes tipos de variables que existen, las Escalares son las más comunes:
Código de introducción a las variables:
```sql
cl scr
SET SERVEROUTPUT ON
DECLARE
NAME VARCHAR2(100);
LASTNAME VARCHAR2(100);
BEGIN
NAME:='JOHN';
LASTNAME:='CONNORS';
DBMS_OUTPUT.PUT_LINE(NAME);
DBMS_OUTPUT.PUT_LINE(LASTNAME);
DBMS_OUTPUT.PUT_LINE(NAME||' '||LASTNAME);
END;
```
### Constantes y NULL
Las constantes nos permiten crear variables que no pueden ser modificadas una vez creadas.
En la sección DECLARE pondríamos lo siguiente:
```sql
cl scr
SET SERVEROUTPUT ON
DECLARE
X CONSTANT NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(X);
END;
```
No nos funcionará, ¿por qué? Primero tenemos que inicializar la variable con algún valor, de esta forma:
```sql
cl scr
SET SERVEROUTPUT ON
DECLARE
X CONSTANT NUMBER:=10;
BEGIN
DBMS_OUTPUT.PUT_LINE(X);
END;
```
Las constantes no pueden modificarse (solo pueden ser utilizadas en modo lectura), el siguiente código generaría un error:
```sql
cl scr
SET SERVEROUTPUT ON
DECLARE
X CONSTANT NUMBER:=10;
BEGIN
X:=20;
DBMS_OUTPUT.PUT_LINE(X);
END;
```
NOT NULL significa un valor nulo, se podría declarar una variable que es de tipo NOT NULL, que sería una variable que no puede estar vacía.
Ejemplo:
```sql
cl scr
SET SERVEROUTPUT ON
DECLARE
X CONSTANT NUMBER:=10;
Z NUMBER NOT NULL:=20;
BEGIN
DBMS_OUTPUT.PUT_LINE(X);
Z:=30;
DBMS_OUTPUT.PUT_LINE(Z);
END;
```
Ctrl+F7 formatea e identida el código.
### Variables Booleanas
Una variable booleana puede ser:
* TRUE: Verdadero
* FALSE: Falso
* NULL: Que en ese momento no tiene ningún valor
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
B1 BOOLEAN;
BEGIN
B1:=TRUE;
B1:=FALSE;
B1:=NULL;
END;
```
### %TYPE
%TYPE permite crear una variable que sea del mismo tipo que otro.
Se puede decir que una variable es del mismo tipo que una columna de una base de datos, de una tabla.
Si no sabes de qué tipo es una columna, gracias a esto te despreocupas porque puedes realizar asignaciones de tipo sin preocuparse del tipo real del que sea el objeto del que proviene.
Ejemplo:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
X NUMBER;
Z X%TYPE;
EMPLE EMPLOYEES.SALARY%TYPE;
BEGIN
EMPLE:=100;
END;
```
En la sección DECLARE.
Z sería del mismo tipo que X (numérica).
EMPLE sería del mismo tipo que la columna SALARY de la tabla EMPLOYEES.
### Operadores
Un NULL no es un 0.
Si un operador tiene un nulo, el resultado siempre va a ser un nulo.
El operador de concantenación || nos permite concatenar caracteres. Oracle intenta convertir si puede de cadenas de caracteres a números, etc.
SYSDATE es una función que devuelve la fecha actual.
D+1 sería el día siguiente.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
/* OPERADORES MÁS HABITUALES
+ SUMA
- RESTA
/ DIVISIÓN
* MULTIPLICACIÓN
** EXPONENTE
|| CONCATENAR
*/
X NUMBER:=5;
Z NUMBER:=10;
A VARCHAR2(100):='EXAMPLE';
D DATE:='10-01-1990';
BEGIN
DBMS_OUTPUT.PUT_LINE(SYSDATE);
DBMS_OUTPUT.PUT_LINE(D+1);
END;
```
### Comentarios
Los comentarios nos permiten hacer anotaciones respecto al código.
Hay comentarios de una sola línea y de múltiples líneas.
```sql
CL SCR
-- esto es un comentario
DECLARE
X NUMBER; -- eSTO ES UNA VARIABLE NUMERICA
/*
EN ESTAS LINEAS
TODO ES COMENTARIO
*/
BEGIN
X:=10;
END;
```
### Bloques anidados
Un bloque anidado nos permite agrupar u ordenar trozos de bloque.
Un bloque anidado es un bloque hijo, de subbloque que se encuentra en el bloque primario.
```sql
CL SCR
SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line('EN EL PRIMER BLOQUE');
DECLARE
x NUMBER := 10;
BEGIN
dbms_output.put_line(x);
END;
END;
```
Los bloques son totalmente independientes pero heredan cosas.
### Ámbito de variables en bloques anidados
Cualquier variable del bloque padre puede ser accedida por el bloque hijo, pero no al contrario.
El siguiente código provocaría un error porque la variable y es una variable que es solo propiedad del hijo, no se puede usar en el bloque del padre.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
x NUMBER := 20; --GLOBAL
Z NUMBER:=30;
BEGIN
dbms_output.put_line('X:='|| x);
DECLARE
x NUMBER := 10; --LOCAL
z number:=100;
y number:=200;
BEGIN
dbms_output.put_line('X:='|| x);
dbms_output.put_line('Z:='|| Z);
END;
dbms_output.put_line('Y:='|| y);
END;
```
### Funciones PL/SQL
Las funciones de SQL se utilizan directamente en PL/SQL, no hay que poner SELECT ni nada por el estilo.
Aunque la función SUBSTR() se llame igual que en SQL, "no es la misma" porque se ejecuta en el motor PL, no en el motor SQL. Pero a efectos prácticos, hace lo mismo.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
X VARCHAR2(50);
BEGIN
X:='HELLO';
DBMS_OUTPUT.PUT_LINE(SUBSTR(X,1,3));
END;
/
```
Solo valen funciones simples, no valen funciones de grupo. No se puede usar COUNT, AVG, etc. dentro de un solo comando porque se esperan múltiples valores.
La función DECODE no existe. CASE sería la alternativa en PL.
FLOOR() devuelve la parte entera de un número.
Ejemplo:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
X VARCHAR2(50);
MAYUS VARCHAR2(100);
FECHA DATE;
Z NUMBER:=109.80;
BEGIN
X:='Ejemplo';
DBMS_OUTPUT.PUT_LINE(SUBSTR(X,1,3));
MAYUS:=UPPER(X);
DBMS_OUTPUT.PUT_LINE(MAYUS);
FECHA:=SYSDATE;
DBMS_OUTPUT.PUT_LINE(FECHA);
DBMS_OUTPUT.PUT_LINE(FLOOR(Z));
END;
/
```
## Estructuras de Control
### Operadores lógicos y relacionales
A modo de recordatorio.
```
/*
Operadores relacionales o de comparación
= (igual a)
<> (distinto de)
< (menor que)
> (mayor que)
>= (mayor o igual a)
<= (menor o igual a)
Operadores lógicos
AND (y lógico)
NOT (negación)
OR (o lógico)
*/
```
### Comando IF
No se necesitan paréntesis y se termina con END IF;.
Ejemplos:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
X NUMBER := 20;
BEGIN
IF
X = 10
THEN
dbms_output.put_line('X:=10');
ELSE
dbms_output.put_line('X:=OTHER VALUE');
END IF;
END;
/
```
La otra estructura que viene con el IF es el ELSIF, cuando queremos poner múltiples IF/ELSE:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
sales NUMBER:=25000;
bonus NUMBER:= 0;
BEGIN
IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500;
ELSIF sales >20000 THEN
bonus := 150;
ELSE
bonus := 100;
END IF;
DBMS_OUTPUT.PUT_LINE (
'Sales = ' || sales || ', bonus = ' || bonus || '.'
);
END;
/
```
### Comando CASE
ELSIF puede llegar a ser complejo si tenemos muchas opciones, CASE es una forma más simple de construir condiciones múltiples.
Este CASE nos permite hacer condiciones de igualdad.
Ejemplos:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
v1 CHAR(1);
BEGIN
v1 := 'B';
CASE v1
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such value');
END CASE;
END;
/
```
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
v1 number;
BEGIN
v1 := 50;
CASE v1
WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 20 THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 30 THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 40 THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 50 THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such value');
END CASE;
END;
/
```
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
v1 varchar2(20);
BEGIN
v1 := 'Fair';
CASE v1
WHEN 'Excellent' THEN DBMS_OUTPUT.PUT_LINE('A');
WHEN 'Very Good' THEN DBMS_OUTPUT.PUT_LINE('B');
WHEN 'Good' THEN DBMS_OUTPUT.PUT_LINE('C');
WHEN 'Fair' THEN DBMS_OUTPUT.PUT_LINE('D');
WHEN 'Poor' THEN DBMS_OUTPUT.PUT_LINE('E');
ELSE DBMS_OUTPUT.PUT_LINE('No such value');
END CASE;
END;
/
```
### Searched CASE
Searched CASE no solo nos permite hacer condiciones de igualdad, sino con otros operadores.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
bonus number;
BEGIN
bonus := 100;
CASE
WHEN bonus > 500 THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN bonus <= 500 and bonus > 250 THEN DBMS_OUTPUT.PUT_LINE('Very good');
WHEN bonus <= 250 and bonus > 100 THEN DBMS_OUTPUT.PUT_LINE('Good');
ELSE DBMS_OUTPUT.PUT_LINE('POOR¡¡¡¡');
END CASE;
END;
/
```
### Bucle LOOP
Es el bucle infinito más sencillo.
Crearemos un bucle que nos cuente los 10 primeros números.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
x NUMBER := 1;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 1;
IF x = 11
THEN
EXIT;
END IF;
END LOOP;
END;
/
```
EXIT es la claúsula que nos permite salir de un bucle, en LOOP es obligatorio para evitar bucles infinitos.
Tenemos otra opción más potentes para bucles, que es el EXIT WHEN:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
x NUMBER := 1;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 1;
/* IF x = 11
THEN
EXIT;
END IF;*/
EXIT WHEN x = 11;
END LOOP;
END;
/
```
Da el mismo resultado que con el IF pero es más limpio y está asociado directamente a los bucles.
En los LOOP siempre hay que controlar la salida con EXIT.
### Loops anidados
Se pueden nombrar los bucles con la etiqueta `<<nombre>>`. Estas etiquetas se pueden utilizar para salir de otros bucles con EXIT.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
s PLS_INTEGER := 0;
i PLS_INTEGER := 0;
j PLS_INTEGER;
BEGIN
<<parent>>
LOOP
i := i + 1;
j := 100;
DBMS_OUTPUT.PUT_LINE('Parent:'||i);
<<child>>
LOOP
--Print child
dbms_output.put_line('Child:'||j);
j:=j+1;
EXIT parent WHEN (i> 3);
EXIT child WHEN (j > 105);
END LOOP child;
END LOOP parent;
DBMS_OUTPUT.PUT_LINE('FINISH¡¡¡');
END;
/
```
### Comando CONTINUE
Si encontramos un CONTINUE dentro de un bucle salta de nuevo a la primera línea del bucle, es decir, no continúa con las líneas siguientes, sino que hace un paso y se va hacia arriba otra vez.
En otras palabras, impide que se sigan ejecutando líneas y volvamos de nuevo al principio del bucle.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
x NUMBER := 0;
BEGIN
LOOP -- CON CONTINUE SALTAMOS AQUI
DBMS_OUTPUT.PUT_LINE ('LOOP: x = ' || TO_CHAR(x));
x := x + 1;
/*IF x < 3 THEN
CONTINUE;
END IF;*/
CONTINUE WHEN X <3;
DBMS_OUTPUT.PUT_LINE
('DESPUES DE CONTINUE: x = ' || TO_CHAR(x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE (' DESPUES DEL LOOP: x = ' || TO_CHAR(x));
END;
/
```
Al igual que con EXIT WHEN, tenemos CONTINUE WHEN.
### Bucle FOR
Los números del rango los transforma a PLS_INTEGER.
```sql
CL SCR
SET SERVEROUTPUT ON
BEGIN
FOR i IN 1..10 LOOP
dbms_output.put_line(i);
END LOOP;
END;
/
```
Bucle FOR a la inversa utilizando REVERSE:
```sql
CL SCR
SET SERVEROUTPUT ON
BEGIN
FOR i IN REVERSE 1..15 LOOP
dbms_output.put_line(i);
END LOOP;
END;
/
```
Aunque sea en modo inverso, los números tienen que mantenerse utilizando un índice, de principio a final.
Se puede utilizar el EXIT WHEN, CONTINUE WHEN, etc.
```sql
CL SCR
SET SERVEROUTPUT ON
BEGIN
FOR i IN REVERSE 1..15 LOOP
dbms_output.put_line(i);
EXIT WHEN i=10;
END LOOP;
END;
/
```
En el bucle FOR no es necesario declarar la variable, es una excepción. El FOR forma una especie de bloque anidado, i no se puede utilizar fuera del bucle.
Un error de muchos programadores principiantes es declarar i.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
i VARCHAR(100):='AAAAA';
BEGIN
FOR i IN REVERSE 1..15 LOOP
dbms_output.put_line(i);
EXIT WHEN i=10;
END LOOP;
dbms_output.put_line(i);
END;
/
```
devolverá:
```
15
14
13
12
11
10
AAAAA
```
FOR se suele usar con cursores.
### Bucle WHILE
WHILE nos permite crear un bucle controlado, mientras se cumpla una condición.
Todos los bucles llevan un LOOP y un END LOOP;.
```sql
CL SCR
DECLARE
done BOOLEAN := false;
x NUMBER:= 0;
BEGIN
WHILE x <10 LOOP
dbms_output.put_line(x);
x := x+1;
EXIT WHEN x = 5;
END LOOP;
WHILE done LOOP
dbms_output.put_line('No imprimas esto.');
done := true;
END LOOP;
WHILE NOT done LOOP
dbms_output.put_line('He pasado por aquí');
done := true;
END LOOP;
END;
/
```
### Comando GOTO
GOTO permite ir a otra parte del bloque, pero debería ser para usos muy restringidos. Es una cláusula que no se suele recomendar en los lenguajes de programación, porque destruye la programación lógica o estructurada.
Las etiquetas también se pueden utilizar con GOTO.
```sql
CL SCR
DECLARE
p VARCHAR2(30);
n PLS_INTEGER :=5;
BEGIN
FOR j in 2..ROUND(SQRT(n)) LOOP
IF n MOD j = 0 THEN
p := ' no es un número primo';
GOTO print_now;
END IF;
END LOOP;
p := ' Es un número primo';
<<print_now>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);
END;
/
```
## Usar SQL en PLSQL
### SELECTS dentro de PL/SQL
El resultado del SELECT se tiene que guardar en variables.
Siempre que vayamos a utilizar un SELECT en PL/SQL hay que usar INTO.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
salario NUMBER;
BEGIN
SELECT
salary INTO salario
FROM
employees
WHERE
employee_id = 100;
dbms_output.put_line(salario);
END;
/
```
Si tenemos más de un SELECT:
Separamos con comas.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
salario NUMBER;
nombre EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
SELECT --SOLO PUEDE DEVOLVER UNA FILA
salary, first_name INTO salario, nombre
FROM
employees
WHERE
employee_id = 100;
dbms_output.put_line(salario);
dbms_output.put_line(nombre);
END;
/
```
Tantas filas como nos devuelva la query, tantas variables para recibir ese valor.
MUY IMPORTANTE: Tal y como lo tenemos ahora, solo puede devolver una fila exclusivamente. Si nos devuelve más de una o 0 (ninguna) generaría un error. Esto se solucionará posteriormente mediante cursores.
Una variable única no pueder albergar por ejemplo 100 nombres.
Los siguientes códigos darían error:
**Más filas de las esperadas**
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
salario NUMBER;
nombre EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
SELECT
salary, first_name INTO salario, nombre
FROM
employees;
/* WHERE
employee_id = 100;*/
dbms_output.put_line(salario);
dbms_output.put_line(nombre);
END;
/
```
**No ha encontrado datos**
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
salario NUMBER;
nombre EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
SELECT
salary, first_name INTO salario, nombre
FROM
employees
WHERE
employee_id = 10000;
dbms_output.put_line(salario);
dbms_output.put_line(nombre);
END;
/
```
### %ROWTYPE
%ROWTYPE crea una variable especial que es similar a la tabla asociada.
%ROWTYPE es como %TYPE pero en lugar de un campo hace referencia a toda una fila, en este caso no es necesario indicar el campo, pero si la tabla.
Es útil cuando se quieren manejar grandes cantidades de información.
Cualquier cambio realizado en la variable, no afecta a la fila de por debajo.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
salario NUMBER;
nombre EMPLOYEES.FIRST_NAME%TYPE;
empleado EMPLOYEES%ROWTYPE;
BEGIN
SELECT
* INTO empleado
FROM
employees
WHERE
employee_id = 100;
dbms_output.put_line(EMPLEADO.SALARY*100);
dbms_output.put_line(EMPLEADO.FIRST_NAME);
END;
/
```
### INSERTS
El uso del INSERT es idéntico al SQL tradicional.
Comenzaremos creando una nueva tabla:
```sql
CREATE TABLE TEST
(
C1 VARCHAR2(20)
, C2 VARCHAR2(20)
);
```
El siguiente script insertaría información en la tabla TEST:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
COL1 TEST.C1%TYPE;
BEGIN
COL1:=10;
INSERT INTO TEST (C1,C2) VALUES (COL1,'AAAAAAA');
END;
/
```
Si no estuviera activado el COMMIT automático los datos podrían perderse.
Pese a estar en un entorno de desarrollo, es importante indicarle COMMIT o ROLLBACK en aquellos puntos o momentos en los que se quiere guardar la información.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
COL1 TEST.C1%TYPE;
BEGIN
COL1:=20;
INSERT INTO TEST (C1,C2) VALUES (COL1,'BBBBBBB');
COMMIT;
END;
/
```
### UPDATES
Exactamente igual que en SQL.
La única diferencia es que se están utilizando variables de tipo PL/SQL dentro de código SQL.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
T TEST.C1%TYPE;
BEGIN
T:=10;
UPDATE TEST SET C2='CCCCC' WHERE C1=T;
COMMIT;
END;
/
```
### DELETES
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
T TEST.C1%TYPE;
BEGIN
T:=10;
DELETE FROM TEST WHERE C1=T;
COMMIT;
END;
/
SELECT * FROM TEST;
```
La barra del final de bloque / en el END, es necesaria cuando juntamos bloques PL/SQL con otros componentes (otros SQL o PL/SQL).
## Excepciones
### Introducción a las excepciones
Las excepciones sirven para controlar problemas.
Los errores no siempre están en las líneas que dice Oracle.
Generar una excepción sintáctica, ponemos ROWTYE en vez de ROWTYPE:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
EMPL EMPLOYEES%ROWTYE;
BEGIN
SELECT * INTO EMPL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=100;
DBMS_OUTPUT.PUT_LINE(EMPL.FIRST_NAME);
END;
/
```
Generar una excepción del código, ponemos un empleado que no existe, SELECT tiene que devolver UNA fila:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
EMPL EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO EMPL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=1000;
DBMS_OUTPUT.PUT_LINE(EMPL.FIRST_NAME);
END;
/
```
Hay dos tipos de excepciones:
* Las propias de Oracle, que empiezan por ORA-X o PL-X.
* Las que podemos crear nosotros de manera explícita.
### Sintaxis de las excepciones
El cuarto bloque que podemos poner en PL/SQL es EXCEPTION. EXCEPTION va entre el BEGIN y el END.
EXCEPTION es la zona de excepciones. Esto significa que cuando se produce un error en el bloque de código automáticamente se redirige a la zona de excepciones.
En la zona de EXCEPTION hay que comprobar o controlar si se ha disparado una determinada excepción.
```sql
EXCEPTION
WHEN excepcion THEN
NULL;
WHEN OTHERS THEN
NULL;
```
Parafraseando: Cuando se produzca un determinada error o excepción, entonces ejecuta el siguiente código. Con WHEN OTHERS, cuando se produzca otra excepción de las que no se hayan puesto antes, ejecuta el siguiente código.
En Oracle, hay miles de excepciones, del propio Oracle, PL/SQL, otros componentes, etc. En los WHEN del EXCEPTION sería imposible poner una lista con todas las excepciones disponibles. Lo que se hace en PL/SQL es poner las excepciones que pueden dispararse habitualmente, y el resto lo tratamos con WHEN OTHERS.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
EMPL EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO EMPL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=100;
DBMS_OUTPUT.PUT_LINE(EMPL.FIRST_NAME);
EXCEPTION
WHEN EX1 THEN
NULL;
WHEN EX2 THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
/
```
### Excepciones predefinidas
Las excepciones predefinidas son excepciones con nombre creadas por Oracle para controlar las excepciones más habituales, para no tener que saber el código (ej.: ORA-XXXX).
Errores más habituales son:
* **NO_DATA_FOUND**: Cuando en una SELECT no encuentra no encuentra una fila.
* **TOO_MANY_ROWS**: Cuando la query devuelve más de una fila.
* **ZERO_DIVIDE**: Error aritmético provocado al intentar dividir un valor por cero.
* **DUP_VAL_ON_INDEX**: Cuando la clave primaria y única ya existe.
Ejemplo con NO_DATA_FOUND:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
EMPL EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO EMPL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=1000;
DBMS_OUTPUT.PUT_LINE(EMPL.FIRST_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR, EMPLEADO INEXISTENTE');
WHEN OTHERS THEN
NULL;
END;
/
```
Todos los errores de Oracle, normalmente tienen un código que identifica el producto y luego un número negativo.
La excepción predefinida NO_DATA_FOUND está asociada al error ORA-01403.
Ejemplo con TOO_MANY_ROWS:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
EMPL EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO EMPL
FROM EMPLOYEES
WHERE EMPLOYEE_ID>1;
DBMS_OUTPUT.PUT_LINE(EMPL.FIRST_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR, EMPLEADO INEXISTENTE');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('ERROR, DEMASIADOS EMPLEADOS');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR INDEFINIDO');
END;
/
```
Errores predefinidos junto con su código:
### Excepciones no predefinidas
¿Cómo controlar otro tipo de errores que puedan provenir de Oracle?
Hay que dejar claro que manejaremos excepciones de Oracle, no son creadas por nosotros.
Buscar errores, para capturarlos y tratarlos: https://docs.oracle.com/pls/db92/db92.error_search
Ejemplo con ORA-00937: https://docs.oracle.com/cd/B10501_01/server.920/a96525/e900.htm#1000563
Este error aparece cuando se intentan utilizar funciones como AVG, COUNT, etc. en un query de tipo simple. No se podían utilizar datos SELECT de fila simple con funciones de grupo.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
MI_EXCEP EXCEPTION;
PRAGMA EXCEPTION_INIT(MI_EXCEP,-937);
V1 NUMBER;
V2 NUMBER;
BEGIN
SELECT EMPLOYEE_ID,SUM(SALARY) INTO V1,V2 FROM EMPLOYEES;
DBMS_OUTPUT.PUT_LINE('V1');
EXCEPTION
WHEN MI_EXCEP THEN
DBMS_OUTPUT.PUT_LINE('FUNCION DE GRUPO INCORRECTA');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR INDEFINIDO');
END;
/
```
PRAGMA es una especie de orden al compilador, es como una especie de macro que siempre que aparezca algo, lo sustituye por otra cosa.
La claúsula EXCEPTION_INIT(excepcion, número) sirve para asociar la excepción al número de error que se quiere capturar.
En este código de ejemplo, está asociando la excepción MI_EXCEP con el código -937.
Todos los errores de Oracle son negativos, menos el 100.
Cuando se dispare el error 937, en el bloque EXCEPTION se capturará la excepción para gestionarla.
`SELECT EMPLOYEE_ID,SUM(SALARY) INTO V1,V2 FROM EMPLOYEES;` devolverá un error porque se está mezclando una función de grupo con una columna simple.
En Oracle SQL Developer se pueden activar los números de línea desde la opción "Conmutar Números de Línea".
### SQLCODE y SQLERRM
Son dos funciones integradas dentro de PL/SQL (no se pueden utilizar en comandos SQL) muy útiles a la hora de trabajar con el WHEN OTHERS.
Si en vez de poner error indefinido o genérico queremos saber qué error ha dado, existen dos funciones:
* **SQLCODE**: Devuelve el código de error que ha generado la excepción:
* **SQLERRM**: Devuelve el mensaje de error de SQL.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
EMPL EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO EMPL FROM EMPLOYEES;
DBMS_OUTPUT.PUT_LINE('EMPL.SALARY');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
```
Otro ejemplo:
```sql
CREATE TABLE ERRORS
(
CODE NUMBER,
MESSAGE VARCHAR2(100)
);
```
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
EMPL EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO EMPL FROM EMPLOYEES;
DBMS_OUTPUT.PUT_LINE('EMPL.SALARY');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
INSERT INTO ERRORS VALUES (SQLCODE,SQLERRM);
END;
/
```
El último código no funcionaría al ser ejecutado porque se están utilizando funciones de PL/SQL dentro de un INSERT. Esto no es válido porque SQL desconoce esas funciones.
PL/SQL se ejecuta en su propio motor y SQL lo hace en el suyo. Aunque PL/SQL sabe casi todo de SQL, no es así al contrario, SQL no sabe nada de PL/SQL.
Para solucionarlo, habría que crear una variable para albergar el error.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
EMPL EMPLOYEES%ROWTYPE;
CODE NUMBER;
MESSAGE VARCHAR2(100);
BEGIN
SELECT * INTO EMPL FROM EMPLOYEES;
DBMS_OUTPUT.PUT_LINE('EMPL.SALARY');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
CODE:=SQLCODE;
MESSAGE:=SQLERRM;
INSERT INTO ERRORS VALUES (CODE,MESSAGE);
END;
/
```
El error se almacenará en la tabla ERRORS.
### Controlar SQL con excepciones
No siempre las excepciones son un error. Hay situaciones en las que cuando salta una excepción es lo correcto.
Ejemplo:
```sql
DECLARE
REG REGIONS%ROWTYPE;
REG_CONTROL REGIONS.REGION_ID%TYPE;
BEGIN
REG.REGION_ID:=100;
REG.REGION_NAME:='AFRICA';
SELECT REGION_ID INTO REG_CONTROL FROM REGIONS
WHERE REGION_ID=REG.REGION_ID;
DBMS_OUTPUT.PUT_LINE('LA REGION YA EXISTE');
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO REGIONS VALUES (REG.REGION_ID,REG.REGION_NAME);
COMMIT;
END;
```
En este ejemplo si la región número 100 existe, nos lo dice por pantalla. Si no existe, inserta la región con ID 100 llamada AFRICA.
Como vemos, la zona de excepción no solo vale para capturar errores. También, nos vale para gestionar el código normal a través de la zona de excepciones.
### Excepciones personalizadas por el desarrollador
Son las excepciones creadas por el usuario.
RAISE es una cláusula que se utiliza dentro de las excepciones personalizadas o de usuario y sirve para disparar excepciones.
En las excepciones de Oracle se disparaban solos. En cambio, cuando una excepción es nuestra, Oracle no sabe cuándo tiene que dispararla y se lo tenemso que decir.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
reg_max EXCEPTION;
regn NUMBER;
regt varchar2(200);
BEGIN
regn:=101;
regt:='ASIA';
IF regn > 100 THEN
RAISE reg_max;
ELSE
insert into regions values (regn,regt);
commit;
END IF;
EXCEPTION
WHEN reg_max THEN
DBMS_OUTPUT.PUT_LINE('La region no puede ser mayor de 100.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error indefinido');
END;
/
```
### Ámbito de las excepciones
Pasa igual que con las variables globales y locales.
El hijo puede leer al padre, pero el padre no puede leer al hijo.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
regn NUMBER;
regt varchar2(200);
BEGIN
regn:=101;
regt:='ASIA';
DECLARE
reg_max EXCEPTION;
BEGIN
IF regn > 100 THEN
RAISE reg_max;
ELSE
insert into regions values (regn,regt);
commit;
END IF;
EXCEPTION
WHEN reg_max THEN
DBMS_OUTPUT.PUT_LINE('La region no puede ser mayor de 100.BLOQUE HIJO');
END;
EXCEPTION
/* WHEN reg_max THEN
DBMS_OUTPUT.PUT_LINE('La region no puede ser mayor de 100.');*/
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error indefinido');
END;
/
```
### Comando RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR es una función que nos permite devolver un error personalizado y cortar el programa. Útil cuando se producen errores lo suficientemente graves como para abortar el programa.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
regn NUMBER;
regt varchar2(200);
BEGIN
regn:=101;
regt:='ASIA';
IF regn > 100 THEN
-- EL CODIGO DEBE ESTAR ENTRE -20000 Y -20999
RAISE_APPLICATION_ERROR(-20001,'LA ID NO PUEDE SER MAYOR DE 100');
ELSE
insert into regions values (regn,regt);
commit;
END IF;
END;
/
```
## Cursores
### Introducción a los cursores
Con el uso del SELECT individual solo podíamos recuperar una sola fila y no se podían trabajar por ejemplo, con 1000 empleados.
Los cursores son objetos que permiten almacenar filas de la base de datos y trabajar con ellas de manera mucho más dinámica que trabajando solo con SELECT.
Hay dos tipos de cursores:
* **Implícitos**: Son relativamente simples y se generan automáticamente al lanzar comandos, como con SELECT. Oracla se encarga de abrir el área de memoria correspondiente, hacer la consulta, recuperar la fila y guardar la memoria.
* **Explícitos**: Se generan de forma manual por parte del programador. Son aquellos en los que yo defino las SELECT, los abro, los relleno, me muevo a través de ellos y por último, los cierro. Son mucho más potentes.
### Atributos implícitos
Atributos disponibles:
* **SQL%ISOPEN** (no vale para los implícitos, porque el implícito se abre y se cierra automáticamente), devuelve FALSE porque nunca va a estar abierto
* **SQL%FOUND**
* **SQL%NOTFOUND**
* **SQL%ROWCOUNT**
SQL es el nombre que se le pone a los cursores implícitos.
Si tenemos el siguiente código:
```sql
SELECT
SELECT
SQL%FOUND
```
Si se trabaja con cursores implícitos y se quiere hacer algo con alguno, hay que hacerlo justo después de lanzarlo.
Si después, tengo cualquier otro cursor de cualquier tipo (SELECT, INSERT, DELETE, etc.) automáticamente se borra el valor que tuviera SQL y lo sobrescribe con el último.
En este caso, preguntaríamos por la última SELECT. La primera SELECT ya la habríamos perdido.
Ejemplos:
```sql
CL SCR
SET SERVEROUTPUT ON
BEGIN
UPDATE TEST SET C2='PPPPP' WHERE C1=10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('ENCONTRADO');
END IF;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('NO ENCONTRADO');
END IF;
END;
/
```
Es muy útil porque nos permite saber cuántas filas se han visto implicadas tras un INSERT, UPDATE, etc. o, si existe o no existe una fila.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
X NUMBER;
BEGIN
UPDATE TEST SET C2='PPPPP' WHERE C1=10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('ENCONTRADO');
END IF;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('NO ENCONTRADO');
END IF;
SELECT C1 INTO X FROM TEST WHERE C1=1000;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('FILA NO EXISTE');
END IF;
END;
/
```
El SQL%NOTFOUND no se puede utilizar cuando una SELECT no devuelve filas. Como da un error porque solo puede devolver una fila, dispara una excepción y se sale, no llega al IF del NOTFOUND.
En los DELETE, UPDATE, etc. no hay problema porque no da errores.
### Ciclo de vida de un cursor
1. Tenemos una base de datos Oracle con los datos sobre los que queremos trabajar.
2. El cursor se declara en la zona de DECLARE, cuando se declara un cursor se crea una SELECT.
3. Abrimos el cursor, es decir, creamos una zona de memoria para recibir las filas de la SELECT.
4. Leemos fila a fila.
5. Preguntamos si ya hemos terminado el cursor, si es así, cerramos el cursor. En caso contrario, volvemos a leer la fila del cursor.
Con los cursores, una vez que ya tenemos una serie de filas cargadas en memoria, trabajamos sobre ellas.
Con los cursores de Oracle no se puede ir adelante y hacia atrás, siempre es abrir-cerrar.
### Crear un cursor
Mientras no se abra el cursor, el cursor se mantiene vacío o cerrado y no reserva la zona de memoria para traer las filas.
FETCH es el comando que permite leer el cursor, utilizando `FETCH nombredelcursor INTO variable`.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT * FROM REGIONS;
V1 REGIONS%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.REGION_NAME);
CLOSE C1;
END;
/
```
Al ejecutarlo, solo devolverá una fila. Lo normal sería utilizar bucles para ir leyendo filas.
Si queremos leer la primera y segunda fila:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT * FROM REGIONS;
V1 REGIONS%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.REGION_NAME);
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.REGION_NAME);
CLOSE C1;
END;
/
```
### Atributos (Recordatorio)
* **C1%NOTFOUND**: Si no encuentra alguna fila.
* **C2%FOUND**: Si encuentra alguna fila.
* **C3%ISOPEN**: Si el cursor ya estaba abierto.
* **C4%ROWCOUNT**: Número de filas que han sido procesadas por el cursor.
Los cursores implícitos son los que se generan automáticamente y que tenían estos atributos, excepto ISOPEN que no tenía sentido.
En los cursores implícitos hay que poner SQL y en los cursores explícitos hay que poner el nombre del cursor porque puede haber varios.
### Recorrer un cursor con el bucle LOOP
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT * FROM REGIONS;
V1 REGIONS%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V1;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V1.REGION_NAME);
END LOOP;
CLOSE C1;
END;
/
```
Con EXIT WHEN indicamos que salga cuando ya no haya más filas.
Normalmente, el bloque PL/SQL cierra automáticamente los cursores si no lo hemos hecho a mano, pero con bloques PL/SQL más complejos (utilizando funciones, procedimientos, etc.) el bucle podría no cerrarse.
Siempre que el cursor ya no se vaya a utilizar hay que cerrarlo para no ocupar zonas de memoria sin necesidad.
### Recorrer un cursor con el bucle FOR
Es una versión mucho más potente que con el bucle LOOP, nos ahorra algunos de los pasos anteriores.
```sql
DECLARE
CURSOR C1 IS SELECT * FROM REGIONS;
V1 REGIONS%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V1;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V1.REGION_NAME);
END LOOP;
CLOSE C1;
----------------------
---BUCLE FOR
FOR i IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(i.REGION_NAME);
END LOOP;
END;
/
```
En este caso, ¿dónde se abre, dónde se cierra, dónde se lee? Todos esos comandos están implícitos en el momento de la creación.
Ya hace el FETCH, EXIT de forma automática.
### Bucle FOR con subqueries
Permite crear el cursor dentro del propio bucle. No hace falta declarar el cursor. En vez de poner C1, ponemos el cursor directamente.
```sql
CL SCR
SET SERVEROUTPUT ON
BEGIN
FOR i IN (SELECT * FROM REGIONS) LOOP
DBMS_OUTPUT.PUT_LINE(i.REGION_NAME);
END LOOP;
END;
/
```
Es útil para crear bucles que solo se van a utilizar una sola vez para recorrer todas las filas.
### Cursores con parámetros
Hasta ahora hemos abierto cursores de manera estática con una query concreta, si queremos hacer cursores más dinámicos tenemos los cursores con parámetros.
En vez de poner un dato fijo en el cursor, creamos un cursor e indicamos que queremos pasarle argumentos (uno o varios separados por comas).
Al abrir el cursor en OPEN le pasamos el dato.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
CURSOR C1(SAL number) IS SELECT * FROM employees
where SALARY> SAL;
empl EMPLOYEES%ROWTYPE;
BEGIN
OPEN C1(10000);
LOOP
FETCH C1 INTO empl;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(empl.first_name||' '||empl.salary);
END LOOP;
dbms_output.put_line('He encontrado '||c1%rowcount||' empleados');
CLOSE C1;
END;
/
```
Esto es especialmente interesante con procedimientos y funciones, para que por ejemplo el resultado sea distinto cada vez que se ejecute un procedimiento.
### UPDATES y DELETES con WHERE CURRENT OF
Una de las características más interesantes que tienen los cursores es la posibilidad de modificar al vuelo la fila del cursor donde me encuentro en ese momento.
Hacemos una query, recuperamos una serie de filas y las bloqueamos con FOR UPDATE porque las vamos a modificar, de esta forma, nos aseguramos que otro usuario no las modifique en ese momento.
WHERE CURRENT OF se refiere a la fila donde me encuentro en ese momento.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
empl employees%rowtype;
CURSOR cur IS SELECT * FROM employees FOR UPDATE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO empl;
EXIT WHEN cur%notfound;
IF EMPL.COMMISSION_PCT IS NOT NULL THEN
UPDATE employees SET SALARY=SALARY*1.10 WHERE CURRENT OF cur;
ELSE
UPDATE employees SET SALARY=SALARY*1.15 WHERE CURRENT OF cur;
END IF;
END LOOP;
CLOSE cur;
END;
/
```
## Procedimientos y funciones almacenadas
### Introducción a Procedimientos y Funciones
Hasta ahora, se han utilizado bloques anónimos. El inconveniente de los bloques anónimos es que hay que guardarlos en un fichero .sql y luego volver a ejecutarlos.
Se denominan anónimos porque no tienen nombre y porque se guardan en algún sitio del sistema de archivos, son independientes de la base de datos.
Si queremos trabajar de forma más directa con la base de datos, ahí entrarían en juego:
* PROCEDURES
* FUNCTIONS
* PACKAGES
* TRIGGERS
Estos se guardan en la base de datos compilados para poder ser reutilizados desde la propia base de datos, no se pierden como los bloques anónimos.
Necesitan un nombre y algunas características.
Pasos:
1. Crear el objeto
Código fuente
Código pseudo-compilado
2. Invocar en cualquier momento (SQL)
Son muchos más rápidos.
### Crear un procedimiento
Los procedimientos no empiezan con un DECLARE. El DECLARE es para los bloques anónimos.
Para crear un procedimiento utilizamos CREATE para guardarlo en la base de datos sin ejecutarlo. Los bloques anónimos al lanzarlos si se ejecutan. Al crear procedimientos, primero se crea el programa, se compila el código fuente y después se llama.
El DECLARE es sustituido por IS o AS. Entre el IS y el BEGIN sería la sección del DECLARE.
```sql
CREATE PROCEDURE P1
IS
X NUMBER:=10;
BEGIN
DBMS_OUTPUT.PUT_LINE(X);
END;
```
Desde SQL Developer podemos acceder a los Procedimientos.
Para lanzar un procedimiento:
```sql
CL SCR
SET SERVEROUTPUT ON
BEGIN
PR1;
END;
```
o
`EXECUTE PR1;`
El EXECUTE en realidad es un BEGIN-END.
No se pueden tener dos procedimientos que se llamen igual con el mismo usuario.
Mediante la cláusula CREATE OR REPLACE si el procedimiento no existe lo crea y si existe lo sustituye.
```sql
CREATE OR REPLACE PROCEDURE PR1
IS
X NUMBER:=10;
BEGIN
DBMS_OUTPUT.PUT_LINE(X);
END;
/
EXECUTE PR1;
```
### Crear procedimiento con SqlDeveloper
Automáticamente nos crea el procedimiento.
En el END podemos poner el nombre del procedimiento para saber cuándo empieza y cuándo termina.
Ejecutar un procedimiento:
Se abrirá una ventana con el procedimiento a ejecutar, los parámetros y el código.
### Ver el código fuente de procedimientos y funciones
¿Cómo conocer los elementos de la base de datos sin SQL Developer?
Vistas interesantes:
* **USER_OBJECTS**: Tablas, vistas, procedimientos, etc.
```sql
SELECT * FROM USER_OBJECTS
WHERE OBJECT_TYPE='PROCEDURE';
```
Hay una columna llamada "STATUS" que indica si el procedimiento es válido o inválido. Si es inválido quiere decir que el procedimiento no está bien compilado y que tiene algún problema.
Para ver todos los objetos en el esquema y la cantidad:
```sql
SELECT OBJECT_TYPE,COUNT(*) FROM USER_OBJECTS
GROUP BY OBJECT_TYPE;
```
* **USER_SOURCE**
El nombre de los objetos va en mayúsculas.
Para ver el código fuente de un procedimiento:
```sql
SELECT TEXT FROM USER_SOURCE
WHERE NAME='P1';
```
### Parámetros de funciones y procedimientos
Desde un bloque PL/SQL queremos invocar a un procedimiento pasándole parámetros y que devuelva valores.
Tipos de parámetros:
* **IN** (por defecto): Se lleva desde el bloque PL/SQL hasta el procedimiento un valor en forma de constante, no se puede modificar.
* **OUT**: Lo contrario que IN, el procedimiento le devuelve un valor al bloque PL/SQL, este valor quedará guardado en una variable del programa que llama.
* **IN/OUT**: La conjunción de los dos anteriores. Se le pueden pasar argumentos al procedimiento y en ese mismo argumento se puede devolver un valor.
### Parámetros tipo IN
Se pasa un valor que no se puede modificar (read-only) y después se utiliza dentro del bloque PL/SQL. Es el parámetro por defecto, por lo tanto, no hace falta identificarlo.
En el siguiente ejemplo se pasan dos argumentos (ID de empleado y porcentaje de impuesto):
```sql
CREATE OR REPLACE PROCEDURE CALC_TAX
(EMPL IN EMPLOYEES.EMPLOYEE_ID%TYPE,
T1 IN NUMBER)
IS
TAX NUMBER:=0;
SAL NUMBER:=0;
BEGIN
SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=EMPL;
TAX:=SAL*T1/100;
DBMS_OUTPUT.PUT_LINE('SALARY:'||SAL);
DBMS_OUTPUT.PUT_LINE('TAX:'||TAX);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EXISTE EL EMPLEADO');
END;
/
```
```sql
CL SCR
SET SERVEROUTPUT ON
BEGIN
CALC_TAX(1000,20);
END;
```
El siguiente código no funcionaría y daría error porque la expresión T1, que es una variable de entrada, no puede ser utilizado como asignación. Las variables de tipo IN no pueden ser utilizadas nada más que para ser leídas:
```sql
CREATE OR REPLACE PROCEDURE CALC_TAX
(EMPL IN EMPLOYEES.EMPLOYEE_ID%TYPE,
T1 IN NUMBER)
IS
TAX NUMBER:=0;
SAL NUMBER:=0;
BEGIN
SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=EMPL;
T1:=0;
TAX:=SAL*T1/100;
DBMS_OUTPUT.PUT_LINE('SALARY:'||SAL);
DBMS_OUTPUT.PUT_LINE('TAX:'||TAX);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EXISTE EL EMPLEADO');
END;
/
```
Lo lógico sería declarar las variables que se van a utilizar:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
A NUMBER;
B NUMBER;
BEGIN
A:=120;
B:=5;
CALC_TAX(A,B);
END;
```
Controlar valores incorrectos (porcentaje de impuesto entre 0 y 60):
```sql
CREATE OR REPLACE PROCEDURE CALC_TAX
(EMPL IN EMPLOYEES.EMPLOYEE_ID%TYPE,
T1 IN NUMBER)
IS
TAX NUMBER:=0;
SAL NUMBER:=0;
BEGIN
IF T1 < 0 OR T1 > 60 THEN
RAISE_APPLICATION_ERROR(-20000,'EL PORCENTAJE DEBE ESTAR ENTRE 0 Y 60');
END IF;
SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=EMPL;
TAX:=SAL*T1/100;
DBMS_OUTPUT.PUT_LINE('SALARY:'||SAL);
DBMS_OUTPUT.PUT_LINE('TAX:'||TAX);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EXISTE EL EMPLEADO');
END;
/
```
El RAISE_APPLICATION_ERROR se sale del programa lanzando un mensaje de error personalizado.
### Parámetros tipo OUT
Un parámetro de tipo OUT permite devolver un valor desde dentro del procedimiento.
Normalmente en otros lenguajes, los procedimientos no devuelven nada, ejecutan algo y ya, para devolver algo están las funciones. Pero en el caso de PL/SQL y en otros lenguajes que también existe este concepto, lo que hacemos es pasar una variable que en realidad es como un puntero a una dirección de memoria que si se modifica en el procedimiento también se cambia en el padre (el programa que llama).
La variable R1 de tipo OUT devuelve el resultado de cálculo del impuesto.
Como R1 se trata de una dirección de memoria también se puede utilizar en el programa llamado.
```sql
CREATE OR REPLACE PROCEDURE CALC_TAX_OUT
(EMPL IN EMPLOYEES.EMPLOYEE_ID%TYPE,
T1 IN NUMBER,
R1 OUT NUMBER)
IS
--TAX NUMBER:=0;
SAL NUMBER:=0;
BEGIN
IF T1 < 0 OR T1 > 60 THEN
RAISE_APPLICATION_ERROR(-20000,'EL PORCENTAJE DEBE ESTAR ENTRE 0 Y 60');
END IF;
SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=EMPL;
R1:=SAL*T1/100;
DBMS_OUTPUT.PUT_LINE('SALARY:'||SAL);
--DBMS_OUTPUT.PUT_LINE('TAX:'||TAX);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EXISTE EL EMPLEADO');
END;
/
```
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
A NUMBER;
B NUMBER;
R NUMBER;
BEGIN
A:=120;
B:=10;
R:=0;
CALC_TAX_OUT(A,B,R);
DBMS_OUTPUT.PUT_LINE('R='||R);
END;
/
```
### Parámetros tipo IN-OUT
Si intentamos cambiar a `R:=1000;` al ser de tipo OUT el valor que tuviera la variable en origen no se le pasa al destino (procedimiento).
Si queremos utilizar variables de tipo OUT, variables que se quieren utilizar, así no valdría.
T1 ahora traerá y devolverá un número.
```sql
CREATE OR REPLACE PROCEDURE CALC_TAX_IN_OUT
(EMPL IN EMPLOYEES.EMPLOYEE_ID%TYPE,
T1 IN OUT NUMBER)
IS
SAL NUMBER:=0;
BEGIN
IF T1 < 0 OR T1 > 60 THEN
RAISE_APPLICATION_ERROR(-20000,'EL PORCENTAJE DEBE ESTAR ENTRE 0 Y 60');
END IF;
SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=EMPL;
DBMS_OUTPUT.PUT_LINE('T1='||T1);
T1:=SAL*T1/100;
DBMS_OUTPUT.PUT_LINE('SALARY:'||SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EXISTE EL EMPLEADO');
END;
/
```
En el siguiente caso B va a llegar con un valor de 10.
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
A NUMBER;
B NUMBER;
BEGIN
A:=120;
B:=10;
CALC_TAX_IN_OUT(A,B);
DBMS_OUTPUT.PUT_LINE('B='||B);
END;
/
```
Las funciones solo devuelven un valor, con variables de tipo OUT o IN OUT se podrían tener dos variables.
### Funciones
Diferencias de los procedimientos:
* Hay que poner CREATE OR REPLACE FUNCTION
* Después de los argumentos y antes del IS hay que poner RETURN tipoQueDevuelve, por ejemplo, RETURN NUMBER
```sql
CREATE OR REPLACE FUNCTION CALC_TAX_F
(EMPL IN EMPLOYEES.EMPLOYEE_ID%TYPE,
T1 IN NUMBER)
RETURN NUMBER
IS
TAX NUMBER:=0;
SAL NUMBER:=0;
BEGIN
IF T1 < 0 OR T1 > 60 THEN
RAISE_APPLICATION_ERROR(-20000,'EL PORCENTAJE DEBE ESTAR ENTRE 0 Y 60');
END IF;
SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=EMPL;
TAX:=SAL*T1/100;
RETURN TAX;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EXISTE EL EMPLEADO');
END;
/
```
El valor que devuelve hay que dejarlo en una variable, en este caso, R:
```sql
CL SCR
SET SERVEROUTPUT ON
DECLARE
A NUMBER;
B NUMBER;
R NUMBER;
BEGIN
A:=120;
B:=10;
R:=CALC_TAX_F(A,B);
DBMS_OUTPUT.PUT_LINE('R='||R);
END;
/
```
También se pueden tener variables de tipo OUT e IN/OUT, pero no es el mejor sitio porque no son buenas prácticas.
### Funciones en comandos SQL
Las funciones se pueden utilizar en SQL, se pueden poner en un SELECT, INSERT, DELETE, etc. siempre que se cumplan una serie de condiciones:
* Solo se pueden tener parámetros de tipo IN
* Tiene que estar en la base de datos, no se pueden llamar funciones desde ficheros .sql
* Tiene que devolver un dato SQL o PL/SQL
`SELECT FIRST_NAME,SALARY,CALC_TAX_F(EMPLOYEE_ID,18) FROM EMPLOYEES;`
## Triggers
### Introducción a los Triggers
Los triggers son objetos especiales que hay dentro de PL/SQL útiles para auditoría, seguridad, cambiar la lógica del negocio, etc.
Si intentamos hacer cualquier tipo de operación (INSERT, UPDATE, DELETE, etc.) sobre una tabla podemos hacer que se dispare un trigger. Un trigger es un trozo de PL/SQL nos podría permitir:
* Controlar que se está intentando grabar una tabla con un INSERT
* Impedir que alguien intente hacer un borrado en una fila
* Cuando se modifica una fila guardar el resultado que había antes en una tabla de forma segura
* Cambiar la lógica del negocio, poner los nombres de los empleados en mayúsculas
* Etc.
Existen diferentes tipos de triggers:
* **DML**: INSERT, UPDATE y DELETE. Acceso a los objetos y a los datos de la base de datos.
* **DDL**: CREATE, DROP, etc. Para controlar posibles operaciones sobre objetos como creaciones, borrados, etc.
* **DATABASE**: LOGON, SHUTDOWN, etc. Para controlar operaciones concretas de la base de datos. Están orientados a la administración.
### Tipos y eventos en los Triggers
Un trigger (disparador) lo que hace es dispararse cuando se da algún determinado evento.
Tipos:
* **BEFORE**: Controlar la acción antes de que se haga la operación.
* **AFTER**: Controlar la acción después de que se haga la operación.
* **INSTEAD OF**
Filas afectadas:
* **STATEMENT**: Solo se disparan una vez. Si se hace un DELETE de 1000 filas, solo se dispara una vez.
* **ROW**: Se disparan por cada fila. Si se hace un DELETE de 1000 filas, se dispara 1000 veces.
### Crear un trigger
Cada vez que alguien inserte algo en la tabla regiones, que se guarde en una tabla llamada LOG_TABLE que se ha hecho una inserción y el usuario que la he hecho.
Crear tabla de logs:
```sql
CREATE TABLE LOG_TABLE
(
LOG_COLUMN VARCHAR2(200)
, USER_NAME VARCHAR2(20)
);
```
Crear trigger:
```sql
CREATE OR REPLACE TRIGGER INS_EMPL
AFTER INSERT ON REGIONS
BEGIN
INSERT INTO LOG_TABLE VALUES('INSERCION EN LA TABLA REGIONS',USER);
END;
```
Probar a insertar:
`INSERT INTO REGIONS VALUES(1000,'TEST_REGION');`
Comprobar tabla de logs:
`SELECT * FROM LOG_TABLE;`
Desde SQL Plus hay que hacer COMMIT; después de lanzar un INSERT por ejemplo.
### Impedir operaciones con Triggers
Controlar que no se grabe una fila definitivamente en la base de datos.
Solo el usuario HR podrá insertar filas en la tabla REGIONS.
La mejor forma de parar un trigger es con RAISE_APPLICATION_ERROR.
```sql
CREATE OR REPLACE TRIGGER TR1_REGION
BEFORE INSERT ON REGIONS
BEGIN
IF USER <> 'HR' THEN
RAISE_APPLICATION_ERROR(-20000,'SOLO HR PUEDE INSERTAR EN REGIONS');
END IF;
END;
```
Probar a insertar:
`INSERT INTO REGIONS VALUES(1002,'REGION3');`
### Crear triggeres con eventos múltiples
No es necesario crear un trigger por cada INSERT, UPDATE, DELETE, etc.
```sql
CREATE OR REPLACE TRIGGER TR1_REGION
BEFORE INSERT OR UPDATE OR DELETE ON REGIONS
BEGIN
IF USER <> 'HR' THEN
RAISE_APPLICATION_ERROR(-20000,'SOLO HR PUEDE TRABAJAR EN REGIONS');
END IF;
END;
```
```sql
UPDATE HR.REGIONS SET REGION_NAME='XXXX'
WHERE REGION_ID=1000;
```
UPDATE que afecte a una columna determinada con OF:
```sql
CREATE OR REPLACE TRIGGER TR1_REGION
BEFORE INSERT OR UPDATE OF REGION_NAME ON REGIONS
BEGIN
IF USER <> 'HR' THEN
RAISE_APPLICATION_ERROR(-20000,'SOLO HR PUEDE TRABAJAR EN REGIONS');
END IF;
END;
```
### Controlar el tipo de evento
Guardar la operación que ha realizado un usuario sobre REGIONS en la tabla LOG_TABLE.
```sql
create or replace TRIGGER TR1_REGION
BEFORE INSERT OR UPDATE OR DELETE
ON REGIONS
BEGIN
IF INSERTING THEN
INSERT INTO LOG_TABLE VALUES ('INSERCION',USER);
END IF;
IF UPDATING('REGION_NAME') THEN
INSERT INTO LOG_TABLE VALUES ('UPDATE DE REGION_NAME', USER);
END IF;
IF UPDATING('REGION_ID') THEN
INSERT INTO LOG_TABLE VALUES ('UPDATE DE REGION_ID', USER);
END IF;
IF DELETING THEN
INSERT INTO LOG_TABLE VALUES ('DELETE', USER);
END IF;
END;
```
Borrar trigger:
`DROP TRIGGER INS_EMPL;`
### Triggers de tipo ROW
Con los triggers de tipo ROW se pueden controlar los datos de cada fila.
Tipos de datos en Triggers de tipo ROW:
Se puede acceder al dato antiguo y nuevo.
### Crear un trigger de tipo ROW
Solo hay que poner FOR EACH ROW.
```sql
create or replace TRIGGER TR1_REGION
BEFORE INSERT OR UPDATE OR DELETE
ON REGIONS
FOR EACH ROW
BEGIN
IF INSERTING THEN
:NEW.REGION_NAME:=UPPER(:NEW.REGION_NAME);
INSERT INTO LOG_TABLE VALUES ('INSERCION',USER);
END IF;
IF UPDATING('REGION_NAME') THEN
:NEW.REGION_NAME:=UPPER(:NEW.REGION_NAME);
INSERT INTO LOG_TABLE VALUES ('UPDATE DE REGION_NAME', USER);
END IF;
IF UPDATING('REGION_ID') THEN
INSERT INTO LOG_TABLE VALUES ('UPDATE DE REGION_ID', USER);
END IF;
IF DELETING THEN
INSERT INTO LOG_TABLE VALUES ('DELETE', USER);
END IF;
END;
```
Con `:NEW.REGION_NAME:=UPPER(:NEW.REGION_NAME);` estaríamos modificando el nombre de las regiones a mayúsculas antes de que se inserten en la base de datos.
### Cláusula WHEN
Podemos hacer que el trigger solo salte bajo determinadas circunstancias mediante WHEN.
Importante: No hay que poner ; al final del WHEN.
```sql
create or replace TRIGGER TR1_REGION
BEFORE INSERT OR UPDATE OR DELETE
ON REGIONS
FOR EACH ROW
WHEN (NEW.REGION_ID> 1000)
BEGIN
IF INSERTING THEN
:NEW.REGION_NAME:=UPPER(:NEW.REGION_NAME);
INSERT INTO LOG_TABLE VALUES ('INSERCION',USER);
END IF;
IF UPDATING('REGION_NAME') THEN
:NEW.REGION_NAME:=UPPER(:NEW.REGION_NAME);
INSERT INTO LOG_TABLE VALUES ('UPDATE DE REGION_NAME', USER);
END IF;
IF UPDATING('REGION_ID') THEN
INSERT INTO LOG_TABLE VALUES ('UPDATE DE REGION_ID', USER);
END IF;
IF DELETING THEN
INSERT INTO LOG_TABLE VALUES ('DELETE', USER);
END IF;
END;
```
Sign in with Wallet
Connect another wallet