¡Descarga Creación de Procedimientos Almacenados en MS SQL Server y más Apuntes en PDF de Programación de Bases de Datos solo en Docsity!
Facultad de Estadística e Informática
MANUALITO MS-SQL SERVER
Contenido
1. Crear Store Procedures en MS SQL Server .................................................................................. 1
2. Crear Triggers en MS SQL Server................................................................................................. 5
3. Crear Vistas en MS SQL Server .................................................................................................... 9
1. Crear Store Procedures en MS SQL Server
Si lo hacemos por el Enterprise Manager, encima de la base de datos, desplegaremos la carpeta de
storeds, botón derecho y "New Stored Procedure"
El Enterprise Manager por defecto pone:
CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS
Un store procedure o procedimiento almacenado es un programa dentro de la base de datos que
ejecuta una acción o conjunto de acciones específicas.
Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código.
Los procedimientos almacenados pueden devolver valores (numérico entero) o conjuntos de
resultados.
Para crear un procedimiento almacenado debemos emplear la sentencia CREATE PROCEDURE.
CREATE PROCEDURE <nombre_procedure> [@param1 , ...]
AS
-- Sentencias del procedure
Para modificar un procedimiento almacenado debemos emplear la sentencia ALTER PROCEDURE.
ALTER PROCEDURE <nombre_procedure> [@param1 , ...]
Facultad de Estadística e Informática
AS
-- Sentencias del procedure
El siguiente ejemplo muestra un procedimiento almacenado, denominado spu_addCliente que inserta
un registro en la tabla "CLIENTES".
CREATE PROCEDURE spu_addCliente @nombre varchar ( 100 ),@apellido1 varchar ( 100 ),@apellido varchar ( 100 ),@codCli varchar ( 20 ),@fxNaciento datetime
AS
INSERT INTO CLIENTES(nombre, apellido1, apellido2, codcli, fxnacimiento) VALUES (@nombre, @apellido1, @apellido2, @codcli, @fxNaciento)
Para ejecutar un procedimiento almacenado debemos utilizar la sentencia EXEC. Cuando la ejecución
del procedimiento almacenado es la primera instrucción del lote, podemos omitir el uso de EXEC.
El siguiente ejemplo muestra la ejecución del procedimiento almacenado anterior.
DECLARE @fecha_nacimiento datetime
set @fecha_nacimiento = convert( datetime , '29/12/1976', 103 )
EXEC spu_addCliente 'David', 'Sarmiento', 'Cervantes', '00000002323', @fecha_nacimiento
Siempre es deseable que las instrucciones del procedure estén dentro de un bloque TRY CATCH y
controlados por una transacción.
ALTER PROCEDURE spu_addCliente @nombre varchar ( 100 ),@apellido1 varchar ( 100 ),@apellido varchar ( 100 ),@codCli varchar ( 20 ),@fxNaciento datetime
AS
BEGIN TRY
BEGIN TRAN
INSERT INTO CLIENTES(nombre, apellido1, apellido2, codcli, fxnacimiento) VALUES (@nombre, @apellido1, @apellido2, @codcli, @fxNaciento)
COMMIT
END TRY
Facultad de Estadística e Informática
IF ( SELECT SALDO FROM CUENTAS
WHERE NUMCUENTA = @numCuenta) < 0
BEGIN
RETURN 1
END
ELSE
RETURN 0
END
El siguiente ejemplo muestra como ejecutar el procedure y obtener el valor devuelto.
DECLARE @rv int
EXEC @rv = spu_EstaEnNumerosRojos '200700000001'
PRINT @rv
Otra característica interesante de los procedimientos almacenados es que pueden devolver uno o
varios conjuntos de resultados.
El siguiente ejemplo muestra un procedimiento almacenado que devuelve un conjunto de resultados.
CREATE PROCEDURE spu_MovimientosCuenta @numCuenta varchar ( 20 ) AS
BEGIN
SELECT @numCuenta, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO
FROM MOVIMIENTOS
INNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTA
WHERE NUMCUENTA = @numCuenta
ORDER BY FXMOVIMIENTO DESC
END
La ejecución del procedimiento se realiza normalmente.
EXEC spu_MovimientosCuenta '200700000001'
Facultad de Estadística e Informática
El resultado de la ejecución:
NUMCUENTA SALDO_ANTERIOR SALDO_POSTERIOR IMPORTE FXMOVIMIENTO
2. Crear Triggers en MS SQL Server
Un trigger (o desencadenador) es una clase especial de procedimiento almacenado que se ejecuta
automáticamente cuando se produce un evento en el servidor de bases de datos.
SQL Server proporciona los siguientes tipos de triggers:
o Trigger DML , se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista. o Trigger DDL , se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.
Trigger DML.
Los trigger DML se ejecutan cuando un usuario intenta modificar datos mediante un evento de
lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.
La sintaxis general de un trigger es la siguiente:
CREATE TRIGGER <Trigger_Name, sysname , Trigger_Name>
ON <Table_Name, sysname , Table_Name>
AFTER <Data_Modification_Statements, , INSERT , DELETE , UPDATE > AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
-- Insert statements for trigger here
Facultad de Estadística e Informática
Una consideración a tener en cuenta es que el trigger se ejecutará aunque la instrucción DML
(UPDATE, INSERT o DELETE) no haya afectado a ninguna fila. En este caso inserted y deleted devolverán un conjunto de datos vacío.
Podemos especificar a qué columnas de la tabla debe afectar el trigger.
ALTER TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON impide que se generen mensajes de texto
-- con cada instrucción
SET NOCOUNT ON ;
IF UPDATE (SALDO) -- Solo si se actualiza SALDO
BEGIN
INSERT INTO HCO_SALDOS(IDCUENTA, SALDO, FXSALDO) SELECT IDCUENTA, SALDO, getdate() FROM INSERTED
END
END
Los trigger están dentro de la transacción original (Insert, Delete o Update) por lo cual si dentro de
nuestro trigger hacemos un RollBack Tran, no solo estaremos echando atrás nuestro trigger sino también toda la transacción; en otras palabras si en un trigger ponemos un RollBack Tran, la transacción de Insert, Delete o Update volverá toda hacia atrás.
ALTER TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE AS
BEGIN
Facultad de Estadística e Informática
-- SET NOCOUNT ON impide que se generen mensajes de texto
-- con cada instrucción
SET NOCOUNT ON ;
INSERT INTO HCO_SALDOS (IDCUENTA, SALDO, FXSALDO) SELECT IDCUENTA, SALDO, getdate() FROM INSERTED
ROLLBACK
END
En este caso obtendremos el siguiente mensaje de error:
La transacción terminó en el desencadenador. Se anuló el lote.
Podemos activar y desactivar Triggers a través de las siguientes instrucciones.
-- Desactiva el trigger TR_CUENTAS
DISABLE TRIGGER TR_CUENTAS ON CUENTAS
GO
-- activa el trigger TR_CUENTAS
ENABLE TRIGGER TR_CUENTAS ON CUENTAS
GO
-- Desactiva todos los trigger de la tabla CUENTAS
ALTER TABLE CUENTAS DISABLE TRIGGER ALL
GO
-- Activa todos los trigger de la tabla CUENTAS
ALTER TABLE CUENTAS ENABLE TRIGGER ALL
Facultad de Estadística e Informática
Nota: No siempre podremos actualizar los datos de una vista, dependerá de la complejidad de la
misma (dependerá de si el conjunto de resultados tiene acceso a la clave principal de la tabla o no), y del gestor de base de datos. No todos los gestores de bases de datos permiten actualizar vistas, ORACLE, por ejemplo, no lo permite, mientras que SQL Server sí.
Para crear una vista debemos utilizar la sentencia CREATE VIEW, debiendo proporcionar un nombre a
la vista y una sentencia SQL SELECT válida.
CREATE VIEW <nombre_vista> AS (<sentencia_select>);
Ejemplo : Crear una vista sobre nuestra tabla alquileres, en la que se nos muestre el nombre y
apellidos del cliente en lugar de su código.
CREATE VIEW vAlquileres AS ( SELECT nombre, apellidos, matricula
FROM tAlquileres, tClientes
WHERE ( tAlquileres.codigo_cliente = tClientes.codigo ) )
Si queremos, modificar la definición de nuestra vista podemos utilizar la sentencia ALTER VIEW, de
forma muy parecida a como lo hacíamos con las tablas. En este caso queremos añadir los campos fx_alquiler y fx_devolucion a la vista.
ALTER VIEW vAlquileres AS (
SELECT nombre, apellidos, matricula, fx_alquiler, fx_devolucion
FROM tAlquileres, tClientes
WHERE ( tAlquileres.codigo_cliente = tClientes.codigo ) )
Por último podemos eliminar la vista a través de la sentencia DROP VIEW. Para eliminar la vista que
hemos creado anteriormente se utilizaría:
DROP VIEW vAlquileres;
Una vista se consulta como si fuese una tabla.
Para mayores referencias sobre MS SQL Server consultar:
http://technet.microsoft.com/es-es/library/default.aspx
