Guia lab11 bd

Universidad Nacional Jorge Basadre Grohmann /FACIEscuela Académico Profesional de Ingeniería en Informática y SistemasCurso: Bases de Datos y Sistemas Distribuidos Año :IIIPeriodo Académico: 2007

Prof: Ing. Edgar Taya Acosta-http://www.edgartaya.net - [email protected] Pág. 1/7 Ing. Edwin Ramos Velásquez

Guía de práctica de laboratorio 11Tema: Procedimientos almacenados

OBJETIVOS Programación de lenguaje SQL con Procedimientos almacenados: Creación,

Modificación, Eliminación, Permisos. Ejecución. Parámetros (Input/Output).Tipo de datos. Declaración de Variables. Variables locales y globales

Gestión de Objetos Temporales

1.1.Ejemplo1: Realice un procedimiento, que permita buscar Proveedores deun determinado País. La información a obtener es: nombre del proveedor,ciudad, teléfono y código postal. El nombre del país es indicado por elusuario.

1.2.En el administrador Corporativo, seleccionar su base datos y ubícate en lasección Procedimientos Almacenados. Ahora ir al menú Herramientas/Analizador de Consultas. Usted debe conectarse con el usuario01 paraque pueda crear el procedimiento almacenado. Verificar que esteconectado a su base de datos correspondiente:

1.3.Nota: No se olvide de guardar el código del procedimiento (archivo .sql) y elresultado (archivo .rpt) en su disco de trabajo.

1.4.Creación del Procedimiento: Tipear el siguiente código

CREATE PROCEDURE dbo.usp_BuscarProveedorPorPais@Pais varchar(40)ASSELECT Nombre, Direccion_1, Region, TelefonoFROM ProveedorWHERE Pais = @PaisIf @@RowCount = 0

Begin PRINT 'No se encontraron proveedores en este país' RETURN -1 /*Devolver código de estado */

EndRETURN 0

1.5.Compilación del procedimiento: Seleccionar todo el procedimiento y luegopulse la tecla F5 para compilar y almacenarlo en el servidor de base dedatos.

1.6.Si existen errores en la compilación, corrige estos errores con la ayuda delprofesor.

1.7.En caso de que vea el mensaje de error: "Permiso CREATEPROCEDURE denegado en la base de datos 'DistribComerXXX'." Consultea su profesor sobre como puede otorgar permisos al Usuario01 para quepueda CREAR ó MODIFICAR procedimientos.

http://www.edgartaya.net

[email protected]

@Pais

@Pais

@RowCount



1.8.Nota: Si desea modificar el procedimiento Usted puede usar el comandoALTER: (solo debe cambiar la palabra CREATE por ALTER y volver acompilar el procedimiento)

ALTER PROCEDURE dbo.usp_BuscarProveedorPorPais

1.9.Ejecución del procedimiento: Ahora debe probar si funciona correctamente,por ejemplo buscar los proveedores de "Japón".

EXEC usp_BuscarProveedorPorPais 'Japón'

1.10. ¿Buscar los proveedores de los siguientes Países?; Canadá, EE.UU,Francia, Reino Unido

1.11. Ahora abrir otra ventana del analizador de consultas, y conéctate conel usuario02 (Usuario limitado)

1.12. Intente ejecutar el procedimiento almacenado con el comando:

EXEC usp_BuscarProveedorPorPais 'Canadá'

1.13. ¿Puede ejecutar el procedimiento almacenado?1.14. ¿Cómo se puede otorgar permisos al usuario02 para que pueda

ejecutar este procedimiento almacenado?. ¿Cómo se realiza elotorgamiento de permisos en el Administrador corporativo? y ¿Quécomando usaría en el Analizador de consultas para dar permisos?

1.15. Ejemplo2: Crear un procedimiento, que permita buscar undeterminado empleado por su código. El código se debe transferircomo parámetro INPUT del procedimiento. Si existe el código se debemostrar la siguiente información: Apellidos y Nombres (en una solacolumna), Teléfono, Edad y Obs. En la columna Obs de debemostrar según su edad si es "Menor de Edad" ó "Empleado Joven" o"Empleado Adulto". En caso de que no exista se debe mostrar unmensaje apropiado.

1.16. Creación del Procedimiento: Tipear el siguiente código

CREATE PROCEDURE usp_BuscarEmpleado@CodEmp int = 0-- Valor inicial será CEROAS IF Exists ( SELECT cod_empleado FROM empleado WHEREcod_empleado = @CodEmp)Begin

SELECT [Apellidos y Nombres] = Apellidos + ', '+ Nombres ,Telefono = Tfno_Particular , Edad =(datediff(month,Fecha_Nacimiento,GetDate()))/12 , Obs = CASE WHEN(datediff(month,Fecha_Nacimiento,GetDate()))/12 < 18


[email protected]

@CodEmp

@CodEmp



THEN 'Es menor de edad' WHEN(datediff(month,Fecha_Nacimiento,GetDate()))/12 < 25THEN 'Empleado Joven'

ELSE 'Empleado Adulto' END FROM empleado WHERE cod_empleado = @CodEmpEnd

ELSE Begin PRINT 'No existe el empleado con el codigoindicado..:'+Str(@CodEmp) RETURN -1 /*Devolver codigo de estado */ EndRETURN 0

1.17. Compilación del procedimiento: Seleccionar todo el procedimiento yluego pulse la tecla F5 para compilar y almacenarlo en el servidor debase de datos.

1.18. Si existen errores en la compilación, corrige estos errores con la ayudadel profesor.

1.19. Ejecución del procedimiento: Ahora debe probar si funcionacorrectamente, ejecutando lo siguiente

EXEC usp_BuscarEmpleado 7

1.20. Resultado:

Apellidos y Nombres Telefono Edad Obs-------------------------------- ------------------King, Robert 715555598 44 Empleado Adulto

(1 filas afectadas)

1.21. Ahora responda las siguientes preguntas: Consulte a su profesor encaso que tenga dudas.

1.21.1. ¿Cuántos parámetros tiene el procedimiento y de que tipo dedatos es?

1.21.2. ¿Qué cláusulas SQL usamos?1.21.3. ¿Qué operadores usamos?1.21.4. ¿Cuántas columnas son físicas y cuantas columnas son

calculadas?1.21.5. ¿Qué realiza RETURN y PRINT?1.21.6. ¿Para que sirve CASE?1.21.7. ¿En qué parte del procedimiento existen comentarios?


[email protected]

@CodEmp

@CodEmp



1.21.8. ¿Qué permite BEGIN y END?1.22. Ahora abrir ó seleccionar la otra ventana del analizador de consultas

donde esta conectado el usuario02 (Usuario limitado)1.23. Intente ejecutar el procedimiento almacenado con el comando:

EXEC usp_BuscarEmpleado 7

1.24. Intente ejecutar el procedimiento almacenado con el comando:¿Puede ejecutar el procedimiento almacenado? ¿Qué tiene querealizar para que pueda ejecutarlo?

2.3. Ejemplo3. Realice un procedimiento que permita devolver una listado deClientes que pidieron un determinado producto y en el mes indicado por elusuario (Transferir estos datos como parámetros). Se debe obtener: Códigodel Cliente, Nombre del cliente, Fecha de pedido y la Precio unitario. Elresultado debe estar ordenado por la fecha de pedido.a) Creación del Procedimiento: Tipear el siguiente código

CREATE PROCEDURE dbo.usp_PedidoClienteProducto@CodProd int, @Mes int = 1AS SELECT Pedido.Cod_Cliente, Cliente.Nombre_cliente, Pedido.Fecha_Pedido, Detalle_pedido.Precio_por_unidad FROM Pedido INNER JOIN Cliente ON Pedido.Cod_cliente = Cliente.cod_cliente INNER JOIN Detalle_Pedido ON Pedido.Id_pedido = Detalle_pedido.Id_pedido WHERE Detalle_Pedido.cod_producto = @CodProd AND Month(Pedido.Fecha_pedido) = @Mes ORDER BY Pedido.Fecha_pedidoRETURN 0

b) Compilación del procedimiento: Seleccionar todo el procedimiento yluego pulse la tecla F5 para compilar y almacenarlo en el servidor de basede datos.

• Si existen errores, consulte a su profesor.

c) Ejecución del procedimiento: Ahora debe probar si funcionacorrectamente, ejecutando lo siguiente

• Ahora ejecútelo. Por ejemplo para el producto que tiene codigo "1101" y enel mes de Febrero


[email protected]

@CodProd

@Mes

@CodProd

@Mes



EXEC usp_PedidoClienteProducto 1101, 2

• RESULTADO:

Cod_Cliente Nombre_cliente Fecha_Pedido Precio_por_unidad----------- --------------------- -------------------------- ----------------- 30 Spokes for Folks 2002-02-19 00:00:00.000 14.5000 57 Tek Bikes 2002-02-22 00:00:00.000 14.5000 33 Fulcrum Cycles 2003-02-18 00:00:00.000 14.5000 58 Making Tracks 2003-02-18 00:00:00.000 14.5000

(4 filas afectadas)

• Ahora ejecute el procedimiento de otra forma. ¿Explique el resultado?

EXEC usp_PedidoClienteProducto 1101, DEFAULT

d) Ahora responda las siguientes preguntas: Consulte a su profesor en casoque tenga dudas.• ¿Cuántos parámetros tiene el procedimiento y de que tipo de datos es?• ¿Cuántas columnas son físicas y cuantas columnas son calculadas?• ¿Qué permite DEFAULT?.

e) Ahora abrir ó seleccionar la otra ventana del analizador de consultas dondeesta conectado el usuario02 (Usuario limitado)• Intente ejecutar el procedimiento almacenado con el comando:• ¿Puede ejecutar el procedimiento almacenado? ¿Qué tiene que realizar paraque pueda ejecutarlo?

2.4. Ejemplo4. Crear un procedimiento almacenado similar al ejemplo2, peroahora la información se debe retornar por parámetrosOUTPUT.

a) Creación del Procedimiento: Tipear el siguiente código

CREATE PROCEDURE dbo.usp_BuscarEmpleado2 @CodEmp int = 0, @ApeNom varchar(40) OUTPUT, @TelEmp Varchar(10) OUTPUT, @EdadEmp Numeric(2) OUTPUT, @ObsEdad varchar(25) OUTPUT AS IF Exists ( SELECT cod_empleado FROM empleado WHEREcod_empleado = @CodEmp)Begin

SELECT @ApeNom = Apellidos + ', ' + Nombres , @TelEmp = Tfno_Particular , @EdadEmp = Year(GetDate()) - Year(Fecha_Nacimiento) ,


[email protected]

@CodEmp

@ApeNom

@TelEmp

@EdadEmp

@ObsEdad

@CodEmp

@ApeNom

@TelEmp



@ObsEdad = CASE WHEN Year(GetDate()) -Year(Fecha_Nacimiento) < 18 THEN 'Es menor de edad' WHENYear(GetDate()) - Year(Fecha_Nacimiento) < 25 THEN 'EmpleadoJoven' ELSE 'Empleado Adulto'

END FROM empleado WHERE cod_empleado = @CodEmpEnd

ELSE Begin PRINT 'No existe el empleado con el codigoindicado..:'+Str(@CodEmp) RETURN -1 /*Devolver codigo de estado */ EndRETURN 0

b) Compilación del procedimiento: Seleccionar todo el procedimiento y luegopulse la tecla F5 para compilar y almacenarlo en el servidor de base de datos.

• Si existen errores, consulte a su profesor.

c) Ejecución del procedimiento: Ahora debe probar si funciona correctamente,ejecutando lo siguiente

• Ahora ejecútelo.

DECLARE @ApeNom Varchar(40), @Tel Varchar(10) DECLARE @EdadNumeric(2), @Obs Varchar(25)

EXEC usp_BuscarEmpleado2 7, @ApeNom OUTPUT, @Tel OUTPUT, @EdadOUTPUT, @Obs OUTPUT Select @ApeNom as [Apellidos y Nombres], @Telas Telefono, @Edad as Edad, @Obs as Observ

• RESULTADO:

Apellidos y Nombres Telefono Edad Observ---------------------------------------- ------King, Robert 715555598 44 Empleado Adulto

(1 filas afectadas)

d) Ahora responda las siguientes preguntas: Consulte a su profesor en casoque tenga dudas.• ¿Dónde se almacenan los Procedimientos Almacenados y donde losprocedimientos temporales?• ¿Cómo puede modificar un procedimiento desde el Administrador corporativo?

@EdadEmp


[email protected]

@ObsEdad

@CodEmp

@CodEmp

@ApeNom

@Tel

@Edad

@Obs

@ApeNom

@Tel

@Edad

@Obs

@ApeNom

@Tel

@Edad

@Obs



• ¿Cuántos parámetros tiene el procedimiento y de que tipo de datos es?• ¿Qué permite DECLARE?.

e) Ahora abrir ó seleccionar la otra ventana del analizador de consultas dondeesta conectado el usuario02 (Usuario limitado)• Intente ejecutar el procedimiento almacenado con el comando:• ¿Puede ejecutar el procedimiento almacenado? ¿Qué tiene que realizar paraque pueda ejecutarlo?

2.5. Ejercicios para ser realizado por el alumno, con el apoyo del Profesor:.

a) Realice un procedimiento que permita mostrar los Productos solicitados porun determinado Cliente, solo se debe mostrar los productos donde la cantidad depedido es menor a 3: Se debe obtener Código del producto, Fecha de pedido,Nombre del producto, precio unitario, Cantidad pedida.

b) Realice un procedimiento que muestre el total de clientes que piden cadaproducto en un determinado trimestre. Si el total de clientes de un producto esmenor a 5 se debe mostrar en una columna de Observaciones el texto "PocosClientes", en otro caso esta columna debe mostrar "Muchos clientes"

Sugerencia: puede usar Tablas Temporales o Físicas en la consulta. Consulte alprofesor para más información.

c) Crear un procedimiento que permita agregar un nuevo registro en latabla Pedido. Los nuevos datos se deben transferir como parámetros. Se debeverificar si ocurrieron errores, es decir, debe comprobar si los campos de tipo FKtienen un valor valido para cumplir con la integridad referencial.

III.- Información Adicional3.1 Que realiza los siguientes comandos (Ejecutar con el usuario01)

GRANT INSERT, UPDATE, DELETE ON ClienteTO Edgar02, Elvis02, Walter02

GO

GRANT CREATE PROCEDURE TO Edgar02

3.2 Que realiza la siguiente siguiente comando (Ejecutar con el usuario01)

GRANT EXECON dbo.usp_BuscarEmpleadoTO Edgar02


[email protected]

Guia lab11 bd

Documents

Transcript of Guia lab11 bd