Procedimiento Almacenado

Informe de Laboratorio Laboratorio Implementación Implementación de bases de d atos Tema: Procedimientos Almacenados

RESUMEN

El presente laboratorio consiste en indagar sobre procedimientos almacenados desde su generalidad hasta ejemplos específicos, partiendo de una base teórica conceptual para afianzar afianzar los conocimientos previos que tengan los participantes La investigación se realizara en dos partes: 1) La primera parte tratara sobre la concepción, creación, implementación y seguridad de procedimientos almacenados 2) Uso práctico de los procedimientos almacenados almacenados para insertar, modificar y eliminar registros Con la presente investigación investigación se espera conocer y afianzar el uso de procedimientos almacenados almacenados en nuestros sistemas que contengan base de datos para hacer transacciones más limpias en código , rapidez y seguridad al ejecu tarla. Para ello el equipo encargado del desarrollo desarrollo del presente laboratorio está conformado conformado por 7 personas cuyos roles se han definidos para mejor desarrollo del laboratorio.       

Angulo Méndez Angelo Rafael Salvador Levano Castilla Carlos Miguel Quispe Arcos Hans Donayre Chacaltana Robert Pardo Figueroa Herencia Jhonatan Carlos Daniel Pillpe Leon William Ramírez Gamboa Marlon

En el proceso de desarrollo de investigación y ejecución del laboratorio se usara como recursos el gestor de base de datos SQL Server 2008, el tiempo recomendado para el desarrollo del laboratorio es de 10 minutos por laboratorio y el tiempo que ud requiera para asimilar bien la teoría. Dicho punto es fundamental para poder proseguir con los ejercicios, lea atentamente cada indicación y teoría, no intente avanzar a otro punto sin antes haber asimilado el punto anterior. Con respecto a los recursos financieros y el gasto que implica implica desarrollar el laboratorio solo será de que Ud. cuente con la licencia necesaria para ejecutar el SQL Sever Conocimientos previos requeridos:  Manejo de Transact SQL para la implementación implementación de consultas estructuradas estructuradas  Manejo y conocimientos del gestor de base de datos SQL S erver

Palabras clave: Ejemplo: Bases de Datos . SQL Server 2008.

The world's largest digital library

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.











1.

INTRODUCCION

Desde que el hombre comenzó a utilizar medios digitales para almacenar su información comenzó a enfrentarse con problemas para hacer mas práctico este proceso, es lógico l ógico pensar que desde la utilización de los archivos ( que son los antecesores de las bases de datos ) se les fueron integrando algunas mejoras hasta llegar a la concepción que actualmente se tienen de base de datos, al paso del tiempo las empresas de la industria de software en especial la relacionada con las bases de datos incorporaron mecanismos como los mencionados en el sección 2.2 hasta llegar paulatinamente a incorporar, los procedimientos almacenados. Los procedimientos almacenados no son nuevos en la industria de las bases de datos, como referencia se tiene a ORACLE, que presentó PL/SQL 2, su implementación de un lenguaje procedimental para SQL, esto por el año de 1991, SYBASE, PROSTGRESSQL Y DB2 están entre los otros DBMS que en breve siguieron este tan socorrido lenguaje procedimental para sentencias SQL. A la publicación de este documento es MYSQL el más reciente poseedor de procedimientos almacenados, alma cenados, esto invita a algunos expertos en la materia materia a mencionar que con esta incorporación este DBMS ha llegado a su mayoría de edad, aunque sin aventurarse a mencionar que pudiera ser una competencia seria para los grandes en esta rama como lo son ORACLE Y SQLSERVER. Cubrir las diferentes necesidades de los usuarios de un DBMS debe de ser la filosofía a seguir de la industria de las bases de datos y este comentario es seguro que no pasó desapercibido por los desarrolladores ya que la totalidad de las bases de datos están haciendo o hicieron esfuerzos por incorporar los procedimientos almacenados a su software. Y se menciona de esta manera porque en realidad en estos tiempos en los que gran parte de la información del mundo se encuentra alojada en BD esto fue una necesidad, como lo pueden corroborar los capítulos que continúan. La tendencia de las bases de datos actualmente va encaminada a darle más conocimiento a las bases de datos que a la aplicación, esto quiere decir que el cliente esté enterado lo menos posible de la estructura lógica de la DB, o al menos esto muestra la clara incorporación de algunos elementos como la integridad referencial, actualización y eliminación en cascada, disparadores, UDF´S y ahora procedimientos almacenados, los cuales realizan labores que antes eran propias de las aplicaciones cliente. Los procedimientos almacenados son una herramienta que todo desarrollador debe tener en cuenta siempre, ya que proporcionan un rendimiento en términos de velocidad e increme incrementan ntan la seguridad en su sistema de base de datos, es por ello que su empleo en los diferentes proyectos incrementa la calidad del desarrollo de software. Ahora se muestra la definición de un procedimiento almacenado. “Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse en el servidor y se ejecutan en él cuando un usuario con permisos para ello lo solicite”

2. OBJETIVOS 

Aprender y usar procedimientos almacenados almacenados para tareas comunes en sistemas











3. MARCO CONCEPTUAL 3.1) ¿Que son procedimientos almacenados?

Un procedimiento es un subprograma que ejecuta una acción específica y que no devuelve ningún valor. Un procedimiento tiene un no mbre, un conjunto de parámetros (opcional) y un bloque de código. La sintaxis de un procedimiento almacenado almacenado es la siguiente:

CREATE [OR REPLACE] PROCEDURE [( [( [IN|OUT|IN OUT] , , [IN|OUT|IN OUT] , , ...)] IS -- Declaracion de variables locales BEGIN -- Sentencia Sen tenciass [EXCEPTION] -- Sentencias control de excepcion END []; ];

El uso de OR REPLACE permite sobreescribir sobreescribir un u n procedimiento existente. Si se omite, y el procedimiento existe, se producirá, un error. La sintaxis es muy p arecida a la de un bloque anónimo, salvo porque se reemplaza reemplaza la seccion DECLARE por la secuencia PROCEDURE ... IS en la esp ecificación del procedimiento. Debemos especificar el tipo de datos de cada parámetro. Al especificar el tipo de dato del parámetro no debemos especificar la longitud del tipo . Los parámetros pueden ser de entrada (IN), de salida ( OUT) o de entrada salida (IN OUT ). El valor por defecto es IN, y se toma ese valor en caso de q ue no especifiquemos nada.

CREATE OR REPLACE PROCEDURE Actualiza_Saldo(cuenta NUMBER , new_saldo NUMBER ) IS -- Declaracion de variables locales BEGIN -- Sentencia Sen tenciass UPDATE SALDOS_CUENTAS SET SALDO = new_saldo, FX_ACTUALIZACION = SYSDATE











También podemos asignar un valor por defecto a los parámetros, utilizando la clausula DEFAULT o el operador de asiganción (:=) .

CREATE OR REPLACE PROCEDURE Actualiza_Saldo(cuenta NUMBER , new_saldo NUMBER DEFAULT 10 ) IS -- Declaracion de variables locales BEGIN -- Sentencia Sen tenciass UPDATE SALDOS_CUENTAS SET SALDO = new_saldo, FX_ACTUALIZACION = SYSDATE WHERE CO_CUENTA = cuenta; END Actualiza_Saldo;

Una vez creado y compilado el pr ocedimiento almacenado almacenado podemos ejecutarlo. Si el sistema nos indica que el pro cedimiento cedimiento se ha creado con errores de compilación compilación podemos ver estos errores de compilación con la orden SHOW ERRORS en SQL *Plus. Existen dos formas de p asar argumentos a un procedimiento almacenado almacenado a la hora de ejecutarlo (en realidad es válido para cualquier subprograma). Estas son: Notación posicional: Se pasan los valores de los parámetros en el mismo orden en que el pro cedure los define. BEGIN Actualiza_Saldo(200501,2500); COMMIT; END; Notación nominal: Se pasan los valores en cualquier orden nombrando explicitamente explicitamente el parámetro. BEGIN Actualiza_Saldo(cuenta => 200501,new_saldo 200501,new_saldo => 2500); COMMIT; END; 3.2) Propósito y ventajas de los procedimientos procedimientos almacenados almacenados

Los procedimientos procedimientos almacenados proporcionan ventajas de performance, un marco de trabajo, trabajo,











limitaciones de privilegios que permiten mantener a los usuarios fuera de la vista de la estructura de la base de datos subyacente, mientras se los habilita a ejecutar procedimientos almacenados almacenados que actúan sobre la base base de datos. Rendimiento

Cada vez que un comando Transact-SQL, o conjunto de comandos, es enviado el servidor para su procesamiento, el servidor debe determinar si el remitente tiene suf icientes privilegios privilegios para ejecutar esos comandos y si los comandos son válidos. Una vez que los permisos y la sintaxis de los comandos se han verificado, SQL Server construye un plan de ejecución para procesar el pedido. Los procedimientos almacenados son más eficientes en parte porque el procedimiento es almacenado en el SQL Server cuando se crea. La sintaxis de los comandos contenidos en un procedimiento almacenado se comprueba que este libre de errores antes de ser guardado. El nombre del procedimiento almacenado se almacena en la tabla SysObjects, mientras que el texto del procedimiento se guarda en la tabla SysComments. Por otro lado, invocar al procedimiento almacenado implica ejecutar un solo comando en vez de cientos de comandos que un procedimiento almacenado almacenado podría contener. La primera vez que se ejecuta el procedimiento, se crea un plan de ejecución y se compila al procedimiento almacenado. Los procesamientos subsecuentes del procedimiento procedimiento almacenado son mucho más rápidos ya que el SQL Server no vuelve a controlar la sintaxis, ni recrea un plan de ejecución, ni se recompila el procedimiento. Por último se verifica el caché por si ya existe un plan de ejecución para ese procedimiento antes de generar un nuevo plan de ejecución. ejecución. La relativa pérdida de rendimiento producida por ubicar los planes de ejecución de los procedimientos almacenados en el caché de procedimiento se reduce ya que los planes de ejecución para todos los comandos SQL se guardan ahora en el caché de procedimientos. Por lo que un comando Transact-SQL tratará de utilizar un plan de ejecución ya existente en todos casos posibles. Marco de programación

Una vez que s e crea un procedimiento almacenado, almacenado, puede ser llamado llamado todas las veces q ue sea necesario. Esta capacidad provee modulación y habilita la reutilización del código. La reutilización del código mejora el mantenimiento de la base de datos al aislar la base de datos de los cambios en las prácticas del negocio. Si las reglas de negocios cambian en una organización, se puede modificar a los procedimientos almacenados para cumplir con las nuevas reglas de negocio. Todas las aplicaciones que llaman a esos procedimientos almacenados almacenados cumplirán con la nuevas reglas, sin tener que ser d irectamente modificados. modificados. Tal y como otros lenguajes de programación, programación, los procedimientos procedimientos almacenados pueden aceptar parámetros de ingreso, retornar parámetros de salida, producir información de retroalimentación de la ejecución en la forma de códigos de estatus y texto descriptivo, y llamar a otros procedimientos. Por ejemplo, un procedimiento almacenado puede retornar un











Los desarrolladores de software pueden escribir sofisticados en un lenguaje como el C++; luego, se puede utilizar un tipo especial de procedimiento almacenado, denominado procedimiento almacenado almacenado extendido, para invocar al programa desde den tro del SQL Server. Ante cualquier tarea simple, se debería escribir un procedimiento almacenado. Mientras más genérico sea el procedimiento más útil será para muchas bases de datos. Por ejemplo; el procedimiento almacenado sp_rename cambia el nombre de un objeto creado por el usuario, tal como una tabla, una columna o un tipo de datos definido por el usuario en la base de datos actual, pudiéndose aplicar a cualquier base de datos. Seguridad

Otro capacidad importante de los p rocedimientos almacenados almacenados es que mejoran la seguridad a través de la encriptación y el aislamiento. Los usuarios de las bases de datos pueden tener permisos de ejecutar un procedimiento almacenado sin tenerlos para acceder directamente a los objetos de la bases de datos sobre las que o pera el procedimiento procedimiento almacenado. Además un procedimiento almacenado almacenado puede ser encriptado cuando se lo crea o modifica inhabilitando inhabilitando a los usuarios a leer los comandos Transact-SQL contenidos en el procedimiento almacenado. Estas capacidad de seguridad permiten aislar la estructura de la base d e datos del usuario d e la base de datos, con la con siguiente ganancia en seguridad. 3.3) Categorías de procedimientos procedimientos almacenados almacenados

En la actualidad existes cinco categorías de procedimientos almacenados, entre los que podemos mencionar:     

Procedimientos almacenados almacenados del d el sistema Procedimientos almacenados locales Procedimientos almacenados temporarios Procedimientos almacenados extendidos Procedimientos almacenados remotos.

o Procedimientos almacenados del sistema Son procedimientos propios del sistema que son almacenados almacenados en la base de datos master y so n identificados con el prefijo sp. Entre las tareas que realizan realizan están la d e soportar aplicaciones aplicaciones externas para datos de las tablas del s istema, procedimientos para la administración administración de base de datos y funciones de seguridad seguridad o Procedimientos almacenados locales Son usualmente almacenados almacenados en una base de datos, diseñadas por el desarrollador de base de datos para tareas comunes propias del sistema y de las necesidades del negocio, también se puede usar para crear tareas personalizadas en base a procedimientos del sistema o Procedimientos almacenados temporarios Son similares a los procedimientos almacenados locales pero existe so lo hasta que se cierre la conexión que lo creo son almacenados en la base de datos TempDb. Hay tres tipos de procedimientos almacenados almacenados temporarios:













Se pueden configurar permisos para ellos. Existen aún después que la conexión que los creó se terminan No son removidos hasta que el SQL Server no sea apagado. apagado. Procedimientos almacenados extendidos

o

Usan un programa externo, compilado como un DLL para expandir las capacidades de un procedimiento almacenado. almacenado. La mayoría mayoría de los procedimientos almacenados almacenados extendidos usan el prefijo xp_ como un convención de nombre. Sin embargo, hay algunos procedimientos almacenados extendidos que comienzan con el prefijo sp_, y hay algunos procedimientos almacenados del sistema que no son procedimientos extendidos y usan el prefijo xp_. Por lo tanto, no se puede depender sobre convención de nombres para identificar procedimientos almacenados almacenados del sistema y procedimientos almacenados extendidos. 3.4) Tipos de proc edimientos almacenados a.

Procedimiento sin parámet parámetros. ros. Como su nombre lo dice son procedimientos que no

necesitan ningún parámetro extra para ser ejecutado Ejemplo: CREATE PROCEDURE PROCEDURE spSumaSinParametros AS --Declaracion de variables DECLARE @Numero1 FLOAT, @Numero2 FLOAT, @Resultado FLOAT, FLOAT, @Operacion NVARCHAR(25) --Asignacion de valores iniciales SET @Numero1 = 5.55 SET @Numero2 = 15

--Operacion. --Nota: Se puede hacer uso de SELECT y/o SET para la asignacion de valores a las variables. SELECT @Resultado = ISNULL(@Numero1, 0) + ISNULL(@Numero2, 0) SET @Operacion = CAST(@Numero1 AS VARCHAR) + ' + ' + CAST(@Numero2 AS VARCHAR) + '











@Resultado FLOAT, FLOAT, @Operacion NVARCHAR(25)

--Operacion. --Nota: Se puede hacer uso de SELECT y/o SET para la asignacion de valores a las variables. SELECT @Resultado = ISNULL(@Numero1, 0) + ISNULL(@Numero2, 0) SET @Operacion = CAST(@Numero1 AS VARCHAR) + ' + ' + CAST(@Numero2 AS VARCHAR) + ' = ' + CAST(@Resultado AS VARCHAR)

--Realizar un select con el resultado de la operacion. SELECT @Numero1 AS Numero1, @Numero2 AS Numero2, @Resultado AS Resultado, @Operacion AS Operacion GO

c.

Procedimiento con parámetros de salida. Como su nombre lo dice son

procedimientos que no necesitan algún parámetro extra para ser ejecutado conocida como variable de salida donde alma almacenar cenar el resultado. Ejemplo: CREATE PROCEDURE spSumaConParametroDeSalida @Numero1 FLOAT, @Numero2 FLOAT, @Resultado FLOAT OUTPUT AS --Declaracion de variables DECLARE @Operacion NVARCHAR(25)

--Operacion. --Nota: Se puede hacer uso de SELECT y/o SET para la asignacion de valores a las variables. SELECT @Resultado = ISNULL(@Numero1, 0) + ISNULL(@Numero2, 0) SET @Operacion = CAST(@Numero1 AS VARCHAR) + ' + ' + CAST(@Numero2 AS VARCHAR) + ' = ' + CAST(@Resultado AS VARCHAR)

--Realizar un select con el resultado de la operacion. SELECT @Numero1 AS Numero1, @Numero2 AS Numero2, @Operacion AS Operacion











4) Procedimientos a seguir para desarrollar los laboratorios

Pasos a seguir: a) Leer la base teórica del ej ercicio , ayudarse del manual o de inf ormación que encuentre en intenet b) Ejecutar las demos planteadas c) Verificar que el resultado sea igual al planteado d) Verificar que se cumplan los objetivos objetivos Mejoras: Para sugerencias y mejoras de los procedimientos procedimientos colgar su respuesta o publicación en nuestro blog y/o wikispace: http://jhacs.blogspot.com http://jhacs.wikispaces.com Códigos Fuentes y Aplicaciones: estarán a su disposición en dicho blog. 5) LABORATORIOS Laboratorio 01 (Lab Básico): Creando y ejecutando un procedimiento procedimiento almacenado

Un procedimiento es un programa el cual es almacenado físicamente en una base de datos. Su implementación varía de un gestor de bases de datos a otro. La ventaja de un procedimiento almacenado es que al ser ejecutado, en respuesta a una petición de usuario, es ejecutado directamente en el motor de bases de datos, el cual usualmente corre en un servidor separado. Como tal, posee acceso directo a los datos que necesita manipular y sólo necesita enviar sus resultados de regreso al usuario, deshaciéndose de la sobrecarga resultante de comunicar grandes cantidades de datos salientes y entrantes Trabajaremos con las tablas Clientes y Empleado











En este ejemplo usaremos la base de datos: Eurekabank

Procedemos a crear un nuevo procedimiento : Usando la tabla cliente

Al finalizar finalizar el código usamos: exec ejemplo01 el cual indicara que el procedimiento procedimiento a sido realizado realizado correctamente…











Lab 1.1 Ahora crearemos y ejecutaremos un nuevo Procedimiento Procedimiento usando parámetros: Usando Usando tabla Empleado

Laboratorio 02: Modificando un proc edimiento edimiento almacenad al macenado o ya existente

En la siguiente imagen se muestra un pr ocedimiento almacenado almacenado sin parámetros.













Ahora mostramos un procedimiento procedimiento almacenado con p arámetros arámetros lo cual vamos a enseñarles a modificar a continuación. Este es el procedimiento denominado datos_clientes y pasamos como parámetros @identificador Para mostrar el cliente según su código lo cual vamos a modificar para que nos muestre todos los cliente según el código de ciudad











Laboratorio 03 (Lab Intermedio): Intermedio): Creando un procedimiento procedimiento almacenado para insertar registros Vamos a crear un procedimiento almacenado almacenado para insertar empleados. 

Creación del procedimiento procedimiento almacenado.













Ahora verificamos el registro insertado











Podremos crear Procedimientos Almacenados de 2 maneras: 1. PODEMOS CREAR UN PROCEDIMIENTO ALMACENADO PARA PODER USARLO CUANTOS VECES QUERAMOS.











2. Podriamos haberlo creado de la siguiente manera. Esto es mas que nada para saber si se ingresa o No un valor.













Nos queda de esta manera:











begin print 'No existe este codigo en el registro de empleados' return 2 end --- realizamos la consulta set @sucursal= @sucursal = (select s.vch_sucunombre+ vch_sucunombre + ',' ','+ + s.vch_sucudireccion + ' ' + s.vch_sucuciudad from Sucursal as s inner join Asignado as a on s.chr_sucucodigo= chr_sucucodigo =a.chr_sucucodigo inner join Empleado as e on e.chr_emplcodigo= chr_emplcodigo =a.chr_emplcodigo where e.chr_emplcodigo= chr_emplcodigo =@cod) @cod) return 0 go -----------------Hasta aqui el procedimienteto ---Ahora lo ejecutamos











-- No diferencia mayúsculas ni minúsculas en nombre de la variable

--el

A continuacion tenemos un ejemplo de variables usadas dentro de los procedimientos almacenados almacenados

--El procedimiento nos almacena un nuevo cliente --solicitamos los datos necesarios para realizar el insert into con los --paramatros y al final final devolveremos el el codigo que se a generado en base --a un contador que llevamos desde una table llamada contador y lo --transformaremos a char(carácter) create procedure usp_insertarcliente @vch_cliepaterno varchar varchar( (25)=null, 25)=null, @vch_cliematerno varchar varchar( (25)=null, 25)=null, @vch_clienombre varchar varchar( (30)=null, 30)=null, @chr_cliedni char char( (8)=null, @vch_clieciudad varchar varchar( (30)=null, 30)=null,











--ahora crearemos el codigo de cliente que sera un char(5) donde --trendremos el numero de cliente que se genera y llenaremos los espacios -- con ceros set @chr_cliecodigo =RIGHT( =RIGHT('00000' '00000'+ +CONVERT CONVERT( (varchar varchar( (5), ),@int_contitem @int_contitem), ),5 5) --ahora junto a los datos que obtuvimos de los parametros y el codigo que --generamos realizaremos el insert into la tabla cliente begin transaction insert into Cliente( Cliente(chr_cliecodigo, chr_cliecodigo ,vch_cliepaterno, vch_cliepaterno ,vch_cliematerno, vch_cliematerno ,vch_clienomb re, re, chr_cliedni, chr_cliedni ,vch_clieciudad, vch_clieciudad ,vch_cliedireccion vch_cliedireccion, ,vch_clietelefono vch_clietelefono, ,vch_c lieemail) lieemail )











--en la variable @rtn esta ves guardaremos el return que es un valor --entero que nos indicara a que parte del procedimiento se llego mediante --los datos insertados execute @rtn= @rtn=usp_insertarcliente @pat, @pat,@mat, @mat,@nom, @nom,@dni, @dni,@ciudad, @ciudad ,@direccion, @direccion ,@telefono, @telefono ,@email, @email,@cod output

select when 0 when 1 when 2 when 3

estado =CASe @rtn then 'proceso ok' then 'no se permite nulos' then 'no c suma cliente' then 'no c ingreso demanera correcta'

end, end ,[codigo]= [codigo]=@cod













6) CONCLUSIONES

Con los laboratorios antes realizados ,se ha podido notar la manera de encapsular sentencias SQL para operaciones frecuentes en sistemas de información y su utilidad que ello conlleva. Si bien en estos laboratorios no se ha podido mostrar una comparativa comparativa con respecto a tiempos de ejecución, ejecución,

Procedimiento Almacenado

Recommend Documents