PL/SQL Francisco Moreno Universidad Nacional
description
Transcript of PL/SQL Francisco Moreno Universidad Nacional
![Page 1: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/1.jpg)
PL/SQLPL/SQL
Francisco Moreno
Universidad Nacional
![Page 2: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/2.jpg)
Introducción al PL/SQL
¿Por qué PL/SQL?• A pesar de que SQL tiene mecanismos de
control condicional (cláusula CASE WHEN*) e iterativos (implícitos) en ocasiones se requiere: - Manipular y controlar los datos de una manera
secuencial - Mejorar el desempeño de las aplicaciones
• Existen problemas cuya solución puede ser más “sencilla” y eficiente mediante un lenguaje procedimental que mediante SQL “puro”
* PL/SQL también la tiene. Más adelante se ve un ejemplo.
![Page 3: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/3.jpg)
Introducción al PL/SQL• Ejemplo: inserción de 500 filas en una tabla:
DROP TABLE plana;CREATE TABLE plana(nro NUMBER(3)
PRIMARY KEY, dato VARCHAR2(80));BEGINFOR i IN 1..500 LOOP INSERT INTO plana VALUES (i,'No rayar las sillas');END LOOP;END;/
![Page 4: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/4.jpg)
• Incorporación de PSM* a SQL (1992) Incluye estructuras de secuencia, decisiónsecuencia, decisión, , iteracióniteración, creación de procedimientos, funciones, etc.
• La versión PSM de Oracle se llama PL/SQL (Procedural Language/SQL). En SQL Server se llama Transact-SQL (T-SQL).
• En PL/SQL se pueden crear procedimientosprocedimientos con o sin nombre (anónimos), funciones, funciones, disparadoresdisparadores (triggers) y bibliotecas de funciones y procedimientos llamadas paquetespaquetes.
*Persistent Stored Modules
Introducción al PL/SQL
![Page 5: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/5.jpg)
Un bloque PL/SQL es una pieza de código dividida en tres secciones:
DECLARE Sección de declaración
BEGIN Sección ejecutable
EXCEPTION Sección de manejo de excepciones
END;
• Las secciones de manejo de excepciones y de declaración son opcionales.
• Los bloques pueden contener otros bloques (sub-bloques) ver luego
• Los comentarios van entre /* */. Si no ocupan más de una línea, se pueden escribir después de -- (dos guiones).
Bloques PL/SQL
![Page 6: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/6.jpg)
Variables y constantes• Tipos de datos* en PL/SQL: NUMBER, CHAR,
VARCHAR/VARCHAR2, DATE, BOOLEAN, entre otros.
• La sintaxis para declarar variables o constantes es:
nombre [CONSTANT] TIPO [NOT NULL][:= expresión];
• No se diferencian mayúsculas y minúsculas.
* Algunos tienen precisión.
Los corchetes indican las
partes opcionales
![Page 7: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/7.jpg)
• Se pueden declarar variables refiriéndose al tipo de datos de otros elementos tales como variables, columnas y tablas, ver ejemplos más adelante.
• El operador de asignación es := y el de igualdad es =.
![Page 8: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/8.jpg)
AlcanceEl alcance o visibilidad de las variables sigue estas reglas:1. Una variable es visible en el bloque en el cual se declara
y en todos sus sub-bloques, a menos que se aplique la regla 2.
2. Si se declara una variable en un sub-bloque con el mismo nombre que una variable del bloque contenedor, la variable del sub-bloque es la que tiene prioridad en el sub-bloque*.
* Es posible acceder en el sub-bloque a la variable del bloque contenedor mediante etiquetas (luego se ejemplifican), pero lo más sencillo es usar nombres diferentes para las variables.
![Page 9: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/9.jpg)
AlcanceDECLARE aa NUMBER(2) := 10;BEGIN DBMS_OUTPUT.PUT_LINE('Valor de a externa '|| aa); DECLARE a NUMBER(3) := 20; BEGIN DBMS_OUTPUT.PUT_LINE('Valor de a interna '|| a); END; DBMS_OUTPUT.PUT_LINE('Valor de a '|| aa); END;/
Imprime:Valor de a externa 10Valor de a interna 20Valor de a 10
Nota: Para ver los resultados de la impresión en SQL*Plus se debe ejecutar:
SQL> SET SERVEROUTPUT ON
Sub-bloque
Operador de concatenación ||
Para ejecutar en SQL*Plus
![Page 10: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/10.jpg)
• En PL/SQL se puede usar directamente el sublenguaje de manipulación de datos DML de SQL, es decir, INSERT, DELETE, UPDATE, SELECT (el SELECT requiere usar INTO o estar asociado con un cursor, ver luego).
• Para usar sentencias DDL en PL/SQL , es decir, CREATE, DROP, ALTER se puede hacer así:
![Page 11: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/11.jpg)
BEGINEXECUTE IMMEDIATE 'CREATE TABLE
t(ced NUMBER(8))';END;/
• La sentencia DDL NO lleva punto y coma dentro de las comillas simples.
• Lo que sigue a IMMEDIATE puede ser una variable de caracteres Luego se verán más ejemplos
![Page 12: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/12.jpg)
• Usar EXECUTE IMMEDIATE solo cuando sea indispensable, lo siguiente es innecesario, aunque funciona:BEGIN
EXECUTE IMMEDIATE 'INSERT INTO t VALUES(97)';END;/
• Es más simple:BEGININSERT INTO t VALUES(97);END;/
![Page 13: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/13.jpg)
• En PL/SQL, las funciones numéricas (SQRT, ROUND, POWER etc.), de caracteres (LENGTH, UPPER, INITCAP, etc.) , de fechas (ADD_MONTHS, MONTHS_BETWEEN); se pueden usar por fuera de una sentencia SQL pero las funciones de grupo (COUNT, SUM, AVG, MAX, entre otras) solo se pueden usar dentro de una sentencia SQL.
![Page 14: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/14.jpg)
Ejemplo
DROP TABLE emp;CREATE TABLE emp( cod NUMBER(8) PRIMARY KEY, nom VARCHAR2(20) NOT NULL, fecha_ing DATE, sueldo NUMBER(8) CHECK(sueldo > 0));
![Page 15: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/15.jpg)
DECLAREnom emp.nom%TYPE := INITCAP('adam');fi emp.fecha_ing%TYPE;BEGINfi := ADD_MONTHS(SYSDATE,-14);INSERT INTO emp VALUES (4329, nom, fi, 10000);END;/
Las vbles. se pueden inicializar en el
DECLARE o en
el BEGINAcá se pueden colocar los valores directamente y prescindir de las vbles.
nom queda de tipo nom de
emp
![Page 16: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/16.jpg)
Sobre las consultas SQL en PL/SQL:• Se debe proporcionar un “lugar” para guardar
los datos devueltos por una consulta (SELECT)• Esto se puede lograr mediante la cláusula
SELECT … INTO.• Sin embargo, un SELECT ... INTO debe
retornar una y solo una fila: – Si la consulta no recupera filas o recupera múltiples
filas, ocurre un error (excepción, se verán luego). – Los cursores (se ven luego) sirven para consultas
que recuperan 0, 1 o más filas.
![Page 17: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/17.jpg)
Ejemplo de un borrado desde PL/SQL.
DECLARE limite emp.sueldo%TYPE := 8000; cuantos NUMBER(8);BEGIN SELECT COUNT(*) INTO cuantos FROM emp; DBMS_OUTPUT.PUT_LINE(cuantos); DELETE FROM emp WHERE sueldo > limite; SELECT COUNT(*) INTO cuantos FROM emp; DBMS_OUTPUT.PUT_LINE(cuantos);END;/
¿Qué pasaría si limite fuese un atributo de la tabla emp?
![Page 18: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/18.jpg)
Volver a insertar el empleado anteriorAhora ejecutar:
DECLARE nom emp.nom%TYPE; sue emp.sueldo%TYPE;BEGIN SELECT nom, sueldo INTO nom, sue FROM emp WHERE cod = 4329; /*Aquí se manipulan los datos recuperados, por ejemplo, imprimirlos:*/ DBMS_OUTPUT.PUT_LINE('El empleado ' || nom
|| ' tiene sueldo ' || sue);END;/
Luego se verá como
enviar parámetros
![Page 19: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/19.jpg)
Control de Flujo
• Las comparaciones lógicas son la base del control condicional en PL/SQL.
Los resultados de las comparaciones son verdadero (TRUE), falso (FALSE) o nulo (NULL).
• Cualquier “cosa” comparada con NULL retorna NULL (desconocido).
• Los operadores lógicos son : >, <, =, !=, <=, >=, <>
![Page 20: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/20.jpg)
La sentencia IF tiene la sintaxis:
IF condición THEN secuencia de instrucciones[ELSIF condición THEN secuencia de instrucciones]--Los ELSIF se pueden repetir[ELSE secuencia de instrucciones]END IF;
![Page 21: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/21.jpg)
Comparación con nulo: ¿Qué imprime el siguiente programa?:
DECLARE a NUMBER := NULL;BEGIN IF a = a THEN DBMS_OUTPUT.PUT_LINE('O sea que NULL =
NULL'); ELSIF a <> a THEN DBMS_OUTPUT.PUT_LINE('O sea que NULL <>
NULL'); ELSE DBMS_OUTPUT.PUT_LINE('Indefinido, NULL no es
ni = ni <> a NULL'); END IF;END;/
![Page 22: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/22.jpg)
Lo anterior también se puede escribir con CASE así:
DECLARE a NUMBER := NULL;BEGINCASE WHEN a = a THEN DBMS_OUTPUT.PUT_LINE('O sea que NULL =
NULL');WHEN a <> a THEN DBMS_OUTPUT.PUT_LINE('O sea que NULL <>
NULL'); ELSE DBMS_OUTPUT.PUT_LINE('Indefinido, NULL no es ni
= ni <> a NULL'); END CASE;END;/
![Page 23: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/23.jpg)
Ciclos o iteraciones
a) Ciclo simple sin límite: LOOP
LOOP secuencia de instruccionesEND LOOP;
Para salir del ciclo se usa:
EXIT [WHEN condición];
![Page 24: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/24.jpg)
Ejemplo.
DECLARE cont NUMBER(4) := 0;BEGINDELETE plana; LOOP INSERT INTO plana VALUES(cont,
CEIL(DBMS_RANDOM.VALUE(1,100000))); cont := cont + 1; EXIT WHEN cont = 1000; END LOOP;END;/
![Page 25: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/25.jpg)
b) Ciclo para: FOR Permite repetir una secuencia de instrucciones un número fijo de veces. Su sintaxis es:
FOR índice IN [REVERSE] entero .. entero LOOP secuencia de instruccionesEND LOOP;
Notas: - El incremento del FOR siempre es 1. - Aunque el ciclo se haga “en reversa” los
límites siempre se colocan de menor a mayor. Veamos un ejemplo:
![Page 26: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/26.jpg)
Ejemplo:
BEGINDELETE plana; FOR i IN REVERSEREVERSE 1..5001..500 LOOP INSERT INTO plana VALUES (i, 'No rayar las sillas'); END LOOP;END;/
![Page 27: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/27.jpg)
c) Ciclo mientras que: WHILEWHILE repetirá una secuencia de instrucciones
hasta que la condición controladora del ciclo deje
de ser cierta. Su sintaxis es:
WHILE condición LOOP secuencia de instruccionesEND LOOP;
![Page 28: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/28.jpg)
Ejemplo:
DECLARE cont NUMBER(3) := 500;BEGINDELETE PLANA; WHILE cont > 0 LOOP INSERT INTO plana VALUES (cont, DBMS_RANDOM.STRING('u',60) || cont); cont := cont - 1; END LOOP;END;/
u: mayúsculasl: minúsculasa: combinacíon de mayúsculas y minúsculasx: alfanuméricos
Tamaño
![Page 29: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/29.jpg)
Instrucción CONTINUE
• CONTINUE pasa el control inmediatamente a la siguiente iteración de un ciclo. Solo se puede usar en ciclos.
![Page 30: PL/SQL Francisco Moreno Universidad Nacional](https://reader035.fdocumento.com/reader035/viewer/2022062518/56814664550346895db3855c/html5/thumbnails/30.jpg)
DECLAREi NUMBER := 0;BEGIN LOOP i := i + 1; IF i BETWEEN 5 AND 15 THEN CONTINUE; END IF; DBMS_OUTPUT.PUT_LINE(i); EXIT WHEN i = 20; END LOOP;END;/
CONTINUE