Oracle PL SQL es un lenguaje de programación creado por Oracle como una extensión de SQL. Este lenguaje puede combinar las consultas SQL y las instrucciones de procedimientos (condicional y procesamiento iterativo) para crear un tratamiento complejo y ser almacenados en la base de datos.
Este tutorial explica paso a paso cómo escribir código en PL SQL.
1. Bloque PL/SQL.
Un bloque en PL SQL es una estructura compuesta por código que debe de cumplir una serie de premisas, a continuación vemos los diferentes elementos que lo componen:
-- En esta sección realizaremos la declaración de variables.
BEGIN
-- Código, es el cuerpo del programa.
EXCEPCION
-- Control de errores, esta sección es opcional.
END;
-- Fin del bloque
/
El caracter ‘/ ‘ valida la transacción al ejecutar el código.
Oracle 11g PL/SQL. Curso práctico de formación
Autor: Antolín Muñoz Chaparro
Fecha Edición: Marzo 2012
Idioma: 
Español
En la sección DECLARE, ponemos todas las declaraciones de variables, en BEGIN escribimos el código, en esta parte utilizaremos las variables de al sección DECLARE. La sección EXCEPCION se llama cuando se produce un error en este curso veremos cómo controlar los diferentes errores.
2. Las Variables.
Las variables que se utilicen deben ser declaradas en la sección DECLARE. Declaramos las variables y constantes en la sección DECLARE de la siguiente manera:
DECLARE
NombreVariable TipoDato;
2.1. Tipos de datos estándar.
Los diferentes tipos de datos estándar en Oracle SQL son los siguientes:
- NUMBER(p [,e]), números donde p es la precisión, e es opcional y representa la escala para decimales.
- VARCHAR2(limite), cadena de caracteres de longitud variable, limite representa el número máximo de caracteres.
- DATE, fecha, puede contener día, mes, año, hora, minutos y segundos.
Para ver todos los tipos de datos soportador por Oracle consultar el artículo 'Tipos de datos en Oracle 11g'
2.2. Tipos de datos de una tabla.
También se pueden realizar la declaración de variables usando como origen de la declaración una tabla de la base de datos, por ejemplo, si tenemos una tabla en nuestra base de datos llamada usuarios y con la columna nombre VARCHAR2(100), podemos realizar la declaración de una variable del mismo tipo que nombre de esta manera: Vlnombre usuarios.nombre%TYPE. De esta manera garantizamos la coherencia entre los datos de la tabla y el código PL SQL.
Podemos declarar también la estructura completa de una tabla con %ROWTYPE. La estructura de una tabla es el conjunto de columnas y sus correspondientes tipos, si queremos realizar una declaración para todas las columnas de la Personal lo haremos con la declaración Vlpersonal usuarios%ROWTYPE.
2.3 Tablas anidadas.
Una tabla anidada es un array que puede ser considerado como una tabla de una columna, este array tiene la ventaja que es dinámico.
Declaración:
TYPE nombre IS TABLE OF tipodato INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(limite)];
Funciones asociadas:
- Exist(x): devuelve true si el elemento x existe.
- COUNT(*): número de elementos.
- FIRST/LAST: índice del primer y del último elemento.
- DELETE: Borra el contenido.
- DELETE(x): elimina el elemento x.
- DELETE(x,y): elimina desde el elemento x a y.
2.4. Tipo registro RECORD.
El tipo registro puede crear nuevos tipos de datos que se basan en las jerarquías de tipos de datos básicos.
Declaración:
TYPE RecUsuarios IS RECORD (codigo NUMBER(3), nombre VARCHAR2(50);
Creación de una variable de tipo RecUsuarios:
MisUsuarios RecUsuarios;
2.5. Conversión de tipos.
Las funciones SQL de conversión de tipos son TO_CHAR(), TO_NUMBER(), TO_DATE(), etc.
2.6. Asignación de variables.
Para asignar un valor a una variable, utilizamos ':=', Por ejemplo:
DECLARE
nombre VARCHAR2(20);
BEGIN
nombre := 'Antonio';
END;
3. Uso de SQL en PL/SQL.
El lenguaje PLSQL no permite cambiar la estructura de la base de datos, es decir, no podemos utilizar instrucciones DDL como CREATE TABLE, ALTER TABLE, etc. Por el contrario, si podemos utilizar instrucciones DML como por ejemplo:
- COMMIT, confirma los cambios realizados desde el inicio de una transacción.
- ROLLBACK: cancelar todo lo que se ha hecho desde la última transacción.
- SELECT, INSERT, UPDATE, DELETE
La instrucción SELECT en SQL permite realizar consultas a la base de datos. La instrucción SELECT devuelve un solo valor en PL/SQL. Para guardar el resultado de la consulta en una variable se usa:
SELECT … INTO Variable FROM … WHERE …
4. Condicionales y bucles.
4.1 Condicional.
Estructuras condicionales:
IF condición THEN
instrucciones;
ELSIF condición THEN
instrucciones;
ELSE
instrucciones;
END IF;
4.2 Bucles.
Hay varios tipos de bucles:
El bucle FOR permite completar un número determinado de iteraciones,
FOR indice IN inf..sup LOOP
-- instrucciones;
END LOOP;
El bucle WHILE puede iterar hasta que una condición no se verifique,
WHILE condición LOOP
-- instrucciones;
END LOOP;
5. Cursores.
5.1 Definición.
Un cursor es una estructura de almacenamiento de información. Esta es un área de trabajo privado la base de datos. El cursor puede realizar operaciones básicas sobre la base de datos (INSERT, DELETE, etc.).
También puede crear cursores para manejar consultas (Querys) que devuelvan más de una línea.
5.2 Funciones asociadas.
- %OPEN: abre el cursor y ejecuta la consulta.
- %CLOSE: Cierra el cursor.
- %FETCH: recorre el cursor línea a línea.
- %FOUND: devuelve true si se encuentra al final del cursor.
- %ISOPEN: devuelve true si el cursor está abierto.
- %ROWCOUNT: número de líneas que contiene el cursor.
5.3 Utilización.
En el ejemplo siguiente se utiliza para almacenar los valores de una tabla.
SET SERVEROUTPUT ON;
DECLARE
--Declaración del cursor
CURSOR NombreCursor IS SELECT * FROM NombreTabla;
--Declaración de una variable de tipo ROWTYPE
VLinea NiombreTable%ROWTYPE;
BEGIN
--Abrimos el cursor
OPEN NombreCursor;
--Cargamos el primer elemento del cursor en VLinea
FETCH NombreCursor INTO VLinea;
--Recorremos el cursor hasta llegar al final
WHILE NombreCursor%FOUND LOOP
--Imprimimos por pantalla
DBMS_OUTPUT.PUT_LINE(VLinea.attribut);
--Cargamos la siguiente línea
FETCH NombreCursor INTO VLinea;
END LOOP ;
--Cerramos el cursor
CLOSE NombreCursor;
END ;
/
El guión medio doble ‘--‘ se utiliza para incluir comentarios en el código.
6. Gestión de errores.
Las excepciones se generan durante la ejecución. Pueden ser desencadenados por Oracle o en el bloque de PL/SQL. Las excepciones son procesadas al final de PL/SQL. Si no son tratadas, se propaga al bloque de la llamada.
6.1. Controlar las excepciones.
Para controlar una excepción, la sección EXCEPTION es la encarga de carturar y procesar las excepciones cuando se produce un error.
DECLARE
--Declaraciones
BEGIN
--Instrucciones
EXCEPTION
--Interceptamos las excepciones
WHEN NombreExcepcion THEN
Instruciones a ejecutar;
WHEN OTHERS THEN
Instruciones a ejecutar;
END ;
/
Principales excepciones:
NO_DATA_FOUND: si un SELECT no devuelve ninguna fila.
TOO_MANY_ROWS: si un SELECT devuelve más de una fila.
INVALID_CURSOR: operación no válida en un cursor.
ZERO_DIVIDE: división por 0
DUP_VAL_ON_INDEX: en un INSERT se intenta insertar una fila que en una tabla que tiene un índice unico.
6.2. Excepciones personalizadas.
En la sección DECLARE declaramos una excepción junto con un número de error Oracle. En el código podemos generar la excepción con un RAISE NombreExcepcion.
DECALRE
--Declaración de la excepción
NombreExcepcion EXCEPTION
--Asociación de un número a la excepción (opcional)
PRAGMA EXCEPTION_INIT(NombreExcepcion, 1)
BEGIN
--Generamos la excepción
RAISE NombreExcepcion;
END ;
/
Para obtener información acerca de las excepciones generadas consultar, SQLCODE devuelve el error ORACLE y SQLERRM devuelve el texto del error.
7. Procedimientos y funciones.
PL/SQL permite crear procedimientos y funciones.
7.1. Sintaxis.
- Para visualizar los posibles errores.
SHOW DE ERROR;
- Creación de una función.
CREATE OR REPLACE FUNCTION NombreFuncion(param1, param2, ….) IS
--Declaración de las variables locales (remplaza a DECLARE)
BEGIN
-- Cuerpo de la función
RETURN valor
END;
7.2. Compilación.
Las funciones y procedimientos se compilan de forma automática. Se puede volver a compilar escribiendo:
ALTER FUNCTION NombreFuncion COMPILE;
Para eliminar una función, escriba:
DROP FUNCTION NombreFuncion;
8. Paquetes.
Los paquetes son una colección encapsulada de procedimientos, funciones y otros objetos guardados en la base de datos.
9. Triggers (Disparadores).
Los Triggers pueden ejecutar uno o más procedimientos después de un evento, por ejemplo, insert de de datos, borrado, actualización, etc. Un Trigger se aplica a una tabla base de datos. Un Trigger está asociado con uno o más procedimientos y explicarse si que fueron hechas antes o después del evento o el lugar de acción.
Sintaxis:
- Crear TRIGGER
CREATE TRIGGER NombreTrigger
-- Se especifica cuándo y con qué acciones queremos ejecutar
BEFORE INSERT OR UPDATE OF NombreVariable
-- Se especifica el nombre de la tabla en cuestión
ON NombreTabla
FOR EACH ROW
BEGIN
--Instrucciones
END;
Cuando se utilizan triggers, es necesario hacer referencia a los valores de la tabla nuevos o antiguos, para diferenciarlos, será precedida por la palabra clave NEW o OLD. Por ejemplo, si el cambio resulta en una modificación del atributo de "atributo", puede ser IF: NEW.atributo <>:OLD.atributo.
10. Objetos y tipos de datos especiales en la base de datos.
Los objetos y los tipos de datos especiales se han creado para hacer frente a los problemas planteados por las bases de datos relacionales para el almacenamiento de datos de gran tamaño como ficheros, imágenes, videos, etc.
10.1. Tipos de datos.
Los datos pueden tener un tamaño atómico (NUMBER, CHAR, etc.), pero también puede ser colecciones, referencias, objetos definidos por el usuario.
10.1.1 Tipos de datos LOB internos.
Los tipos de datos LOB (Large OBject) internos son almacenados en la base datos y pueden manejar grandes volúmenes de información como fotos, vídeos, etc. de hasta 4 GB de tamaño. Hay 3 tipos de de datos LOB internos:
- BLOB: datos binarios no estructurados.
- CLOB: datos de tipo carácter para grandes volúmenes.
- NCLOB: los datos de caracteres mediante un conjunto de caracteres Unicode.
Estos tipos se utilizan como los tipos normales. Por ejemplo, para crear una tabla que contiene una columna que guardará fotos:
CREATE TABLE mis_fotos (
CodPhoto NUMBER(3) PRIMARY KEY,
NombreFoto VARCHAR2(20),
Foto BLOB )
;
Se puede insertar un valor vacío en una columna de tipo LOB utilizando EMPTY_BLOB() ejemplo:
INSERT INTO mis_fotos VALUES(1, 'Foto1', EMPTY_BLOB();
El tipo de valor vacío es diferente al de NULL.
10.1.2. El tipos de datos LOB externos.
El tipo de dato BFILE guarda un puntero a un archivo en el sistema operativo y es de solo lectura. Por ejemplo,
CREATE TABLE mis_fotos (
CodPhoto NUMBER(3) PRIMARY KEY,
nomPhoto VARCHAR2(20),
NombreFoto BFILE)
;
Para insertar una nueva línea utilizaremos la función BFILENAME():
INSERT INTO mis_fotos VALUES(2, 'Foto2', BFILENAME('/home/user/Images', 'imagen.jpg'));
10.2. Objetos.
10.2.1. Declaración de un objeto.
Ejemplo de creación del objeto punto:
-- AS OBJECT significa que el TYPE es un objeto
CREATE TYPE punto AS OBJECT(
-- Definimos los atributos de uno a uno
x NUMBER(3),
y NUMBER(3)
);
10.2.2. Manejar objetos en las tablas.
Los objetos se utilizan como TYPES, hemos creado la tabla de la siguiente manera:
CREATE TABLE puntos (
CodPoint NUMBER(3),
NombrePunto VARCHAR2(1),
punto PUNTO
);
Para eliminar un TYPE: DROP TYPE PUNTO FORCE;
La opción FORCE es opcional y elimina el TYPE aunque este en uso, sin embargo, los objetos que utilizan el TYPE borrado se convierten en inaccesibles.
