base de datos - esaavedra.ublog.clesaavedra.ublog.cl/archivos/930/ayudantia_7... · eduardo...

IntroducciónProgramación

Procedimientos en Procedimientos...SP Genérico

FuncionesReferencias

Base de datosProcedimientos Almacenados y Funciones

Eduardo Saavedra A.

Universidad Nacional Andrés Bello4 de noviembre de 2008

Eduardo Saavedra A. SQL Server 2005

Ventajas

Contenidos

1 IntroducciónVentajas

2 ProgramaciónCreación de un SPInvocando un SPDeclaración de variablesDeclaración de Parámetros

3 Procedimientos en Procedimientos...

4 SP Genérico

5 FuncionesConceptosEjemplo

6 Referencias

Ventajas

Procedimiento Almacenado (SP)

Un procedimiento almacenado (SP) es un programa (o procedimiento) el cual esalmacenado físicamente en una base de datos.

Generalmente son escritos en un lenguaje de bases de datos propietario comoPL/SQL para Oracle database o PL/PgSQL para PostgreSQL.

La ventaja de un procedimiento almacenado es que al ser ejecutado, enrespuesta a una petición de usuario, es ejecutado directamente en el motor debases de datos, el cual usualmente corre en un servidor.

Como tal, posee acceso directo a los datos que necesita manipular y solonecesita enviar sus resultados de regreso al usuario, deshaciéndose de lasobrecarga resultante de comunicar grandes cantidades de datos salientes yentrantes.

Ventajas

Contenidos

4 SP Genérico

6 Referencias

Ventajas

Diseño modular.

Aplicaciones que acceden la misma Base de Datos pueden compartir losprocedimientos almacenados, eliminando el código doble y reduciendo el tamañode las aplicaciones.

El fácil mantenimiento.

Mejoras en tráfico de RED!!!

Cuando un procedimiento se actualiza, los cambios se reflejan automáticamenteen todas las aplicaciones, sin la necesidad de recompilar y relinkear. Lasaplicaciones son compiladas sólo una vez para cada cliente.

Los procedimientos almacenados son ejecutados por el servidor, no por el clientelo que reduce el tráfico en la red y mejora el performance o desempeño,especialmente para el acceso del cliente remoto.

Están almacenados en los servidores y asegurados por las medidas tomadas enla instalación, lo que impide que los usuarios normales puedan modificarlos eincluso desconocen su existencia. Este es un elemento de gran valor en lo que aseguridad respecta.

Creación de un SPInvocando un SPDeclaración de variablesDeclaración de Parámetros

Contenidos

4 SP Genérico

6 Referencias

Contenidos

4 SP Genérico

6 Referencias

Un procedimiento almacenado

Utilizando la base de datos Autos:1 CREATE PROCEDURE sp_Ciudades2 AS3 SELECT DISTINCT Ciudad4 FROM Cliente;

¿Qué cree que genera el código anterior?Lo que ocurrirá será que para la base de datos Autos, se creará un procedimientoalmacenado llamado sp_Ciudades.

Este procedimiento podrá ser llamado desde cualquier punto en alguna aplicaciónexterna al SGBD.

Por supuesto, también puede ser llamado de forma “interna”

Este procedimiento es de los más básicos, donde simplemente se genera unatabla temporal mostrando las ciudades de la tabla cliente.

¿Qué cree que genera el código anterior?

Lo que ocurrirá será que para la base de datos Autos, se creará un procedimientoalmacenado llamado sp_Ciudades.

Contenidos

4 SP Genérico

6 Referencias

Invocación de un procedimiento almacenado

El siguiente código invoca un SP.1 EXEC sp_Ciudades;

Como es de esperar el resultado de la ejecución es la siguiente:

Contenidos

4 SP Genérico

6 Referencias

Declarar variables en un SP

Uno de los potenciales de los SP es que son capaces de manejar Variables, tal ycomo en un lenguaje de programación de alto nivel.

Todas la variables deben ser antecedidades por la palabra. clave DECLARE yantes de la definición de su nombre una @.

Las variables a declarar pueden ser de cualquier tipo que el SGBD soporte.

Para SQL Server por ejemplo se tendrían:varchar(n)intbigintfloatdateetc...

Además para asignar un valor a una variable (A←′ 5′) se debe utilizar la palabraclave SET.

Se pueden “Setear” a una variable los resultados de un SQL que devuelva UNregistro.

Una variable puede ser declarada como de “Salida” (output)

Para SQL Server por ejemplo se tendrían:

varchar(n)intbigintfloatdateetc...

Para SQL Server por ejemplo se tendrían:varchar(n)

intbigintfloatdateetc...

Para SQL Server por ejemplo se tendrían:varchar(n)int

bigintfloatdateetc...

Para SQL Server por ejemplo se tendrían:varchar(n)intbigint

floatdateetc...

Para SQL Server por ejemplo se tendrían:varchar(n)intbigintfloat

dateetc...

Para SQL Server por ejemplo se tendrían:varchar(n)intbigintfloatdate

etc...

Por lo tanto sería invalido declarar @Nivel como int y tratar de “Setearlo” con’Hola!’(de tipo varchar).

Ejemplo de uso de variablesALTER PROCEDURE sp_CiudadesAS

DECLARE @Nivel varchar(80)DECLARE @Ciudad varchar(80)

SET @Ciudad=(SELECT TOP 1 CiudadFROM Cliente as CINNER JOIN AUTOMOVIL AS A ON A.RUT = C.RUTORDER BY A.PRECIO DESC)

IF @Ciudad = ’Santiago’BEGIN

SET @Nivel = ’1’END

ELSESET @Nivel = ’0’

SELECT @Nivel as ’Nivel’;

Lo marcado en rojo es lo que el SGBD desplegará, esto no siempre es válido yaque un SP debe devolver algún valor.

De todas maneras es útil para ver lo que ocurre en el SP.

De todas maneras es útil para ver lo que ocurre en el SP.Eduardo Saavedra A. SQL Server 2005

Contenidos

4 SP Genérico

6 Referencias

Parámetros de SP

Como es de esperar, los SP al igual que las funciones en lenguajes deprogramación, son capaces de aceptar parámetros de entrada y salida.

Esto logra una gran versatilidad del uso de los SP

Para definir parámetros, ellos deben estar antes de la palabra clave AS

No se requiere declararlos con la palabra DECLARE

Respectivo a los parámetros de entrada

Un usuario puede usar un SP con distintos parámetros de entrada y obtendrásiempre resultados distintos.

Respectivo a los parámetros de salida

Los parámetros de salida SIEMPRE deben estar declarados de la siguienteforma:

@Parámetro OUTPUT

Para adquirir los parámetros de salida, es fundamental haber definido la variableantes de llamar al SP.

Parámetros de SP

@Parámetro OUTPUT

Parámetros de SP

@Parámetro OUTPUT

Parámetros de SP

@Parámetro OUTPUT

Parámetros de SP

@Parámetro OUTPUT

Parámetros de SP

@Parámetro OUTPUT

Parámetros de SP

@Parámetro OUTPUT

Parámetros de SP

Ejemplo de uso de parámetrosALTER PROCEDURE sp_SemestreMax@Anio varchar(80), @Semestre varchar(80) outAS

SET @Semestre = (SELECT Semestre_Precio.SemestreFROM (

SELECT YEAR(FECHA) as ANHO, MONTH(FECHA)/7+1 as Semestre, PRECIOFROM AUTOMOVILWHERE YEAR(FECHA)=@Anio

) as Semestre_PrecioGROUP BY Semestre_Precio.SemestreHAVING sum(Semestre_Precio.PRECIO) = (

SELECT max(maximo.Suma_Periodo)FROM(

SELECT sum(Semestre_Precio.PRECIO) as Suma_PeriodoFROM (

SELECT YEAR(FECHA) as ANHO, MONTH(FECHA)/7+1 as Semestre, PRECIOFROM AUTOMOVILWHERE YEAR(FECHA)=@Anio) as Semestre_Precio

GROUP BY Semestre_Precio.Semestre ) as maximo)

) SELECT @Semestre as ’Semestre’;

Nuevamente se utiliza la sentencia final para mostrar una tabla con el resultado,sin embargo, lo ideal es usar el parámetro de salida @Semestre !!

Entonces... ¿Como llamarlo?

Parámetros de SP

Entonces... ¿Como llamarlo?Eduardo Saavedra A. SQL Server 2005

Llamadas de un SP con parámetros de salida

Como se podría pensar, la llamada debería ser:

EXEC sp_SemestreMax ’2008’

Sin embargo, como se definió una variable de salida, es necesario declarar esavariable antes de llamar al SP. Luego de esto se utiliza como parámetro paraquedar “cargada” con el valor de salida.

Luego de terminar el SP es posible utilizar esa variable declarada:

Ejemplo de uso de parámetros

DECLARE @Semestre varchar(80)EXEC sp_SemestreMax ’2007’, @Semestre OUTPUTSELECT @Semestre

Las llamadas a SP con parámetros deben realizarce utilizando la palabra claveEXEC seguidas de el nombre del procedimiento, luego todos los parámetrosrequeridos deben ir separados por , (coma).

Contenidos

4 SP Genérico

6 Referencias

SPs “Anidados”

Como bien se menciono anteriormente, un SP puede ser utilizado dentro de otro

Por ejemplo, si agregamos esto en el primer procedimiento visto:ALTER PROCEDURE sp_CiudadesAS

DECLARE @Semestre varchar(80)EXEC sp_SemestreMax ’2007’, @Semestre outputPRINT @Semestre

Y por supuesto, la variable @Semestre podría ser utilizada en cualquier lugar delprocedimiento Padre ;)

SPs “Anidados”

Por ejemplo, si agregamos esto en el primer procedimiento visto:

ALTER PROCEDURE sp_CiudadesAS

SPs “Anidados”

Contenidos

4 SP Genérico

6 Referencias

La estructura de un SP

Si llegamos a un concenso, podríamos decir que la estructura de un SP típico esla siguiente:

CREATE PROCEDURE nombre_sp@Param1 tipo OUT|IN,@Param2 tipo OUT|IN,...@ParamN tipo OUT|IN

--Cuerpo SPDECLARE @Var1 tipoDECLARE @Var2 tipo...DECLARE @VarN tipo

Sentencias de control &|SQLs &|Llamadas a otros SP &|Etc.

--Fin Cuerpo SP;

Ahora si se quiere actualizar el código del SP, en vez de usar la palabra CREATEinicialmente se utiliza ALTER .

Asimismo para borrar un SP, basta con:DROP PROCEDURE nombre_sp ;

--Fin Cuerpo SP;

Asimismo para borrar un SP, basta con:

DROP PROCEDURE nombre_sp ;

--Fin Cuerpo SP;

ConceptosEjemplo

Contenidos

4 SP Genérico

6 Referencias

ConceptosEjemplo

Contenidos

4 SP Genérico

6 Referencias

ConceptosEjemplo

Declarar funciones

La estructura es casi idéntica a un SP

Existen para SQL server 3 tipos:

Escalares: Retorna valores “Constantes”

De tablas: Maneja tablas de tal forma de retornar un set de tuplas.

De multiples declaraciones: Maneja tablas incluyendo su estructura base.

Sin duda los mas utilizados son los escalares, ya que los demás pueden sersuplidos por vistas (a excepción del de multiples declaraciones que puede ser unaherramienta sumamente potente)

ConceptosEjemplo

Declarar funciones

Existen para SQL server 3 tipos:

Escalares: Retorna valores “Constantes”

ConceptosEjemplo

Declarar funciones

Existen para SQL server 3 tipos:Escalares: Retorna valores “Constantes”

ConceptosEjemplo

Declarar funciones

ConceptosEjemplo

Declarar funciones

ConceptosEjemplo

Declarar funciones

ConceptosEjemplo

Contenidos

4 SP Genérico

6 Referencias

ConceptosEjemplo

Declarar funciones

Las funciones son muy similares a las utilizadas en cualquier lenguaje deprogramación, se usan paréntesis para los parámetros y se utiliza la palabra clavereturn para devolver valores.

Ejemplo:CREATE FUNCTION dbo.Metros_a_pulgadas (@Metros DECIMAL(10,3), @VAR_DUMMY INT)RETURNS DECIMAL(10,3)ASBEGINDECLARE @Pulgadas DECIMAL(10,3)SET @Pulgadas = (@Metros * 3.281 ) * 12 +@VAR_DUMMYRETURN @PulgadasEND;

Las palabras marcadas son las mas importantes.

se debe declarar el tipo de retorno de la función

Al final de la instrucciones debe ir un RETURN, con la variable a retornar, seguidode un END.

ConceptosEjemplo

Declarar funciones

ConceptosEjemplo

Declarar funciones

Ejemplo:

CREATE FUNCTION dbo.Metros_a_pulgadas (@Metros DECIMAL(10,3), @VAR_DUMMY INT)RETURNS DECIMAL(10,3)ASBEGINDECLARE @Pulgadas DECIMAL(10,3)SET @Pulgadas = (@Metros * 3.281 ) * 12 +@VAR_DUMMYRETURN @PulgadasEND;

ConceptosEjemplo

Declarar funciones

ConceptosEjemplo

Declarar funciones

ConceptosEjemplo

Declarar funciones

ConceptosEjemplo

Declarar funciones

Contenidos

4 SP Genérico

6 Referencias

Referencias

Bibliografía

[Funcionalidades SQL Server, 2005] Microsoft Team.MSDN

[SQL Server 2005 for dummies, 2003] Microsoft Team.

Capitulos 14 y 15

[Manual SP y Funciones, 2002] Mitchell Harper.http://www.devarticles.com

Dudas, Consultas?4 de noviembre de 2008

Referencias

Bibliografía

Capitulos 14 y 15

Referencias

Bibliografía

Capitulos 14 y 15

base de datos - esaavedra.ublog.clesaavedra.ublog.cl/archivos/930/ayudantia_7... · eduardo...

Documents