Introducción a PL/SQL
• ¿Qué es PL/SQL?
• Estructura de bloque
[DECLARE
-- declaraciones]
BEGIN
-- sentencias
[EXCEPTION
-- manejadores]
END;
Variables
• Pueden corresponder a cualquier tipo de
dato de SQL (VARCHAR2, NUMBER, ...)
• Declaración: edad number(3);
• Asignación:
– Mediante :=
– Mediante into en un sentencia Select
Constantes
• Pueden corresponder a cualquier tipo de
dato de SQL (VARCHAR2, NUMBER, ...)
• Declaracion: credito_max CONSTANT real := 5000.00 ;
Cursores
• Áreas de trabajo que permiten ejecutar
sentencias SQL y procesar la información
obtenida de ellos.
• Tipos: implícitos y explícitos
• Ejemplo:
DECLARE
CURSOR curs_01 IS
SELECT empno, ename, job FROM emp WHERE deptno=20;
Cursores
Result set
1 MARTINEZ PEDRO
Cursor 2 PÉREZ MANUEL
3 GARCÍA ANTONIO
4 IGLESIAS JUAN
Manejo de errores
• Excepciones
• Exception handlers
• Excepciones predefinidas y de usuario
Subprogramas
• Procedimientos
• Funciones
Paquetes
• Conjunto de tipos de datos relacionados,
variables, cursores e incluso subprogramas
• Partes de los paquetes:
– Especificación
– Cuerpo
• La primera vez que se ejecutan se
almacenan en memoria
Paquetes
CREATE PACKAGE emp_actions as -- especificación
PROCEDURE hire_employee (empno NUMBER, ename CHAR, …) ;
PROCEDURE fire_employee (empid NUMBER) ;
END emp_actions ;
CREATE PACKAGE BODY emp_actions AS -- cuerpo
PROCEDURE hire_employee (empno NUMBER, ename CHAR, …) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, …);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Ventajas de PL/SQL
• Utiliza SQL
• Aumenta el rendimiento
• Alta productividad
• Portabilidad
• Integración con Oracle (%TYPE)
• Seguridad: objetos compilados en el
servidor impiden manejo desde cliente
Fundamentos del lenguaje
• Set de caracteres y unidades léxicas
– Letras mayúsculas y minúsculas de la A a la Z
– Números del 0 al 9
– Los símbolos ( ) + - * / < > = ! ~ ^ ; . ‘ @ % , “
#$&_|{}?[]
– Tabuladores, espacios y saltos de carro
• No es sensible a mayúsculas o minúsculas
Unidades léxicas
• Delimitadores (símbolos simples y
compuestos)
• Identificadores (incluye palabras
reservadas)
• Literales
• Comentarios
Ejemplo Unidades Léxicas
Por ejemplo en la instrucción:
bonus := salary * 0.10; -- cálculo del bono
Se observan las siguientes unidades léxicas:
• Los identificadores bonus y salary
• El símbolo compuesto :=
• Los símbolos simples * y ;
• El literal numérico 0.10
• El comentario “cálculo del bono”
Legibilidad del código
• Añadir espacios entre identificadores o símbolos.
• Utilizar saltos de línea e indentaciones para permitir una
mejor legibilidad.
• Ejemplo:
IF x>y THEN max := x; ELSE max := y; END IF;
IF x > y THEN
max := x;
ELSE
max := y;
END IF;
Delimitadores
• Símbolo simple o compuesto que tiene un
significado especial dentro de PL/SQL
(p.e.: representar operaciones aritméticas +
- *)
• Delimitadores simples y compuestos (>=,:=,
etc)
Identificadores
• Constantes, variables, excepciones,
cursores, subprogramas y paquetes.
• Letra, seguida opcionalmente de otras
letras, números, signo de moneda,
underscore y otros signos numéricos.
• Máximo 30 caracteres
• Palabras reservadas
Tipos de datos
• Definen forma de almacenamiento,
restricciones y rango de valores válidos .
• Tipos escalares, compuestos y de referencia
Tipos de datos
Tipos de datos y conversiones
• Tipos de datos especiales:
– %TYPE: id_empleado [Link]%type
– %ROWTYPE: empleado emp%rowtype
• Conversiones:
– Implícitas
– Explícitas
Alcance y visibilidad
DECLARE
x real;
y varchar2(5);
BEGIN
DECLARE
x real;
BEGIN
...
END;
...
END;
Estructuras de control
Selección Iteración Secuencia
T F F
T
Estructuras de control
• Control condicional o de selección:
IF condición THEN
secuencia_de_sentencias_1
ELSE
secuencia_de_sentencias_2
END IF;
• Anidamiento de sentencias de control
Estructuras de control
• Control condicional, ejemplo:
IF tipo_trans = ‘CR’ THEN
UPDATE cuentas SET balance = balance + credito WHERE …
ELSE
IF nuevo_balance >= minimo_balance THEN
UPDATE cuentas SET balance = balance – debito WHERE
…
ELSE
RAISE fondos_insuficientes;
END IF;
END IF;
Estructuras de control
• Control condicional (IF... THEN ... ELSIF)
IF condición_1 THEN
secuencia_de_sentencias_1
ELSIF condición_2 THEN
secuencia_de_sentencias_2
ELSE
secuencia_de_sentencias_3
END IF;
Estructuras de control
• Ejemplo IF... THEN ... ELSIF:
BEGIN
…
IF sueldo > 50000 THEN
bonus : = 1500;
ELSIF sueldo > 35000 THEN
bonus : = 500;
ELSE
bonus : = 100;
END IF;
INSERT INTO sueldos VALUES (emp_id, bonus, );
END;
Estructuras de control
• Control de iteración:
– LOOP
– WHILE-LOOP
– FOR-LOOP
Estructuras de control
• LOOP
• EXIT – EXIT WHEN
LOOP LOOP
IF ranking_credito < 3 THEN EXIT WHEN ranking_credito < 3;
…
EXIT; ...
END IF; END LOOP;
END LOOP;
Estructuras de control
• Etiquetas
<<rótulo>>
LOOP
secuencia de sentencias
END LOOP rótulo;
Estructuras de control
• WHILE – LOOP
WHILE condición LOOP
secuencia_de_sentencias
END LOOP;
Estructuras de control
No necesita ser declarado
• FOR –LOOP
FOR contador IN [REVERSE] valor_minimo..valor_maximo
LOOP
secuencia_de_sentencias
END LOOP;
FOR cont IN 1..10 LOOP No está permitido
…
END LOOP;
sum := cont + 1 ;
Estructuras de control
• FOR-LOOP: uso de exit
FOR i IN 1..5 LOOP
FOR j IN 1..10 LOOP …
... FOR j IN 1..10 LOOP
EXIT WHEN j=2; ....
… EXIT externo WHEN j=2;
END LOOP; …
END LOOP;
END LOOP externo;
Control de transacciones
• Savepoint: nombrar y marcar un punto determinado donde
se podrá retornar el control después de un rollback
DECLARE
emp_id [Link]%TYPE;
BEGIN
UPDATE emp SET … WHERE empno=emp_id;
DELETE FROM emp WHERE …
…
SAVEPOINT do_insert;
INSERT INTO emp VALUES (emp_id, …);
…
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO do_insert;
END;
Gestión de excepciones
• Tipos:
– Interna: producida por Oracle
– Explícita: definida por el usuario (RAISE)
• Ventajas de trabajar con excepciones:
– Mayor claridad en el código
– Sencillez de programación
Excepciones de usuario
• Declaración de excepciones
DECLARE
error_01 EXCEPTION;
• Reglas de alcance: similar a cualquier
variable
• Sentencia Raise
Ejemplo excepciones
DECLARE
out_of_stock EXCEPTION; -- declaración de la excepción
total NUMBER(4);
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
…
IF total < 1 THEN
RAISE out_of_stock; -- llamada a la excepción
END IF;
EXCEPTION
WHEN out_of_stock THEN
-- manejar el error aquí
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errores VALUES(err_num, err_msg);
END;
Cursores
• Utilidad: Manejar grupos de datos que se
obtienen como resultado de una consulta.
• Tipos de cursores:
– Implícitos (uso de INTO)
SELECT EMPNO INTO v_empno WHERE ....
– Explícitos.
Declaración de cursores
• Sintaxis:
DECLARE
CURSOR nombre_cursor [ (parámetro1 [, parámetro2]…) ]
[RETURN tipo_de_retorno] IS sentencia_select ;
• Ejemplos:
DECLARE
CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE
sal>1000 ;
CURSOR c2 RETURN dept%ROWTYPE IS
SELECT * from dept WHERE deptno = 10 ;
…
Apertura de cursores
• Se ejecuta inmediatamente la consulta e identifica el
conjunto resultado
• Sintaxis:
DECLARE
emp_name [Link]%TYPE;
salary [Link]%TYPE;
CURSOR c1 (name VARCHAR2, salary NUMBER) IS SELECT…
BEGIN
emp_name:=‘JOSÉ MARTÍNEZ’;
salary:=21000;
OPEN c1(emp_name, salary);
...
END;
Recuperación de filas
• Sentencia FETCH
FETCH c1 INTO my_empno, my_ename, my_deptno;
• Control del cursor:
– %FOUND
– %NOTFOUND
– %ISOPEN
– %ROWCOUNT
Cierre de cursores
• Sentencia Close
• Ejemplo:
Close c1;
Ejemplo
DECLARE
CURSOR c1 IS SELECT…
BEGIN
LOOP
FETCH c1 INTO …
EXIT WHEN c1%NOTFOUND;
…
END LOOP;
CLOSE C1;
END;
Subprogramas
• Tipos:
– Procedimientos: para ejecutar una acción
específica
– Funciones: para calcular un valor
• Ventajas de su uso:
– Reutilización
– Encapsulación
– Mantenibilidad
Procedimientos
• Subprograma que ejecuta una acción especifica
• Sintaxis:
PROCEDURE nombre [ (parámetro [, parámetro, …] ) ] IS
[declaraciones_locales]
BEGIN
sentencias_ejecutables
[EXCEPTION
condiciones_de_excepción]
END [nombre] ;
parámetro [IN | OUT | IN OUT ] tipo_de_dato
Ejemplo de procedimiento
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrown EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts WHERE acct_no = acct_id;
new_balance := old_balance – amount;
IF new_balance < 0 THEN
RAISE overdrown;
ELSE
UPDATE accts SET bal = new_balance WHERE acct_no = acct_id;
END IF;
EXCEPTION
WHEN overdrown THEN
…
END debit_account;
Funciones
• Subprograma que calcula un valor
• Sintaxis
FUNCTION nombre [ (parámetro [, parámetro, …] ) ] RETURN tipo_de_dato IS
BEGIN
sentencias_ejecutables
[EXCEPTION
condiciones_de_excepción]
END [nombre] ;
parámetro [IN | OUT | IN OUT ] tipo_de_dato
Ejemplo de función
FUNCTION revisa_salario (salario REAL, cargo CHAR(10)) RETURN BOOLEAN IS
salario_minimo REAL;
salario_maximo REAL;
BEGIN
SELECT lowsal, highsal INTO salario_minimo, salario_maximo
FROM salarios WHERE job = cargo ;
RETURN (salario >= salario_minimo) AND (salario <= salario_maximo)
END revisa_salario ;
DECLARE
renta_actual REAL;
codcargo CHAR(10);
BEGIN
…
IF revisa_salario (renta_actual, codcargo) THEN …
Uso de parámetros
• Tipos de parametros:
– IN: dentro del subprograma es como una cte.
– OUT: funciona como una variable local
– INOUT: funciona como una variable local con
un valor inicial
Polimorfismo
• Subprogramas con el mismo nombre y
distintos parámetros
Ejemplo de polimorfismo
PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := SYSDATE;
END LOOP;
END initialize;
PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := 0.0;
END LOOP;
END initialize;
Paquetes
• Esquema u objeto que agrupa tipos de
PL/SQL relacionados, ítems y
subprogramas
• Estructura de paquete:
– Especificación
– Cuerpo
Paquetes
• Ventajas de uso:
– Encapsulación
– Comienzo rápido de aplicaciones
– Ocultar información (público, privado)
– Posibilidad de compartir cursores
– Mejoran rendimiento: se almacenan en
memoria
Especificación de paquetes
• Contiene las declaraciones públicas de variables,
paquetes, funciones, ...
• Ejemplo:
CREATE OR REPLACE PACKAGE emp_actions AS -- Especificación del paquete
TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER);
PROCEDURE fire_employee(emp_id NUMBER);
END emp_actions;
Cuerpo del paquete
• Define completamente a cursores y subprogramas e implementa lo que se declaró inicialmente en la
especificación
CREATE OR REPLACE PACKAGE BODY emp_actions AS
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr,
SYSDATE, sal, comm, deptno);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;