Transcript INTRODUCCION AL PL/SQL

INTRODUCCIÓN A
PL/SQL*
*Tomado del curso de Francisco Moreno
20/07/2015
Laboratorio de Bases de Datos
1
Introducción al PL/SQL
¿Por qué PL/SQL?
 A pesar de que SQL posee mecanismos de control
condicional (cláusula WHEN CASE) 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 es más “sencilla”
mediante un lenguaje procedimental que mediante SQL
“puro”
20/07/2015
Laboratorio de Bases de Datos
2
Introducción al PL/SQL



Incorporación de PSM* a SQL (1992)  Incluye estructuras
de iteración, decisión y secuencia. Creación de
procedimientos, funciones etc.
ORACLE proporciona su versión de PSM llamada PL/SQL
(Procedural Language/SQL).
En SQL Server se llama Transact SQL (T-SQL).
En PL/SQL se pueden construir procedimientos con o sin
nombre (anónimos), funciones, disparadores y bibliotecas de
funciones y procedimientos llamadas paquetes.
*Persistent Stored Modules
20/07/2015
Laboratorio de Bases de Datos
3
Bloques PL/SQL
Un bloque PL/SQL es una pieza de código dividida en 3 secciones:
DECLARE
Sección de declaración
BEGIN
Sección ejecutable
EXCEPTION
Sección de manejo de excepciones
END;



La sección de manejo de excepciones, así como la de declaración de
variables es opcional.
Los bloques pueden a su vez contener otros bloques (sub-bloques)
Los comentarios van encerrados entre /* */.
Si éstos no ocupan más de una línea, pueden escribirse después de
-- (dos guiones)
20/07/2015
Laboratorio de Bases de Datos
4
Variables y constantes


Los tipos de datos* en PL/SQL son: NUMBER,
CHAR,VARCHAR/VARCHAR2, DATE, BOOLEAN entre otros.
La sintaxis para declarar variables o constantes es:
nombre_vble [CONSTANT] TIPO [NOT NULL][:= expresión];


En vez de := se puede usar DEFAULT.
No se diferencian mayúsculas y minúsculas
* Algunos tienen precisión.
20/07/2015
Laboratorio de Bases de Datos
5
Ejemplos
DECLARE
nombre VARCHAR(30);
apellido VARCHAR2(30);
proximo_chequeo DATE NOT NULL := '05-OCT-07';
cedula CHAR(8) := '76123467';
disponible BOOLEAN DEFAULT FALSE;
x NUMBER := SQRT(665) * .8;
pi CONSTANT NUMBER(5,4) := 3.1416;
BEGIN
NULL;
END;
/
20/07/2015
Laboratorio de Bases de Datos
6
También es posible declarar variables haciendo referencia al tipo de otros objetos tales
como variables, constantes o con el tipo correspondiente a objetos de la base de
datos como columnas y tablas.
Ej: Sea la tabla:
CREATE TABLE emp( cedula CHAR(8) PRIMARY KEY,
nombre VARCHAR2(20) NOT NULL);
DECLARE
cant_comprada NUMBER(6);
cant_vendida cant_comprada%TYPE; --el tipo de otra variable
nombre emp.nombre%TYPE; --el tipo de una columna de una tabla
vendedor emp%ROWTYPE; --el tipo de una tabla (registro)
BEGIN
NULL;
END;
/
20/07/2015
Laboratorio de Bases de Datos
7
INSERT INTO emp VALUES('123 ', 'Juan');
DECLARE
vendedor emp%ROWTYPE;
BEGIN
SELECT * INTO vendedor FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Identificacion = ' || vendedor.cedula);
DBMS_OUTPUT.PUT_LINE('Nombre = ' || vendedor.nombre);
END;
/
Nota: Para ver los resultados de la
impresión en SQL*Plus se debe
ejecutar:
SQL> SET SERVEROUTPUT ON
20/07/2015
Laboratorio de Bases de Datos
8
Asignación
El operador de asignación es ":=" ; mientras que el
operador lógico de igualdad es "=".
DECLARE
valor NUMBER(2) := 4;
b BOOLEAN;
materia VARCHAR(20);
nomcurso VARCHAR2(40);
falta NUMBER(1);
nro NUMBER(3);
fecha DATE;
BEGIN
nro := 5 * valor * 0.5;
b := FALSE;
materia := 'Laboratorio de';
nomcurso := materia || ' ' || 'Bases ' || 'de ' || 'Datos';
fecha := SYSDATE; --Fecha del sistema
b := 5 >= nro;
END;
20/07/2015
Laboratorio de Bases de Datos
/
9


El PL/SQL permite utilizar directamente el sublenguaje
de manipulación de datos DML del SQL, es decir: INSERT,
DELETE, UPDATE, SELECT.
Para la utilización de sentencias DDL en PL/SQL (CREATE,
DROP, ALTER) se puede realizar así:
a) Desde la versión 7 mediante el uso del paquete:
DBMS_SQL. Ej:
20/07/2015
Laboratorio de Bases de Datos
10
DECLARE
c BINARY_INTEGER; --Tipo de datosque maneja enteros
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c,'CREATE TABLE test(a NUMBER)',
DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(c);
END;
/
20/07/2015
Laboratorio de Bases de Datos
11
b) A partir de de Oracle 8i se puede utilizar el comando EXECUTE
IMMEDIATE:
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE t(f DATE)';
END;
/

Nótese que la instrucción DDL no lleva punto y coma dentro las
comillas simples.

Lo que sigue a IMMEDIATE puede ser una variable de caracteres.
20/07/2015
Laboratorio de Bases de Datos
12
Ejemplo de una inserción desde PL/SQL:
Crear por medio de SQL*Plus la siguiente tabla
DROP TABLE emp;
CREATE TABLE emp(
cod NUMBER(8) PRIMARY KEY,
¿El campo sueldo admite nulos?
nom VARCHAR2(10) NOT NULL,
cargo VARCHAR2(10),
fecha_ing DATE,
sueldo NUMBER(8) CHECK (sueldo >0),
depto NUMBER(3)
);
20/07/2015
Laboratorio de Bases de Datos
13
DECLARE
mi_sueldo emp.sueldo%TYPE := 10000;
mi_nom emp.nom%TYPE := 'Juan';
fec_ingreso emp.fecha_ing%TYPE := SYSDATE;
BEGIN
INSERT INTO emp (cod,nom,cargo,fecha_ing,sueldo,depto)
VALUES (4322,mi_nom,'ANALISTA',fec_ingreso,mi_sueldo,20);
END;
/
20/07/2015
Laboratorio de Bases de Datos
14
Ejemplo de un borrado:
DECLARE
limite emp.sueldo%TYPE := 8000;
BEGIN
DELETE FROM emp
WHERE sueldo > limite;
END;
/
¿Qué pasa si limite es un campo de la tabla emp?
20/07/2015
Laboratorio de Bases de Datos
15
Las consultas en PL/SQL:
 Se debe proporcionar un “lugar” para guardar los datos
devueltos por una consulta (SELECT) mediante la
cláusula INTO.
 Un SELECT ... INTO debe retornar una y sólo una fila.
 Si no trae filas o trae múltiples filas ocurre un error
(excepción, se verán luego).
 Cuando se traen múltiples filas es necesario el uso de
cursores (ver luego).
20/07/2015
Laboratorio de Bases de Datos
16
Volver a insertar el empleado con cédula 4322...
Ahora ejecutar:
DECLARE
nom emp.nom%TYPE;
salario emp.sueldo%TYPE;
BEGIN
SELECT nom, sueldo INTO nom, salario
FROM emp WHERE cod = 4322;
-- Aquí se manipulan los datos traídos,
-- por ejemplo imprimirlos
DBMS_OUTPUT.PUT_LINE('El empleado ' || nom || ' tiene salario ' ||
salario );
END;
/
20/07/2015
Laboratorio de Bases de Datos
17

Todas las funciones de SQL pueden ser
referenciadas dentro de una orden DML
de SQL:
INSERT INTO emp
(cod,nom,cargo,fecha_ing,sueldo,depto)
VALUES (4329,UPPER('Pedro'),'Analista',
ADD_MONTHS(SYSDATE,-14),10000,NULL);
20/07/2015
Laboratorio de Bases de Datos
18

Por fuera de un comando SQL, las funciones
numéricas (SQRT, ROUND, POWER etc.), de caracteres
(LENGTH, UPPER, INITCAP, etc.) , de fechas
(ADD_MONTHS, MONTHS_BETWEEN) están disponible
exceptuando las funciones de grupo (COUNT,SUM,AVG,
MAX, etc.)
20/07/2015
Laboratorio de Bases de Datos
19
DECLARE
x NUMBER(8,4);
y NUMBER(8):= 2;
apellido VARCHAR2(10) := 'gómez';
fecha DATE := '24-DEC-1998';
edad NUMBER(3);
BEGIN
x := SQRT(y);
y := ROUND(SQRT(y)) ;
apellido := INITCAP(apellido);
edad := FLOOR(MONTHS_BETWEEN(SYSDATE, fecha)/12);
DBMS_OUTPUT.PUT_LINE(x ||' '|| y || ' '|| apellido ||' ' || edad);
END;
/
Salida: 1,4142 1 Gómez 7;
20/07/2015
Laboratorio de Bases de Datos
20
Control de Flujo



Las comparaciones lógicas forman la base del control
condicional en PL/SQL.
Los resultados de las comparaciones siempre son
verdadero (TRUE), falso (FALSE) o nulo (NULL).
Cualquier “cosa” comparada con NULL da como
resultado NULL (Desconocido).
Los operadores lógicos son : >, <, =, !=, <=, >=,
<>
20/07/2015
Laboratorio de Bases de Datos
21
La sentencia IF tiene la siguiente 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;
20/07/2015
Laboratorio de Bases de Datos
22
CREATE TABLE actor (idactor NUMBER(5) PRIMARY KEY,
nom VARCHAR2(8),calif VARCHAR(8));
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
actor
actor
actor
actor
VALUES
VALUES
VALUES
VALUES
(1,
(2,
(3,
(4,
'Pablo', NULL);
'Ana', NULL);
'Luisa', NULL);
'Juan', NULL);
CREATE TABLE actuacion (idactor NUMBER(5) REFERENCES actor, idpelicula NUMBER(5),PRIMARY
KEY(idactor, idpelicula));
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
actuacion
actuacion
actuacion
actuacion
actuacion
actuacion
actuacion
20/07/2015
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(1,
(1,
(1,
(2,
(2,
(3,
(4,
88);
89);
83);
88);
89);
83);
81);
Laboratorio de Bases de Datos
23
DECLARE
nro_papeles NUMBER(7);
BEGIN
SELECT COUNT(*) INTO nro_papeles
FROM actuacion
WHERE idactor = &&identificacion;
IF nro_papeles > 9 THEN
UPDATE actor SET calif = 'Veterano'
WHERE idactor = &&identificacion;
ELSIF nro_papeles > 5 THEN
UPDATE actor SET calif = 'Maduro'
WHERE idactor = &&identificacion;
ELSE
UPDATE actor SET calif = 'Novato'
WHERE idactor = &&identificacion;
END IF;
END;
/
20/07/2015
Pide datos por
pantalla (en
SQL*Plus)
Para reinicializar identificacion usar: UNDEFINE
identificacion en SQL*Plus
Laboratorio de Bases de Datos
24
Comparaciones con nulo: ¿Qué imprime el siguiente programa?:
DECLARE
a NUMBER := NULL;
b NUMBER := NULL;
BEGIN
IF a=b THEN
DBMS_OUTPUT.PUT_LINE('O sea que NULL = NULL');
ELSIF a<>b 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;
/
20/07/2015
Laboratorio de Bases de Datos
25
Ciclos o iteraciones
a) Ciclo simple sin límite
LOOP
<secuencia de instrucciones>
END LOOP;
Ya que es un ciclo infinito, se necesita de un
mecanismo de salida:
EXIT [WHEN <condición>];
20/07/2015
Laboratorio de Bases de Datos
26
Ejemplo.
Sea la tabla:
CREATE TABLE plana(nro NUMBER(3), mensaje VARCHAR2(20));
DECLARE
cont NUMBER(4) := 0;
BEGIN
LOOP
INSERT INTO plana VALUES(cont, 'No debo tirar tizas');
cont := cont + 1;
EXIT WHEN cont = 1000;
END LOOP;
END;
/
20/07/2015
Laboratorio de Bases de Datos
27
b) Ciclo para: FOR
Permite repetir una secuencia de instrucciones un
número fijo de veces con la cláusula FOR, cuya
sintaxis es:
FOR <índice> IN [REVERSE] <entero> .. <entero> LOOP
<secuencia de instrucciones>
END LOOP;
Notas: - El incremento del FOR siempre es 1
- Aunque el ciclo se haga “en reversa” los
límites del ciclo se colocan siempre de menor a mayor
20/07/2015
Laboratorio de Bases de Datos
28
Ejemplo:
DELETE plana;
DELETE plana;
BEGIN
BEGIN
FOR i IN REVERSE 1..500 LOOP
FOR i IN 1..500 LOOP
INSERT INTO plana
INSERT INTO plana
VALUES (i, 'Ser ético');
VALUES (i, 'Ser ético');
END LOOP;
END LOOP;
END;
END;
/
/
20/07/2015
Laboratorio de Bases de Datos
29
c) Mientras que: WHILE
La instrucción WHILE repetirá una secuencia de
instrucciones hasta que una condición específica
deje de ser cierta.
La sintaxis de WHILE es:
WHILE <condición> LOOP
<secuencia de instrucciones>
END LOOP;
20/07/2015
Laboratorio de Bases de Datos
30
Ejemplo:
DELETE PLANA;
DECLARE
cont number(3) := 500;
BEGIN
WHILE cont > 0 LOOP
INSERT INTO plana VALUES
(cont, 'Solicitud nro ' || cont);
cont := cont - 1;
END LOOP;
END;
/
20/07/2015
Laboratorio de Bases de Datos
31
GOTO
LOOP
…
GOTO salida;
…
END LOOP;
…
<<salida>>
…

No se puede hacer GOTO:


Desde el exterior hacia el interior de: un ciclo, un IF o un subbloque
Desde una excepción hacia al bloque que la contiene
Se ven luego
20/07/2015
Laboratorio de Bases de Datos
32