La programación

Sintaxis general

Un programa se puede descomponer en tres partes:

La parte declarativa permite declarar e inicializar todas las variables utilizadas en la parte de tratamiento. En un programa PL/SQL, se pueden utilizar los tipos Oracle para las variables, así como crear sus propios tipos.

La parte de gestión de errores permite indicar las instrucciones que se ejecutarán cuando se encuentre un error en el proceso.

Estas dos secciones (declarativa y errores) son opcionales.

La sintaxis de un programa es la siguiente:

[DECLARE 
...] 
BEGIN 
... 
... 
[EXCEPTION 
...] 
END; 

Ejemplo de script SQL Server para ejecutar

DECLARE @Hotel int 
BEGIN 
SET @Hotel = 2 
SELECT NumHabitacion, Descripcion 
FROM Habitaciones INNER JOIN TipoHabitacion ON TipoHabitacion.idTipoHabitacion = 
Habitaciones.TipoHabitacion 
WHERE Hotel = @Hotel; 
END; 

Resultado

Por ejemplo, seleccione la etiqueta, el número de estrellas...

Los cursores

Un cursor es un elemento que permite almacenar una consulta que devuelva varias filas. Esto le permite navegar por cada línea para utilizarlas.

Hay que declararlo en la sección declarativa.

Hay que abrirlo con un OPEN, ejecutarlo con un FETCH y cerrarlo con un CLOSE.

En el ejemplo, el tipo de cama buscado se pasa como parámetro al cursor: CURSOR C_habitaciones_por_tipo_cama(TipoCama IN VARCHAR2) IS.

TipoCama se indica en el OPEN CURSOR con la variable que contiene la descripción del tipo de cama: OPEN C_habitaciones_por_tipo_cama(TipoCama _buscada). 

Ejemplo con la misma consulta anterior:

DECLARE 
-- Declaración del cursor C_habitaciones_por_tipo_cama 
CURSOR C_habitaciones_por_tipo_cama (TpCama in varchar2) IS 
SELECT Hoteles.Etiqueta, Habitaciones.NumHabitacion, 
TiposHabitacion.NumeroCama, 
TiposHabitacion.Descripcion 
FROM Habitaciones INNER JOIN 
Hoteles ON Habitaciones.Hotel = Hoteles.idHotel INNER JOIN 
TiposHabitacion ON Habitaciones.TipoHabitacion = 
TiposHabitacion.idTipoHabitacion 
WHERE TipoCama = tipocama and Estrella = '**'; 
 
-- Declaración de las variables receptoras 
Etiqueta_hotel varchar2(50); 
Num_Habitacion varchar2(6); 
NumCama number(38,0); 
Descripcion varchar2(255); 
 
-- Declaración de las otras variables 
TpCama_buscada varchar2(20) := 'cama...

El control del flujo

DECLARE  
-- Declaración del cursor C_habitaciones_por_tipo_cama 
CURSOR C_habitaciones_por_tipo_cama (TpCama in varchar2) IS 
SELECT Habitaciones.idHabitacion, Hoteles.Etiqueta, 
Habitaciones.NumHabitacion, TiposHabitacion.NumeroCama, 
TiposHabitacion.Descripcion 
FROM Habitaciones INNER JOIN 
Hoteles ON Habitaciones.Hotel = Hoteles.idHotel INNER JOIN 
TiposHabitacion ON Habitaciones.TipoHabitacion = 
TiposHabitacion.idTipoHabitacion 
WHERE TipoCama = tipocama and Estrella = '**'; 
 
-- Declaración de las variables receptoras 
id_habitacion number :=0; 
Etiqueta_hotel varchar2(50); 
Num_Habitacion varchar2(6); 
NumCama number(38,0); 
Descripcion varchar2(255); 
 
-- Declaración de las otras variables 
TpCama_buscada varchar2(20) := 'cama individual'; 
 
BEGIN 
-- Apertura 
OPEN C_habitaciones_por_tipo_cama(tipocama_buscada); 
-- Lectura del primer elemento 
FETCH C_habitaciones_por_tipo_cama 
INTO id_habitacion, Etiqueta_hotel, Num_Habitacion, NumCama, 
descripcion; 
-- Bucle de lectura mientras que el identificador de la habitacion 
es < 10 
WHILE id_habitacion < 10 
LOOP 
-- Visualización de los elementos recuperados 
DBMS_OUTPUT.PUT_LINE('Id Habitación: '||id_habitacion); 
DBMS_OUTPUT.PUT_LINE('Nombre del hotel: '||Etiqueta_hotel); 
DBMS_OUTPUT.PUT_LINE('Número de habitaciones: 
'||num_habitacion);  
DBMS_OUTPUT.PUT_LINE('Número de camas: '||numcama); 
DBMS_OUTPUT.PUT_LINE('Tipo de cama: '||tipocama_buscada); 
DBMS_OUTPUT.PUT_LINE('Descripción: '||descripcion); 
-- Lectura del elemento siguiente 
FETCH C_habitaciones_por_tipo_cama 
INTO id_habitacion, Etiqueta_hotel, Num_Habitacion, NumCama, 
descripcion; 
EXIT WHEN C_habitaciones_por_tipo_cama%NOTFOUND; 
END LOOP; 
-- Cierre del cursor (liberación de memoria) 
CLOSE C_habitaciones_por_tipo_cama; 
END; 

Id Habitación: 8 
Nombre...

La gestión de los errores en Transact SQL

La gestión de los errores permite anticiparse a los problemas que pueden aparecer durante la ejecución de un programa.

El principio consiste en probar el código en un primer bloque con BEGIN TRY … END TRY y después interceptar la excepción con BEGIN CATCH …. END CATCH.

Sintaxis

BEGIN 
 
... ... 
 
[BEGIN TRY 
... ... 
END TRY] 
[BEGIN CATCH 
... ... 
END CATCH] 
END; 

Ejemplo

DECLARE @i int 
BEGIN 
BEGIN TRY 
SET @i = 2 
SET @i = @i / 0 
END TRY 
BEGIN CATCH 
SELECT ERROR_NUMBER() AS ErrorNumber 
    , ERROR_MESSAGE() AS ErrorMessage 
    , ERROR_LINE() AS ErrorLine;END CATCH 
END; 

Resultado

También es posible tratar la excepción.

DECLARE @i int 
BEGIN 
BEGIN TRY 
SET @i = 2 
SET @i = @i / 0 
END TRY 
BEGIN CATCH 
    IF @@ERROR = 8134 
        SET @i = @i / 1 
print @i 
END CATCH 
END; 

Resultado

2

Es posible devolver un error gracias a la función RAISERROR(). Esta función acepta tres argumentos (constante o mensaje o variable, número de gravedad del error y estado del error).

Ejemplo

DECLARE @i int 
DECLARE @f float ...

Creación de un procedimiento almacenado

Cuando se quiere compartir un trozo de código realizado en PL/SQL, se puede grabar en la base de datos y así el resto de programadores pueden acceder a él. Un procedimiento almacenado es un bloque de código compilado y almacenado en la base de datos. Basta con llamarlo por su nombre para ejecutarlo.

La principal ventaja del procedimiento almacenado, es que está guardado en un formato « ejecutable », el servidor de la base de datos no va a interpretar los comandos sino que los ejecuta directamente, con la ganancia de tiempo considerable respecto a la ejecución de la misma consulta desde un programa.

Otra ventaja del procedimiento almacenado es que se le pueden pasar parámetros. 

Sintaxis Oracle

CREATE OR REPLACE PROCEDURE <nombre procedimiento> 
 [(<variable entrada 1> IN <formato>, 
   <variable entrada 2> IN <formato>, 
   ... ... 
   <variable salida> OUT <formato>)] 
 IS 
 
BEGIN 
 
... ... 
 
[EXCEPTION 
... ... 
] 
END; 

Sintaxis SQL Server

CREATE OR ALTER PROCEDURE <nombre procedimiento> 
 [(@<variable 1> <formato>, 
   @<variable 2> <formato>, 
   ... ... )] 
 AS 
 
BEGIN 
 
... ... 
 ...

Creación de una función almacenada

En el mismo ejemplo, también es posible crear una función en lugar de un procedimiento. ¿Cuál es la diferencia entre una función y un procedimiento? Es que la primera devuelve un valor. Es posible incluirlo en una nueva consulta.

Sintaxis Oracle

CREATE OR ALTER FUNCTION <nombre función> 
 [(<variable entrada 1> IN <formato>, 
   <variable entrada 2> IN <formato>, 
   ... ... ] 
   RETURN <formato> 
 IS 
<variable salida> <formato>)] 
BEGIN 
 
... ... 
 
[EXCEPTION 
... ... 
] 
END; 

Sintaxis SQL Server

CREATE OR ALTER FUNCTION <nombre función> 
 [(@<variable 1> <formato>, 
  @<variable 2> <formato>, 
   ... ... )] 
  RETURNS <formato> 
 AS 
 
BEGIN 
 
... ... 
 
END; 

Por ejemplo, la siguiente función PL/SQL devuelve el precio de la habitación a partir, del nombre del hotel, del tipo y la cantidad de camas y de una fecha.

CREATE OR REPLACE FUNCTION PRECIO_HABITACION 
(vhotel IN VARCHAR2, vtipocama IN VARCHAR2, inumcama IN INT, dfecha IN DATE) 
RETURN NUMBER 
IS 
DPrecio NUMBER; 
BEGIN 
SELECT Precio INTO dPrecio FROM Tarifas t 
INNER...

Los packages

La denominación «package» significa que se agrupan bajo un mismo nombre los procedimientos y funciones sobre el mismo tema, y así podemos crear verdaderas aplicaciones.

En un paquete, podemos tener declaraciones de variables públicas o privadas, así como funciones y procedimientos privados que no se pueden ver desde fuera. 

En un paquete, hay que crear una zona de declaraciones y una zona donde están las funciones y los procedimientos.

En la zona de declaraciones, se listarán los procedimientos y funciones que se describen en la otra zona. Todas las funciones que estén declaradas aquí serán «públicas». Las variables funcionan del mismo modo, si están en la zona de declaraciones, son «públicas».

Los packages no existen para SQL Server.

Sintaxis

CREATE OR REPLACE PACKAGE <nombre paquete> IS 
      PROCEDURE <nombre procedimiento 1>; 
      FUNCTION <nombre función 1> (<variable 1> IN <formato>) RETURN 
<formato>; END; 
/ 
CREATE OR REPLACE PACKAGE BODY <nombre paquete> IS 
 
   FUNCTION <función 1> 
    ... ... 
   END; 
 
   PROCEDURE <procedimiento 1> IS 
     ......

Los triggers

Un trigger permite lanzar comandos que se van a ejecutar después de cada evento producido en una tabla.

El contenido del código ejecutado por un trigger normalmente es PL/SQL o C o Java.

Los triggers normalmente se utilizan para gestionar toda la funcionalidad de una aplicación. Permiten realizar controles sobre el contenido de las tablas automáticamente. Los triggers también pueden servir para recuperar información a lo largo de un día completo sobre las actividades de la base de datos para, a continuación, ser tratados esos datos por otra aplicación.

En general, se identifican los controles en los programas aplicativos ejecutados en la parte cliente. Los triggers permiten añadir otros controles que se ejecutarán  en la parte del servidor.

La primera ventaja del trigger es que está relacionado con una acción de la base de datos (INSERT, UPDATE, DELETE), por lo que no se corre el riesgo de olvidarse de modificar un programa. De hecho, normalmente es complicado modificar todos los programas de una aplicación para agregar un control asociado a un INSERT, por ejemplo. Sería necesario encontrar todos los programas afectados, modificarlos y probar cada uno de los programas modificados.

El trigger se desencadena de manera sistemática, por lo que no se puede olvidar una actualización y la modificación se hace independientemente de los programas aplicativos.

Un trigger puede desencadenarse antes o después de la sentencia SQL solicitada. Se indica con un AFTER o BEFORE. En SQL Server, se ejecuta antes o en lugar de (INSTEAD OFF) pero no antes.

En un trigger BEFORE, se puede controlar, antes de cualquier modificación, algunos elementos de la base de datos e impedir de esta manera las actualizaciones.

En un trigger AFTER, la actualización ya ha tenido lugar y se desencadenan las acciones que resultan de ella.

Sintaxis general de un trigger PL/SQL

CREATE OR REPLACE TRIGGER <nombre del trigger> 
[AFTER] [INSERT o DELETE o UPDATE] 
ON <nombre de tabla> 
[FOR EACH ROW] 
[WHEN] 
 
DECLARE 
... ... 
BEGIN 
... ... 
 
END; 

La cláusula FOR EACH ROW significa que el trigger actúa en todas las líneas afectadas por la sentencia SQL.

La cláusula WHEN permite añadir un criterio adicional...

Soluciones a los ejercicios

Primer ejercicio

Sintaxis Oracle 
CREATE OR REPLACE FUNCTION CALCUL_EDAD_ACTOR 
(FECHA_NACIMIENTO IN DATE) RETURN NUMBER 
IS 
EDAD_ACTOR NUMBER(5); 
 
BEGIN 
 
SELECT (SYSDATE - FECHA_NACIMIENTO)/365 INTO EDAD_ACTOR FROM DUAL; 
RETURN  (EDAD_ACTOR); 
 
EXCEPTION 
  WHEN OTHERS THEN 
     DBMS_OUTPUT.PUT_LINE( ' El número de error es: '|| 
TO_CHAR( SQLCODE )) ; 
     DBMS_OUTPUT.PUT_LINE( 'correspondiente a: '|| 
TO_CHAR( SQLERRM )) ; 
END; 
SELECT CALCUL_EDAD_ACTOR(FECHA_NACIMIENTO) FROM ACTOR; 
 
Sintaxis SQL Server 
CREATE OR ALTER FUNCTION CALCUL_EDAD_ACTOR (@fechaNacimiento fecha) 
RETURNS integer 
AS 
BEGIN 
DECLARE @Edad integer 
SELECT @Edad= DATEDIFF(YEAR, @fechaNaciemiento, GETDATE()); 
RETURN @Edad 
END; 
 
SELECT dbo.CALCUL_EDAD_ACTOR (FECHA_NACIMIENTO) FROM ACTOR; 
 
Sintaxis MySQL 
CREATE FUNCTION CALCUL_EDAD_ACTOR (fechaNacimiento DATE) 
RETURNS INT DETERMINISTIC 
BEGIN 
DECLARE vEdad INT; 
SELECT ROUND(DATEDIFF(CURRENT_DATE, fechaNacimiento) / 365) 
INTO vEdad FROM DUAL; 
RETURN vEdad; 
END 
 
SELECT CALCUL_EDAD_ACTOR(FECHA_NACIMIENTO) FROM ACTOR; 
 
Syntaxe PostgreSQL 
CREATE OR REPLACE FUNCTION public."CALCUL_EDAD_ACTOR" ...