practica seis delphi

I.S.T. “F.P.G.V.” Computación e Informática Programación Distribuida y Concurrente

Docente: Lic. José Luis Ponce Segura www.vigilvirtual.com Prac06 (1 de 8)

GUÍA DE LABORATORIO N° 06

OBJETIVOS El alumno podrá utilizar procedimientos almacenados para realizar: • Consultas en SQL Server • Actualizaciones de datos METODOLOGÍA • El alumno ingresará al administrador Analizador de Consultas de SQL Server identificándose previamente (por

ejemplo como UsuarioBD, con su password respectivo). • El alumno creará procedimientos almacenados en SQL Server 2000 según el mismo formato que sintaxis que

especifica SQL Server. • El alumno ejecutará las consultas SQL Server desde el mismo Analizador de consultas SQL (abierto desde el

Administrador Corporativo de SQL Server). SECCION 01: PROCEDIMIENTOS DE INICIALIZACION DE LA CONEXIÓN DESDE EL ANALIZADOR DE

CONSULTAS DE SQL SERVER 1. Ir al menú Inicio!Programas!Microsoft SQL Server!Analizador de consultas, y activarla 2. Al Conectarse con el servidor Serverxxx se le mostrará una ventana de diálogo similar a la que se muestra a

continuación.

3. Conectarse con la base de datos Sucursalxxx. Seleccionarla como base de datos activa. La ventana del administrador de consultas se asemejará a la que se muestra a continuación:

4. El administrador de Consultas SQL está operativo para realizar consultas SQL

PasswordUsuario

Ejecutar consultaBase de datos

activa

Nueva consulta

Aventana de edición

Analizar sintaxis



SECCION 02: CREACIÓN DEL PROCEDIMIENTO ALMACENADO usp_BuscarEmpleadoPorCod (codEmp: Integer)

1. Presionar el botón Nueva Consulta, y en la ventana de edición, escribir lo siguiente:

CREATE PROCEDURE dbo.usp_BuscarEmpleadoPorCod_ASQL @CodEmp INT /* Parámetros de entrada */ AS IF EXISTS(SELECT cod_empleado FROM empleado WHERE cod_empleado=@CodEmp) BEGIN SELECT Apellidos, Nombres, Cargo, Fecha_contratacion, Años_servicio = YEAR(GETDATE()) - YEAR(Fecha_contratacion) FROM empleado WHERE cod_empleado = @CodEmp END ELSE BEGIN PRINT 'No existe el codigo de empleado que buscas...:' + CAST(@CodEmp as varchar(6)) RETURN -1 /*Devolver codigo de estado */ END RETURN 0

2. Guardar el archivo como BuscarEmpleadoPorCod_ASQL.sql 3. Ejecutar la consulta (presionar el botón Ejecutar consulta )

Se habrá creado con éxito el procedimiento almacenado usp_BuscarEmpleadoPorCod_ASQL 4. Presionar el botón Nueva Consulta, y en la ventana de edición, escribir lo siguiente:

CREATE PROCEDURE dbo.usp_BuscarEmpleadoPorCod @CodEmp INT /* Parámetros de entrada */ AS SELECT Apellidos, Nombres, Cargo, Fecha_contratacion, Años_servicio = YEAR(GETDATE()) - YEAR(Fecha_contratacion) FROM empleado WHERE cod_empleado = @CodEmp RETURN 0

5. Guardar el archivo como BuscarEmpleadoPorCod.sql 6. Presionar el botón Analizar consulta. Corrija los errores si lo hubieran. 7. Ejecutar la consulta (presionar el botón Ejecutar consulta)

Se habrá creado con éxito el procedimiento almacenado usp_BuscarEmpleadoPorCod a) Ejecución del procedimiento almacenado usp_BuscarEmpleadoPorCod_ASQL

Abra una nueva ventana para ejecutar el procedimiento con instrucciones por ejemplo: Probamos como funciona el procedimiento remoto:

Buscaremos el empleado que tiene por código el nro. 8 Exec usp_BuscarEmpleadoPorCod_ASQL 8

El resultado de este procedimiento almacenado es similar a: Apellidos Nombres Cargo Fecha_contratacion Años_servicio ---------- ------- ----------------------------- ---------------------- ----------- Callahan Laura Coordinador Interno de Ventas 1993-01-30 00:00:00.000 12

Buscaremos el empleado que tiene por código el nro. 6

Exec usp_BuscarEmpleadoPorCod_ASQL 6

El resultado de este procedimiento almacenado es similar a: Apellidos Nombres Cargo Fecha_contratacion Años_servicio ---------- ------- ------------------------- ---------------------- ------------ Suyama Michael Representante de Ventas 1992-09-13 00:00:00.000 13

b) Ejecución del procedimiento almacenado usp_BuscarEmpleadoPorCod

Ejecutar la consulta BuscarEmpleadoPorCod.sql y compare los resultados con la anterior consulta. ¿Cuál es la diferencia en los resultados de ambos procedimientos almacenados? ¿Por qué utilizaríamos la instrucción PRINT en un procedimiento almacenado mientras que en otro no es utilizado?

SECCION 03: CREACIÓN DEL PROCEDIMIENTO ALMACENADO

usp_BuscarEmpleadoPorApeNom (Filtro: String[70])



1. Presionar el botón Nueva Consulta, y en la ventana de edición, escribir lo siguiente: CREATE PROCEDURE dbo.usp_BuscarEmpleadoPorApeNom @Filtro VARCHAR(70) AS SELECT codigo = cod_empleado, ApeNom = Apellidos + ', ' + Nombres, Tfno_particular, Fecha_Nacimiento FROM empleado WHERE apellidos LIKE '%' + @Filtro + '%' OR nombres LIKE '%' + @Filtro + '%' RETURN 0

2. Presionar el botón Analizar consulta. Corrija los errores, si los hubiera. 3. Guardar el archivo como BuscarEmpleadoPorApeNom.sql 4. Ejecutar la consulta (presionar el botón Ejecutar consulta)

Se habrá creado con éxito el procedimiento almacenado usp_BuscarEmpleadoPorApeNom

5. Abra una nueva ventana para ejecutar el procedimiento con instrucciones por ejemplo: Probamos como funciona el procedimiento remoto:

Buscaremos los empleados cuyo nombre o apellido empieza con las letras “pe” EXEC usp_BuscarEmpleadoPorApeNom pe

El resultado de este procedimiento almacenado es similar a: Codigo ApeNom tfno_Particular Fecha_Nacimiento ------ ----------------- ---------------- ------------------------ 4 Peacock, Margaret 2065558122 1937-09-19 00:00:00.000 15 Pereira, Laurent 88010168 1965-12-09 00:00:00.000

Buscaremos los empleados cuyo nombre o apellido empieza con las letras “LA” EXEC usp_BuscarEmpleadoPorApeNom LA

El resultado de este procedimiento almacenado es similar a: Codigo ApeNom tfno_Particular Fecha_Nacimiento ------ ----------------- ---------------- ------------------------ 8 Callahan, Laura 2065551189 1958-01-09 00:00:00.000 15 Pereira, Laurent 88010168 1965-12-09 00:00:00.000

SECCION 04: CREACIÓN DEL PROCEDIMIENTO ALMACENADO

usp_EstadPedXProducto (Anio: String[4]) 1. Presionar el botón Nueva Consulta, y en la ventana de edición, escribir lo siguiente:

CREATE PROCEDURE dbo.usp_EstadPedXProducto @Anio CHAR(4) AS SELECT TOP 10 d.cod_producto, Total=SUM(Cantidad), a.Nombre_producto FROM detalle_pedido d INNER JOIN pedido p ON d.id_pedido = p.id_pedido INNER JOIN producto a ON d.cod_producto = a.cod_producto WHERE YEAR(fecha_pedido) = CAST(@Anio AS INTEGER ) GROUP BY d.cod_producto, a.nombre_producto ORDER BY 2 DESC RETURN 0

2. Presionar el botón Analizar consulta. Corrija los errores, si los hubiera. 3. Guardar el archivo como EstadPedXProducto.sql 4. Ejecutar la consulta (presionar el botón Ejecutar consulta)

Con esto se habrá creado con éxito el procedimiento almacenado usp_EstadPedXProducto




Buscaremos los 10 primeros artículos vendidos según sus cantidades totales el año 2002 EXEC usp_EstadPedXProducto 2002

El resultado de este procedimiento almacenado es similar a: Cod_Producto Total Nombre_Producto ----------- ------ ----------------------------- 3305 15 Guardian Mini Lock 4105 14 InFlux Lycra Glove 301151 14 SlickRock 2202 13 Triumph Pro Helmet 401002 13 Mini Nicros 1101 12 Active Oudoors Crochet Glove 1111 11 Active Oudoors Lycra Glove 303182 11 Nicros 2204 10 Triumph Pro Helmet 2209 10 Triumph Vertigo Helmet

SECCION 05: CREACIÓN DEL PROCEDIMIENTO ALMACENADO usp_EstadMejorClientePorProducto (Anio: String[4])


CREATE PROCEDURE dbo.usp_EstadMejorClientePorProducto @Anio INT = 0 As SET NOCOUNT ON -- Seleccionar los clientes del mes segun Cantidad de Producto Pedido SELECT d.cod_producto, p.cod_cliente, Total = SUM(d.cantidad), c.Nombre_cliente INTO #TotalCantPedido FROM Detalle_pedido d INNER JOIN Pedido p ON d.id_pedido = p.id_pedido INNER JOIN Cliente c ON p.cod_cliente = c.cod_Cliente WHERE YEAR(p.fecha_pedido) = @Anio GROUP BY p.cod_cliente, d.cod_producto, c.Nombre_cliente ORDER BY p.cod_cliente, d.cod_producto, 3 DESC -- Seleccionar solo los productos del Año SELECT d.cod_producto INTO #Productos FROM Detalle_pedido d INNER JOIN Pedido p ON d.id_pedido = p.id_pedido WHERE YEAR(p.fecha_pedido) = @Anio GROUP BY d.cod_producto ORDER BY d.cod_producto CREATE TABLE #LosMejores ( Cod_producto INT NULL, Cod_cliente INT NULL, Total_pedido DECIMAL(5,2) NULL, Nom_cliente CHAR(50) NULL ) -- DECLARE @CodProd INT -- DECLARE Cursor_Prod CURSOR SCROLL FOR SELECT Cod_producto FROM #Productos OPEN Cursor_Prod FETCH FIRST FROM Cursor_Prod INTO @CodProd WHILE @@FETCH_STATUS = 0 BEGIN INSERT #LosMejores (Cod_producto, Cod_cliente, Total_pedido, Nom_cliente) ( SELECT Top 3 Cod_producto, Cod_cliente, Total, Nombre_cliente FROM #TotalCantPedido WHERE Cod_producto = @CodProd ) FETCH NEXT From Cursor_Prod INTO @CodProd END CLOSE Cursor_Prod DEALLOCATE Cursor_Prod -- SET NOCOUNT OFF SELECT * FROM #LosMejores ORDER BY Cod_producto, Total_pedido DESC RETURN 0

2. Presionar el botón Analizar consulta. Corrija los errores, si los hubiera. 3. Guardar el archivo como usp_EstadMejorClientePorProducto.sql 4. Ejecutar la consulta (presionar el botón Ejecutar consulta)



Con esto se habrá creado con éxito el procedimiento almacenado usp_EstadMejorClientePorProducto


Buscaremos los 10 primeros artículos vendidos según sus cantidades totales el año 2002 EXEC usp_EstadMejorClientePorProducto 2002

El resultado de este procedimiento almacenado es similar a: Cod_Producto Cod_Cliente Total_Pedido Nom_Cliente ----------- ------------- ------------ ----------------- 1 1101 30 3.00 Spokes for Folks 2 1101 25 2.00 Extreme Cycling 3 1101 1 1.00 City Cyclists 4 1102 29 2.00 Blazing Bikes ….. ….. 239 402002 18 3.00 Bikes and Trikes 240 402002 33 3.00 Fulcrum Cycles 241 402002 15 1.00 The Bike Cellar

SECCION 06: CREACIÓN DEL PROCEDIMIENTO ALMACENADO usp_LeerProducto 1. Presionar el botón Nueva Consulta, y en la ventana de edición, escribir lo siguiente:

CREATE PROCEDURE dbo.usp_LeerProducto AS SET NOCOUNT ON DECLARE Producto_cursor CURSOR FOR SELECT * FROM producto OPEN Producto_cursor /*Abre el cursor*/ FETCH Producto_cursor /*mueve el cursor al siguiente registro*/ WHILE @@FETCH_STATUS = 0 BEGIN FETCH Producto_cursor /*mueve el cursor al siguiente registro*/ END CLOSE Producto_cursor DEALLOCATE Producto_cursor

2. Presionar el botón Analizar consulta. Corrija los errores, si los hubiera. 3. Guardar el archivo como LeerProducto.sql 4. Ejecutar la consulta (presionar el botón Ejecutar consulta) 5. Probarlo ejecutando la instrucción:

EXEC usp_LeerProducto SECCION 07: CREACIÓN DE LA VISTA Lista_Personas 1. Presionar el botón Nueva Consulta, y en la ventana de edición, escribir lo siguiente:

CREATE VIEW dbo.vw_Lista_Personas AS SELECT cod_Persona = 'P'+ CAST(Cod_Proveedor AS VARCHAR(5)), Nombre, Dirección = Direccion_1, tipo = 'Proveedor' FROM Proveedor UNION ALL SELECT cod_Persona = 'C' + CAST (Cod_Cliente AS VARCHAR(5)), Nombre = Nombre_Cliente, Direccion=Direccion_1, tipo='Cliente' FROM Cliente UNION ALL SELECT cod_Persona = 'E' + CAST(Cod_Empleado AS VARCHAR(5)), Nombre = Apellidos + ', ' + Nombres, Direccion, tipo='Empleado' FROM Empleado

2. Presionar el botón Analizar consulta. Corrija los errores, si los hubiera. 3. Guardar el archivo como VistaLista_Personas.sql 4. Ejecutar la consulta (presionar el botón Ejecutar consulta) 5. Probarlo ejecutando:

SELECT * FROM vw_Lista_Personas NOTA: Note que una vista es tratada de la misma forma que una tabla al momento de seleccionar sus datos para mostrarlo.

SECCION 08: CREACIÓN DE LA VISTA vw_10Mejores_Clientes




CREATE VIEW dbo.vw_10Mejores_Clientes AS SELECT TOP 10 C.Cod_Cliente, Nombre = C.Nombre_Cliente, MontoComprado = SUM(P.Sub_Total) FROM Cliente C INNER JOIN Comprobante_Pago P ON C.cod_Cliente = P.cod_Cliente GROUP BY C.Cod_Cliente, C.Nombre_Cliente ORDER BY MontoComprado DESC, C.Cod_cliente

2. Presionar el botón Analizar consulta. Corrija los errores, si los hubiera. 3. Guardar el archivo como vw_10Mejores_Clientes.sql 4. Ejecutar la consulta (presionar el botón Ejecutar consulta) 5. Probarlo ejecutando:

SELECT * FROM vw_10Mejores_Clientes

El resultado de esta consulta es similar al que se muestra Cod_Cliente Nombre MontoComprado ----------- ----------- -------------- 49 Rocky Roadsters 89764.7800 26 Blazing Saddles 65017.6300 38 Tyred Out 63961.2900 22 Crank Components 61962.1600 39 Wheels Inc. 60736.1500 97 Bicyclette Bourges Nord 60732.6500 72 Cycle City Rome 56420.4400 74 Fahrkraft Räder 56203.3400 54 Cyclopath 53274.3600 64 SAB Mountain 52034.5900

SECCION 9: MANIPULACIÓN DE CURSORES Utilización de cursores a) En el siguiente ejemplo mostraremos la capacidad de desplazamiento a través de las filas de los resultados

generados por un cursor: Ejecute las siguientes instrucciones una por una y verifique el resultado de cada una: DECLARE cliente_cursor SCROLL CURSOR FOR SELECT cod_cliente, nombre_cliente, pais FROM cliente OPEN cliente_cursor

A continuación ejecute cada uno de las siguientes batchs y observe el resultado de cada uno -- primera fila del cursor – Ejecución 1 FETCH NEXT FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO



-- última fila del cursor – Ejecución 2 FETCH LAST FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO -- la misma fila anterior – Ejecución 3 FETCH RELATIVE 0 FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO -- primera fila del cursor – Ejecución 4 FETCH FIRST FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO -- siguiente fila (segunda fila) – Ejecución 5 FETCH NEXT FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO -- La sexta fila del cursor – Ejecución 6 FETCH ABSOLUTE 6 FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO -- Borra de la tabla la sexta fila del cursor – Ejecución 7 DELETE FROM cliente WHERE CURRENT OF cliente_cursor GO -- no existen datos para la sexta fila – Ejecución 8 FETCH RELATIVE 0 FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO -- Leer la fila anterior – Ejecución 9 FETCH PRIOR FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO -- la fila no existe en el cursor – Ejecución 10 -- (Ojo: No hay 7200 filas en la tabla clientes) FETCH ABSOLUTE 7200 FROM cliente_cursor SELECT "Estado del cursor " = @@FETCH_STATUS GO -- cerrar y eliminar el cursor el cursor – Ejecución 11 CLOSE cliente_cursor DEALLOCATE cliente_cursor GO

b) Creación y utilización de un cursor desde un Stored Procedure Revise la implementación de la rutina de creación del procedimiento usp_LeerProducto Ejecute el procedimiento con la instrucción: EXEC usp_LeerProducto

TAREAS Escriba un procedimiento almacenado: 1. Que devuelva el número total de cantidades vendidas

Formato: Cod_Producto – Nombre_Producto – CantidadVendida – Nombre_Tipo_producto 2. Que devuelva una lista de los 30 primeros registros de Comprobantes de pago registrados el año XXXX (ejemplo 2004)

Formato: Nro_Comprobante – Total – fecha_emision 3. Que muestre todos los nombres de los clientes que realizaron algún tipo de pedido

Formato: Nombre_Cliente – Nro_Pedido – fecha_emision – Total 4. Que muestre todos los nombres de los clientes que realizaron algún tipo de compra

Formato: Nombre_Cliente – Nro_Comprobante – fecha_emision – Total 5. Que muestre todos los nombres de los empleados que registraron algún tipo de pedido

Formato: NombresyApellido – Nro_Comprobante – fecha_emision – Total 6. Que muestre todos los nombres de los empleados que registraron algún tipo de compra

Formato: NombresyApellido – Nro_Comprobante – fecha_emision – Total 7. Que muestre un listado de todos los pedidos no entregados



PREGUNTAS PARA SU ANÁLISIS ¿Cómo ejecutaría los siguientes procedimientos almacenados6

? CREATE PROCEDURE usp_MejoresClientes @maxClientes INT

Rpta: EXEC usp_MejoresClientes 10 CREATE PROCEDURE usp_BuscaCliente @codCliente INT

Rpta: CREATE PROCEDURE usp_BuscaNombreCliente @iNombre VARCHAR(25)

Rpta: EXEC usp_BuscaNombreCliente 'Juan' CREATE PROCEDURE usp_BuscaEmpleado @iApellido VARCHAR(25)

Rpta: ¿ ? ¿En que se diferenciaría el siguiente procedimiento almacenado con el procedimiento? dbo.usp_BuscarEmpleadoPorCod y dbo.usp_BuscarEmpleadoPorCod_ASQL

CREATE PROCEDURE dbo.usp_BuscarEmpleadoPorCod_Optional @CodEmp Int AS DECLARE @CurrentApp varchar(35) SET @CurrentApp = APP_NAME() IF (@CurrentApp = 'MS SQL Query Analyzer')OR (@CurrentApp = 'Analizador de consultas SQL') BEGIN IF Exists(SELECT cod_empleado FROM empleado WHERE cod_empleado=@CodEmp) SELECT Apellidos, Nombres, Cargo, Fecha_contratacion, Años_servicio = Year(Getdate()) - Year(Fecha_contratacion) FROM empleado WHERE cod_empleado = @CodEmp ELSE Begin PRINT 'No existe el codigo de empleado que buscas...:' + Cast(@CodEmp as varchar(6)) RETURN -1 /*Devolver codigo de estado */ End END ELSE SELECT Apellidos, Nombres, Cargo, Fecha_contratacion, Años_servicio = Year(Getdate()) - Year(Fecha_contratacion) FROM empleado WHERE cod_empleado = @CodEmp Return 0

Nota: (6) Ninguno de los procedimientos almacenados ha sido creado en esta práctica. Sin embargo, al responder estas preguntas, asuma que sí fueron creados.

practica seis delphi

Education