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...

Los cursores

Un cursor es un elemento que permite almacenar una consulta que devuelva varias filas.

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;  ...

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...

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 

... ... 

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

Por ejemplo, el siguiente procedimiento muestra la lista de las habitaciones...

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? Que la primera devuelve un valor.

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 devuelve el precio de la habitación a partir de una fecha, del nombre del hotel, del tipo y de la cantidad de camas.

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 JOIN Hoteles h ON t.hotel = h.idHotel  
INNER JOIN TiposHabitacion th ON th.idTipoHabitacion...

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).

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>   
[BEFORE o 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...

Soluciones a los ejercicios

Primer ejercicio

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 del error es: '||   
TO_CHAR( SQLCODE )) ;   
    DBMS_OUTPUT.PUT_LINE( 'se corresponde con: '||   
TO_CHAR( SQLERRM )) ;   
END; 

Segundo ejercicio

CREATE PROCEDURE LISTA_PELICULAS IS   
  
  -- Declaración del cursor C_PELICULAS   
  
  CURSOR C_PELICULAS IS   
  SELECT PELICULA.TITULO, PELICULA.FECHA_ESTRENO,   
         REAL.NOMBRE||' '||REAL.APELLIDO DIRECTOR, ACTOR.NOMBRE 
NOMBRE,   
         ACTOR.APELLIDO APELLIDO, ACTOR.FECHA_NACIMIENTO,   
         ACTOR.NUM_PELICULA,STAT.PRESUPUESTO, STAT.NUM_ENTRADA 
_FRANCIA ENTRADAS,   
         CALCUL_EDAD_ACTOR(ACTOR.FECHA_NACIMIENTO)   
  FROM   PELICULA PELICULA, DIRECTOR REAL, CASTING CAST,   
       ...