Ejercicio para el uso de los procedimientos almacenados con los datos de una tabla.
En este ejercicio se crea una tabla para carreras en la Universidad SQL, se crean los procedimientos almacenados para insertar un registro, modificar los datos del registro, listar los registros ordenados por descripción y borrar un registro. El borrado del registro es lógico. (Ver Eliminación de registros)
Seguir leyendo
Las Funciones definidas por el usuario son rutinas que aceptan parámetros de manera opcional, realizan acciones y devuelven el resultado como un valor o como una tabla.
El valor devuelto puede ser un valor escalar único o un conjunto de resultados.
Las Funciones Definidas por el usuario explicadas en este artículo son:
1. Las que retornan UN VALOR – InLine,se utilizan en otras instrucciones
2. Las que retornan UNA TABLA
Para crear las que devuelven un valor.
Create function Esquema.NombreFuncion([Parámetros]) Returns TipoDato
As
Begin
Instrucciones….
Return ValorRetornado
End
go
Para crear las FDU que retornan UNA TABLA
Create function Esquema.NombreFuncion([Parámetros]) Returns Table
As
Return (Select….)
go
Usando Northwind
use Northwind
go
FDU que retorne los productos con tengan mas Unidades en Orden que Stock actual
Create function fduProductoCompraUrgente () returns Table
As
Return (select * from Products where UnitsOnOrder > UnitsInStock)
go
Para usar la función creada
select * from fduProductoCompraUrgente()
go
Listar de Pedidos de un cliente, escribir el nombre del cliente
Primero: buscar el código del cliente.
Ejemplo «Universidad SQL»
select CustomerID from Customers where CompanyName = ‘Universidad SQL’
go
Se comprueba que no existe el cliente
Para el cliente «Antonio Moreno Taquería»
select CustomerID from Customers where CompanyName = ‘Antonio Moreno Taquería’
go
Resultado: ANTON
Para las Órdenes del Cliente se puede usar una sub consulta. (Ver Sub consultas)
SELECT * FROM Orders where CustomerID = (select CustomerID from Customers
where CompanyName = ‘Antonio Moreno Taquería’)
go
En una FDU
Create function fduOrdenesPorCliente(@Cliente nvarchar(40)) Returns Table
As
Return (SELECT * FROM Orders
where CustomerID = (select CustomerID from Customers
where CompanyName =@Cliente ))
go
Usando la FDU anterior, las Órdenes para ‘Antonio Moreno Taquería’
select * from fduOrdenesPorCliente(‘Antonio Moreno Taquería’)
go
Productos con precio mayor a valor ingresado
Create function fduProductosPrecioHaciaArriba(@Precio Numeric(9,2)) Returns Table
As
Return (select * from Products where UnitPrice > @Precio)
go
Usar la FDU
select * from fduProductosPrecioHaciaArriba(80)
go
Pedidos de un cliente en un rango de fechas
Create function fduOrdenesPorClienteRangoFechas(@Cliente nvarchar(40), @FechaInicial Date,
@FechaFinal Date) Returns Table
As
Return (SELECT * FROM Orders
where CustomerID = (select CustomerID from Customers where CompanyName =@Cliente )
and (OrderDate >= @FechaInicial and OrderDate <= @FechaFinal)
)
go
Usar la FDU fduOrdenesPorClienteRangoFechas
select * from fduOrdenesPorClienteRangoFechas(‘Antonio Moreno Taquería’,’01/06/1997′,’31/12/1997′)
go
Listado de las categorías y el valor del Stock de cada una
Valor del Stock para la categoria 1
select SUM(UnitPrice * UnitsInStock) from Products where CategoryID = 1
go
La FDU para calcular el valor del stock
Create function fduValorStockPorCategoria(@CodigoCategoria int)
Returns Numeric(9,2)
As
Begin
— Variable para capturar el Valor
Declare @ValorTotalStock Numeric(9,2)
set @ValorTotalStock = (select SUM(UnitPrice * UnitsInStock)
from Products where CategoryID = @CodigoCategoria )
Return @ValorTotalStock
End
go
Usar la FDU fduValorStockPorCategoria
select C.CategoryID As ‘Código’, C.CategoryName As ‘Categoría’,
dbo.fduValorStockPorCategoria(C.CategoryID) As ‘Valor Stock’
from Categories As C order by [Valor Stock] desc
go
Costo consultando el Plan de Ejecución: 0.0146903
El mismo resultado usando Subconsultas
select C.CategoryID As ‘Código’, C.CategoryName As ‘Categoría’,
(select SUM(UnitPrice * UnitsInStock)
from Products As P where P.CategoryID = C.CategoryID)
As ‘Valor Stock’
from Categories As C
order by [Valor Stock] desc
go
Costo consultando el Plan de Ejecución: 0.0196404
Se puede ver que la FDU es más rápida que la sub consulta.
Eliminar una FDU
Drop function fduOrdenesPorCliente
go
Importante: Se recomienda tener cuidado en la eliminación de una FDU y en general de cualquier objeto, este puede estar referenciado en algún otro script y si se elimina cuasará problema en el sistema.
Los procedimientos almacenados son bloques de código reutilizable guardados en la base de datos que tienen un propósito. (Ver Procedimientos Almacenados)
Existen procedimientos almacenados que no tienen parámetros, es decir, no necesitan de ningún valor para que se ejecuten, las tareas que realizan estos generalmente son sencillas.
Seguir leyendo
En una consulta a través de internet nos pidieron hacer una función que al darle una cadena de caracteres, reporte la cantidad de vocales y la cantidad de consonantes. Aquí la solución.
Las funciones definidas por el usuario permiten obtener resultados que las funciones propias de SQL Server no pueden mostrarnos, son de mucha utilidad para optimizar el trabajo de consultas con parámetros. Para mejor información ver Funciones definidas por el usuario.
Seguir leyendo
Las operaciones con Pivot nos permitirá convertir los resultados de una consulta que se presentan en filas y mostrarlos en columnas.
Pivot utiliza las funciones de agregado para presentar los datos en columnas. Para información de las funciones de agregado Ver Funciones de agregado.
Un desencadenador o Trigger es una clase de procedimiento almacenado que se ejecuta automáticamente cuando se realiza una transacción en la bases de datos. En este artículo explicamos los tipos de Triggers existentes y desarrollamos ejemplos de Triggers DML.
Seguir leyendo
Los triggers instead of son un tipo de Triggers que reemplazan las instrucciones que hace que se dispare, use estos tipos de Triggers cuando es necesario comprobar algunas condiciones al momento de realizar transacciones con los registros de tablas o vistas.
Por ejemplo: si se crea un Trigger de tipo instead of para la tabla Clientes al insertar un registro, al ejecutar un Insert es cuando este tipo de Trigger se va a ejecutar en lugar de la instrucción Insert en la tabla o vista.
Se lo puede definir como un identificador de un objeto en la BD. El objeto del que se crea el sinónimo no es necesario que exista al momento de crear el sinónimo, SQL Server comprueba la existencia del objeto en tiempo de ejecución.
Seguir leyendo