PL/SQL

Procedural Language/Structured Query Language

Introducción
SQL es un lenguaje de consulta para los sistemas de bases de datos
relaciónales, pero que no posee la potencia de los lenguajes de
programación.
Select – Insert – Update – Delete
PL/SQL amplia SQL con los elementos de un lenguaje de
programación, soporta todas las consultas ya que la manipulación de
datos que se usa es la misma que en SQL, incluyendo nuevas
características:
• El manejo de variables.
• Estructuras modulares.
• Estructuras de control de flujo y repeticiones.
PL/SQL - Características
• es un lenguaje de programación que proporciona Oracle
• extiende al SQL estándar con otro tipo de instrucciones
• se pueden construir bloques PL/SQL para utilizarlos
como procedimientos o funciones
• se pueden almacenar en la base de datos como otro objeto
• los usuarios que estén autorizados tienen acceso a estos
paquetes
• se ejecutan en el servidor para ahorrar recursos a los
clientes
Utilidades:
Con PL/SQL vamos a poder programar las unidades de programa de la
base de datos ORACLE:
• Procedimientos almacenados
• Funciones
• Triggers
• Scripts
Pero además PL/SQL nos permite realizar programas sobre las
siguientes herramientas de ORACLE:
• Oracle Forms
• Oracle Reports
• Oracle Graphics
Tipo de Datos
Tipo de dato Descripción
number(p, e) La precisión máxima es de 38 dígitos. Number(7,2). Ej . saldo
NUMBER(16,2);

char (tamaño) Donde tamaño es el número de caracteres a almacenar. Son

cadenas de ancho fijo. Se rellena con espacios. Ej. nombre
CHAR(20);

varchar2 (tamaño) Donde tamaño es el número de caracteres a almacenar. Son

cadenas de ancho variable. Ej. nombre VARCHAR2(20);

date Una fecha entre el 1 de Enero de 4712 A.C. y el 31 de Diciembre de

9999 D.C.

boolean Se emplea para almacenar valores TRUE o FALSE. Ej. hay_error

BOOLEAN;
 Atributo de Tipo:
Un atributo de tipo PL/SQL es un modificador que puede ser
usado para obtener información de un objeto de la base de
datos. El atributo %TYPE permite conocer el tipo de una
variable, constante o campo de la base de datos. El atributo
%ROWTYPE permite obtener los tipos de todos los campos de
una tabla de la base de datos, de una vista o de un cursor. Ej.:

• V_nombre [Link]%TYPE;
• Reg_datos Tabla%ROWTYPE;
Identificadores
• nombre que se le pone a un objeto que interviene en un programa
• pueden ser variables, constantes, procedimientos, excepciones,
cursores
• el nombre debe tener un máximo de 30 caracteres que empiece
siempre con una letra y puede contener letras, números, los
símbolos $, #, _, mayúsculas y minúsculas indiferentemente
• los identificadores no pueden ser palabras reservadas (SELECT,
INSERT, DELETE, UPDATE, DROP)
 DBMS_OUTPUT
DBMS_OUTPUT es un depurador de Oracle que sirve para visualizar cualquier
cosa, pero antes lo debemos tener activado (en SQL*plus, se activa con el comando
SET SERVEROUTPUT ON):
BEGIN
DBMS_OUTPUT.PUT_LINE (‘Hola’);
END;

declare
a number;
j number;
begin
a:=2;
j:=5;
dbms_output.put_line(a);
dbms_output.put_line(j);
 Operadores en PL/SQL
• asignación “:=”
• aritméticos “+, -, *, /, **(exponente 3**2, 5**3)”
• comparación “=, <>, >, <, >=, <= ”
• lógicos “ AND, OR, NOT(negación)”
• concatenación “||”
DECLARE
nom_emple VARCHAR2(15) DEFAULT 'Pedro';
edad number;
ban boolean;
BEGIN
DBMS_OUTPUT.PUT_LINE ('nombre: '||nom_emple);
nom_emple:='Maria';
DBMS_OUTPUT.PUT_LINE (nom_emple);
edad:=23;
edad:=edad**2;
ban:= edad>30;
DBMS_OUTPUT.PUT_LINE (edad);
DBMS_OUTPUT.PUT_LINE (edad+8);
END;
Estructura Condicional IF
DECLARE
IF (expresion) THEN
a NUMBER;
-- Instrucciones
b NUMBER;
ELSIF (expresion) THEN BEGIN
-- Instrucciones a:=10;
ELSE b:=10;
-- Instrucciones IF (a>b) THEN
END IF; dbms_output.put_line('mayor a');
elsif (a=b) then
Un aspecto a tener en cuenta dbms_output.put_line('a es igual a b');
es que la instrucción else
condicional anidada es ELSIF dbms_output.put_line('mayor b');
y no "ELSEIF". END IF;
END;
Sentencia GOTO
• Desvía el flujo de ejecución a una determinada etiqueta.
• En PL/SQL las etiquetas se indican del siguiente modo: << etiqueta >>
DECLARE
flag NUMBER;
BEGIN
flag :=1 ;
IF (flag = 1) THEN
GOTO paso2;
END IF;
<<paso1>>
dbms_output.put_line('Ejecucion de paso 1');
<<paso2>>
dbms_output.put_line('Ejecucion de paso 2');
Bucles
En PL/SQL tenemos a nuestra disposición las siguientes estructuras
repetitivas o bucles:
LOOP WHILE FOR
 El bucle LOOP, se repite tantas veces como sea necesario hasta que
se fuerza su salida con la instrucción EXIT.
LOOP
-- Instrucciones
IF (expresion) THEN
-- Instrucciones
EXIT;
END IF;
END LOOP;
 El bucle WHILE, se repite mientras que se cumpla una expresión.

WHILE (expresion) LOOP

-- Instrucciones
END LOOP;

 El bucle FOR, se repite tanta veces como le indiquemos en los

identificadores inicio y final.

FOR contador IN [REVERSE] inicio..final LOOP

-- Instrucciones
END LOOP;
En el caso de especificar REVERSE el bucle se recorre en sentido inverso.
Bloques PL/SQL
• Un programa de PL/SQL está compuesto por bloques.
• Un programa está compuesto como mínimo de un bloque.
• Los bloques pueden ser: Bloques anónimos o Subprogramas
• Los bloques anónimos no los identifica con ningún nombre.
• La estructura de los bloques PL/SQL esta formada por tres partes bien
diferenciadas:
1. La sección declarativa en donde se declaran todas las constantes y
variables que se van a utilizar en la ejecución del bloque.
2. La sección de ejecución que incluye las instrucciones a ejecutar en el
bloque PL/SQL.
3. La sección de excepciones en donde se definen los manejadores de
errores que soportará el bloque PL/SQL.
Estructura de un bloque en pl/sql
DECLARE
/*Parte declarativa*/
nombre_variable DATE;
BEGIN
/*Parte de ejecución
* Este código asigna el valor de la columna "nombre_columna
* a la variable identificada por "nombre_variable"
*/
SELECT SYSDATE
INTO nombre_variable
FROM DUAL;

EXCEPTION
/*Parte de excepciones*/
WHEN OTHERS THEN
dbms_output.put_line('Se ha producido un error');
 Declaración de Variables
• En esta parte se declaran las variables que va a necesitar nuestro programa.
• Una variable se declara asignándole un nombre o "identificador" seguido del
tipo de valor que puede contener.
• Podemos especificar si se trata de una constante, si puede contener valor nulo
y asignar un valor inicial.
La sintaxis genérica para la declaración de constantes y variables es:

nombre_variable [CONSTANT] <tipo_dato> [NOT NULL][:=valor_inicial]

ESTRUCTURA DE UN BLOQUE ANÓNIMO

DECLARE
v_location VARCHAR2(15) := ‘Granada’;
PI CONSTANT NUMBER := 3.1416;
v_nombre tabla_empleados.nombre%TYPE;
reg_datos micursor%ROWTYPE;
BEGIN
/*Parte de ejecución*/
EXCEPTION
/*Parte de excepciones*/
END;
ESTRUCTURA DE UN SUBPROGRAMA:

CREATE PROCEDURE simple_procedure IS

v_location VARCHAR2(15) := ’Granada’;
PI CONSTANT NUMBER := 3.1416;
v_nombre tabla_empleados.nombre%TYPE;
reg_datos micursor%ROWTYPE;
BEGIN
/*Parte de ejecucion*/
EXCEPTION
/*Parte de excepciones*/
END;
 Cursores en PL/SQL
• PL/SQL utiliza cursores para gestionar las instrucciones SELECT.
• Un cursor es un conjunto de registros devuelto por una instrucción SQL.
• Los cursores son fragmentos de memoria reservados para procesar los
resultados de una consulta SELECT.
IMPLICITOS: se utiliza para operaciones SELECT
INTO. Se usan cuando la consulta devuelve un único registro.

CURSORES
EXPLICITOS: Son los cursores que son declarados y
controlados por el programador. Se utilizan cuando la
consulta devuelve un conjunto de registros. Pueden ser
utilizados en consultas que devuelven un solo registro.
Definición de un Cursor:
Un cursor se define como cualquier otra variable de PL/SQL y debe
nombrarse de acuerdo a los mismos convenios que cualquier otra variable.
Los cursores implícitos no necesitan declaración.

Declaración de un cursor explicito:

declare
cursor c_paises is
SELECT CO_PAIS, DESCRIPCION
FROM PAISES;
begin
/* Sentencias del bloque ...*/
end;
Parámetros en un Cursor:
Un cursor admite el uso de parámetros. Los parámetros deben declararse junto
con el cursor.
El siguiente ejemplo muestra la declaración de un cursor con un parámetro,
identificado por p_continente.
declare
cursor c_paises (p_continente IN VARCHAR2) is
SELECT CO_PAIS, DESCRIPCION
FROM PAISES
WHERE CONTINENTE = p_continente;
begin
/* Sentencias del bloque ...*/
end;
Declaración de un cursor implícito:
• Con cada cursor implicito debe existir la palabra clave INTO.
• Las variables que reciben los datos devueltos por el cursor tienen que contener el
mismo tipo de dato que las columnas de la tabla.
• Los cursores implícitos solo pueden devolver una única fila. En caso de que se
devuelva más de una fila (o ninguna fila) se producirá una excepción.

declare
vdescripcion VARCHAR2(50);
begin
SELECT DESCRIPCION INTO vdescripcion
from PAISES
WHERE CO_PAIS = 'ESP';
dbms_output.put_line('La lectura del cursor es: ' || vdescripcion);
end;
 Cursores Explicitos
Los cursores explícitos se emplean para realizar consultas SELECT que pueden
devolver cero filas, o más de una fila.
• Declarar el cursor con IS.
• Abrir el cursor con la instrucción OPEN.
• Leer los datos del cursor con la instrucción FETCH.
• Cerrar el cursor y liberar los recursos con la instrucción CLOSE.
 CURSOR nombre_cursor IS
instrucción_SELECT
 Con parámetros:
CURSOR nombre_cursor(param1 tipo1, ..., paramN tipoN) IS
instrucción_SELECT
Para abrir el cursor:
 OPEN nombre_cursor;
o bien (en el caso de un cursor con parámetros)
 OPEN nombre_cursor(valor1, valor2, ..., valorN);

Para recuperar los datos en variables :

 FETCH nombre_cursor INTO lista_variables;
-- o bien ...
 FETCH nombre_cursor INTO registro_PL/SQL;

Para cerrar el cursor:

 CLOSE nombre_cursor;
Ejemplo:

DECLARE
CURSOR cpaises
IS
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES;
co_pais VARCHAR2(3);
descripcion VARCHAR2(50);
continente VARCHAR2(25);
BEGIN
OPEN cpaises;
FETCH cpaises INTO co_pais,descripcion,continente;
CLOSE cpaises;
END;
Podemos simplificar el ejemplo utilizando el atributo de tipo %ROWTYPE
sobre el cursor:

DECLARE
CURSOR cpaises
IS
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES;
registro cpaises%ROWTYPE;
BEGIN
OPEN cpaises;
FETCH cpaises INTO registro;
CLOSE cpaises;
END;
El mismo ejemplo, pero utilizando parámetros:

DECLARE
CURSOR cpaises (p_continente VARCHAR2)
IS
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES
WHERE CONTINENTE = p_continente;
registro cpaises%ROWTYPE;
BEGIN
OPEN cpaises('EUROPA');
FETCH cpaises INTO registro;
CLOSE cpaises;
END;