EL T-SQL se puede utilizar desde multitud de aplicaciones y desde diferentes lenguajes de progrmación :
- Desde Visual Basic:NET
- Desde C#
- Desde Visual C++
- Desde ASP.NET
- Desde PHP
- Desde Python
- Desde cualquier otro lenguaje que tenga drivers para SQL Server
No se utiliza dentro de estos lenguajes sino en desde los llamados Stored Procedures (SP) que estan en la propia base de datos. Seguro que muchos habeis oido hablar de ellos, hasta es más que probable que los utiliceis, si es así podeis saltaros este pequeño ejemplo y pasar al siguiente punto. Pero desde aquí vamos a profundizar un poco más en ese tema de los SP y ver que no solo sirven para hacer consultas, inserts, updates y deletes.
Una muestra de T-SQL y de SP seria:
Alter Procedure pubs_listar_authors As Select * From authors
Este SP devuelve un conjunto de resultados de la base de datos PUBS, devuelve todos los registros de la tabla authors.
Una buena referencia para aprender y ver el poder de los SP es la documentación de Microsoft que tiene el SQL Server: https://docs.microsoft.com/es-es/sql/t-sql/language-reference?view=sql-server-ver15 y podemos ver los procedmientos almacenados de sistema https://docs.microsoft.com/es-es/sql/relational-databases/system-stored-procedures/system-stored-procedures-transact-sql?view=sql-server-ver15
T-SQL como lenguaje de programación.
Pero esto es la parte sencilla del T-SQL, como he comentado antes no solo sirve para hacer consulta o insert, T-SQL es un potente lenguaje de programación orientado al SQL Server y como tal tiene :
- instrucciones para el control de flujo,
- variables,
- tipos de datos
- Funciones matemática, de tratamiento de cadenas, de fecha y hora
Pero además incluye funciones propias del SQL Sever para trabajar con las bases de datos.
Estructura del lenguaje
Tipos de datos.
Como todo lenguaje de programación T-SQL posee una serie de tipos de datos, estos corresponden con los tipos de datos que pueden utilizarse en SQL Server al definir tablas, entre ellos podemos destacar :
- int Datos enteros (números enteros) comprendidos entre -2^31 (-2.147.483.648) y 2^31 - 1 (2.147.483.647).
- decimal Datos de precisión y escala numérica fijas comprendidos entre -1038 +1 y 1038 – 1.
- numeric Funcionalmente equivalente a decimal.
- char Datos de caracteres no Unicode de longitud fija con una longitud máxima de 8.000 caracteres.
- varchar Datos no Unicode de longitud variable con un máximo de 8.000 caracteres.
- datetime Datos de fecha y hora comprendidos entre el 1 de enero de 1753 y el 31 de diciembre de 9999, con una precisión de 3,33 milisegundos.
Tiene más tipos de datos pero no los voy a poner todos aqui porque los podeis encontrar en los books OnLine del SQL Sever. También podemos definir nuestros propios tipos de datos (tipos de datos definidos por el usuario) para esto utilizaremos el SP de sistema sp_addtype que se encuentra en la base de datos MASTER.
sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @nulltype = ] ''null_type'' ] [ , [ @owner = ] ''owner_name'' ]
Donde :
@typename = type - Es el nombre del tipo de datos definido por el usuario.
@phystype = system_data_type - Es el tipo de datos físico, o proporcionado por Microsoft® SQL Server (decimal, numeric, char)
@nulltype = ''null_type'' - Indica la forma en que el tipo de datos definido por el usuario trata los valores nulos
@owner = ''owner_name'' - Especifica el propietario o el creador del nuevo tipo de datos
Veamos un ejemplo: Creamos un tipo de dato que sea DNI que contendra el DNi y no permitira nulos, con una loguitud de 12 cifras (99.999.999-P)
USE master EXEC sp_addtype dni, ''varchar(12)'', ''NOT NULL''
USE MASTER significa que cuando usamos el SP sp_addtype utilizaremos la base de datos Master que es la que contiene los SP de sistema, esta instrucción solo es necesaria ponerla cuando ejecutamos SP de otras bases de datos como puede ser la MASTER, si este SP estuviera en la base de datos PRUEBAS utilizariamos USE PRUEBAS en lugar de la MASTER.
Comentarios.
Como todo lenguaje de programación en T-SQL también podemos comentar nuestro código para que éste pueda ser mas amigable y leerse con más comodidad. Los comentarios se identifican de la siguiente forma :
-- Comentario de una linea /* comentario de varias lineas */
Variables.
Para declarar variables dentro del SP utilizaremos la palabra reservada Declare seguida de @ nombre de variable y tipo de datos, de la siguiente forma:
Declare @NombreVariable Varchar(40)
Para inicializarla utilizaremos la palabra reservada Set o Select :
Set @NombreVariable = ''PRUEBAS'' Select @NombreVariable = ''PRUEBAS''
Otra forma de utilizar variables es recibiendolas como parametros de entrada o de salida desde el SP, la forma de utilizarlas sería:
Create sp_Pruebas @Var1 numeric, @Var2 numeric = 0, @Var3 numeric output As
En este ejemplo se crean tres tipos de variables, la primera recibirá un parámetro desde el exterior ya sea desde ASP, VB, C++ o desde otro SP ... la segunda puede recibir parámetros o no, si no los recibe asumirá como parámetro por defecto el 0 que es con el que la hemos inicializado y la última recibirá un parámetro y devolverá un valor al exterior.
Control del flujo del programa.
Para controlar el flujo del programa disponemos de una serie de instrucciones:
Palabra clave definición
BEGIN...END Define un conjunto de instrucciones.
BREAK Sale de un bucle while.
CONTINUE Continua un bucle while.
IF...ELSE Define una ejecución condicional y, opcionalmente, una ejecución alternativa si la condición es FALSE.
RETURN Sale del Stored Procedure si ejecutar nada más.
WAITFOR Espera cierto tiempo a seguir con la ejecución de SP.
WHILE Repite instrucciones mientras una condición específica sea TRUE.
En los ejemplos veremos como funcionan todas estas instrucciones y como utilizarlas en los Stored Procedures.
Funciones
Bajo estas lineas pongo un ejemplo de diferentes funciones en T-SQL (no están todas las funciones), pero como todo lo que explico en este artículo podéis encontrar más información y el listado de todas las funciones en los Books OnLine que vienen con el SQL Server.
Funciones de Cadena Entre otras disponemos de:
- ASCII Devuelve el código ASCII del carácter más a la izquierda de una expresión de caracteres.
- CHAR Una función de cadena que convierte un código ASCII int en un carácter.
- LEN Devuelve el número de caracteres.
- LTRIM Devuelve una expresión de caracteres después de quitar los espacios en blanco a la izquierda.
- REPLACE Reemplaza por una tercera expresión todas las apariciones de la segunda expresión de cadena proporcionada en la primera expresión de cadena.
- SUBSTRING Devuelve parte de una expresión de caracteres.
- UPPER Devuelve una expresión de tipo carácter con datos de carácter en minúscula convertidos a mayúscula.
- ...
Funciones de fecha y hora Entre otras disponemos de:
- DATEADD Devuelve un valor datetime nuevo que se basa en la suma de un intervalo a la fecha especificada.
- DATEDIFF Devuelve el número de límites de fecha y hora que hay entre dos fechas especificadas.
- DATEPART Devuelve un entero que representa la parte de la fecha especificada de la fecha indicada.
- GETDATE Devuelve la fecha y hora actuales del sistema.
- ...
Además de estas tenemos también
- Funciones de agregado
- Funciones de configuración
- Funciones de cursor
- Funciones matemáticas
- Funciones de Seguridad
- Funciones de sistemas
- Funciones de texto e imagen
Además de lo explica en esta sección sobre la estructura del Transact-SQL, éste posee unas funciones y/o instrucciones propias del SQL Server para trabajar exclusivamente con las bases de datos, algunas conocidas y/o utilizadas y otras no tanto, entre ellas tenemos:
- SELECT Para realizar consultas a las bases de datos.
- INSERT Para insertar datos.
- DELETE Para borrar datos
- UPDATE Para modificar datos
- DROP Para borrar tablas, SP, Indices, Triggers ...
- CREATE Para crear tablas, SP, Indices, Triggers
- ALTER Para agregar Colummnas, Modificar Procedures ...
Ejemplo de Stored Procedures
El poder del T-SQL lo vemos al trabajar con un lenguaje de programación y además con bases de datos.
Veamos un ejemplo para ilustrar lo que podemos hacer los SP y la base de datos :
El ejemplo que realizaremos consiste en insertar unos datos de un usuario que se de de alta en un web, estos datos se insertaran en dos tablas diferentes desde el mismo SP y enviaremos un mail al usuario confirmado su inscripción, veremos que no es necesario volver al programa cada vez que hacemos una inserción de datos en la base de datos. La estructura de las tablas es la siguiente :
Tabla 1 : pr_usuarios
IDUsuario numeric
Nombre varchar(50)
login varchar(20)
contrasena varchar(20)
email varchar(250)
Tabla 2 : pr_aficiones
IDAficiones numeric
IDUser numeric
Clase_Aficion varchar(250)
Qué hace este Stored Procedure?
Primero declaramos las variables de entrada que serán las que reciban los parámetros desde el exterior
Alter Procedure add_usuario
@Nombre varchar(50),
@login varchar(20),
@contrasena varchar(20),
@email varchar(250),
@clase_aficion varchar(50)
As
-- Declaramos una variable para obtener el ID del usuario después
-- de insertar sus datos en la tabla para poder luego insertar las
-- aficiones en la segunda tabla.
Declare @IDUser numeric
-- Declaramos una variable para el texto del mail que le enviaremos
-- al usuario después de darse de alta
Declare @Mensaje varchar(1000)
-- Antes de insertar los datos comprobamos que ese login no exista
-- en la base de datos para no tener usuarios repetidos.
if not exists(Select IDUsuario From pr_usuarios
Where login = @login And contrasena = @contrasena)
Begin
-- Si el usuario no existe, insertamos los datos
insert into pr_usuarios(Nombre, login, contrasena, email)
values(@Nombre, @login, @contrasena, @email)
-- Si no se produce error a la hora de hacer el insert
-- Obtenemos el id del registro recien insertado.
if @@ERROR <> 0
Begin
Select @IDUser = (Select @@IDENTITY)
-- E insertamos los datos de las aficiones del usuario
-- en la segunda tabla
insert into pr_aficiones(IDUsuario, Clase_Aficion)
Values(@IDUser, @clase_aficion)
-- Escribimos el texto del mail
Select @Mensaje = ''Gracias por darse de alta ''
Select @Mensaje = @Mensaje + Char(13) + Char(10)
Select @Mensaje = @Mensaje + ''Login : '' + @login
Select @Mensaje = @Mensaje + Char(13) + Char(10)
Select @Mensaje = @Mensaje + ''Contraseña : '' + @contrasena
-- Enviamos mail de confirmación de registro.
-- Ejecutamos el SP de envío de mail que esta en
-- la base de datos master.
Exec master.dbo.xp_sendmail
@recipients = @email,
@subject = ''Bienvenidos al web.com'',
@message = @Mensaje
End
End
En este SP nos ahorramos volver al programa en varias ocasiones, es decir, cuando hacemos el primer insert, después volveríamos al programa para lanzar otra sentencia SQL para obtener el IDENTITY utilizando el max(IDUsuario) de la siguiente forma :
Select max(IDUsuario) From pr_usuarioCon el peligro que tiene esta sentencia en ASP si acceden dos usuarios simultáneamente, después volveríamos hacer otro insert para insertar los datos en la segunda tabla y por último enviaríamos el mail, de esta forma con el SP los cuatro pasos que hacemos en ASP o VB los realizamos en uno solo, con el ahorro que esto lleva de enviar datos desde el SQL al programa y viceversa.
Para poder probar el Stored Procedure solo teneis que copiar el código verde y ejecutarlo.
Cursores
¿Cursores? que son y para que se usan.
Las sentencias SQL producen un conjunto de resultados que después procesamos desde nuestro programa, pero a veces es más útil que este conjunto de resultados se devuelve fila a fila y poderlo procesarlo en el mismo Stored Procedure.
Para definiros de una forma clara se podría decir que son como un blucle que procesa registros en bucle, es decir, es un conjunto de registros que devuelve una sentencia Select. Lo que haríamos con un while o for en Transact SQL es un cursor. Pero como mejor lo entenderemos es con un ejemplo.
Pero antes del ejemplo explicar un como funcionan los cursores:
Declararemos una varable de cursor con la sentencia:
Declare NombreCursor Cursor ForDespués aplicaremos una sentencia Select para recuperar un conjunto de resultados y procesarlos, también podemos ejecutar la select desde otro SP con un Exec y abrimos el cursor con :
Open NombreCursory recuperamos la primera fila del cursor con FECH NEXT:
Fetch Next From NombreCursor Into @Var1, @Var2donde Var1, Var2 ... Varn son las variables donde se dejaran los datos extraídos de la sentencia select, se han de poner tantas variables como campos se devuelvan de la sentencia select y en el mismo orden.
Una vez abierto el cursor comprobamos que todo funcione correctamente y recuperamos el resto de los datos para esto utilizamos un WHILE ...FETCH de la siguiente forma:
While @@FETCH_STATUS = 0
Begin
........
Fetch Next From NombreCursor
Into @Var1, @Var2
End
@@FETCH_STATUS puede tener los siguientes valores dependiendo del resultado de la consulta :
Valor - definición
y por ultimo cerraremos el cursor y liberamos los recursos utilizados.
Ejemplo.
Este ejemplo es uno muy común que se encuentra en todas las webs "recordar contraseña", ¿ quien no ha olvidado la contraseña alguna vez ?.
Supongamos que un usuario se ha dado de alta mas de una vez con el mismo email y diferentes logins, bien, con este Stored Procedures con cursores podremos enviarle todos sus usuarios con sus passwords en un mismo mail. La explicación de SP esta entre el código en color verde, si queréis probar el SP solo tenéis que hacer Copy & Paste.
Create Procedure recordar_password-- Recibimos desde un programa exterior el mail
-- del usurio al que se le ha de enviar sus datos
@email varchar(250) As-- Declaramos la variable a utilizar en el SP
-- sMensaje, es el mensaje que enviaremos con los datos
Declare @sMensaje varchar(1000)-- Declaramos variable para el retorno de carro y el salto de linea
-- y le asignamos los valores
Declare @CRLF varchar(100) Select @CRLF = Char(10) + Char(13) + Char(10) + Char(13)-- Declaramos las variables que recogeran del cursor los
-- logins y passwords de un usuario
Declare @Login varchar(20) Declare @Pass varchar(20)-- Declaramos el cursor para hacer el bucle para
-- extraer los logins y passwords de un usuario concreto
Declare CursorUsuarios Cursor For-- Realizamos la Select para extraer los datos del usuario
Select Login, contrasena From pr_usuarios where email = @email-- Abrimos el cursor
Open CursorUsuarios-- Devolvemos la primera fila de resultados del cursor en las
-- variables que hemos declarado anteriormente
Fetch Next From CursorUsuarios Into @Login, @Pass-- Miramos que la instrucción FETCH se ejecutó correctamente.
if @@FETCH_STATUS = 0 Begin-- Iniciamos la parte del mensaje que enviaremos al usuario
Select @sMensaje = ''Su Login y Password. ''
Select @sMensaje = @sMensaje + @CRLF
-- Mientras hay resultados para procesar
While @@FETCH_STATUS = 0
Begin
-- Creamos el mensake con todos los logins y passwords-- que el usuario tiene.
Select @sMensaje = @sMensaje + ''Sus datos de acceso son:'' + Char(13)
Select @sMensaje = @sMensaje + '' Login : '' + @Login + Char(13)
Select @sMensaje = @sMensaje + '' Password : '' + @Pass + @CRLF
Select @sMensaje = @sMensaje + '' ------------------------'' + @CRLF
-- Recuperamos la siguiente fila
Fetch Next From CursorUsuarios
Into @Login, @Pass
End
-- Una vez recuperadas todas las filas para ese usuario
-- se envia un mail con sus datos
Exec master.dbo.xp_sendmail
@recipients = @email,
@subject = ''Sus Datos de acceso'',
@message = @sMensaje
End
-- Cerramos el cursor
Close CursorUsuarios
-- Quita la referencia al cursor y el SQL Server libera la-- estructura de datos ocupada por el cursor.
Deallocate CursorUsuarios
Triggers
En español se llaman o están traducidos por desencadenador son lo mismo que los Stored Procedures pero éstos se ejecutan desantendidamente y automáticamente cuando un usuario realiza una acción con la tabla de una base de datos que lleve asociado este trigger. Se pueden crear triggers para las sentencias de SQL Insert, Update y Delete.
Por ejemplo en la anterior tabla pr_usuararios podemos crear un trigger que cada vez que se inserte un nuevo registro envíe un mail de aviso al webmaster del web (este ejemplo esta puesto al final de esta página).
La estructura de un trigger es:
Create Trigger pr_usuarios_Trigger1 On dbo.pr_usuarios For /* Insert, Update, Delete */ AsLos triggers pueden incluir cualquier número y clase de instrucción de Transact-SQL. Desde el Trigger podremos obtener los datos de la fila que se ha modificado o añadido utilizando inserted o deleted:
Select * from deletedLimitaciones de los triggers.
- Solo se pueden aplicar a una tabla especifica, es decir, un trigger no sirve para dos o más tablas
- El trigger se crea en la base de datos que de trabajo pero desde un trigger puedes hacer referencia a otras bases de datos.
- Un Trigger devuelve resultados al programa que lo desencadena de la misma forma que un Stored Procedure aunque no es lo mas idóneo, para impedir que una instrucción de asignación devuelva un resultado se puede utilizar la sentencia SET NOCOUNT al principio del Trigger.
- Las siguientes instrucciones no se pueden utilizar en los triggers :
ALTER DATABASE CREATE DATABASE
DISK INIT DISK RESIZE
‘DROP’ DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE
RESTORE DATABASE RESTORE LOG
Ejemplo. Como ejemplo crearemos un Trigger que avise al webmaster con un mail cuando un usuario se da de alta en nuestro web, para crear el trigger solo se han de seguir los pasos arriba indicados, como muestran las imágenes y el código es el siguiente, haciendo Copy & Paste funciona. El funcionamiento del trigger es muy sencillo, declaramos dos variables, una para el mensaje que se enviará en el mail y otra para obtener el ID del registro recién insertado y luego este ID lo concatenamos al mensaje para enviárselo al webmaster.
Alter Trigger Trigger_Aviso_al_Webmaster On dbo.pr_usuarios For Insert As-- Declaramos las variables del mensaje y del ID del nuevo usuario
Declare @Mensaje varchar(200) Declare @ID numeric-- Obtenemos el id del usuario recien insertado
Select @ID = (Select IDUsuario From Inserted )
Select @Mensaje = ''Nuevo Usuarios en el web : '' + Convert(varchar(10), @ID)
Exec master.dbo.xp_sendmail
@recipients = ''webmaster@dominio.com'',
@subject = ''Nuevo usuario'',
@message = @Mensaje
Enlaces relacionadas:
Procedimientos almacenados (motor de base de datos)
Y esto es todo, y feliz programming
Saludos
Alex.
Si te ha gustado el artículo compartelo en:
