LENGUAJE TRANSACT SQL
DEFINICION
Transact-SQL también
es conocido por sus abreviatura como T-SQL, como definición es una extensión
del SQL Microsoft junto son Sybase. Lo que conocemos a SQL como el lenguaje de
búsqueda estructurado lo que viene hacer T-SQL es expander los estándares de
SQL no incluyendo programación procedural, como algunas otras funciones para
matemáticas, fechas, etc.
T-SQL
es un lenguaje sumamente potente que nos ayuda a definir tareas mientras
trabajamos con bases de datos. Debido a que este lenguaje tiene algunas
restricciones en el propio lenguaje casi siempre lo vemos utilizado en la
creación de los procedimientos almacenados, funciones de usuario y también en
los triggers. Podemos hacer uso de T-SQL en lenguajes de programación
como puede ser Visual Basic. NET, C, Java. También lo podemos ejecutar en un
entorno SQL Server Management Studio. Se dice que T-SQL es un lenguaje muy
parecido al lenguaje hablado.
COMANDOS
La escritura y ejecución de instrucciones de
Transact-SQL es una de las formas en que se puede realizar una consulta en SQL
Server. Cuando escriba y ejecute instrucciones de Transact-SQL, utilizará:
Ø Instrucciones
del Lenguaje de definición de datos (DDL), que se utilizan para crear objetos
en la base de datos.
Ø Instrucciones
del Lenguaje de control de datos (DCL), que se utilizan para determinar quién
puede ver o modificar los datos.
Ø Instrucciones
del Lenguaje de tratamiento de datos (DML), que se utilizan para consultar y
modificar los datos.
Instrucciones del Lenguaje de
definición de datos (DDL)
Las
instrucciones de DDL definen la base de datos mediante la creación de bases de
datos, tablas y tipos de datos definidos por el usuario. Las instrucciones de
DDL se utilizan también para administrar los objetos de la base de datos.
Algunas instrucciones de DDL son:
CREATE nombreObjeto
ALTER nombreObjeto
DROP nombreObjeto
De
forma predeterminada, sólo los miembros de la función sysadmin, dbcreator,
db_owner o db_ddladmin pueden ejecutar instrucciones de DDL. En
general, se recomienda no utilizar otras cuentas para crear objetos de base de
datos. Si distintos usuarios crean sus propios objetos en una base de datos, se
requiere que el propietario de cada objeto conceda los permisos adecuados a
cada usuario de esos objetos. Esto genera trabajo administrativo y debe
evitarse. Restringir los permisos de instrucciones a esas funciones también
evita los problemas de propiedad de los objetos que se pueden producir cuando
el propietario de un objeto se ha quitado de una base de datos o cuando el
propietario de un procedimiento almacenado o vista no es propietario de las
tablas subyacentes.
Ejemplo
La
secuencia de comandos siguiente crea una tabla llamada TbProducto en la
base de datos BdTailor. Incluye las columnas CodProducto, NombreProducto,
Precio, existencia, FechaIngreso, idMarca y idCategoria
USE
BdTailor
Ø Crear
Una Tabla
create table TbProducto
(
CodProducto char(6) NOT NULL,
NombreProducto Varchar(30) NOT NULL,
Precio money NOT NULL,
existencia int NOT NULL,
FechaIngreso datetime NOT NULL,
idMarca char(4) NOT NULL,
idCategoria char(3) NOT NULL
)
Go
Ø Modificar
Una Tabla
alter table TbProducto ADD constraint PK_Producto PRIMARY KEY(CodProducto)
go
Ø Eliminar
Una Tabla
Drop
table TbProducto
Instrucciones del Lenguaje de
Manipulación de datos (DML)
Las instrucciones de DML funcionan con los datos de la base de
datos. Mediante estas instrucciones puede cambiarlos o recuperar información.
Las instrucciones de DML incluyen:
SELECT
INSERT
UPDATE
DELETE
De forma predeterminada, sólo los miembros de las funciones sysadmin,
dbcreator, db_owner o db_datawriter pueden ejecutar
instrucciones de DML.
Ejemplo
En este ejemplo se recupera el identificador de categoría, nombre
de producto, identificador de producto y precio por unidad de los productos de
la base de datos BdTailor.
Use
BdTailor
v Select codProducto,NombreProducto,Precio,existencia from TbProducto
Go
v
Insert Into TbMarca(CodMarca,NombreMarca) values('M001','SAMSUNG')
Go
v
Update TbMarca set NombreMarca= 'LG' where CodMarca='M001'
Go
v
Delete TbMarca where CodMarca='M001'
Go
Instrucciones del Lenguaje de
control de datos (DCL)
Las instrucciones de DCL
se utilizan para cambiar los permisos asociados con un usuario o función de la
base de datos. En la tabla siguiente se describen las instrucciones de DCL.
Instrucción
|
Descripción
|
GRANT
|
Crea una entrada en el sistema de seguridad que permite a un
usuario trabajar con datos o ejecutar ciertas instrucciones de Transact-SQL.
|
DENY
|
Crea una entrada en el sistema de seguridad que deniega un
permiso de una cuenta de seguridad e impide que el usuario, grupo o función
herede el permiso a través de su pertenencia a grupos o funciones.
|
REVOKE
|
Quita un permiso concedido o denegado previamente.
|
De forma predeterminada, sólo los miembros de la función sysadmin,
dbcreator, db_owner o db_securityadmin pueden ejecutar
instrucciones DCL.
Ejemplo
En este ejemplo se concede a la función public el permiso
para consultar la tabla TbProducto
USE BdTailor
GRANT SELECT ON TbProducto TO public
GO
1) Autorizaciones
Para
autorizar, el SQL dispone de la siguiente sentencia:
Donde
tenemos que:
a) privilegios puede ser:
•
ALL PRIVILEGES: todos los privilegios sobre el objeto especificado.
•
USAGE: utilización del objeto especificado; en este caso el dominio.
•
SELECT: consultas.
•
INSERT [(columnas)]: inserciones. Se puede concretar de qué columnas.
•
UPDATE [(columnas)]: modificaciones. Se puede concretar de qué columnas.
•
DELETE: borrados.
•
REFERENCES [(columna)]: referencia del objeto en restricciones de integridad.
Se
puede concretar de qué columnas.
b) Objeto debe ser:
•
DOMAIN: dominio
SET
TRANSACTION READ WRITE;
UPDATE
empleados SET sueldo = sueldo – 1000 WHERE num_proyec = 3;
UPDATE empleados
SET sueldo = sueldo + 1000 WHERE num_proyec = 1;
COMMIT;
GRANT
privilegios ON objeto TO usuarios
[WITH GRANT
OPTION];
©
FUOC • P06/M2109/02149 50 El lenguaje SQL
•
TABLE: tabla.
•
Vista.
c)
Usuarios puede ser
todo el mundo: PUBLIC, o bien una lista de los identificadores de los usuarios
que queremos autorizar.
d) La opción WITH GRANT OPTION permite que el usuario que autoricemos pueda, a
su vez, autorizar a otros usuarios a acceder al objeto con los mismos privilegios
con los que ha sido autorizado.
2) Desautorizaciones
Para
desautorizar, el SQL dispone de la siguiente sentencia:
Donde
tenemos que:
a) privilegios, objeto y usuarios son
los mismos que para la sentencia GRANT.
b) La opción GRANT OPTION FOR se utilizaría en el caso de que quisiéramos eliminar
el derecho a autorizar (WITH GRANT OPTION).
c) Si un usuario al que hemos autorizado
ha autorizado a su vez a otros, que al mismo tiempo pueden haber hecho más
autorizaciones, la opción CASCADE hace
que queden desautorizados todos a la vez.
d) La opción RESTRICT no nos permite desautorizar a un usuario si éste ha autorizado
a otros.
INSTRUCCIONES DEL LENGUAJE DE
CONTROL DE FLUJO (CFL)
Disponemos de diferentes
elementos para el control de flujo, como pueden ser RETURN, IF... ELSE, WHILE,
BREAK, CONTINUE, GO TO, EXECUTE, etc. En los siguientes apartados aprenderemos
cómo utilizarlos.
IF…
ELSE
Proporciona una ejecución condicional,
permite ejecutar o no ciertas instrucciones dependiendo de si se cumple o no
una determinada condición.
Si la condición se
cumple (da como resultado TRUE) se ejecuta la instrucción SQL o bloque de
instrucciones que aparecen a continuación de la condición, si la condición no
se cumple se ejecutan las sentencias que aparecen después de la palabra ELSE.
El bloque ELSE es opcional.
Ejemplo: Si nos queremos guardar en
una consulta todos los ejemplos para probarlos en cualquier momento, es
conveniente antes de los CREATE PROCEDURE colocar un DROP PROCEDURE para que la
instrucción CREATE no dé error si el procedimiento ya existe, pero la primera
vez la instrucción DROP PROC nos dará error porque el procedimiento todavía no
existe, así que lo mejor es ejecutar el DROP sólo si el procedimiento existe,
utilizando la función object_id(‘nombre_de_objeto’,’tipo de objeto’) que nos
devuelve el id del objeto y NULL si el objeto no existe.
Otro ejemplo. Ahora
queremos el procedimiento y si no existe se mandará un mensaje "el
procedimiento no existe":
Cuando queremos definir
un bloque de instrucciones utilizamos los delimitadores BEGIN..END Se pueden
anidar varias sentencias IF hasta el límite que permita la memoria.
WHILE
- BREAK – CONTINUE
Esta instrucción permite
definir un bucle que repite una sentencia o bloque de sentencias mientras se
cumpla una determinada condición.
Podemos anidar bucles,
colocar un bucle WHILE dentro de otro.
BREAK Produce la salida
del bucle WHILE más interno. La instrucción BREAK interna sale al siguiente
bucle más externo. Todas las instrucciones que se encuentren después del final
del bucle interno se ejecutan primero y después se reinicia el siguiente bucle
más externo.
CONTINUE Hace que se
reinicie el bucle WHILE y omite las instrucciones que haya después de la
palabra clave CONTINUE.
Por ejemplo, tenemos los
siguientes bucles anidados:
LENGUAJE DE CONTROL DE TRANSACCIONES (TCL)
COMMIT: Guarda el trabajo realizado
ROLLBACK: Restaurar la base de datos a la
original, hasta el último COMMIT
Transacciones implicitas
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.
Para activar-desactivar el modo de transacciones implícitas debemos
ejecutar la siguiente Instrucción.
Resumen
En este trabajo hemos presentado los
comandos más utilizadas del lenguaje Transact sql.
Como ya hemos comentado el Lenguaje
Transact SQL es un lenguaje sumamente
potente que nos ayuda a definir tareas mientras trabajamos con base de datos.
Debido a que este lenguaje tiene algunas restricciones en el propio lenguaje
casi siempre lo vemos utilizado en la creación de los procedimientos
almacenados, funciones de usuario y también en los triggers
Recordemos cómo será la creación de
una base de datos con SQL:
1)
En
primer lugar, tendremos que dar nombre a la base de datos, con la sentencia
CREATE DATABASE, si la hay, o con CREATE SCHEMA.
2)
A
continuación definiremos las tablas, los dominios, las aserciones y las vistas
que formarán nuestra base de datos.
3)
Una
vez definidas las tablas, que estarán completamente vacías, se deberán llenar
con la sentencia INSERT INTO.
Cuando la base de datos tenga un
conjunto de filas, la podremos manipular, ya sea actualizando filas o bien
haciendo consultas.
Además, podemos usar todas las
sentencias de control que hemos explicado.
Summary
In this paper we have presented the most commonly
used language of Transact-SQL commands.
As mentioned the Transact SQL language is a very
powerful language that helps us define tasks while working with database.
Because this language has some restrictions on the language itself we see
almost always used in the creation of stored procedures, user roles and also in
triggers
Remember how will the creation of a database with
SQL:
1) First, we have to give a name to the database
with the CREATE DATABASE, if any, or CREATE SCHEMA.
2) Then define the tables, domains, assertions and
views that form our database.
3) Once you have defined tables, which will be
completely empty, they should be filled with the INSERT INTO statement.
When the database has a set of rows, we can manipulate,
either updating rows or doing consultations.
In addition, we can use all control statements that
we have explained.
Recomendaciones
Al
momento de Utilizar el Lenguaje Transact Sql hay que tener algunas
consideraciones:
- No utilizar un SELECT *, y esto más que buena práctica
debería ser regla
- No use la cláusula into nombre de tabla
(“SELECT… INTO”). Esto bloqueará mientras se ejecuta la consulta las
tablas del sistema. En su lugar cree primero las tablas y luego re-escribe
la sentencia como INSERT INTO tabla_name SELECT.
- Si usa el operador UNION y existe la seguridad de que
ambos select NO tienen registros duplicados, entonces es mejor usar UNION
ALL, para evitar que implícitamente se haga uso del operador DISTINCT el
cual puede requerir que se almacenen todos los datos de salida en una
tabla temporal para que luego se reordenen y se filtren los datos
duplicados, lo cual aumenta considerablemente el costo de la consulta.
- Es recomendable usar joins a un subquery.
- Promover el uso de EXISTS y NOT EXISTS,
en lugar de IN y NOT IN.
Conclusiones
El lenguaje Transact sql nos Permite:
§ Definir
bloques de instrucciones SQL que se tratan como unidades de ejecución.
§ Realizar
ejecuciones Condicionales.
§ Realizar
ejecuciones iterativas o repetitivas.
§ Garantizar
el tratamiento modular con la declaración de variables locales y el uso de
procedimientos almacenados.
§ Manipular
tupla a tupla el resultado de una consulta.
Apreciación del Equipo
Transact-SQL
es fundamental para trabajar con SQL Server. Ya que todas las aplicaciones que
se comunican con SQL Server lo hacen enviando instrucciones Transact-SQL al
servidor, independientemente de la interfaz de usuario de la aplicación.
Glosario de términos
DBCREATOR: Es un tipo de archivo DAO asociado a Third-Party Application desarrollado por
DreamMail para el Sistema Operativo de Windows.
CASCADE: Borra o actualiza el registro en la
tabla padre y automáticamente borra o actualiza los registros coincidentes en
la tabla hija. Tanto ON DELETE CASCADE como ON UPDATE CASCADE están disponibles
en MySQL 5.0. Entre dos tablas, no se deberían definir varias cláusulas ON
UPDATE CASCADE que actúen en la misma columna en la tabla padre o hija.
SET NULL: Borra o actualiza el registro en la
tabla padre y establece en NULL la o las columnas de clave foránea en la tabla
hija. Esto solamente es válido si las columnas de clave foránea no han sido
definidas como NOT NULL. MySQL 5.0 soporta tanto ON DELETE SET NULL como ON
UPDATE SET NULL.
NO ACTION: En el estándar ANSI SQL-92, NO ACTION
significa ninguna acción en el sentido de que unintento de borrar o actualizar
un valor de clave primaria no sera permitido si en la tabla referenciada hay
una valor de clave foránea relacionado. (Gruber, Mastering SQL, 2000:181). En
MySQL 5.0, InnoDB rechaza la operación de eliminación o actualización en la
tabla padre.
RESTRICT: Rechaza la operación de eliminación o
actualización en la tabla padre. NO ACTION y RESTRICT son similares en tanto
omiten la cláusula ON DELETE u ON UPDATE. (Algunos sistemas de bases de datos
tienen verificaciones diferidas o retrasadas, una de las cuales es NO ACTION.
En MySQL, las restricciones de claves foráneas se verifican inmediatamente, por
eso, NO ACTION y RESTRICT son equivalentes.)
SET DEFAULT: Esta acción es reconocida por el
procesador de sentencias (parser), pero InnoDB rechaza definiciones de tablas
que contengan ON DELETE SET DEFAULT u ON UPDATE SET DEFAULT.
ROLLBACK (REVERSIÓN): es una operación que devuelve a la
base de datos a algún estado previo. Los Rollbacks son importantes para la integridad de la
base de datos, a causa de que significan que la base de datos puede ser
restaurada a una copia limpia incluso después de que se han realizado
operaciones erróneas.
Bibliografías y Fuentes de
información
https://www.codejobs.biz/es/blog/2014/01/28/que-es-transact-sqlLINK DE DESCAGA SLIDESHARE: Lenguaje Transact SQL
