Sql Server

Procedimientos almacenados

• Es fundamental en la gestión de BD– velocidad– eficiencia

– Los procedimientos almacenados están diseñados para optimizar la obtención de datos.

Concepto de procedimientos almacenados

• consulta almacenada en una BD en Sql Server y en lugar de almacenarse en el código cliente se almacena en el equipo de servidor

Razones para almacenar consultas en el servidor

• rendimiento• compilación• administración

Rendimiento

• consulta normal– Use tu base de datos– select campos, campos,..– from nombre tabla– where condicion– order by campo a ordenar

• en esta consulta sencilla– muchos usuarios ejecutando la misma consulta

todo el rato• trafico de red

– envian datos desde sus equipos al servidor– evitar trafico de red

» reducir cantidad codigo que se envia entre equipos» almacenar código en el servidor, no en el cliente

• el unico codigo que deben enviar los clientes será• EXEC nombredelprocedimiento

Compilación

• Sql server compila una consulta– lee la consulta– reconoce los elementos• clausualas

– compara consulta con todos los indices disponibles para ganar tiempo

– crea el plan de ejecución– almacena el plan en memoria

• los procedimientos almacenados ya están precompilados– ya ha pasado el proceso de compilación– tienen un plan en memoria– se ejecutan muy rápido

Administración

• para modificar una consulta existente– hay que hacer cambios en todos los equipos– en procedimento almacenado• solo se cambia una vez

• si una consulta se va a ejecutar una sola vez– no hay que crear procedimientos

• si los usuarios van a usar la consulta frecuentemente– crear un procedimiento definido por el usuario

que la contenga

Procedimientos almacenados definidos por el usuario

• Procedimiento almacenado que no requiere ningún parámetro– abrimos Management Studio– Explorador de objetos• Base de datos – tu base de datos – Programación• Procedimientos almacenados – boton derecho – nuevo• se abre una plantilla en la ventana de consulta

• CREATE PROCEDURE esquema.nombre del procedimiento

• WITH EXECUTE AS CALLER– SQL Server personaliza al llamador al ejecutar el

procedimiento• SELECT– los campos a mostrar

• WHERE– condicion a cumplir

• EJECUTAR

• CREATE PROCEDURE nombreprocedimiento WITH EXECUTE AS CALLER

• AS BEGIN SELECT Nombre, Color, precio, fechaventa FROM productos WHERE fechaventa> ‘10/01/2005’ ORDER BY fechaventa, Nombre END GO

• para ejecutar el procedimiento• abrimos una nueva consulta en Sql Server– use tu base de datos– EXEC nombreprocedimiento

• aquí los valores son estáticos– si losusuarios quieren productos de otra fecha– solución• parametros de entrada

Utilizar parámetros de entrada

• son marcadores de posición para los datos que necesita introducir el usuario– son variables en memoria– su contenido puede variar

– parametro para almacenar la fecha

• Bases de datos – Programación – Procedimientos almacenados– en el procedimiento – boton derecho – modificar– ahora pone Alter Procedure en lugar de Create

Procedure– debemos crear un parametro• @fecha• tipo de datos [datetime]

– modificamos la consulta

• ALTER PROCEDURE nombre• @fecha [datetime]• WITH…

• WHERE fecha>@fecha• …– END

• ALTER PROCEDURE nombre• @pais nvarchar(15)• WITH…

• WHERE Country>@pais• …– END

• exec clienteporpais ‘Spain’

• Para probarlo– exec nombreprocedimiento ‘1/1/1999’

– puedes probar las fehcas que quieras• si el usuario se olvida de poner la fecha puede dar error

– solución» poner para estos casos un valor predeterminado

• @fecha [datetime ] = ‘1/1/20003’

– prueba ahora poniendo y sin poner fecha

• los usuarios quieren ver un conjunto de resultados, sino un calculo matemático– parametros de salida

Utilizar parámetros de salida

• el procedimiento almacenado devuelve un valor para utilizarlo en futuras consultas– el parametro de salida se crea en el mismo lugar

que los parametros de entrada• entre nombre procedimiento• secciones AS de código

– pero se define con la palabra OUTPUT justo después

• CREATE PROCEDURE nombreprocedimiento @primero int,

– @segundo int,– @resultado int OUTPUT

• WITH EXECUTE AS CALLER• AS

BEGIN SET NOCOUNT ON;

• SET @resultado = @primero + @segundo

• END GO

• para probar el Procedimiento almacenado– debes indicar dónde se coloca el parametro de

salida

• ejecutamos el codigo en consulta– DECLARE @respuesta int– EXEC nombreprocedimento anterior 10,20,

@respuesta OUTPUT– SELECT ‘la solución es ‘ , @respuesta

Optimizar procedimientos almacenados

• cuando sql ejecuta una consulta por primera vez– antes la compila• analiza la consulta para ver lo que se intenta conseguir• mira las tablas, columnas que se ponen en where• plan de ejecución• lo almacena en la cache de procedimientos

– area de ram dedicado para esto– si cambiamos algo en la consulta

» recompila» pero a veces errores pq debe comenzar desde principio

• Obligar a Sql a compilar de nuevo un procedimiento almacenado– crearlo con la instrucción WITH RECOMPILE• obliga a crear un nuevo plan de ejecución• cuando el procedimiento almacenado tiene parametros

de entrada que cambian dráticamente

• CREATE PROCEDURE nombreprocedimiento• @fecha [datetime] = ‘1/1/2003’• WITH RECOMPILE, EXECUTE AS CALLER• AS

BEGIN SELECT Nombre, Color, precio, fechaventa FROM productos WHERE fechaventa> @fecha ORDER BY fechaventa, Nombre END GO

• tb puedes dejar el procedimiento original y al ejecutar

– EXEC nombreprocedimiento WITH RECOMPILE

• permite que el procedimiento se ejecute con más rapidez

Protección de procedimientos

• al crear un procedimiento– se crea una consulta almacenada en el servidor– se guardan en la tabla del sistema syscomments• en cada base de datos• se puede acceder a ellas• un usuario puede ver todo y cambiar el código

– justo el procedimiento es para que el usuario no tenga que hacer esto

– clausula WITH ENCRYPTION

• Select tabla.campo, tabla.campo• from syscomments com• join sysobjects ob• on ob.id=com.id• where ob.name=‘tabla que quieras’

• si ejecutamos se ve todo

Para cifrarlo

• alter procedure nombre• @fecha [ datetime] = ‘1/1/2000’• WITH ENCRYPTION, EXECUTE AS CALLER• AS • BEGIN• Select tabla.campo, tabla.campo• from syscomments com• join sysobjects ob• on ob.id=com.id• where ob.name=‘tabla que quieras’

• hasta aquí procedimientos almacenados definidos por el usuario

• Microsoft nos ofrece más– preestablecidos para trabajar con las tablas del

sistema• procedimientos almacenados del sistema• extendidos

• Microsoft usa metadatos– info sobre objetos del servidor• tamaño fichero• permisos de un usuario…

• podemos abrir directamente las tablas de sistema y consultar o modificar estos datos– dificil leer estos datos

Procedimientos almacenados del sistema

• cuando se añade una BD• se añade un registro• se crea un indice• modifica objeto del servidor– se producen cambios en las tablas del sistema– donde Sql almacena la info sobre los objetos

• los procedimientos de sistema actuan sobre las bases de datos– master– msdb

» comienzan por sp_

• sp_tables– muestra objetos que se puede utilizar en la

clausula from de una consulta select– es util si hemos olvidado el nombre exacto de una

tabla o vista

• procedimiento que devuelve información acerca de las tablas que pertenecen al esquema esquema1 de la base de datos tu base.

• USE tu base de datos;• GO • EXEC sp_tables• @table_name = '%',• @table_owner = ‘dbo',• @table_qualifier = ‘tubase';

• sp_stored_procedured– lista procedimientos almacenados listos para su

uso

• USE tu base;• EXEC sp_stored_procedures;

• sp_server_info– indica cómo se configuró sql server durante la

instalación– versión que se usa

• sp_databases– listatodas las BO disponibles en el servidor

• sp_configure• establece opciones de configuración global para

cmabiar el comportamiento de Sql

• sp_monitor– da una instantanea de cómo funciona el servidor

• USE master• EXEC sp_monitor

Usar procedimientos de sistema

• en consultas– USE tu base de datos– EXEC sp_help ‘tabla elegida’– EXEC sp_monitor

Procedimientos almacenados extendidos

• extienden las capacidades de Sql para que pueda hacer cosas de las que el servidor de BD no podria hacer– ejecutar un comando desde simbolo de sistema• procedimiento almacenado xp_cmdshell

– puede hacerlo

• los extendidos son codigo C++ guardados en biblioteca de vinculos dinamicos (DLL) desde la que se ejecutan

• muchos de ellos se ejecutan con otros procedimientos almacenados

• xp_cmdshell– ejecuta programas desde el shell de comandos– como el comando dir o md (crear directorio)

• xp_fileexist– comprobar si un fichero existe

• USE tu base de datos• DECLARE @resultado int• EXEC xp_fileexist ‘c:\fichero.txt’, @resultado

OUTPUT• SELECT @resultado

• devolvera 1 o 0

• xp_fixeddrives– muestra letras de unidades de discos fijos y

cuantos MB libres tienen

Ejecutar procedimientos extendidos

• para obtener una lista de directorios de la unidad c

• EXEC xp_cmdshell ‘dir c:’

Ejemplo

• Este ejemplo muestra el uso de xp_cmdshell en un procedimiento almacenado.

• Este ejemplo notifica a los usuarios (con net send) que se va a cerrar SQL Server, detiene el servidor (con net pause) y, después, apaga el servidor (con net stop).

• CREATE PROCEDURE APAGADO10 • AS • EXEC xp_cmdshell net send /domain:SQL_USERS ''SQL Server shutting down • in 10 minutes. No more connections allowed.', no_output • EXEC xp_cmdshell 'net pause sqlserver' • WAITFOR DELAY '00:05:00' • EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down • in 5 minutes.', no_output • WAITFOR DELAY '00:04:00' • EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down • in 1 minute. Log off now.', no_output • WAITFOR DELAY '00:01:00' • EXEC xp_cmdshell 'net stop sqlserver', no_output

Procedimiento permite insertar un registro a la tabla empleados pero antes verifica si existe algun empleado con el mismo nombre y apellido en ese caso lo rechaza no se toma en cuenta el campo idempleado porque es autonumerico

• CREATE PROCEDURE INSERTAEMPLEADO( • @NOMBRE VARCHAR(50), • @APELLIDOS VARCHAR(50), • @CARGO VARCHAR(25), • @FECHACONTRATACION SMALLDATETIME, • @FECHANACIMIENTO SMALLDATETIME, • @DIRECCION VARCHAR(50), • @CIUDAD VARCHAR(50), • @PAIS VARCHAR(50), • @FONO VARCHAR(7)) • AS • IF (SELECT COUNT(*) FROM EMPLEADOS WHERE APELLIDOS=@APELLIDOS AND NOMBRE=@NOMBRE)>0 • BEGIN • PRINT ‘EL EMPLEADO CON ESE NOMBRE YA EXISTE’ • END • ELSE • BEGIN • INSERT INTO EMPLEADOS(APELLIDOS,NOMBRE,CARGO,FECHANACIMIENTO,FECHACONTRATACIÓN,DIRECCION, • CIUDAD,PAÍS,FONO) • VALUES(@APELLIDOS,@NOMBRE,@CARGO,@FECHANACIMIENTO,@FECHACONTRATACION,@DIRECCION,@CIUDAD, • @PAIS,@FONO) • END • GO • EXECUTE INSERTAEMPLEADO ‘sanchez’,’enrique’,’DOCENTE’,’22/09/71’,’23/09/98’,’arenal 8’,’madrid’,’españa’,’5340806’

• Procedimiento que permite borrar un empleado especificando su idempleado para eliminarlo

• CREATE PROCEDURE ELIMINAEMPLEADO(@IDEMPLEADO INT)

• AS • DELETE FROM EMPLEADOS WHERE

IDEMPLEADO=@IDEMPLEADO • Go • EXEC ELIMINAEMPLEADO 2

• Procedimiento que permite visualizar los detalles de un pedido espècificando el numero de pedido

• AS • SELECT • [DETALLES DE PEDIDOS].IDPEDIDO,PRODUCTOS.NOMBREPRODUCTO,

[DETALLES DE • PEDIDOS].PRECIOUNIDAD, • [DETALLES DE PEDIDOS].CANTIDAD, • CANTIDAD*[DETALLES DE PEDIDOS].PRECIOUNIDAD AS TOTAL • FROM [DETALLES DE PEDIDOS] INNER JOIN PRODUCTOS • ON PRODUCTOS.IDPRODUCTO=[DETALLES DE PEDIDOS].IDPRODUCTO • WHERE [DETALLES DE PEDIDOS].IDPEDIDO=@NUMPED • GO • EXEC VERDETALLES 10248

• podemos crear un procedimiento para recuperar el nombre de un Cliente, cuyo código se pasa por parámetro

• CREATE PROCEDURE ObtenerNombre @IDCLIENTE varchar(11) AS

• SELECT NOMBRECOMPAÑíA • FROM CLIENTES • WHERE IDCLIENTE = @IDCLIENTE

• Procedimiento para obtener el número de clientes y el número de productos que tenemos en la base de datos,

• CREATE PROCEDURE num_clientes_productos @clientes int OUTPUT, @Productos int OUTPUT AS

• SELECT * FROM clientes • SELECT @clientes = @@ROWCOUNT • SELECT * FROM productos • SELECT @productos = @@ROWCOUNT • RETURN (0)

Explicación

• A continuación devolvemos en el parámetro @clientes el valor obtenido, utilizando @@ROWCOUNT.

• Acto seguido se realiza lo mismo para la tabla Productos. Nótese como la forma de asignar un valor a un atributo es mediante una sentencia SELECT, igualando el parámetro al valor.

• La función @@ROWCOUNT devuelve el número de filas que se han seleccionado. Es equivalente a la sentencia que aparece en el Código fuente

• SELECT COUNT(*) FROM Productos

• procedimento que me diga todas las filas en la tabla clientes y productos

• CREATE PROCEDURE num_clientes_productos @clientes int OUTPUT, @clientes int OUTPUT AS

• SELECT @clientes = (SELECT COUNT(*) FROM clientes)

• SELECT @productos = (SELECT COUNT(*) FROM Productos)

• RETURN (0)

Ejecutar procedimiento• Para ejecutar el anterior procedimiento, seguiremos los siguientes pasos: • Declarar las variables que vamos a utilizar para llamar al procedimiento.

La sintaxis para declarar una variable es utilizar la palabra reservada DECLARE, seguido del nombre de la variable y el tipo.

• DECLARE @num_clientes int • DECLARE @num_Productos int • Ejecutar el procedimiento. La sintaxis es utilizar la palabra reservada

EXEC, seguida del nombre del procedimiento, y los parámetros, separados por comas, especificando si son de retorno.

• EXEC num_clientes_productos @num_clientes OUTPUT, @num_productos OUTPUT

• Mostrar los resultados • SELECT clientes = @num_clientes, productos = @num_productos

• Modificar el procedimiento anterior para que solo liste los clientes de alemania

• ALTER PROCEDURE num_clientes_productos @clientes int OUTPUT, @productos int OUTPUT AS

• SELECT @clientes = (SELECT COUNT(*) FROM clientes where pais=’alemania’)

• SELECT @productos = (SELECT COUNT(*) FROM productos where preciounidad>10)

• RETURN (0)

• Eliminar el procedimento anterio

• Si queremos borrar un procedimiento almacenado, ejecutaremos la sentencia DROP PROCEDURE, seguido del nombre del procedimiento. Por ejemplo, si queremos borrar el procedimiento almacenado, creado en el anterior ejemplo, escribiremos el Código fuente

• DROP PROCEDURE num_clientes_clientes

• crear procedimiento que liste los clientes de un pais como parametro

• si no decimos nada, le damos un parametro predeterminado

• CREATE PROCEDURE LISTARCLIENTES(@PAIS VARCHAR(25) ='ARGENTINA')

• AS • SELECT

IDCLIENTE,NOMBRECOMPAÑÍA,DIRECCIÓN,PAÍS FROM CLIENTES

• WHERE PAÍS=@PAIS