Concepto

Un procedimiento almacenado (PA) es una secuencia de instrucciones sobre el servidor que realizan un objetivo. Pueden ser programados en base a sentencias SQL o por medio de un lenguaje “externo” ( C, java, etc.)

Definición

La sintáxis básica para la definición de un procedimiento SQL es la siguiente:

CREATE PROCEDURE <nombre de procedimiento> (
	<lista de
parámetros>)
<cuerpo del procedimiento>

Un procedimiento puede realizar dos grupos básicos de operaciones:

• Consulta ( SELECT )
• Modificación de la información ( INSERT, UPDATE y DELETE )

EJEMPLO 1

Create procedure sp_uno() dynamic result sets 1 language sql
Begin
Declare vista_cursor cursor with return to caller
For select * from JESUS.tagenda_v1;
Open vista_cursos;
end@

Un Procedimiento Almacenado puede ser invocado desde diferentes origenes:

• Línea de comandos
• Aplicaciones cliente ( aplicación en c, java, etc. )

Llamar a un Procedimiento Almacenado desde línea de comandos en DB2

1.- Primero se tiene que registrar en el servidor el nuevo procedimiento con la instrucción:

Db2 –td@ -vf <nombre del archivo>

EJEMPLO:

Db2 –td@ -vf example_storedProcedure.db2

2.- Invocación del procedimiento

Db2 “call <nombre procedimiento>( <lista de valores de parámetros> )”

EJEMPLO:

Db2 “call sp_uno()”

En este momento se recibe en la interface de “símbolo del sistema” el resultado de la consulta.

Parámetros

Los parámetros de un procedimiento almacenado pueden ser de
entrada, salida y entrada/salida. La sintáxis para definir un
parámetro es:

<tipo de parámetro> <nombre parámetro> <tipo de dato>

Donde:
<tipo de parámetro> = IN | OUT | INOUT
<tipo de dato> = tipos de datos escalares de DB2

Si se desea hacer una lista de parámetros estos deben de ir separados por una coma.

EJEMPLO 1

Create procedure sp_ejemplo( in valor character, out contador int )
language sql
Begin
SELECT count(*) into contador from agenda
WHERE estado= valor;
end@

EJEMPLO 2

Create procedure sp_ejemplo2( in fecha date, in hora time, in descripcion varchar(35))
Language sql
Begin
INSERT into agenda values (fecha,hora,descripcion,’p’)
end@

Variables

Las declaraciones de variables se hacen con la siguiente sintáxis:

DECLARE <nombre> <tipo de dato> DEFAULT <valor> ;

El valor para una variable se determina por medio de SET

SET <nombre> = <valor>

Condicionales ( IF )

La sintaxis es la siguiente:

IF ( <condición> ) THEN
<instrucciones>
ELSE
<instrucciones>
END IF;

Iteraciones ( WHILE )

La sintaxis es la siguiente:

WHILE <condición> do
<instrucciones>
END WHILE;

Dentro de un procedimiento se puede invocar a otros procedimientos o funciones.

EJEMPLO FINAL ( Validaciones periodo )

create procedure sp_validar( in FechaInicio date, in FechaFin date,
in HoraInicio time, in DUR int, in DESCRIP varchar(35),
in semana varchar(7), out RESP int )
language SQL
begin
declare CANT int default 0;
SELECT COUNT(*) into CANT
FROM tAgenda
WHERE FechaInicio<= ffinal AND FechaFin>=finicio AND
HoraIinicio <= (hinicio+decimal(duracion*10000,6,0)) AND (HoraInicio+decimal(DUR*10000,6,0))>= hinicio AND
diasem(dias,semana)=1;
IF CANT>0 then
SET RESP=0;
ELSE
INSERT into tAgenda values (default,FechaInicio,
FechaFin,HoraInicio,DUR,DESCRIP,semana);
SET RESP=1;
END IF;
end@

CREATE function diaSem( ORIGEN varchar(7), DESTINO varchar(7) )
returns integer deterministic no external action contains sql
BEGIN ATOMIC
DECLARE VALOR INTEGER DEFAULT 1;
DECLARE indice integer default 1;
WHILE indice<= 7 do IF (substr(origen,indice,1)='1' ) THEN IF (substr(ORIGEN,indice,1)=SUBSTR(DESTINO,indice,1)) THEN RETURN VALOR; END IF; END IF; SET indice=indice+1; END WHILE; SET valor=0; RETURN VALOR; end@ [/sourcecode]