· Estructura de una Store Procedure: Se compone de 2 zonas: Cabecera y Detalles.
CREATE PROCEDURE NombreDeStoreProcedure
.....................
.................... Cabecera
AS .....................
.................... Detalles
En la cabecera se indica el nombre de la sp y los argumentos (de entrada y/o salida).
En la sección de detalles se declaran las variables y se escribe el código que va a ejecutar la sp.
· Comentarios: Se indican entre los símbolos /* y */. Tambíen se pueden usar – al comienzo de cada fila.
Ej: /* Esto es un comentario */
Ó
--Esto es un comentario
· Variables locales al procedimiento: Se declaran al comienzo de la parte de detalles. Se usa la palabra Declare antes de cada variable. El nombre de las variables deben tener delante el símbolo @. El tipo de la variable se indica entre corchetes a continuación del nombre de la variable. Los tipos de datos que hay son: int, varchar, datetime, float, char ,.........
Ejemplos:
declare @i [int]
declare @gfh [varchar](6)
declare @fecha [datetime]
Para hacer varias declaraciones en una sola línea:
DECLARE @NombreVariable1 varchar(40), @NombreVariable2 varchar(20)
Para hacer actualizar el valor de una variable:
select @ NombreVariable = @NombreVariable +1
Para inicializar el valor de una variable:
select @ NombreVariable =1
ó
select @MiPalabra = 'asier'
· Mostrar mensajes en isql: Se usa la palabra print mensaje. Esto escribe en la pantalla de resultados de isql el mensaje.
Ejemplo:
Print ‘ Hola ‘ Imprime Hola
print @i Imprime el valor de la variable @i
· Ejecución de sentencias sql dentro de SP: Las sentencias sql se van a escribir directamente.
Ejemplo:
Select * from cpiniope where clave = 1
Update ................
..................
Puede interesar recoger los valores de los campos del registro que me devuelve una select. Para eso hay que declara las variables en donde se dejen los valores y ejecutar la sql de la siguiente manera (deja en @numope el valor del campo del registro seleccionado):
Declare @numope [int]
select @numope=numope from cpiniope where clave = 1
De la misma manera puede interesar hacer una insert de unos campos cuyos valores no son fijos (literales). Igual que antes, tendremos unas variable que nos habremos encargado de cargar con valores y escribiremos la sentencia sql de la siguiente manera:
Declare @nordfab [int]
Declare @numope [int]
......................................
......................................
INSERT INTO [NombreBD].[dbo].[NombreTabla] ( [codemp], [nordfab], [numope], [uniobr], [correcto])
VALUES ( '100', @nordfab, @numope, 0, 'S')
· IF-Else: Se usa para crear condiciones en Sql. La sintaxis es:
IF ExpresionEsVerdadera
BEGIN
....................
END
ELSE
BEGIN
....................
END
No existe la posibilidad de hacer como en VB “elseif”. Si queremos hacer varios IF seguidos, se debe anidar, es decir:
IF Expresion1EsVerdadera
BEGIN
....................
END
ELSE
BEGIN
IF Expresion2Verdadera
BEGIN
.................
END
ELSE
IF Expresion3Verdadera
BEGIN
...............
END
ELSE
BEGIN
................
END
END
La Expresión que evalua el If, puede ser cualquier expresión que devuelva Verdadero ó Falso. Por ejemplo:
IF @Valor1 > @valor2
IF (SELECT max(price) FROM titles ) = 10
· Bucles: Para crear bucles podemos usar la sentencia While. Tiene la siguiente estructura:
WHILE expresión verdadera
BEGIN
sentencia 1
sentencia 2
sentencia 3
................
END
Ejemplo:
WHILE @i < 10
BEGIN
print @i
END
· Parámetros: Pueden ser de entrada o salida. Ambos se declaran en la cabecera de la store procedure. Se pueden declarar de varios tipos. ¿Qué tipos?. Los mismos que para las variables declaradas con Declare en la sección de Detalles de la SP. Luego se usarán como cualquier otra variable
Parámetros de Entrada: Se les puede establecer un valor por defecto. Se indica después del tipo del parámetro.
CREATE PROCEDURE NombreDeStoreProcedure
@MiParametro1 TipoDato = ValorPorDefecto1,
@MiParametro2 TipoDato = ValorPorDefecto2,
@MiParametro3 TipoDato = ValorPorDefecto3
AS
Ejemplo:
CREATE PROCEDURE NombreDeStoreProcedure
@MiParametro varchar(255) = 'Bilbao'
AS
select * from authors
where city = @Parametro
En caso de crear un parámetro sin valor por defecto, al llamar al SP, es obligatorio pasarle el parámetro. En cambio, si ponemos un valor por defecto para este parámetro, ese parámetro pasa a ser opcional, de manera que se pueden dar 2 casos:
1. Se llama al SP y se le pasa el parámetro. NO hace caso al valor por defecto y toma por valor el que se le pasa.
2. Se llama al SP sin pasarle el parámetro. El parámetro tomará como valor el que se haya establecido por defecto al declararlo.
ü Parámetros de Salida: Después del tipo de parámetro se debe escribir la palabra OUTPUT para indicar que es un parámetro de salida.
Ejemplo:
CREATE PROCEDURE DatosCiudadRetorno
@nombre varchar(255) OUTPUT
AS
select @nombre = au_fname from authors
where city = ‘Bilbao’
· Cursores: Se usan para recuperar información de la BD. Se debe seguir varios pasos:
1. Declararlos: Se usa la sintaxis: DECLARE nombre SCROLL CURSOR For SentenciaSql.
Hay que tener en cuenta que la palbra clave SCROLL es opcional :
- si se pone, se pueden hacer todas las operaciones con el cursor (Se permite el NEXT, PRIOR, FIRST y LAST).
- Si no se pone, solo podemos movernos en el cursor hacia delante (solo se permite el NEXT).
Ejemplo:
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors ORDER BY au_lname
2. Abrirlos: Se usa la sintaxis: OPEN NombreCursor
Ejemplo:
OPEN authors_cursor
3. Recorrerlos: Se usa la palabra reservada FETCH {NEXT | PRIOR | FIRST | LAST}. La senetecia Fetch, muestra en la ventana de isql los valores de los campos del registro actual (Ano ser que estos se hayan asignado a variables, como se ve más adelante).
ü NEXT: Se mueve al siguiente registro del cursor. Si acabo de abrir el cursor (Acabo de ejecutar OPEN), se coloca en el primer registro de l cursor.
ü PRIOR: Se mueve al anterior registro del cursor.
ü FIRST: Se mueve al primer registro del cursor.
ü LAST: Se mueve al último registro del cursor.
Para saber si hemos llegado al final ( en caso de recorrer el cursor des el ppio al final) ó ppio (en caso de recorrer el cursor desde el final al ppio) del cursor se debe preguntar por el valor de @@FETCH_STATUS = 0 (en ambos casos). Si por ejemplo tenemos un bucle en el que recorremos todos los registro de un cursor hasta fin de fichero, si por lo que sea no controlamos bien ese fin de fichero, al ejecutar la store procedure, no va a cascar como en VB cuando llegas a EOF y no se controla; aquí la SP seguirá ejecutando el bucle indefinidamente pero siempre posicionado en el último registro (Se mueve al registro 1º, luego al 2º, luego al 3º , ................ , luego al último, luego al último, luego al último .......... indefinidamente).
4. Cerrar el cursor: Se usa la sintaxis CLOSE NombreDeCursor
5. Liberar el espacio que usa el cursor: Se usa la palabra reservada DEALLOCATE NombreDeCursor
Ejemplo:
Este SP, lee todos los autores de la tabla ‘authors’ y los recorre 1 a 1, mostrando el valor de su campo.
CREATE PROCEDURE LeerAutores
AS
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors ORDER BY au_lname
OPEN authors_cursor
--Nos movemos al primer registro
FETCH NEXT FROM authors_cursor
--Recorremos todos los registros mientras no lleguemos al ultimo
WHILE @@FETCH_STATUS =0
BEGIN
-- Se mueve al siguiente registro y muestra los valores de los campos por isql.
FETCH NEXT FROM authors_cursor
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
· Asignar Valores devueltos por un cursor a variables: Se deben declarar las variables del mismo tipo que los campos que retorna la select.
Después en la intrucción FETCH añadir :
FETCH ....... INTO @variable1, @variable2, ...
En ese momento, tenemos en las variables @variable1, @variable2, etc, los valores de los campos del registro actual. Los valores se asignan en el mismo orden que están en el select, es decir:
Select campo1, campo2, ..............
Se asignará campo1 a @variable1 y campo2 a @variable2.
Nota: En este caso la instrucción Fetch no mostrará los valores de los campos del registro actual en la ventana de isql. Será necesario usar la sentencia ‘Print’ junto con las variables.
Ejemplo:
Este SP, lee todos los autores de la tabla ‘authors’ y los recorre 1 a 1.
CREATE PROCEDURE LeerAutores
AS
DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors ORDER BY au_lname
OPEN authors_cursor
-- Nos movemos al primer registro
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
-- Recorremos todos los registros hasta llegar al último
WHILE @@FETCH_STATUS =0
BEGIN
-- Mostramos por isql los valores de los campos devueltos.
PRINT "Author: " + @au_fname + " " + @au_lname
-- Nos movemos al siguiente cursor
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
No hay comentarios:
Publicar un comentario