PDF de programación - PL/SQL

PL/SQL

• En caso de error
• Bloques
• Expresiones
• Registros
• Tablas
• Tablas
• Conversiones de datos
• Variables y constantes
• Cursores
• Excepciones
• Estructuras de control

EN CASO DE ERROR

¿Qué hacer en caso de error?
?

Q é h

d

“La base de datos no funciona bien, no hace lo que

quiero”

Proceso de resolución de errores:

1. Revisar la tabla user_errors

2.
3.

i

1. Show errors
2. Select * from user_errors;
Si
?
¿Sintaxis correcta?
¿Problema con una consulta?
1. La consulta no devuelve resultados
2. Fuera del disparador la consulta funciona pero dentro no

Comenta y vencerás.

Fundamentos de las Bases de Datos

- 2

1

INTRODUCCIÓN (I)

PL/SQL: Lenguaje de programación procedimental
l

PL/SQL L
estructurado en bloques que amplía el lenguaje estándar
SQL.

j d

ió

di

Permite:

Manipular datos de una BD Oracle.
Usar técnicas procedurales (bucles, ...)
Controlar las filas de una consulta una a una
Controlar errores (excepciones) definidas por el usuario o propios de
Controlar errores (excepciones) definidas por el usuario o propios de
Oracle (predefinidos)

Disparadores

No diferencia las minúsculas de las mayúsculas

CLIENTE == cliente

Fundamentos de las Bases de Datos

- 3

BLOQUES

Es la unidad básica de cualquier programa PL/SQL.
Estructura básica de un bloque (sólo es obligatorio el

conjunto de sentencias ejecutables):
DECLARE

/* Declaraciones de uso local: variables, cursores, y excepciones de
usuario */

BEGIN

/* Proceso: conjunto de sentencias ejecutables */

EXCEPTION

/* Excepciones: zona de control de errores */

END;

Fundamentos de las Bases de Datos

- 4

2

BLOQUES (II)

Las únicas instrucciones permitidas dentro de un

bloque son: INSERT, UPDATE, DELETE Y
SELECT + manipulación de datos + control de
transacciones.

p

La anidación de bloques se realiza entre las

etiquetas BEGIN Y EXCEPTION y sólo se tiene
una definición de variables.
I
CREATE, ALTER, …

Instrucciones no permitidas son: DROP,
DROP

itid

Los comentarios se ponen: /* Comentarios */
No distingue mayúsculas y minúsculas

i

t

Fundamentos de las Bases de Datos

- 5

BLOQUES (III)

Tipos:

Anónimo: construido de manera dinámica y se ejecuta una vez.
Estructura básica se compilan cada vez que son ejecutados y no se

guardan en la BD.

Nominado: bloque anónimo con etiqueta. Lo primero es la

etiqueta. PE -> <<insertarEnTabla>>

Subprogramas: procedimientos, paquetes y funciones almacenadas

en la BD ya compiladas. Se ejecutan múltiples veces mediante
llamadas explícitas.

Remplazar la palabra clave DECLARE con las palabras CREATE OR

REPLACE PROCEDURE nombre_proc AS

Disparadores: bloques nominados almacenados en la BD (código
fuente). Se ejecutan múltiples veces de forma implícita mediante
eventos sobre una tabla (INSERT, UPDATE o DELETE). Se
compila cada vez que se ejecuta. (se explicarán más adelante)

Fundamentos de las Bases de Datos

- 6

3

EXPRESIONES

i

d

Ti
Tipos de expresiones:
Aritméticas : + - * /
Comparaciones : = != > > >= <=
Concatenación de caracteres ⏐ ⏐

Tipos de variables:

Escalares. Definidos por el lenguaje

NUMBER CHAR VARCHAR VARCHAR2 DATE
NUMBER, CHAR, VARCHAR, VARCHAR2, DATE,
BOOLEAN (TRUE, FALSE, NULL)

Compuestos: Definidos por el usuario

Registros
Tablas y matrices. Pueden almacenar registros y escalares

Fundamentos de las Bases de Datos

- 7

REGISTROS

Registros. Definición
D fi i ió

R i

TYPE tipo_reg IS RECORD (campo1tipo1[NOTNULL][:=expr ];...
campontipon[NOTNULL][:=expr ];);

Registro. Creación de variables

Nombre_variable tipo_reg
Referenciar las variables

Nombre_variable.campo:=valor

Ej
l
Ejemplo

DECLARE

TYPE tcliente IS RECORD (

DNI NUMBER (8,0);
Nombre VARCHAR (30);
FECHA DATE;);

RCLIENTE tcliente;

Fundamentos de las Bases de Datos

- 8

4

TABLAS

d fi i ió

Tablas definición.

T bl
TYPE tipo_tabla IS TABLE OF tipo
INDEX BY BINARY_INTEGER;

Tablas. Definición de variables: vcliente tipo_tabla;
Tablas. Referencia: Tabla(indice).campo;
Tablas. Ejemplo

DECLARE
TYPE ttabla IS TABLE OF cliente%ROWTYPE
TYPE ttabla IS TABLE OF cliente%ROWTYPE

INDEX BY BINARY_INTEGER;
v_cliente ttable;

BEGIN

SELECT * INTO v_cliente(2252480) FROM cliente WHERE DNI=22522480;

DBMS_OUTPUT.PUT_LINE (vcliente(2252480).nombre);
END;

Fundamentos de las Bases de Datos

- 9

Tablas. Atributos

Tablas. Atributos
Atributo

Tipo Devuelto Descripción

COUNT

NUMBER

Devuelve el número de filas de la tabla

DELETE (nº) N/A

Borra las filas de una tabla

EXITS (nº)

BOOLEAN

Devuelve TRUE si existe en la tabla la fila especificada

FIRST

LAST

NEXT

PRIOR

BYNARY_
INTEGER
BYNARY_
INTEGER
BYNARY_
INTEGER
BYNARY_
INTEGER

Devuelve el índice de la primera fila

Devuelve el índice de la última fila

Devuelve el índice de la fila de la tabla
que sigue a la fila especificada
Devuelve el índice de la fila de la tabla
que precede a la fila especificada

Fundamentos de las Bases de Datos

- 10

5

CONVERSIONES DE DATOS

Entre escalares se pueden convertir datos de distintas

p

familias excepto las propias restricciones de las variables
(Ej. CHAR (10) no puede en VARCAHAR2(1))

Función

Descripción

TO_CHAR

TO_DATE

Convierte el argumento en
VARCHAR2
Convierte su argumento en tipo DATE

tipo

TO_NUMBER

Convierte el argumento en
NUMBER

tipo

Fundamentos de las Bases de Datos

- 11

Declaración de variables y constantes

Variables:

Se utilizan para almacenar valores devueltos por una

consulta o para realizar cálculos intermedios
consulta o para realizar cálculos intermedios.

Constantes: Son campos definidos e inalterables
Pasos a seguir

Definición Nombre_campo: nombre de la variable
Declaración Nombre_campo tipo [CONSTANT][NOTNULL] [:=VALOR];
Asignación Variable Tipo. Un identificador es el nombre de la vble.
Tipo: puede ser:

Tipo de datos: tipo de dato de la variable
Identificador%TYPE Se refiere al tipo usado por una columna)
Identificador%ROWTYPE Es una fila vble con los mismos nombres y tipos que las

y p

q

columnaas de una tabla o fila recuperada de un cursor)

[CONSTANT] Palabra reservada para def. de ctes
[NOTNULL]: Obliga a tener valor
[:=VALOR] Asigna como valor inicial con un valor cte

Fundamentos de las Bases de Datos

- 12

6

CTES Y VBLES. EJEMPLO

DECLARE
DECLARE

DNI
Nombre
Factor
DNI2
Rcliente

precio

NUMBER (8,0);
VARCHAR (30);
CONSTANT NUMBER(3,2):=0.10;
cliente.DNI%TYPE;
cliente%ROWTYPE;
(tendría los campos: Rcliente DNI Rcliente Nombre
)
(tendría los campos: Rcliente.DNI, Rcliente.Nombre …)
NUMBER:= 300; (inicializa a un valor)

Fundamentos de las Bases de Datos

- 13

CURSORES

Definición:

Cursor es un área de trabajo definida para las consultas que

devuelven más de una fila.

Permite la manipulación de datos
Se le pueden pasar parámetros

Tipos de cursores

Cursor simple

CURSOR nombre_cursor IS sentencia SELECT;
Ejemplo

DECLARE
….
CURSOR c_cliente IS SELECT cliente.DNI, cliente.nombre FROM cliente

WHERE cliente.tipo_trabajo=´jefe´;

Fundamentos de las Bases de Datos

- 14

7

CURSORES

Cursores con paso de parámetros

CURSOR nombre cursor (nombre parametro tipo parametro)
CURSOR nombre_cursor (nombre_parametro tipo_parametro)

IS Sentencia SELECT utilizando los parámetros

EJEMPLO

CURSOR c_cliente (tipotrabajo VARCHAR2) IS SELECT cliente.DNI,

cliente.Nombre FROM cliente WHERE
cliente.tipo_trabajo=tipotrabajo;

Cursores con actualización

Se dejan preparados para modificar las filas devueltas y se

generan bloqueos exclusivos sobre las filas activas

SINTAXIS :CURSOR nombre_cursor IS sentencia SELECT

FOR UPDATE [OF nombre_columna]

Ejemplo

CURSOR c_cliente IS SELECT cliente.DNI, cliente.nombre FROM

cliente WHERE cliente.tipo_trabajo=´jefe´ FOR UPDATE OF
NOMBRE;

Fundamentos de las Bases de Datos

- 15

CURSORES:OPERACIONES

Operaciones con cursores:

OPEN: abre los cursores
FETCH : lee los datos
CLOSE: cierra los cursores

Pasos a seguir:

1.- Declarar el cursor en la zona DECLARE
2 Abrir el cursor en la zona de procedimiento
2.- Abrir el cursor en la zona de procedimiento
3.- Leer el cursor. Es necesario ejecutar un bucle para leer

todos los cursores
4.- Cerrar el cursor

Fundamentos de las Bases de Datos

- 16

8

CURSORES

Atributos de los cursores. Se le añaden al nombre del cursor.

No devuelven ningún tipo sino un valor que se pueda
emplear como parte de una expresión.

Descripción

Atributo
%NOTFOUND Devuelve TRUE si la ultima lectura falla
porque no hay filas disponibles y FALSE si
recupera. Se usaa para detectar el final
El contrario de %NOTFOUND

%FOUND
%ROWCOUNT Devuelve el número de fila leida
%ISOPEN

Devuelve TRUE si el cursor esta abierto y
FALSE si no lo está



Fundamentos de las Bases de Datos

- 17

EXCEPCIONES

Declaración de excepciones:

p

Se declaran por el usuario
Utilizan las funciones de error de SQL y PL/SQL
Sintaxis:

Ejemplo

p

Nombre_excepción EXCEPTION;
j
DECLARE
….
User_exception EXCEPTION;

Fundamentos de las Bases de Datos

- 18

9

ESTRUCTURAS DE CONTROL

Realizan el control del comportamiento del bloque. Son de

varios tip