SQL Server: Programando Procedimientos, Triggers y funciones

Tipos de datos pueden ser almacenados en una tabla de SQL Server. 

Cadenas de Caracteres:

Para ver un poco como estos tipos de datos son definidos,

Puedes crear una tabla

Trata de definir varchar(8001) y recibes un error, SQL Management Studio en mi versión subraya con rojo que estas cometiendo un error. Si cambias el 8001 con la palabra “max” te permite crear la tabla.

Si se está usando Unicode para permitir más caracteres, se puede agregar una N al principio de estos tipos de datos:

Tipos de datos Numéricos

Los siguientes son los tipos de datos numéricos en SQL Server (Transact-SQL):

Fecha/Hora

Los siguientes son los tipos de datos de Fecha y Hora en SQL Server (Transact-SQL):

Variables también pueden ser definidas en SQL Server. En caso que no sepas que es una variable, a mi me gusta explicar que una variable es como una caja que se encuentra dentro de la memoria de la computadora, y esta caja te puede guardar un valor, y como hay muchas cajas, tienes que darle un nombre a esa caja (o variable) para identificarla luego. La computadora recordara el valor de esa variable hasta que el programa termine.

En su forma más sencilla puedes declarar una variable de la manera siguiente, solo necesitas la palabra reservada declare, seguida con el nombre de la variable, el nombre de la variable comienza con una arroba.  Y por ultimo el tipo de datos que la variable va a tener. Escribe:

declare @calificacion int

para asignar un valor a esa variable hay varias maneras, una es usar la palabra clave “SET”

Escribe lo siguiente para asignar “10” a la variable de calificacion:

set @calificacion = 10

para comprobar que el valor ha sido asignado puedes imprimir el valor usando “print”. Escribe lo siguiente.

print @calificacion

Variables en SQL Server también pueden ser usadas en la cláusula where. Imagina que quieres ver las materias con costo de laboratorio = 100

1. Declara la variable @costo
2. Inicializa la variable y dale el valor de 100
3. Usa la variable @costo en la clausula where:

declare @costo int

set @costo = 100

select * from materia

where Mcostolab = @costo

La sentencia IF  en SQL Server nos permite preguntarle a la computadora si una condición es verdadera o falsa. Generalmente dependiendo si la respuesta es verdadera tomaremos una decisión y si es falsa tomaremos otra decisión diferente.

-- suponiendo quieres ver si hay mas de OCHO materias

-- y si hay mas de ocho materias quieres imprimir un mensaje

-- declara la variable @numMaterias:

declare @numMaterias int

-- cuenta el numero de materias usando la funcion count(*) para contar el numero de registros en la tabla de

-- materia. y asigna el resultado a  la variable @numMaterias

select @numMaterias = count(*) from materia

-- si el numero de materias es mayor a 8, imprime un mensaje indicando eso

if @numMaterias > 8

       print 'Si hay mas de ocho materias'

En SQL Server, While es un tipo de bucle que nos permite validar una condición, y si es verdadera ejecuta el bloque si es falsa no la ejecuta.

DECLARE @vcount INT;

SET  @vcount = 1;

WHILE @vcount <= 3

BEGIN

   print @vcount;

   SET @vcount = @vcount + 1;

END;

Si estas familiarizado con otros lenguajes de programación funciona igual. 

En SQL Server, la sentencia BREAK es usada para detener el while loop antes de que la condición sea falsa. O sea para terminar antes de que termine por si solo.

En SQL Server la sentencia CONTINUE, continua con el bucle WHILE. Todas la instrucciones después de la palabra clave CONTINUE son ignoradas, el bucle se brinca a preguntar la condicion del ciclo while

Es posible que tus scripts en SQL Server encuentren condiciones de error que tengas que manejar. Por ejemplo cuando tratas de agregar filas si hay algún error al insertar desearías desplegar un mensaje apropiado.

En SQL Server, utilizas el par TRY .. CATCH para capturar y manejar errores.

En su sintaxis más sencilla:

BEGIN TRY 

     {Instrucciones de SQL} 

END TRY 

BEGIN CATCH 

     [{instrucciones de SQL}] 

END CATCH  

Si necesitas obtener más información del error generado. SQL Server tiene varias funciones que contienen información del error.

Estas son funciones solamente trabajan dentro del bloque CATCH

• ERROR_NUMBER() que te regresa el número de error.

• ERROR_SEVERITY() regresa la severidad. La severidad se muestra en una tabla enseguida.

• ERROR_PROCEDURE() regresa el nombre del stored procedure o trigger donde ocurrió el error.  
• ERROR_LINE() número de línea donde se encuentra el error.
• ERROR_MESSAGE() descripción del error.

-- RESTRICCIONES en SQL Server (CONSTRAINTS) son usadas para especificar reglas que quieres

-- que tus datos sigan en la base de datos.

-- Esto asegura que tu información sea mas confiable.

-- Existen varios tipos de restricciones:

-- NOT NULL - Asegura la columna no tenga nulos

-- UNIQUE - Aseguro que los valores en una columna sean siempre diferentes

-- PRIMARY KEY - Identifica en manera unica un registro

-- FOREIGN KEY - Identifica un registro en otra tabla

-- CHECK - Verifica que los valores sigan una condicion o regla definida

-- DEFAULT - Valor por defecto cuando el valor de la columna no es proporcionado

En SQL Server, DEFAULT define que hacer cuando una fila es insertada pero no se incluye el valor de la columna. Por ejemplo pudieras hacer que el costo del laboratorio sea por defecto 20, cuando no se proporcione el valor.

• Restricciones por defecto solamente se usan cuando se agregan filas con INSERT y son ignoradas cuando usas UPDATE o DELETE.
• Las restricciones por defecto son generalmente definidas al momento de creación de la tabla.

Procedimientos almacenados o también conocido por su nombre en inglés “stored procedure“ es un conjunto de instrucciones que son guardadas en la base de datos. La razón principal para crear stored procedures es reusar código que tendríamos que escribir una y otra vez.

En su forma más sencilla, esta es la sintaxis para crear un stored procedure. Utilizas Create Procedure, seguido por el nombre y la palabra AS y el código.

CREATE PROCEDURE <nombre de procedimiento>

AS

<Codigo>

Para borrar un stored procedure en SQL Server, simplemente escribes DROP PROCEDURE y el nombre del procedimiento que deseas borrar. Siguiendo con nuestro ejemplo, primero usa SQL Management Studio para validar que el procedimento “getMateriasComputacion” existe, debajo de la base de datos universidad, ve al folder “programmability” y expande ese folder para encontrar el folder de “Stored Procedures”, cuando expandes ese folder, encuentras el stored procedure  getMateriasComputacion si no lo  ves refresca la información presionando F5.

Para borrarlo usa la sentencia DROP PROCEDURE, escribe:

drop procedure getMateria

Una característica muy poderosa de los stored procedures en SQL Server, es que puedes pasar parámetros al procedimiento cuando se va a ejecutar. Cuando creas un Stored Procedure, tienes que saber si va a aceptar parámetros o no. En el siguiente ejemplo vas a crear un procedimiento al que le puedes pasar la carrera 32 para computación, 40 para mecánica o 38 electrónica,

-- aqui estas creando un procedimiento "getMateria" y

-- despues de "create procedure" estas pasando el parametro "carrera" de tipo char(2)

-- en lugar de usar un valor para "Mnocarrera", usas el parámetro que pasaras cuando

-- ejecutas este stored procedure.

CREATE PROCEDURE getMateria @carrera char(2)

AS

SELECT * FROM materia where Mnocarrera = @carrera

Los parámetros en un procedimiento almacenado de SQL Server se pueden inicializar. De tal manera que el valor inicial puede ser utilizado en el caso de que no se proporcione el valor del parámetro cuando se ejecute el procedimiento.

Modifica el mismo stored Procedure getMaterias para que si no usas un parametro te traiga las materias de la carrera de computación:

Crea el mismo stored procedure pero ahora inicializa la variable de carrera igual a 40 que es computación.

CREATE PROCEDURE getMateria @carrera char(2) = '40'

AS

SELECT * FROM materia where Mnocarrera = @carrera

Por supuesto no estas restringido a pasar solamente un parámetro, puedes definir más de un parámetro cuando creas el stored procedure en SQL Server. Simplemente separas con comas cada uno de los parámetros:

Imagina que quieres las materias de una carrera dada que tenga un cierto numero de créditos. La sentencia select se veria como sigue:

select * from materia

where Mnocarrera = '32'

and Mcred = 8

Pudieras crear un procedimiento en donde pases la carrera y el número de créditos. Escribe lo siguiente:

CREATE PROCEDURE getMateriaCreditos @carrera char(2), @creditos integer

AS

SELECT * FROM materia where Mnocarrera = @carrera and mcred = @creditos

En SQL Server, en lugar de pasar valores a un procedimiento, también es posible que el procedimiento regrese un valor.

Es muy fácil declarar un parametro de regreso, simplemente agregamos la palabra reservada OUTPUT después de la declaración del parametro. En lugar de usar la palabra OUTPUT también se puede usar en forma abreviada “OUT”.

CREATE PROCEDURE getCountMateria @carrera char(2), @TotalMaterias integer OUT

AS

SELECT @TotalMaterias = count(*) FROM materia where Mnocarrera = @carrera

En SQL Server, una tabla temporal es una tabla creada temporalmente que existe solamente durante una sesión. Veamos un ejemplo, como sabes, cuando usas SELECT INTO, puedes crear una tabla con los registros leídos y con la estructura de las columnas leídas, esta técnica sirve para tablas permanentes y también tablas temporales. Para declarar una tabla temporal solo agregas un signo de numero # como prefijo en el nombre de tabla que elijas, 

SQL Server trabaja de manera transaccional es decir esta diseñada para correr “todo o nada” por ejemplo cuando seleccionas usando select te trae todos los registros de acuerdo a tu filtro WHERE. Lo mismo cuando actualizas usando UPDATE, todos los registros filtrados con where se actualizan.

Algunas veces necesitaras procesar línea por línea de un resultado o un update. Para lograr esto tienes que usar CURSORES, cursores te permiten procesar individualmente cada fila regresada por una sentencia update o select

Pero tengo que decir que si puedes evitar usar cursores debes de evitarlo pues SQL trata cada fila en manera individual incrementando el tiempo de proceso en la computadora.

En SQL Server, existen dos tablas especiales que se usan cuando se actualiza una tabla. Esas tablas son INSERTED y DELETED. Como sus nombres lo indican INSERTED tiene los registros que se insertaron y DELETED tiene los registros que se borraron. Tiene lógica verdad?

Puedes usar la cláusula OUTPUT para ver los valores insertados y los valores borrados cuando usas una sentencia para modificar, insertar o borrar tus datos. Quise introducir el concepto de las tablas DELETED e INSERTED porque también pueden ser usadas en triggers que son presentados en el siguiente video. Si te estas preguntando porque no existe una tabla especial llamada UPDATED, es porque SQL inserta los valores nuevos en la tabla INSERTED y los valores viejos en la tabla DELETED. 

Triggers son una funcionalidad muy poderosa en SQL Server. Triggers son un tipo especial de Stored Procedures con acciones que se ejecutan cuando un evento especifico ocurre. Estos eventos pueden ser cuando se modifican datos (DML – Data MAnipulation Language) Por ejemplo cuando se actualiza una tabla, se insertan registros o se borran registros en una tabla, o los eventos también pueden ser cuando se cambia la estructura de la base de datos (DDL – Data Definition Language) que se ejecutan durante CREATE, DROP y Login. En este curso solamente veremos los Triggers relacionados con manipulación de datos que son los mas usados. Los triggers DDL son usados principalmente por administradores de la base de datos y aun entre los administradores su uso no es muy común.

Procedimientos Almacenados y funciones en SQL Server proporcionan funcionalidad similar.

Aunque son similares se recomienda usar funciones

• -Cuando se realizan cálculos y no hay cambios en la base datos.
• -Sobre todo si se sabe que estos valores calculados pueden ser usados en varios lugares, incluyendo stored procedures.
• -Una funcion puede ser usada con in SELECT, por ejemplo la funcion GETDATE() que regresa la fecha actual:

SELECT GETDATE()

Es aceptado por definicion que funciones regresan un valor, reciben parámetros. y no alteran la base de datos.

Funcion Escalar en SQL Server, es una función que regresa un solo valor.

Crea una funcion simple para calcular las cuotas, como un 15 por ciento del costo de la materia.

Puedes usar el template para generar una función escalar.

Funciones en SQL Server también pueden regresar una tabla. Como ya sabes crear funciones que regresan valores simples, casi no hay diferencia en como crear una función que regresa una tabla. Veamoslo con un ejemplo: