Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
Curso: Base de Datos II Manual de Programación de T-SQL UNIVERSIDAD NACIONAL DE LA AMAZONÍA PERUANA FACULTAD DE INGENIERÍA DE SISTEMAS E INFORMÁTICA MANUAL DE PROGRAMACIÓN Transact - SQL Autor: José Edgar García Díaz Curso: Base de Datos II IQUITOS – PERÚ 2017 Curso: Base de Datos I Manual Básico de T-SQL 2 INDICE Variables 2 Control “if…else” 3 Condición “case…when…else…end” 4 Control “while” 5 Control de errores “try…catch” 6 Cursores 7 Funciones 12 Procedimientos almacenados 16 Triggers o disparadores 23 I T-SQL T El lenguaje SQL se divide en tres subconjuntos de instrucciones, según la funcionalidad de éstas: variables Una variable es una entidad a la que se asigna un valor. Existen dos tipos: · Variables Locales. Las variables locales se declaran, nombran y escriben mediante la palabra clave declare, y reciben un valor inicial mediante una instrucción select o set. · Variables Públicas. Las variables globales son variables predefinidas suministradas por el sistema. Control “if…else” La palabra clave IF se utiliza para definir una condición, la palabra clave ELSE introduce una instrucción alternativa. condición “case…when…else…end” La estructura CASE evalúa una lista de condiciones y devuelve una de las varias expresiones de resultado posibles. La expresión CASE tiene dos formatos: · La expresión CASE sencilla compara una expresión con un conjunto de expresiones sencillas para determinar el resultado. · La expresión CASE buscada evalúa un conjunto de expresiones booleanas para determinar el resultado. control “while” Se ejecuta en forma repetitiva un conjunto o bloque de instrucciones SQL siempre que la condición especificada sea verdadera. Se puede controlar la ejecución de instrucciones en el bucle WHILE con las palabras clave BREAK y CONTINUE. control de errores “try…catch” Sirve para controlar los errores que pueden suceder durante el proceso de consulta. cursores Es una variable que nos permite recorrer con un conjunto de resultados obtenidos a través de una sentencia SELECT fila por fila. Es una técnica que permite tratar fila por fila el resultado de una consulta, contrariamente al SELECT SQL que trata a un conjunto de fila. …CURSORES Para actualizar los datos de un cursor, debemos especificar la cláusula FOR UPDATE después de la sentencia SELECT en la declaración del cursor, y WHERE CURRENT OF <nombre_cursor> en la sentencia UPDATE manual de PROGRAMACIÓN tRANSACT sql Lenguaje de manipulación de datos - dml RANTACT SQL (T-SQL) es el lenguaje de programación utilizado por el Sistema Gestor de Bases de Datos SQL Server de Microsoft haciendo así que el lenguaje de consulta de SQL se extienda con otras instrucciones y elementos propios de los lenguajes de programación. · DDL (Data Definition Language – Lenguaje de Definición de Datos): se encarga de la manipulación de los objetos de la base de datos, por ejemplo, crear tablas, vistas u otros objetos. · DML (Data Manipulation Language – Lenguaje de Manipulación de Datos): se encarga de la manipulación de los datos. Es lo que usamos de manera más habitual para consultar, generar o actualizar información. · DCL (Data Control Language – Lenguaje de Control de Datos): se encarga de controlar el acceso a los objetos y a los datos, para que los datos sean consistentes y sólo puedan ser accedidos por quien esté autorizado a ello. Ejemplo 1: Declarar y asignar valor a una variable local. DECLARE @PRECIO DECIMAL SET @PRECIO = 50 SELECT * FROM PRODUCTS WHERE UNITPRICE > @PRECIO Ejemplo 2: segunda forma de asignar valor a variable local. DECLARE @MX DECIMAL, @MN DECIMAL SELECT @MX=MAX(UNITPRICE), @MN=MIN(UNITPRICE) FROM PRODUCTS -- IMPRIMIR LOS VALORES DE LAS VARIABLES PRINT 'MAYOR PRECIO:'+STR(@MX) PRINT 'MENOR PRECIO:'+STR(@MN) Ejemplo 3: Declaraciones de algunas variables públicas --@@ERROR CONTIENE 0 SI LA ÚLTIMA TRANSACCIÓN SE --EJECUTÓ DE FORMA CORRECTA CASO CONTRARIO ES UN --NÚMERO DE ERROR DEL SISTEMA. DECLARE @ErrorVar INT RAISERROR(N'Message', 16, 1); IF @@ERROR <> 0 PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)); --LA VERSION DEL SQL SERVER PRINT 'VERSION:' + @@VERSION --LENGUAJE DEL APLICATIVO PRINT 'LENGUAJE:' + @@LANGUAGE --NOMBRE DEL SERVIDOR PRINT 'SERVIDOR:' + @@SERVERNAME --NUMERO DE CONEXIONES PERMITIDAS PRINT 'CONEXIONES:' + STR(@@MAX_CONNECTIONS) Ejemplo 4: Mostrar si un empleado ha realizado órdenes (ingresar su employeeid). DECLARE @IDEMP INT, @CANTIDAD INT SET @IDEMP = 6 --RECUPERAR LA CANTIDAD DE PEDIDOS DEL EMPLEADO DE CODIGO 6 SELECT @CANTIDAD = COUNT(*) FROM ORDERS WHERE EMPLOYEEID = @IDEMP --EVALUA EL VALOR DE CANTIDAD IF @CANTIDAD = 0 PRINT 'EL EMPLEADO NO HA REALIZADO NINGUNA' ELSE IF @CANTIDAD = 1 PRINT 'HA REGISTRADO 1 ORDEN, CONTINUE TRABAJANDO' ELSE PRINT 'HA REGISTRADO ORDENES' GO Ejemplo 5: Utilizar IF para evaluar la existencia de un registro, si existe actualizar sino insertar el registro. DECLARE @EMPLEADOID INT, @NOMBRE VARCHAR(50), @PAIS VARCHAR(20), SET @ID = 5 SET @NOMBRE = 'JUAN' SET @PAIS = 'ESPAÑA' --EVALUA SI EXISTE EL REGISTRO DE LA TABLA, SI EXISTE --ACTUALIZO, SINO INSERTO IF EXISTS(SELECT * FROM EMPLOYEES WHERE EMPLOYEEID = @ID) BEGIN UPDATE EMPLOYEES SET FIRSTNAME = @NOMBRE, COUNTRY = @PAIS WHERE EMPLOYEEID = @EMPLEADOID END ELSE BEGIN INSERT INTO EMPLOYEES(FIRTSNAME, COUNTRY) VALUES(@NOMBRE, @PAIS) END Ejemplo 6: Declare una variable donde se le asigne el número de mes y retorne el mes en letras. DECLARE @M INT, @MES VARCHAR(20) SET @M=4 SET @MES = (CASE @M WHEN 1 THEN 'ENERO' WHEN 2 THEN 'FEBRERO' WHEN 3 THEN 'MARZO' WHEN 4 THEN 'ABRIL' WHEN 5 THEN 'MAYO' WHEN 6 THEN 'JUNIO' WHEN 7 THEN 'JULIO' WHEN 8 THEN 'AGOSTO' WHEN 9 THEN 'SETIEMBRE' WHEN 10 THEN 'OCTUBRE' WHEN 11 THEN 'NOVIEMBRE' WHEN 12 THEN 'DICIEMBRE' ELSE 'NO ES MES VALIDO' END) PRINT @MES Ejemplo 7: Mostrar los datos de los empleados evaluando el valor de cortesía asignado. SELECT (CASE TITLEOFCOURTESY WHEN 'SRTA.' THEN 'SEÑORITA' WHEN 'SR.' THEN 'SEÑOR' WHEN 'DR.' THEN 'DOCTOR' WHEN 'SRA.' THEN 'SEÑORA' ELSE 'NO TRATAMIENTO' END), LASTNAME, FIRSTNAME FROM EMPLOYEES ORDER BY 1 Ejemplo 8: Evaluar el stock de cada producto y definirla en un tipo de estado. DECLARE @STOCK INT SET @STOCK=100 SELECT PRODUCTNAME, UNITPRICE, UNITSINSTOCK, 'ESTADO'= (CASE WHEN UNITSINSTOCK >@STOCK THEN 'STOCKEADO' WHEN UNITSINSTOCK =@STOCK THEN 'LIMITE' WHEN UNITSINSTOCK <@STOCK THEN 'HAGA UNA SOLICITUD' END) FROM PRODUCTS Ejemplo 9: listar los primeros 100 números enteros, visualizando si es par o impar. DECLARE @CONTADOR INT SET @CONTADOR = 0 WHILE (@CONTADOR < 100) BEGIN SET @CONTADOR = @CONTADOR + 1 IF @CONTADOR % 2 =0 PRINT CAST(@CONTADOR AS VARCHAR) + ' ES UN NÚMERO PAR' ELSE PRINT CAST(@CONTADOR AS VARCHAR) + ' ES UN NÚMERO IMPAR' END Ejemplo 10: Listar los 5 primeros productos. DECLARE @CONTA INT = 1; WHILE @CONTA < 6 BEGIN SELECT TOP(1) PRODUCTID, PRODUCNAME, UNITPRICE FROM PRODUCTS WHERE PRODUCTID = @CONTA SET @CONTA += 1 END Ejemplo 11: Actualizar las unidades de existencia de los productos asignándoles el valor de 1000 de aquellos con stock cero. DECLARE @ID INT, @NOMBRE VARCHAR(50) WHILE EXISTS(SELECT * FROM PRODUCTS WHERE UNITSINSTOCK=0) BEGIN SELECTTOP 1 @ID=PRODUCTID, @NOMBRE=PRODUCTNAME FROM PRODUCTS WHERE UNITSINSTOCK=0 UPDATE PRODUCTS SET UNITSINSTOCK=1000 WHERE PRODUCTID=@ID PRINT 'PRODUCTO:'+@NOMBRE + ' SE ACTUALIZO EL STOCK' CONTINUE END Ejemplo 12: Evaluar la división de dos números enteros. BEGIN TRY DECLARE @DIVISOR INT, @DIVIDENDO INT, @RESULTADO INT SET @DIVIDENDO = 100 SET @DIVISOR = 9 -- ESTA LINEA PROVOCA UN ERROR DE DIVISION POR 0 SET @RESULTADO = @DIVIDENDO/@DIVISOR PRINT 'NO HAY ERROR' END TRY BEGIN CATCH PRINT 'SE HA PRODUCIDO UN ERROR' END CATCH Ejemplo 13: Funciones especiales de error únicamente para el bloque CATCH. BEGIN TRY DECLARE @DIVISOR INT, @DIVIDENDO INT, @RESULTADO INT SET @DIVIDENDO = 100 SET @DIVISOR = 9 SET @RESULTADO = @DIVIDENDO/@DIVISOR PRINT 'NO HAY ERROR' END TRY BEGIN CATCH PRINT ERROR_MESSAGE() --DEVUELVE MENSAJE DE ERROR PRINT ERROR_STATE() --DEVUELVE ESTADO DEL ERROR END CATCH; -------------------- ERROR_NUMBER() --DEVUELVE NUMERO DE ERROR ERROR_SEVERITY() --DEVUELVE LA SEVERIDAD DEL ERROR ERROR_PROCEDURE()--DEVUELVE EL PROC ALMACENADO QUE HA PROVOCADO EL ERROR ERROR_LINE() --DEVUELVE EL NÚMERO DE LÍNEA EN LA QUE SE HA PRODUCIDO EL ERROR Ejemplo 14: Eliminar productos con stock < 20, caso contrario mostrar error. BEGIN TRY DELETE FROM PRODUCTS WHERE UNITSINSTOCK<20 END TRY BEGIN CATCH DECLARE @MENSAJE VARCHAR(255) --RECUPERAR LA DESCRIPCION DEL VALOR DE @@ERROR SELECT @MENSAJE= M.DESCRIPTION FROM SYS.SYSMESSAGES M WHERE M.ERROR=@@ERROR PRINT @MENSAJE END CATCH Ejemplo 15: Sintaxis general para trabajar con un cursor. -- DECLARACIÓN DEL CURSOR DECLARE <NOMBRE_CURSOR> CURSOR FOR <SENTENCIA_SQL> -- APERTURA DEL CURSOR OPEN <NOMBRE_CURSOR> -- LECTURA DE LA PRIMERA FILA DEL CURSOR FETCH <NOMBRE_CURSOR> INTO <LISTA_VARIABLES> WHILE (@@FETCH_STATUS = 0) BEGIN -- LECTURA DE LA SIGUIENTE FILA DE UN CURSOR FETCH <NOMBRE_CURSOR> INTO <LISTA_VARIABLES> ... END -- FIN DEL BUCLE WHILE -- CIERRA EL CURSOR CLOSE <NOMBRE_CURSOR> -- LIBERA LOS RECURSOS DEL CURSOR DEALLOCATE <NOMBRE_CURSOR> Ejemplo 16: Mostrar el primer registro de productos. --DECLARAR CURSOR DECLARE MI_CURSOR CURSOR FOR SELECT TOP 1 * FROM PRODUCTS --ABRIR OPEN MI_CURSOR --IMPRIMIR EL PRIMER REGISTRO FETCH NEXT FROM MI_CURSOR --CERRAR CLOSE MI_CURSOR --LIBERAR DEALLOCATE MI_CURSOR Ejemplo 17: Defina un cursor que permita visualizar: el primer registro, el registro de la posición 6 y el último. --DECLARAR CURSOR DECLARE MI_CURSOR CURSOR SCROLL FOR SELECT * FROM PRODUCTS -- ABRIR OPEN MI_CURSOR -- IMPRIMIR LOS REGISTROS FETCH FIRST FROM MI_CURSOR FETCH ABSOLUTE 6 FROM MI_CURSOR FETCH LAST FROM MI_CURSOR -- CERRAR CLOSE MI_CURSOR -- LIBERAR DEALLOCATE MI_CURSOR Ejemplo 18: Listar los clientes de la base de datos. -- DECLARO VARIABLES DE TRABAJO DECLARE @ID VARCHAR(5), @NOMBRE VARCHAR(50), @PAIS VARCHAR(50) -- DECLARO EL CURSOR DECLARE MIS_CLIENTES CURSOR FOR SELECT CUSTOMERID, COMPANYNAME, COUNTRY FROM CUSTOMERS -- ABRIR OPEN MIS_CLIENTES -- LEER EL PRIMER REGISTRO FETCH MIS_CLIENTES INTO @ID, @NOMBRE, @PAIS -- MIENTRAS PUEDA LEER EL REGISTRO WHILE @@FETCH_STATUS=0 BEGIN --IMPRIMIR EL REGISTRO PRINT @ID + ','+@NOMBRE+','+@PAIS --LEER EL REGISTRO SIGUIENTE FETCH MIS_CLIENTES INTO @ID, @NOMBRE, @PAIS END -- CERRAR CLOSE MIS_CLIENTES -- LIBERAR DEALLOCATE MIS_CLIENTES; GO Ejemplo 19: Listar los clientes que han registrado un pedido. -- DECLARO VARIABLES DE TRABAJO DECLARE @NOMBRE VARCHAR(50), @Q INT, @TOTAL INT SET @TOTAL=0 -- DECLARO EL CURSOR DECLARE MIS_PEDIDOS CURSOR FOR SELECT COMPANYNAME, COUNT(*) FROM CUSTOMERS C JOIN ORDERS O ON C.CUSTOMERID=O. CUSTOMERID GROUP BY COMPANYNAME -- ABRIR OPEN MIS_PEDIDOS -- LEER EL PRIMER REGISTRO FETCH MI_CURSOR INTO @NOMBRE, @Q -- MIENTRAS PUEDA LEER EL REGISTRO WHILE @@FETCH_STATUS=0 BEGIN -- IMPRIMIR EL REGISTRO PRINT @NOMBRE+','+CAST(@Q AS VARCHAR) -- ACUMULAR SET @TOTAL += @Q -- LEER EL REGISTRO SIGUIENTE FETCH MI_CURSOR INTO @NOMBRE, @Q END -- CERRAR CLOSE MI_CURSOR -- LIBERAR DEALLOCATE MI_CURSOR; -- IMPRIMIR PRINT 'TOTAL DE PEDIDOS:' + CAST(@TOTAL AS VARCHAR) GO Ejemplo 20: Listar los pedidos por cada año. -- DECLARO VARIABLES DE TRABAJO DECLARE @Y INT, @Y1 INT, @PEDIDO INT, @MONTO DECIMAL, @TOTAL DECIMAL SET @TOTAL=0 -- DECLARO EL CURSOR DECLARE MI_CURSOR CURSOR FOR SELECT YEAR(ORDERDATE), O.ORDERID, SUM(UNITPRICE*QUANTITY) FROM ORDERS O JOIN [ORDER DETAILS] OD ON O.ORDERID=OD.ORDERID GROUP BY YEAR(ORDERDATE), O.ORDERID ORDER BY 1 -- ABRIR EL CURSOR OPEN PEDIDOS_ANUALES -- LEER EL PRIMER REGISTRO FETCH PEDIDOS_ANUALES INTO @Y, @PEDIDO, @MONTO -- ASIGNAR A LA VARIABLE @Y1 EL VALOR INICIAL DE @Y SET @Y1 = @Y -- IMPRIMIR EL PRIMER AÑO PRINT 'AÑO:' + CAST(@Y1 AS VARCHAR) -- MIENTRAS PUEDA LEER EL REGISTRO WHILE @@FETCH_STATUS=0 BEGIN -- SI COINCIDEN LOS VALORES ACUMULAR EL TOTAL IF(@Y = @Y1) SET @TOTAL += @MONTO ELSE -- SI NO COINCIDEN IMPRIMIR EL TOTAL, INICIALIZAR VARIABLES BEGIN PRINT 'IMPORTE EN:' +CAST(@Y1 AS VARCHAR) + SPACE(2)+ 'ES ' +CAST(@TOTAL AS VARCHAR) PRINT 'AÑO:' + CAST(@Y AS VARCHAR) SET @Y1=@Y SET @TOTAL=@MONTO END -- IMPRIMIR EL REGISTRO PRINT CAST(@PEDIDO AS VARCHAR) + SPACE(5)+STR(@MONTO) -- LEER EL REGISTRO SIGUIENTE FETCH PEDIDOS_ANUALES INTO @Y, @PEDIDO, @MONTO END -- CERRAR CLOSE PEDIDOS_ANUALES -- LIBERAR DEALLOCATE PEDIDOS_ANUALES; -- IMPRIMIR LOS TOTALES FINALES PRINT 'IMPORTE EN:'+CAST(@Y1 AS VARCHAR)+SPACE(2)+'ES '+STR(@TOTAL) Ejemplo 21: Actualizar el precio de los productos, si su stock es mayor a 1000 se descuenta un 50%, sino se descuenta un 20%. -- DECLARACION DE VARIABLES PARA EL CURSOR DECLARE @ID INT, @NOMBRE VARCHAR(255), @PRECIO DECIMAL, @ST INT -- DECLARACIÓN DEL CURSOR DE ACTUALIZACION DECLARE CPRODUCTO CURSOR FOR SELECT PRODUCTID, PRODUCTNAME, UNITPRICE, UNITSINSTOCK FROM PRODUCTS FOR UPDATE -- APERTURA DEL CURSOR OPEN CPRODUCTO -- LECTURA DE LA PRIMERA FILA DEL CURSOR FETCH CPRODUCTO INTO @ID, @NOMBRE, @PRECIO, @ST -- MIENTRAS PUEDA LEER EL REGISTRO WHILE (@@FETCH_STATUS = 0) BEGIN IF(@ST>=1000) SET @PRECIO = 0.5*@PRECIO ELSE SET @PRECIO = 0.80*@PRECIO UPDATE PRODUCTS SET UNITPRICE = @PRECIO WHERE CURRENT OF CPRODUCTO --IMPRIMIR PRINT 'EL PRECIO DE PRODUCTO '+ @NOMBRE+ ' ES ' + STR(@PRECIO) -- LECTURA DE LA SIGUIENTE FILA DEL CURSOR FETCH CPRODUCTO INTO @ID, @NOMBRE, @PRECIO, @ST END -- CIERRE DEL CURSOR CLOSE CPRODUCTO -- LIBERAR LOS RECURSOS DEALLOCATE CPRODUCTO funciones Son rutinas que permiten encapsular sentencias TRANSACT-SQL que se ejecutan frecuentemente. Las funciones de usuario, según el tipo de retorno se clasifican en las siguientes: 1. Funciones Escalares 2. Funciones con valores de tabla en línea 3. Funciones con valores de tabla de varias instrucciones Funciones escalares Son aquellas funciones donde retornan un valor único: tipo de datos como int, Money, varchar, real, etc. Pueden ser utilizadas en cualquier lugar, incluso, incorporada dentro de las sentencias SQL. Funciones de tabla en línea Las funciones de tabla en línea son las funciones que devuelven la salida de una simple declaración SELECT. La salida se puede utilizar adentro de JOINS o querys como si fuera una tabla de estándar. Funciones de tabla de multisentencias Son similares a los procedimientos almacenados excepto que vuelven una tabla. Este tipo de función se usa en situaciones donde se requiere más lógica y proceso. PROCEDIMIENTOS ALMACENADOs Los procedimientos almacenados son grupos formados por instrucciones SQL y el lenguaje de control de flujo. Cuando se ejecuta un procedimiento, se prepara un plan de ejecución para que la subsiguiente ejecución sea muy rápida. Los procedimientos almacenados pueden: · Incluir parámetros · Llamar a otros procedimientos · Devolverun valor de estado a un procedimiento de llamada o lote para indicar el éxito o el fracaso del mismo y la razón de dicho fallo. · Devolver valores de parámetros a un procedimiento de llamada o lote · Ejecutarse en SQL Server remotos Uso de cursores en procedimientos almacenados Los cursores son especialmente útiles en procedimientos almacenados. Permiten llevar a cabo la misma tarea utilizando sólo una consulta que, de otro modo, requeriría varias. Modificar datos con procedimientos almacenados los procedimientos almacenados pueden ejecutar sentencias de actualización de datos: INSERT, UPDATE, DELETE Transacciones implícitas y explicitas Para agrupar varias sentencias TRANSACT SQL en una única transacción, disponemos de los siguientes métodos: · Transacciones explícitas: Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK. · Transacciones implícitas: Se inicia automáticamente una nueva transacción cuando se ejecuta una instrucción que realiza modificaciones en los datos, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK. Ejemplo 22: Crear una función que retorne el precio promedio de los productos. CREATE FUNCTION PRECIOPROMEDIO() RETURNS DECIMAL AS BEGIN DECLARE @PROM DECIMAL SELECT @PROM=AVG(PRECIOUNIDAD) FROM PRODUCTS RETURN @PROM END -- MOSTRAR EL RESULTADO PRINT DBO.PRECIOPROMEDIO() Ejemplo 23: Defina una función donde ingrese el id del empleado y retorne la cantidad de pedidos registrados en el año 1997. CREATE FUNCTION PEDIDOSEMPLEADO(@ID INT) RETURNS DECIMAL AS BEGIN DECLARE @Q DECIMAL=0 SELECT @Q=COUNT(*) FROM ORDERS WHERE YEAR(ORDERDATE)= 1997 AND IDEMPLEADO=@ID IF @Q IS NULL SET @Q=0 RETURN @Q END -- MOSTRAR EL RESULTADO DEL EMPLEADO DE CODIGO 4 PRINT DBO.PEDIDOSEMPLEADO(4) Ejemplo 24: Defina una función que liste los registros de los clientes. CREATE FUNCTION CLIENTES() RETURNS TABLE AS RETURN (SELECT CUSTOMERID AS 'CODIGO', COMPANYNAME AS 'CLIENTE', ADDRESS 'DIRECCION', COUNTRY AS 'PAIS' FROM CUSTOMERS) -- EJECUTANDO LA FUNCION SELECT * FROM DBO.CLIENTES() WHERE PAIS='CHILE' GO Ejemplo 25: Defina una función que liste los pedidos de un determinado año. CREATE FUNCTION PEDIDOSXAÑO(@Y INT) RETURNS TABLE AS RETURN (SELECT ORDERID AS 'PEDIDO', ORDERDATE AS 'FECHA', PRODUCTNAME AS 'PRODUCTO', UNITPRICE AS 'PRECIO', QUANTITY AS 'CANTIDAD' FROM ORDERS O JOIN [ORDER DETAILS] OD ON O.ORDERID=OD.ORDERID JOIN PRODUCTS P ON P.PRODUCTID=OD.PRODUCTID WHERE YEAR(ORDERDATE) = @Y) -- EJECUTANDO LA FUNCION SELECT * FROM DBO.PEDIDOSXAÑO(1998) Ejemplo 26: Defina una función que liste inventario de los productos. CREATE FUNCTION INVENTARIO() RETURNS @TABLA TABLE(IDPRODUCTO INT, NOMBRE VARCHAR(50), PRECIO DECIMAL, STOCK INT) AS BEGIN INSERT INTO @TABLA SELECT PRODUCTID, PRODUCTNAME, UNITPRICE, UNITSINSTOCK FROM PRODUCTS RETURN END -- EJECUTANDO LA FUNCION SELECT * FROM DBO.INVENTARIO() Ejemplo 27: Defina una función que liste las ventas por empleado, en un determinado año. CREATE FUNCTION REPORTEVENTAS(@Y INT) RETURNS @TABLA TABLE(ID INT, NOMBRE VARCHAR(50), CANTIDAD INT, MONTO DECIMAL) AS BEGIN INSERT INTO @TABLA SELECT EMPLOYEEID, LASTNAME, COUNT(*), SUM(UNITPRICE*QUANTITY) FROM ORDERS O JOIN [ORDER DETAILS] OD ON O.ORDERID = OD.ORDERID JOIN EMPLOYEES E ON E.EMPLOYEEID = O.EMPLOYEEID WHERE YEAR(ORDERDATE) = @Y GROUP BY E.EMPLOYEEID, LASTNAME RETURN END -- IMPRIMIR EL REPORTE DEL AÑO 1997 SELECT * FROM DBO.REPORTEVENTAS(1997) Ejemplo 28: Defina una función que permita afectar un descuento del 10% a la sumatoria de las compras totales de más de S/. 1000.00. CREATE FUNCTION DESCUENTO_FN (@CODIGO INT) RETURNS DECIMAL(12,2) AS BEGIN DECLARE @VALOR DECIMAL(12,2), @TOTAL DECIMAL(12,2) SELECT @VALOR= SUM(UNITPRICE * QUANTITY) FROM [ORDER DETAILS] WHERE ORDERID = @CODIGO IF @VALOR>1000 SET @TOTAL = @VALOR - (@VALOR * 0.10) ELSE IF @VALOR<1000 SET @TOTAL = @VALOR RETURN @TOTAL END --EJECUCION DECLARE @NUMORDEN INT, @RESULTADO DECIMAL(10,2) SET @NUMORDEN = 10248 SET @RESULTADO = DBO.DESCUENTO_FN(@NUMORDEN) PRINT @RESULTADO Ejemplo 29: Crear una función que permita identificar las ordenes de compras con las que se adquirió un determinado producto. CREATE FUNCTION PRODUC_X_ORDEN (@PRODUC INT) RETURNS @DATOS TABLE (ID_PRODUCTO INT, NOMBRE_PRODUCTO VARCHAR(50), NRO_ORDEN VARCHAR(8), FECHA DATE, CANTIDAD SMALLINT, PRECIO_UNITARIO MONEY, TOTAL NUMERIC(10,2)) AS BEGIN DECLARE @PRODUCTOID INT, @NOMB_PROD VARCHAR(50) -- VAMOS A INCLUIR UN CURSOR PARA MEJOR VISIÓN DEL RESULTADO DECLARE ORDENES CURSOR FOR SELECT PRODUCTID, PRODUCTNAME FROM PRODUCTS WHERE PRODUCTID = @PRODUC OPEN ORDENES FETCH ORDENES INTO @PRODUCTOID, @NOMB_PROD WHILE(@@FETCH_STATUS = 0) BEGIN INSERT INTO @DATOS(ID_PRODUCTO, NOMBRE_PRODUCTO) VALUES (@PRODUCTOID, @NOMB_PROD) INSERT INTO @DATOS(NRO_ORDEN, FECHA, CANTIDAD, PRECIO_UNITARIO, TOTAL) SELECT ORDERS.ORDERID, ORDERS.ORDERDATE, [ORDER DETAILS].QUANTITY, [ORDER DETAILS].UNITPRICE, SUM([ORDER DETAILS].QUANTITY* [ORDER DETAILS].UNITPRICE) TOTAL FROM ORDERS INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID = [ORDER DETAILS].ORDERID INNER JOIN PRODUCTS ON [ORDER DETAILS].PRODUCTID = PRODUCTS.PRODUCTID WHERE PRODUCTS.PRODUCTID = @PRODUC GROUP BY ORDERS.ORDERID, ORDERS.ORDERDATE, [ORDER DETAILS].QUANTITY, [ORDER DETAILS].UNITPRICE FETCH ORDENES INTO @PRODUCTOID, @NOMB_PROD END CLOSE ORDENES DEALLOCATE ORDENES RETURN END --EJECUCION SELECT * FROM PRODUC_X_ORDEN(20) Ejemplo 30: Defina un procedimiento almacenado que liste todos los clientes. CREATE PROCEDURE USP_CLIENTES AS SELECT CUSTOMERID AS CODIGO, COMPANYNAME AS CLIENTE, ADDRESS AS DIRECCION, PHONE AS TELEFONO FROM CUSTOMERS -- EJECUTANDO EL PROCEDIMIENTO ALMACENADO EXEC USP_CLIENTES -- Ó SIMPLEMENTE EJECUTE USP_CLIENTES Ejemplo 31: Defina un procedimiento almacenado que permita buscar los productos por categoría. CREATE PROCEDURE USP_PRODUCTOSXCATEGORIAS @CATEGORIA INT AS SELECT CATEGORIES.CATEGORYNAME AS CATEGORIAS, PRODUCTS.PRODUCTNAME AS PRODUCTOS, PRODUCTS.UNITPRICE AS PRECIOS FROM CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.CATEGORYID = PRODUCTS.CATEGORYID WHERE CATEGORIES.CATEGORYID = @CATEGORIA --LUEGO EJECUTAMOS LO SIGUIENTE EXECUTE USP_PRODUCTOSXCATEGORIAS 2 Ejemplo 32: Defina un procedimiento almacenado que permita buscar los pedidos en una determinada fecha. CREATE PROCEDURE USP_PEDIDOFECHA @F1 DATETIME AS SELECT * FROM ORDERS WHERE ORDERDATE = @F1 -- EJECUTANDO EL PROCEDIMIENTO ALMACENADO EXEC USP_PEDIDOSBYFECHAS @F1='10-01-1996' -- Ó SIMPLEMENTE EJECUTE EXEC USP_PEDIDOSBYFECHAS '10-01-1996' Ejemplo 33: Defina un procedimiento almacenado que permita buscar los pedidos en un rango de fecha. CREATE PROCEDURE USP_PEDIDOSENFECHAS @F1 DATETIME, @F2 DATETIME AS SELECT * FROM ORDERS WHERE ORDERDATE BETWEEN @F1 AND @F2 EXEC USP_PEDIDOSBYFECHAS @F1='10-01-1996', @F2='10-10-1996' Ejemplo 34: Implemente un procedimiento almacenado que retorne la cantidad de pedidos y el monto total de pedidos, registrados por un determinado empleado y en un determinado año. CREATE PROCEDURE USP_REPORTEPEDIDOSEMPLEADO @ID INT, @Y INT, @Q INT OUTPUT, @MONTO DECIMAL OUTPUT AS SELECT @Q= COUNT(*), @MONTO = SUM(UNITPRICE*QUANTITY) FROM ORDERS O JOIN [ORDER DETAILS] OD ON O.ORDERID = OD.ORDERID WHERE EMPLOYEEID = @ID AND YEAR(ORDERDATE) = @Y -- EJECUTANDO EL PROCEDIMIENTO ALMACENADO DECLARE @Q INT, @M DECIMAL EXEC USP_REPORTEPEDIDOSEMPLEADO @ID=2, @Y=1997, @Q=@Q OUTPUT, @MONTO=@M OUTPUT GO PRINT 'CANTIDAD DE PEDIDOS COLOCADOS:' + STR(@Q) PRINT 'MONTO PERCIBIDO:'+STR(@M) Ejemplo 35: Implemente un procedimiento almacenado para imprimir cada uno de los registros de los productos y al final se visualice el total del inventario. CREATE PROCEDURE USP_INVENTARIO AS -- DECLARACION DE VARIABLES PARA EL CURSOR DECLARE @ID INT, @NOMBRE VARCHAR(255), @PRECIODECIMAL, @ST INT, @INV INT SET @INV=0 -- DECLARACIÓN DEL CURSOR DECLARE CPRODUCTO CURSOR FOR SELECT PRODUCTID, PRODUCTNAME, UNITPRICE, UNITSINSTOCK FROM PRODUCTS -- APERTURA DEL CURSOR OPEN CPRODUCTO -- LECTURA DE LA PRIMERA FILA DEL CURSOR FETCH CPRODUCTO INTO @ID, @NOMBRE, @PRECIO, @ST WHILE (@@FETCH_STATUS = 0 ) BEGIN -- IMPRIMIR PRINT STR(@ID) + SPACE(5) + @NOMBRE + SPACE(5) + STR(@PRECIO) + SPACE(5) + STR(@ST) -- ACUMULAR SET @INV += @ST -- LECTURA DE LA SIGUIENTE FILA DEL CURSOR FETCH CPRODUCTO INTO @ID, @NOMBRE, @PRECIO, @ST END -- CIERRE DEL CURSOR CLOSE CPRODUCTO -- LIBERAR LOS RECURSOS DEALLOCATE CPRODUCTO PRINT 'INVENTARIO DE PRODUCTOS:' + STR(@INV) Ejemplo 36: Implemente un procedimiento almacenado para imprimir los pedidos realizados por un empleado en cada año, totalizando el monto de sus operaciones. CREATE PROCEDURE USP_REPORTEPEDIDOSXAÑOXEMPLEADO @EMP INT=1 AS -- DECLARACIÓN DE VARIABLES DE TRABAJO DECLARE @Y INT, @Y1 INT, @PEDIDO INT, @MONTO DECIMAL, @TOTAL DECIMAL SET @TOTAL=0 -- DECLARACIÓN DEL CURSOR DECLARE MI_CURSOR CURSOR FOR SELECT YEAR(ORDERDATE) AS 'AÑO', O.ORDERID, SUM(UNITPRICE*QUANTITY) AS MONTO FROM ORDERS O JOIN [ORDER DETAILS] OD ON O.ORDERID=OD.ORDERID WHERE EMPLOYEEID = @EMP GROUP BY YEAR(ORDERDATE), O.ORDERID ORDER BY 1 -- APERTURA DEL CURSOR OPEN MI_CURSOR -- LECTURA DEL PRIMER REGISTRO FETCH MI_CURSOR INTO @Y, @PEDIDO, @MONTO -- ASIGNACIÓN DEL VALOR INICIAL DE @Y EN LA VARIABLE @Y1 SET @Y1 = @Y -- IMPRIMIR EL PRIMER AÑO PRINT 'AÑO:' + CAST(@Y1 AS VARCHAR) -- RECORRER EL CURSOS MIENTRAS HAYAN REGISTROS WHILE @@FETCH_STATUS=0 BEGIN IF(@Y = @Y1) BEGIN -- ACUMULAR SET @TOTAL += @MONTO END ELSE BEGIN PRINT 'AÑO:' + CAST(@Y1 AS VARCHAR) + SPACE(2)+ 'IMPORTE: ' + CAST(@TOTAL AS VARCHAR) PRINT 'AÑO:' + CAST(@Y AS VARCHAR) SET @Y1=@Y SET @TOTAL=@MONTO END -- IMPRIMIR EL REGISTRO PRINT CAST(@PEDIDO AS VARCHAR) + SPACE(5)+ CAST(@MONTO AS VARCHAR) -- LECTURA DEL SIGUIENTE REGISTRO FETCH MI_CURSOR INTO @Y, @PEDIDO, @MONTO END -- CERRAR EL CURSOR CLOSE MI_CURSOR -- LIBERAR EL RECURSO DEALLOCATE MI_CURSOR; PRINT ' AÑO:' + CAST(@Y1 AS VARCHAR) + SPACE(2)+ 'IMPORTE: ' + STR(@TOTAL) Ejemplo 37: Defina un procedimiento almacenado para insertar un registro en la tabla de proveedores. CREATE PROCEDURE USP_INSERTAPROVEEDOR @COMPANYNAME CHAR(40)='', @CONTACTNAME CHAR(30)='', @CONTACTITLE CHAR(30)='', @ADDRESS CHAR(60)='', @CITY CHAR(15)='', @REGION CHAR(15)='', @POSTALCODE CHAR(10)='', @COUNTRY CHAR(15)='', @PHONE CHAR(24)='', @FAX CHAR(24)='', @HOMEPAGE TEXT, @CODIGO INTEGER OUTPUT AS INSERT INTO SUPPLIERS VALUES(@COMPANYNAME,@CONTACTNAME,@CONTACTITLE,@ADDRESS,@CITY,@REGION,@POSTALCODE,@COUNTRY,@PHONE,@FAX,@HOMEPAGE) SELECT @@IDENTITY as CodigoProveedor -- LUEGO EJECUTE LO SIGUIENTE DECLARE @CODIGO INTEGER EXEC USP_INSERTAPROVEEDOR 'TORRES CIA','JULIO TORRES DIAZ','Administrador', 'Pizarro 920', 'Trujillo', 'La Libertad','044','Peru','044 930282', '044930282', 'www.trujillo.com.pe',@CODIGO OUT Ejemplo 38: Defina un procedimiento almacenado para insertar un registro en la tabla Clientes. CREATE PROCEDURE USP_INSERTACLIENTE @ID VARCHAR(5), @NOMBRE VARCHAR(50), @DIRECCION VARCHAR(100), @IDPAIS CHAR(3), @FONO VARCHAR(15) AS INSERT INTO CUSTOMERS(CUSTOMERID, COMPANYNAME, ADDRESS, COUNTRY, PHONE) VALUES(@ID, @NOMBRE, @DIRECCION, @IDPAIS, @FONO) -- EJECUTANDO EL PROCEDIMIENTO ALMACENADO EXEC USP_INSERTACLIENTE 'ABCDE', 'JUAN CARLOS MEDINA', 'CALLE 25 NO 123','006','5450555' Ejemplo 39: Defina un procedimiento almacenado para insertar un registro en la tabla Clientes, pero controlando la operación a través de una transacción llamada TCLIENTE. CREATE PROCEDURE USP_INSERTACLIENTE @ID VARCHAR(5), @NOMBRE VARCHAR(50), @DIRECCION VARCHAR(100), @IDPAIS CHAR(3), @FONO VARCHAR(15) AS -- INICIO DE LA TRANSACCION BEGIN TRAN TCLIENTE INSERT INTO CUSTOMERS(CUSTOMERID, COMPANYNAME, ADDRESS, COUNTRY, PHONE) VALUES(@ID, @NOMBRE, @DIRECCION, @IDPAIS, @FONO) -- CONTROLAR EL PROCESO IF @@ERROR = 0 BEGIN -- CONFIRMACIÓN DE LA INSERCIÓN COMMIT TRAN TCLIENTE PRINT 'CLIENTE REGISTRADO' END ELSE BEGIN PRINT @@ERROR -- DESHACER LA INSERCIÓN ROLLBACK TRAN TCLIENTE END Ejemplo 40: Defina un procedimiento almacenado para insertar un registro en la tabla Ordenes y Detalle de Ordenes y descontamos el stock del producto solicitado. CREATE PROCEDURE USP_AGREGAPEDIDO -- PARÁMETROS DE PEDIDOSCABE @IDPED INT, @IDCLI VARCHAR(5), @IDEMP INT, @FECPED DATETIME, -- PARÁMETROS DE PEDIDOSDETA @IDPROD INT, @PRE DECIMAL, @CANT INT AS -- INICIO DE LA TRANSACCION BEGIN TRAN TPEDIDO -- INICIO DEL CONTROL DE ERRORES BEGIN TRY -- AGREGANDO UN REGISTRO A ORDERS INSERT INTO ORDERS(ORDERID, CUSTOMERID, EMPLOYEEID, ORDERDATE) VALUES(@IDPED, @IDCLI, @IDEMP, @FECPED) -- AGREGANDO UN REGISTRO A DETALLE DE ORDENES INSERT INTO [ORDER DETAILS] (ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT) VALUES(@IDPED, @IDPROD, @PRE, @CANT, 0) -- DESCONTANDO EL STOCK DE PRODUCTOS UPDATE PRODUCTS SET UNITSINSTOCK -=@CANT WHERE PRODUCTID = @IDPROD -- CONFIRMANDO LA ACTUALIZACION COMMIT TRAN TPEDIDO PRINT 'PEDIDO REGISTRADO' END TRY BEGIN CATCH PRINT @@ERROR ROLLBACK TRAN TPEDIDO END CATCH Ejemplo 41: Defina un procedimiento almacenado para actualizar los datos de la tabla productos CREATE PROCEDURE USP_ACTUALIZARPRODUCTOS @CODIGO INTEGER=0, @PRODUCTNAME CHAR(40)='', @SUPPLIERID INTEGER=0, @CATEGORYID INTEGER=0, @QUANTITYPERUNIT CHAR(20)='', @UNITPRICE MONEY=0, @UNITSINSTOCK SMALLINT=0, @UNITSONORDER SMALLINT=0, @REORDERLEVEL SMALLINT=0, @DISCONTINUED BIT=0 AS UPDATE PRODUCTS SET PRODUCTNAME=@PRODUCTNAME, SUPPLIERID=@SUPPLIERID, CATEGORYID=@CATEGORYID, QUANTITYPERUNIT=@QUANTITYPERUNIT, UNITPRICE=@UNITPRICE, UNITSINSTOCK=@UNITSINSTOCK, UNITSONORDER=@UNITSONORDER, REORDERLEVEL=@REORDERLEVEL, DISCONTINUED=@DISCONTINUED WHERE PRODUCTID=@CODIGO -- LUEGO EJECUTAMOS LO SIGUIENTE EXEC USP_ACTUALIZARPRODUCTOS 77,'Vino Peruano', 15, 4, '500 ml', 40, 15, 1, 5, 0 Ejemplo 42: Defina un procedimiento almacenado para eliminar un registro de la tabla clientes CREATE PROCEDURE USP_ELIMINARCLIENTE @CODIGO CHAR(5) AS DELETE FROM CUSTOMERS WHERE CUSTOMERID=@CODIGO -- LUEGO EJECUTAMOS LO SIGUIENTE EXEC USP_ELIMINARCLIENTE 'ALFKI' triggers o disparadores Un disparador es un tipo especial de procedimiento almacenado que se ejecuta cuando se insertan, eliminan o actualizan datos de una tabla especificada. Permiten realizar cambios “en cascada” en tablas relacionadas, imponer restricciones de columna más complejas que las permitidas por las reglas, compara los resultados de las modificaciones de datos y llevar a cabo una acción resultante. Disparador de Inserción Cuando se inserta una nueva fila en una tabla, SQL Server inserta los nuevos valores en la tabla INSERTED el cual es una tabla del sistema. Disparador de Eliminación Cuando se elimina una fila de una tabla, SQL Server inserta los valores que fueron eliminados en la tabla DELETED el cual es una tabla del sistema. Disparador de Actualización Cuando se actualiza una fila de una tabla, SQL Server inserta los valores antiguos en la tabla DELETED y los nuevos valores los inserta en la tabla INSERTED. Restricciones de los disparadores · Una tabla puede tener un máximo de tres disparadores: uno de actualización, uno de inserción y uno de eliminación. · Cada disparador puede aplicarse a una sola tabla. Sin embargo, un mismo disparador se puede aplicar a las tres acciones del usuario: UPDATE, INSERT y DELETE. · No se puede crear un disparador en una vista ni en una tabla temporal, aunque los disparadores pueden hacer referencia a las vistas o tablas temporales. · Los disparadores no se permiten en las tablas del sistema. Aunque no aparece ningún mensaje de error si crea un disparador en una tabla del sistema, el disparador no se utilizará. Ejemplo 43: Crear un trigger que permita insertar los datos de un producto siempre que el nombre seaúnico CREATE TRIGGER TX_PRODUCTO_INSERTA ON PRODUCTS FOR INSERT AS IF (SELECT COUNT (*) FROM INSERTED, PRODUCTS WHERE INSERTED.PRODUCTNAME = PRODUCTS.PRODUCTNAME)>1 BEGIN ROLLBACK TRANSACTION PRINT 'LA DESCRIPCION DEL PRODUCTO SE ENCUENTRA REGISTRADO' END ELSE PRINT 'EL PRODUCTO FUE INGRESADO EN LA BASE DE DATOS' Ejemplo 44: Crear un trigger que no permita eliminar al cliente que tenga órdenes de pedidos. CREATE TRIGGER TX_ELIMINA_ELIMINA ON CUSTOMERS FOR DELETE AS IF EXISTS (SELECT * FROM ORDERS WHERE ORDERS.CUSTOMERID = (SELECT CUSTOMERID FROM DELETED)) BEGIN ROLLBACK TRANSACTION PRINT 'EL CLIENTE TIENE REGISTRADO POR LO MENOS 1 PEDIDOS' END Ejemplo 45: Crear un trigger que permita actualizar los productos que tengan el precio unitario y el stock mayor a cero. CREATE TRIGGER TX_PRODUCTO_ACTUALIZA ON PRODUCTS FOR UPDATE AS IF (SELECT UNITPRICE FROM INSERTED) <= 0 OR (SELECT UNITSINSTOCK FROM INSERTED) <= 0 BEGIN PRINT 'EL PRECIO O UNIDADES EN STOCK DEBEN SER MAYORES A CERO' ROLLBACK TRANSACTION END Ejemplo 46: Crear un trigger que no permita actualizar el código de la tabla productos. CREATE TRIGGER TX_PRODUCTO_ACTUALIZA_ID ON PRODUCTS FOR UPDATE AS IF UPDATE(PRODUCTID) BEGIN PRINT 'NO SE PUEDE ACTUALIZAR EL ID DEL PRODUCTO' ROLLBACK TRANSACTION END Ejemplo 47: Crear un trigger que audite los registros realizados en la tabla clientes. -- PRIMERO HACEMOS LO SIGUIENTE CREATE TABLE REGISTRO_USUARIOS( ID INT IDENTITY(1,1) PRIMARY KEY, NOTA VARCHAR(25) NOT NULL, USUARIO VARCHAR(25) NOT NULL, FECHA DATE NOT NULL, SOFTWARE VARCHAR(25) NULL, NOMBRE_EQUIPO VARCHAR(25) NOT NULL) -- LUEGO HACEMOS LO SIGUIENTE CREATE TRIGGER REGISTRAR_USUARIO ON CUSTOMERS FOR INSERT AS INSERT INTO REGISTRO_USUARIOS VALUES ('INSERTÓ UN CLIENTE',SUSER_SNAME(), GETDATE(), APP_NAME(), HOST_NAME()) jopegar@gmail.com #jopegar 977275421 CUALESELNEGOCIOSOCIO.blogspot.COM 1 jopegar@gmail.com #jopegar 977275421 CUALESELNEGOCIOSOCIO.blogspot.COM 17
Compartir