{"id":2078,"date":"2020-07-08T21:50:25","date_gmt":"2020-07-08T21:50:25","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2078"},"modified":"2020-07-17T15:56:28","modified_gmt":"2020-07-17T15:56:28","slug":"cursor-en-store-procedure","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2078","title":{"rendered":"Cursor en Store Procedure"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL__D.png\" alt=\"\" class=\"wp-image-2079\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Cursor en Store Procedure SQL Server<\/h2>\n\n\n\n<p>En este ejercicio se va a crear un cursor el cual analizar\u00e1 el volumen de compras de un cliente, los clientes y su volumen de compras se van a guardar en tablas diferentes, separando a los clientes que han comprado por encima de la media de todos los pedidos y los que han comprado menos en otra tabla.<br>Para facilidad del trabajo y que todos puedan verificar los resultados se va a utilizar la informaci\u00f3n de la base de datos Northwind, se crear\u00e1 una nueva base de datos y copiar\u00e1 la informaci\u00f3n de Northwind.<\/p>\n\n\n\n<!--more-->\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>Para m\u00e1s informaci\u00f3n:<\/strong><br><strong>Ver <a href=\"https:\/\/manualsqlserver.com\/?p=343\" target=\"_blank\" rel=\"noreferrer noopener\">Cursores en SQL Server<\/a><br>Ver <a href=\"https:\/\/manualsqlserver.com\/?p=314\" target=\"_blank\" rel=\"noreferrer noopener\">Procedimientos almacenados<\/a><br>Ver <a href=\"https:\/\/manualsqlserver.com\/?p=1493\" target=\"_blank\" rel=\"noreferrer noopener\">Variables en SQL Server<\/a><br>Ver <a href=\"https:\/\/manualsqlserver.com\/?p=225\" target=\"_blank\" rel=\"noreferrer noopener\">Agrupamientos<\/a><\/strong><\/p>\n\n\n\n<p style=\"font-size:28px\"><strong>Desarrollo del ejercicio<\/strong><\/p>\n\n\n\n<p>create database Ventas<br>go<br>use Ventas<br>go<\/p>\n\n\n\n<p><strong>El diagrama ilustra lo que se va a crear:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_01.png\" alt=\"\" class=\"wp-image-2080\" width=\"671\" height=\"661\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_01.png 682w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_01-300x296.png 300w\" sizes=\"auto, (max-width: 671px) 100vw, 671px\" \/><\/figure>\n\n\n\n<p>Create table Cliente<br>(ClienteCodigo nchar(5),<br>ClienteNombre nvarchar(100),<br>ClienteCiudad nvarchar(100),<br>constraint ClientePK primary key (ClienteCodigo)<br>)<br>go<\/p>\n\n\n\n<p><strong>Insertar los clientes de la tab la Customers de Nortwind<\/strong><br>insert into Cliente<br>select CustomerID, CompanyName, City from Northwind.dbo.Customers<br>go<\/p>\n\n\n\n<p><strong>Crear la tabla Pedidos<\/strong><br>Create table Pedidos<br>(<br>PedidosCodigo nchar(5),<br>PedidosFecha Date,<br>ClienteCodigo nchar(5),<br>constraint PedidosPK primary key (PedidosCodigo),<br>constraint PedidosClientesFK foreign key (ClienteCodigo)<br>references Cliente(ClienteCodigo)<br>)<br>go<\/p>\n\n\n\n<p><strong>Insertar las \u00f3rdenes de la tabla Orders de Nortwind<\/strong><br>insert into Pedidos<br>select OrderID, OrderDate, CustomerID from Northwind.dbo.Orders<br>go<\/p>\n\n\n\n<p><strong>Crear la tabla Productos<\/strong><br>Create table Productos<br>(<br>ProductosCodigo int,<br>ProductosDescripcion nvarchar(100),<br>ProductosUnidad nvarchar(100),<br>ProductosPrecio Numeric(9,2),<br>ProductosStock Numeric(9,2),<br>constraint ProductosPK Primary key (ProductosCodigo)<br>)<br>go<\/p>\n\n\n\n<p><strong>Insertar los productos de la tabla Products de Northwind<\/strong><br>insert into Productos<br>select ProductID, ProductName, QuantityPerUnit,<br>UnitPrice, UnitsInStock<br>from Northwind.dbo.Products<br>go<\/p>\n\n\n\n<p><strong>Crear la tabla Detalle de pedidos<\/strong><br>Create table DetallePedidos<br>(<br>PedidosCodigo nchar(5),<br>ProductosCodigo int,<br>PedidoCantidad Numeric(9,2),<br>PedidoPrecio Numeric(9,2),<br>PedidoDescuento Numeric(5,3)<br>constraint DetallePedidosPK primary key (PedidosCodigo, ProductosCodigo),<br>constraint DetallePedidosProductosFK Foreign key (ProductosCodigo)<br>references Productos(ProductosCodigo),<br>constraint DetallePedidosPedidosFK Foreign key (PedidosCodigo)<br>references Pedidos(PedidosCodigo)<br>)<br>go<\/p>\n\n\n\n<p><strong>Insertar los registros de la tabla Order Details de Northwind<\/strong><br>insert into DetallePedidos<br>select OrderID, ProductID, Quantity, UnitPrice, Discount<br>from Northwind.dbo.[Order Details]<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Las tablas para el llenado del resultado son VIP y Normal.<\/strong><\/p>\n\n\n\n<p>create table VIP(<br>IdCliente nchar(5),<br>NombreCliente nvarchar(100),<br>Ciudad nvarchar(100),<br>totalgastado Numeric(9,2)<br>)<br>create table NORMAL(<br>IdCliente nchar(5),<br>NombreCliente nvarchar(100),<br>Ciudad nvarchar(100),<br>totalgastado Numeric(9,2)<br>)<\/p>\n\n\n\n<p><strong>La instrucci\u00f3n select del cursor es la siguiente.<\/strong><br><a href=\"https:\/\/manualsqlserver.com\/?p=225\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Agrupamientos.<\/a><br>select c.ClienteCodigo, C.ClienteNombre, C.ClienteCiudad,<br>SUM(D.PedidoPrecio*D.PedidoCantidad *(1-D.PedidoDescuento))<br>As &#8216;Total&#8217;<br>from Cliente c, DetallePedidos d, Pedidos p<br>where d.PedidosCodigo = p.PedidosCodigo<br>and c.ClienteCodigo =p.ClienteCodigo<br>group by c.ClienteCodigo, c.ClienteNombre, C.ClienteCiudad<br>go<br>La imagen muestra los clientes y el total de compras.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_02.png\" alt=\"\" class=\"wp-image-2081\" width=\"701\" height=\"713\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_02.png 802w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_02-295x300.png 295w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_02-768x781.png 768w\" sizes=\"auto, (max-width: 701px) 100vw, 701px\" \/><\/figure>\n\n\n\n<p>C\u00e1lculo de la media de las compras, Total de ventas\/Total Pedidos<\/p>\n\n\n\n<p><strong>Total Venta<\/strong><br>select sum(d.PedidoPrecio* d.PedidoCantidad <em>(1-D.PedidoDescuento)) As &#8216;Suma&#8217; from DetallePedidos d, Pedidos p where d.PedidosCodigo =p. PedidosCodigo go <\/em><\/p>\n\n\n\n<p><em><strong>Total Pedidos <\/strong><\/em><\/p>\n\n\n\n<p><em>select Count(P.PedidosCodigo) from Pedidos P <\/em><br><em>go <\/em><\/p>\n\n\n\n<p><em><strong>La media de los pedidos <\/strong><\/em><\/p>\n\n\n\n<p><em>Declare @Media Numeric(9,2) <\/em><\/p>\n\n\n\n<p><em>Set @Media = ( select sum(d.PedidoPrecio<\/em> d.PedidoCantidad *(1- D.PedidoDescuento)) As &#8216;Suma&#8217;<br>from DetallePedidos d, Pedidos p<br>where d.PedidosCodigo =p. PedidosCodigo ) \/<br>(select Count(P.PedidosCodigo) from Pedidos P)<br>select @Media<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>El Procedimiento almacenado para llenar las tablas es como sigue:<\/strong><\/p>\n\n\n\n<p>Create procedure spClientesVipNormal<br>As<br>begin<br>set nocount on<br>declare CursorRuta cursor for<br>select c.ClienteCodigo, C.ClienteNombre, C.ClienteCiudad,<br>SUM(D.PedidoPrecio*D.PedidoCantidad *(1-D.PedidoDescuento))<br>As &#8216;Total&#8217;<br>from Cliente c, DetallePedidos d, Pedidos p<br>where d.PedidosCodigo = p.PedidosCodigo<br>and c.ClienteCodigo =p.ClienteCodigo<br>group by c.ClienteCodigo, c.ClienteNombre, C.ClienteCiudad<br>&#8212; Variables<br>declare @id nchar(5)<br>declare @nombre nvarchar (100)<br>declare @ciudad nvarchar (100)<br>declare @totalgastado Numeric(9,2)<br>&#8212; Abrir el Cursor<br>open CursorRuta<br>&#8212; Calcular la media<br>Declare @Media Numeric(9,2)<br>Set @Media =<br>( select sum(d.PedidoPrecio* d.PedidoCantidad *(1-D.PedidoDescuento)) As &#8216;Suma&#8217;<br>from DetallePedidos d, Pedidos p<br>where d.PedidosCodigo =p. PedidosCodigo ) \/<br>(select Count(P.PedidosCodigo) from Pedidos P)<br>&#8212; Leer los datos del primer registro del cursor<br>fetch CursorRuta into @id, @nombre, @ciudad, @totalgastado<\/p>\n\n\n\n<p>&#8212; Eliminar el contenido de las tablas <br>Delete VIP <br>Delete NORMAL <br>&#8212; Variables para los espacios del reporte. <br>Declare @NombreMasLargo int Set @NombreMasLargo = <br>(select max(len(ClienteNombre)) from Cliente) + 2 <br>Declare @CiudadMasLarga int Set @CiudadMasLarga = <br>(select max(len(ClienteCiudad)) from Cliente) + 2 <br>Print &#8216;================================== LISTADO ===================================================&#8217; <\/p>\n\n\n\n<p>while (@@FETCH_STATUS=0)<br>begin<br>if (@totalgastado &gt;= @media)<br>begin<br>insert into VIP values (@id, @nombre, @ciudad, @totalgastado)<br>print @id+&#8217; &#8216;+ @nombre+ space(@NombreMasLargo &#8211; len(@nombre)) +<br>@ciudad+ space(@CiudadMasLarga &#8211; len(@ciudad)) +<br>cast(@totalgastado as nvarchar (10)) + Space(12)+ &#8216;VIP&#8217;<br>end<br>else<br>begin<br>insert into NORMAL values (@id,@nombre, @ciudad,@totalgastado)<br>print @id+&#8217; &#8216;+ @nombre+ space(@NombreMasLargo &#8211; len(@nombre)) +<br>@ciudad+ space(@CiudadMasLarga &#8211; len(@ciudad)) +<br>cast(@totalgastado as nvarchar (10)) + Space(10) + &#8216;NORMAL&#8217;<br>End<br>&#8212; Leer el siguiente registro<br>fetch CursorRuta into @id, @nombre, @ciudad, @totalgastado<br>end<br>close CursorRuta<br>deallocate CursorRuta<br>end<br>go<\/p>\n\n\n\n<p>Se puede usar una variable tipo tabla para evitar el uso de la sentencia Print. <a href=\"https:\/\/manualsqlserver.com\/?p=1630\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Cursores con variables tipo tabla.<\/a><\/p>\n\n\n\n<p>Al ejecutar el procedimiento almacenado<br>Execute spClientesVipNormal<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_03-1024x726.png\" alt=\"\" class=\"wp-image-2082\" width=\"746\" height=\"529\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_03-1024x726.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_03-300x213.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_03-768x545.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_03.png 1148w\" sizes=\"auto, (max-width: 746px) 100vw, 746px\" \/><\/figure>\n\n\n\n<p>Es necesario resaltar que el reporte del procedimiento almacenado usando la sentencia Print no se debe incluir en un sistema en producci\u00f3n, adem\u00e1s de las consideraciones y cuidados que se deben tener en incluir cursores en los sistemas.<\/p>\n\n\n\n<p>La informaci\u00f3n en las tablas VIP y NORMAL<br>Select * from vip<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_04.png\" alt=\"\" class=\"wp-image-2083\" width=\"693\" height=\"743\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_04.png 736w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_04-279x300.png 279w\" sizes=\"auto, (max-width: 693px) 100vw, 693px\" \/><\/figure>\n\n\n\n<p>Select * from NORMAL<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_05.png\" alt=\"\" class=\"wp-image-2084\" width=\"727\" height=\"294\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_05.png 810w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_05-300x121.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQLServer_Cursores_ClientesVIPNORMAL_05-768x311.png 768w\" sizes=\"auto, (max-width: 727px) 100vw, 727px\" \/><\/figure>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Cursor en Store Procedure SQL Server En este ejercicio se va a crear un cursor el cual analizar\u00e1 el volumen de compras de un cliente, los clientes y su volumen de compras se van a guardar en tablas diferentes, separando a los clientes que han comprado por encima de la media de todos los pedidos &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2078\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2079,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[232,233,8],"tags":[25,73,63],"class_list":["post-2078","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cursoressql","category-sp","category-programacion","tag-cursores","tag-declare-cursor","tag-variables","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2078","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2078"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2078\/revisions"}],"predecessor-version":[{"id":2085,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2078\/revisions\/2085"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2079"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2078"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2078"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2078"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}