Lectura 8 - SQL

miércoles, 21 de enero de 2009

INTRODUCCIÓN
En esta lectura veremos la especialización de los procedimientos almacenados: los trigger o desencadenadores.
Además conoceremos su utilidad para actualizar tablas en cascada, mantener la integridad referencial y realizar auditoría de cambios.
Este texto nos va a servir para tomar ciertas decisiones sobre que instrucciones ejecutar en función de algún tipo de condición.

TRIGGERS
Los triggers serán llamados desencadenadores, estos consisten en una especialización de los procedimientos almacenados que se activa cuando se ejecutan sentencias que modifican los datos o sus estructuras de alguna manera. SQL Server incluye soporte para dos tipos de desencadenadores: DML Y DDL.

DESENCADENADORES DML
Se ejecutan específicamente ante instrucciones INSERT, UPDATE o DELATE sobre una tabla o vista y pueden incluir sentencias que se ejecutan dentro del desencadenador como una transacción. Estos desencadenadores son útiles para:
Ø Aplicar cambios en cascadas sobre otras tablas.
Ø Enviar sentencias maliciosas de INSERT, UPDATE y DELETE.
Ø Llevar auditoria en tablas al efecto, de los cambios realizados en la base de datos.
Ø Evaluar el estado de una tabla y después de los cambios.
Ø Mantener actualizadas tablas totalizadoras.
Ø Mantener actualizados campos calculados.

Existen otros tipos de desencadenadores DML:
· AFTER: se ejecutan después que la acción INSERT, UPDATE o DELETE se ejecutó. Estos desencadenadores solo pueden crearse sobre tablas.
· INSTEAD OF: se ejecutan en lugar de la acción disparada. Estos desencadenadores pueden crearse también mediante vistas.
· .NET: estos pueden ser de tipo AFTER o INSTEAD OF y, en lugar de ejecutar sentencias T-SQL, invocan uno o más métodos que son miembros de un ensamblado y están escritos en código administrado, como C#.

DESENCADENADORES DDL.
A diferencia de los DML, pueden tener alcance de base de datos o de servidor (reaccionan ante ciertos eventos disparados en ambos ámbitos).

DESENCADENADORES CLR.
La programación de desencadenadores CLR (Common Languaje Runtime), requiere las siguientes acciones:
o Definir el desencadenador como una clase en lenguaje de .NET.
o Registrar el ensamblado en SQL Server referenciado a aquél creado en el CLR.
Los desencadenadores CLR pueden tener acceso a las tablas INSERTED y DELETED a través de un objeto propio, el SqlCommand, del objeto SqlContext.
También podrá obtener la cantidad de registros modificados por una sentencia UPDATE a través de la propiedad ColumnsUpdate del objeto SqlTriggerContext.
La propiedad ColumnsUpdate tiene como función devolver un array de valores booleanos que representan cada columna en la tabla modificada. Un valor TRUE en el array indica que la columna fue modificada.
La información de los eventos que disparan el desencadenador quedará disponible en la propiedad EventData del objeto SqlTriggersContext. Esta propiedad contiene un valor en formato XML que tiene.
v Hora del evento.
v Identificación del proceso o de la conexión a partir de los cuales se disparó el evento.
v Tipo de evento que disparó el desencadenador.

CREACIÓN DE DESENCADENADORES DDL y DML.
Es posible controlar el momento de ejecución de un desencadenador de varias formas. En los desencadenadores AFTER, el desencadenador se dispara luego de la acción de INSERT, UPDATE o DELETE. Se puede reemplazar la sentencia AFTER por FOR. Los desencadenadores INSTEAD OF se ejecutan en lugar de la acción disparadora y antes del proceso de restricciones.

Antes de crear un desencadenador, debemos tener en cuenta que:
Ø La sentencia CREATE TRIGGER debe ser la primera en la secuencia de comandos.
Ø Los permisos de creación de desencadenador son los de la tabla sobre la cual se está creando el desencadenador y no pueden ser transferidos a otros usuarios.
Ø Los desencadenadores DML son objetos de la base de datos y deben seguir las reglas de identificación de objetos.
Ø Solo se pueden crear desencadenadores en la base de datos en la que esta posicionado, aunque el desencadenador puede apuntar a objetos fuera de está.
Ø No se pueden crear desencadenadores sobre tablas temporarias o las tablas de sistema. Estás ultimas tampoco pueden ser referenciadas por un desencadenador. Por lo tanto, se deberán utilizar las vistas del esquema.
Ø Los desencadenadores INSTEAD OF DELETE e INSTEAD OF UODATE no pueden crearse en tablas que tienen definidas claves foráneas con acciones de UPDATE o DELETE.
Ø A pesar de que las sentencias TRUNCATE TABLE actúa como un DELETE, los desencadenadores definidos para DELETE no se dispararán, ya que la sentencia TRUNCATE no guarda su accionar en el registro de transacciones.
Para crear un desencadenador, se debe indicar:
1. El nombre.
2. La tabla sobre la cual ha de crearse.
3. Cuando se ha de disparar.
4. Las sentencias que lo activan.
5. Las sentencias de modificación de datos que lo componen.
Donde:
· Esquema: es el nombre del esquema al que pertenece el desencadenador.
· Nombre: es el nombre del desencadenador, conformado de acuerdo con las reglas de especificación de nombres de objetos.
· Tabla vistas: es el nombre de la tabla o vistas sobre la cual se está creando.
· DATABASE: aplica el alcance del desencadenador a la base de datos donde se está creando.
· ALL SERVER: aplica el alcance del desencadenador al servidor.
· WITH ENCRYPTION: encripta el texto del desencadenador, por lo cual no se podrá obtener su definición si se consulta la tabla sysmessages.
· EXCUTE AS: especifica el contexto de seguridad en el que se ejecuta el desencadenador.
· AFTER: indica que el desencadenador se disparará cuando todas las sentencias que actúan como disparadores del desencadenador se hayan ejecutado con éxito.
· INSTEAD OF: indica que el desencadenador se ejecutará en lugar de la sentencia disparadora.
· { [DELETE] [,] [INSERT] [,] [UPDATE] }: palabras reservadas que indican hacia que sentencias se disparará el desencadenador, debiéndose indicar por lo menos una acción.
· Tipo_evento: indica el nombre del evento T-SQL que, luego de su ejecución, provocará el disparo de un desencadenador DDL.
· Grupo_evento: grupo predefinido de eventos dentro de los cuales se agrupan las sentencias que actuarán como disparadores del desencadenador.
· WITH APPEND: indica que se deberá agregar un desencadenador de un tipo especificado. Se lo utiliza para mantener compatibilidad con versiones anteriores.
· NOT FOR REPLICATION: indica que el desencadenador no se disparará ante eventos disparados por procesos de replicación.
· AS: indica el inicio del bloque de sentencias SQL que ejecutará el desencadenador.
· Sentencia_sql: se utilizan para fijar las acciones y condiciones del desencadenador. En el caso de las condiciones, permite especificar situaciones en las que el desencadenador no ejecutará las sentencias definidas.
· Especificador_de_metodo: en el caso de desencadenador del CLR, indica el método de un ensamblado que se debe vincular al desencadenador.

FUNCIONES UPDATE () y COLUMNS_UPDATE ()
Se puede utilizar la función UPDATE () para determinar si una sentencia INSERT o UPDATE afectó una columna especifica de una tabla. La función devuelve TRUE o FALSE.
Por su parte COLUMNS_UPDATE permite evaluar varias columnas al mismo tiempo, devolviendo un patrón de bytes por las columnas modificadas. El operador varbinary, y para verificar este patrón, es necesario utilizar operadores binarios y una máscara.
El patrón de bytes estará ordenado de izquierda a derecha con el bit menos significativo en cada byte a la derecha. El bite mas a la derecha del byte mas a la izquierda del patrón representa la primera columna de la tabla. En consecuencia, la función COLUMNS_UPDATE devuelve varios bytes si la tabla tiene más de 8 columnas.
Si tenemos que verificar tablas que tienen más de 8 columnas, se debe utilizar la función SUBSTRING para analizar el bit retornado.

ANULAR LA ACCIÓN DE UN DESENCADENADOR
No es lo mismo ejecutar un ROLLBACK en un desencadenador que en una transacción normal dentro de un procedimiento almacenado.
En los procedimientos almacenados los ROLLBACK cancelará la transacción más externa del bloque, pero permitirá que el flujo de control del programa continúe en proceso.
En el caso de los desencadenadores, se cancelará la acción de toda la ejecución. Por este motivo se utiliza ROLLBACK sólo para el control de errores.
Los desencadenadores DDL se disparan para completar la acción iniciada con anterioridad. Por ejemplo ALTER_TABLE trigger se dispara cuando la tabla destino ha sido modificada con éxito; es por eso que no existen los desencadenadores DDL INSTEAD OF. Puede usar la sentencia ROLLBACK TRANSACTION para abortar la transacción y deshacer cualquier trabajo realizado.

DOCUMENTAR LOS DESENCADENADORES
En el caso de los desencadenadores encriptados, donde conviene documentar el código, se recomienda mantener al menos un gráfico de interacción entre desencadenadores y tablas afectadas, ya que puede resultar muy engorroso entender el funcionamiento del modelo de datos.

DESHABILITAR DESENCADENADORES
Para eliminar un desencadenador, se debe ejecutará la sentencia:
DISABLE TRIGGER [esquema .] nombre ON {nombre_objeto DATABASE SERVER } [;]

MODIFICAR DESENCADENADORES
Al igual que los procedimientos almacenados, la modificación de desencadenador exige la sentencia ALTER y la reescritura de toda su definición.

DESENCADENADORES ANIDADOS Y RECURSIVOS
Se considera que un desencadenador esta anidado cuando es disparado por acción de otro desencadenador. Los desencadenadores pueden anidarse hasta 32 niveles y su configuración puede modificarse por medio de la opción de configuración de servidor nested Triggers.
Por otra parte, los desencadenadores recursivos son aquellos sobre los cuales está permitida la llamada a sí mismos mediante la configuración de la opción RECURSIVE_TRIGGERS de la base de datos.
Existen dos tipos de recursividad:
Recursividad directa: se presenta cuando un desencadenador dispara una acción que provoca que el desencadenador se vuelva a ejecutar.
Recursividad indirecta: sucede cuando un desencadenador ejecuta una acción que dispara otro desencadenador, y este último, a su vez, vuelve a disparar el primero.
La opción de configuración de base de datos RECURSIVE_TRIGGERS solo previene contra la recursividad directa cuando está fijado en OFF. Los desencadenadores es INSTEAD OF no son contemplados por esta opción.

SQL AVANZADO

INSTRUCCIONES DE CONTROL EN T-SQL

IF..ELSE
La instrucción IF en T-SQL y SQL ANSI no utiliza THEN como bien podemos observar, ya que esta queda contenida entre el primer par de BEGIN…END que sucede a la instrucción IF y antecede la instrucción ELSE, por tal motivo no se le escribe.

En este ejemplo el IF modifica la gestión del proceso en función que se satisfaga la condición de ella.

CASE…WHEN…ELSE
Esta instrucción se utiliza para evaluar un conjunto de condiciones, y en función de cuál de ellas se cumpla, devolver un resultado entre muchos resultados posibles.

LA INSTRUCCIÓN BETWEEN
Permite abreviar la consulta de rango entre dos valores dados. Supongamos que usted desea saber cuántos empleados de la empresa poseen un número de legajo mayor a 1000 y menor a 5000. O sea, si están en el rango de legajos entre 1000 y 5000, usted podría hacer la consulta de dos formas diferentes, una sin la instrucción BETWEEN y la otra con la instrucción BETWEEN.

Sin BETWEEN

Con BETWEEN

USO DE CURSORES Y LA ISNTRUCCIÓN WHILE
El uso de la instrucción WHILE es independiente del uso de los cursores.
La instrucción WHILE se utiliza como un ciclo de control repetitivo que permite la ejecución de un conjunto de instrucciones de T-SQL de forma repetida, mientras que la condición que se evalúa con el ciclo WHILE sea verdadera (TRUE) y se interrumpe la repetición en el momento que dicha condición se evalúa como falsa (FALSE).

Los cursores son las estructuras que nos permiten trabajar de a un registro por vez de las diferentes consultas que se realicen a la base de datos. Poseen ciertas características parecen importantes:
El uso de los cursores amplia el procesamiento de los resultados debido a que permiten posicionarse en una fila determinada del conjunto de resultados.
Se pueden modificar los datos de la fila en la que se encuentra posicionado sin ningún tipo de inconvenientes.
Permite definir los distintos niveles de visibilidad de las modificaciones que está realizando usted u otro usuario sobre los datos en cuestión.
Se puede utilizar en procedimientos almacenados, triggers, o en secuencia de instrucciones de T-SQL.

COMBINACIONES DE TABLAS
Para combinar tablas se debe comparar una o más columnas (campos) de una tabla con una o más columnas de otra tabla. Como resultado se obtendrá un conjunto de filas (registros) que poseen columnas seleccionadas de cada una de las tablas combinadas que cumplen con la o las condiciones de combinación.

COMBINACIONES INTERNAS
Las combinaciones conectan dos tablas al cumplir una condición de combinación que devuelve como resultado un conjunto de registros que cumplen con dicha condición, que si desea puede asignar en una nueva tabla. Estas combinaciones devuelven información equivalente cuando se encuentra en ambas tablas información equivalente. Los tipos de combinaciones internas son las equivalentes y las naturales.

COMBINACIONES EQUIVALENTES
Primero veamos el código a aplicar en cada caso. La sintaxis en SQL SERVER es:

La sintaxis de ANSI SQL es:

Obtendremos como resultado lo siguiente:

COMBINACIONES NATURALES

Sintaxis de SQL SERVER

Sintaxis de ANSI SQL

Y esta tabla es la que obtendríamos

COMBINACIONES CRUZADAS O SIN RESTRICCIÓN
Este tipo de combinaciones devuelve como resultado una combinación de todas las filas de todas las tablas involucradas. Una combinación de este tipo no posee cláusula WHERE en la sintaxis de combinación de SQL Server de dos o más tablas, y utiliza la palabra clave CROSS JOIN en el caso de la sintaxis de combinación ANSI.
Este tipo de combinación devuelve como resultado el producto cartesiano de las tablas involucradas. Esto quiere decir que si la tabla A posee N filas y la tabla B posee M filas una combinación cruzada de la tabla A con la B devolverá una tabla de N*M filas.
En general, este tipo de combinaciones no es de mucha utilidad a no ser que uno desee saber cuáles son todas las combinaciones posibles para realizar algún tipo de análisis estadístico o matemático.
La sintaxis de SQL Server es:

La sintaxis ANSI SQL es:

COMBINACIONES EXTERNAS
Por medio de estas combinaciones puede restringir en el conjunto de resultados las filas de una tabla, mientras que permite todas las de la otra tabla. Estas combinaciones sirve para buscar filas huérfanas.
Existen diferentes tipos de operadores de combinación
En SQL Server puede utilizar los siguientes tipos de operadores:
Incluye todas las filas de la primera tabla y solo las filas que coinciden de la segunda tabla (es lo mismo que utilizar un LEFT OUTER JOIN)
Incluye todas las mismas de la segunda tabla y solo las filas que coincidan de la primera tabla (es lo mismo que realizar un RIGHT OUTER JOIN)
El inconveniente de estos operadores es que no se puede garantizar que siempre el conjunto de resultados sea correcto. Suele ocurrir errores cuando alguna de las tablas existen valores NULL en los campos de combinación. Por lo tanto al utilizar la combinación externa se sugiere que use sintaxis del ANSI SQL.

En ANSI SQL se utiliza la siguiente sintaxis:
LEFT OUTER JOIN: incluye todas las filas de la primera tabla (la de la izquierda) y solo las filas que coinciden de la segunda tabla.
RIGHT OUTER JOIN: incluye todas las filas de la segunda tabla (la de la derecha) y solo las filas que coincidan de la primera tabla.
FULL OUTER JOIN: incluye todas las filas no coincidentes de ambas tablas.
Las combinaciones externas LEFT OUTER JOIN y RIGHT OUTER JOIN pueden devolver el mismo conjunto de resultados dependiendo del orden de las tablas.

AUTOCOMBINACIONES
Como lo sugiere el nombre, una autocombinación relaciona las filas de una tabla con otras filas de la misma tabla. Para la mayoría de las autocombinaciones se emplean consultas de comparación. Por ejemplo, si se desea una lista de los contactos de los clientes agrupados por el cargo que poseen y ordenado por él.

CONSULTAS DE AGRUPACIÓN
Este tipo de consultas nos permite obtener información agrupada de una o más tablas. Por ejemplo, saber la cantidad de clientes para un año determinado, las ventas promedio para un determinado lapso de tiempo; son todas consultas de agrupación de datos. La información obtenida con este tipo de consultas es cuantitativa y no cualitativa.
Si se deseara saber la cantidad de contactos que poseen el mismo cargo para cada cargo ordenado por cargo, la consulta sería la siguiente:

La consulta anterior utiliza tres nuevas instrucciones:
Count (*): es una función incorporada en SQL y por ende en T-SQL que cuenta cuantas filas satisfacen la selección. Además el argumento de dichas funciones no tiene porque ser el *, sino que puede ser un campo o conjunto de campos predeterminados.
También parece la instrucción GROUP BY que agrupa por el campo o conjunto de campos que se describan después de ella.
Y la instrucción ORDER BY, que ordena por el campo o conjunto de campo o conjunto de campos que aparecen luego de ella.
Podría hacer una selección más fina y querer ver los cargos que poseen más de un contacto con lo cual la consulta sería:

Como consecuencia de realizar la consulta anterior, obtendríamos la tabla de resultados que se muestra:

CONCLUSIÓN
Tenemos que mencionar que debemos de tener cuidado al trabajar con desencadenadores anidados, ya que si un desencadenador dispara a otro de los que están en la cadena de ejecución, el procesamiento superará la cantidad de desencadenadores anidados permitidos (32) y concluirá su ejecución.
Por eso en esta lectura se dieron a conocer las formas de tener cuidado con los desencadenadores.

BIBLIOGRAFÍA
Rosa (2005). Cap. 10 Triggers
Rozic (2004). Cap. 9 SQL Avanzado

Lectura 7 - SQL

Introducción

Durante esta lectura vamos a estudiar, analizar y comprender los procedimientos almacenados y su uso en el desarrollo de aplicaciones, aprenderemos a usar cursores y funciones y como dar a la aplicación un mejor ambiente transaccional, estas herramientas son de suma importancia en el desarrollo de BD y por lo cual su aprendizaje es de lo más apropiado para el DBA. Manejaremos la creación, modificación y eliminación de vistas a partir del código que permite SQL. Además como punto adicional aprenderemos a construir en el lenguaje de programación de T-SQL la captura y el manejo de excepciones.

1. Procedimientos almacenados
Un procedimiento almacenado son rutinas almacenadas en el servidor bajo un nombre específico los cuales a su vez pueden devolver resultados tabulares y mensajes al cliente que los utiliza, además de invocar sentencias de manipulación y definición de datos y retornar parámetros de salida.
Puntos para la creación de procedimientos almacenados estándares:
la sentencia CREATE PROCEDURE no se puede combinar con otras sentencias en un mismo procedimiento.
Los permisos para crear procedimientos apuntan al propietario de la base, aunque se pueden transferir a otros usuarios.
Los nombres de procedimientos deben seguir las reglas de identificadores, ya que son objetos de la base de datos.
La creación de procedimientos sólo se puede ejecutar en la base en la que se está posicionado al momento de ejecutar la sentencia.
Especificaciones:

Los parámetros de entrada y salida que necesite el procedimiento.
Las sentencias que ejecutará el procedimiento (incluyendo llamadas a otros procedimientos).
El valor de estado de ejecución que devuelve el procedimiento.

Además de estos puntos y características los procedimientos almacenados tienen otra propiedad llamada anidamiento la cual consiste en que un procedimiento puede llamar a otro procedimiento. Cada vez que un procedimiento almacenado invoca a otro, @@NESTLEVEL se incrementa en 1, terminando la ejecución del procedimiento si esta variable supera las 32 a nidaciones.
Recursividad.- es la técnica que permite que un procedimiento almacenado se llame asimismo, ejemplo, el cálculo factorial de un número.


2. Ejecución de sentencias T-SQL
Las sentencias son ejecutadas por el servidor de la siguiente manera:
El servidor verifica que la sintaxis del comando sea correcta (por ejemplo, SELET por SELECT) y los transforma en el lenguaje interno con el que trabaja, se le conoce como árbol de ejecución.
Se compila el árbol generando el plan de ejecución de la consulta, además verificando cuál es el plan más óptimo.
Ejecuta la consulta invocando al responsable de cada una de ellas.

La gran ventaja de utilizar procedimientos almacenados es que su plan de ejecución se almacena en la caché del servidor, con lo cual se obtiene un desempeño muy elevado del motor de SQL Server.
Al momento de crear un procedimiento almacenado, se crea una fila en la tabla sysobjects y otra en syscomments con su definición.

Sintaxis de programación para procedimientos almacenados

La sentencia CREATE PROCEDURE puede contener cualquier sentencia T-SQL, excepto las siguientes: CREATE VIEW, CREATE TRIGGER, CREATE PROCEDURE, CREATE RULE, CREATE DEFAULT.
Se pueden crear tablas temporales dentro de un procedimiento, si son locales éstas existirán mientras el procedimiento esté en ejecución.
Si ejecutamos un procedimiento que involucre a otro, el procedimiento tendrá acceso a todos los objetos del primero, incluyendo sus tablas temporales.
El número máximo de parámetros que acepta un procedimiento almacenado es de 2100.
El número máximo de variables que puede manejar un procedimiento almacenado está restringido por la memoria del servidor.
Dependiendo de la memoria del servidor, el máximo tamaño de un procedimiento almacenado será de 128 MB.
Cuando una aplicación ejecuta procedimientos mediante ODBC, se prefijarán las siguientes variables: SET QUOTED_IDENTIFIER ON, SET TEXTSIZE 2147483647, SET ANSI_DEFAULTS ON, SET CURSOR_CLOSE_ON_COMMIT OFF, SET IMPLICIT_TRANSACTIONS OFF. Estas configuraciones aumenta la portabilidad de las aplicaciones.
Para que la definición del procedimiento permanezca oculta, usamos la opción WITH ENCRYPTION.

Procedimientos de sistema
Consisten en un tipo de procedimientos almacenados en la base master del sistema. Funciona con el prefijo sp_ en el nombre. Puede ser invocado desde cualquier base donde se esté trabajando en ese momento. Por eso se recomienda no utilizar el prefijo sp_ en nuestros procedimientos de usuario

Procedimientos agrupados
La practicidad en el uso del agrupamiento de procedimientos es la eliminación; ya que permite que sean eliminados todos al mismo tiempo. Ejemplo: si existen para una misma aplicación mi_proc; 1, mi_proc; 2, etc., al eliminar mi_proc estaríamos eliminando todos los agrupados bajo ese nombre.

Procedimientos extendidos
Permiten crear rutinas externas en otros lenguajes, como C, para que SQL Server las cargue y ejecute como cualquier otro procedimiento.se crean mediante las DLL que SQL Server carga y ejecuta dinámicamente.

Procedimientos temporales
Se pueden crear procedimientos locales y globales de manera temporal, de la misma forma como creamos las tablas, precediendo su nombre por los caracteres de almohadilla simple y doble (# y ##), dichos procedimientos desaparecen al reiniciar el servidor.

Ejecución de procedimientos almacenados
La sentencia CREATE PROCEDURE permite crear un procedimiento almacenado, es decir, una colección de sentencias T-SQL o una referencia a un método del lenguaje común de referencia del entorno .NET (CLR).
SQL Server 2005 introduce la posibilidad del contexto de ejecución de los módulos definidos por el usuario: funciones, procedimientos, colas y desencadenadores. De esta forma se pueden asignar permisos a usuarios sobre le módulo en lugar de tener que asignárselos a cada uno de los objetos utilizados por éste.

CALLER indica que las sentencias dentro del módulo serán ejecutadas en el contexto de seguridad de quien invoca al módulo. De esta manera, el usuario deberá tener permisos sobre el módulo y los objetos referenciados.
SELF es equivalente a EXECUTE AS nombre_usuario, donde el usuario es el dueño o creador del módulo.
OWNER indica que las sentencias dentro del módulo serán ejecutadas en el contexto de seguridad del propietario del módulo.

La sentencia EXECUTE
Permite ejecutar procedimientos almacenados, funciones definidas por el usuario, procedimientos almacenados extendidos o cadenas de caracteres que conforman sentencias.
Los argumentos implicados en estos códigos fuente son:

EXEC [ UTE ] indica que el comando puede utilizarse en su versión reducida.
@estado de retorno es una variable entera opcional que devuelve el estado de un procedimiento.
Nombre_proc [; numero] indica el nombre del procedimiento a ejecutar.
@parámetro es el parámetro a enviar al procedimiento.
@variable es la variable que contendrá el valor devuelto en el parámetro de salida del procedimiento.
DEFAULT provee al procedimiento, como parámetro de entrada.
WITH RECOMPILE fuerza la creación de un nuevo plan de ejecución para el procedimiento.
@string_variable ejecuta el contenido de una variable de cadena del tipo char, varchar, nchar o nvarchar.
[N]’tsql_string’ es una cadena contante del tipo nvarchar o varchar.

Procedimientos CLR
Los procedimientos desarrollados bajo CLR se implementan como métodos estáticos públicos en clases empaquetadas en un ensamblado .NET. Los métodos pueden ser declarados para devolver enteros o del tipo void.
Descripción sobre la conversión de datos.
TIPOS DE SQL SERVER TIPOS CLR (SQL SERVER) TIPOS CLR (.NET FRAMEWORK)

Varbinary SQLBytes, SQLBinary Byte []

Image - -
Char - -
Nvarchar SQLChars, SQLString String, Char []

Text - -
Bit SQLBoolean Boolean
Smallint SQLInt16 Int16
Money SQLMoney Decimal

Un procedimiento CLR puede devolver resultados en varios formatos:

parámetros de salida: al igual que en los procedimientos almacenados transaccionales, los procedimientos CLR pueden devolver resultados en parámetros de salida.
En resultados tabulares y mensajes: mediante el objeto Sql Pipe se pueden devolver resultados al cliente involucrado al método GetPiupe() de la clase SqlContext.
En mensajes: utilizando SQLPipe.Send (string) es posible enviar mensajes a la aplicación cliente aunque la limitante es el tamaño del mensaje de 8 Kb.
En resultados tabulares: para enviar los resultados rápidamente al cliente se puede utilizar una de las sobrecargas del método Send.
Funciones
Las funciones definidas por el usuario son un conjunto de sentencias T-SQL o del CLR que devuelven un valor y no pueden ser usadas para manipular datos en la base.

Funciones escalares.
Funciones simples (in-line).
Funciones tabulares multi sentencia.
Funciones CIR.

Parámetro de descripción:

Nombre_funcion. Nombre asignado a la función.
Nombre_tipo_esquema.tipo_dato. Tipo de dato y esquema al que pertenece. No está soportado el tipo timestamp.
Expresión_escalar. Refiere a la variable que contiene el valor que calculó la función que se ha de devolver a quien la invoca.
END Expresión. Indica el fin de la secuencia de instrucciones que debe ejecutar la función.
Sentencia_select . Define la tabla que devolverá la función.
Definición_tipo_tabla. Define el tipo de tabla, campos, tipos de datos, restricciones, etc. permite el uso de restricciones PRIMARY KEY, UNIQUE, NULL Y CHECK.
Indicador_metodo. Indica que método de ensamblado (assembly) se vinculará a la función. El nombre del ensamblado debe coincidir con un ensamblado existente en el servidor, y la clase debe ser visible y existir dentro de ese ensamblado.
SCHEMABINDING. Vincula la función al esquema de los objetos donde se está creando. Si se utiliza esta opción, los objetos a los que hace referencia no pueden ser alternados.

RETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT Indica que si alguno de los parámetros de la función invocada es nulo, el cuerpo de la función se ejecutará igualmente. Si se especifica RETURNS NULL ON NULL INPUT, el cuerpo de la función no se ejecutará.

Consideraciones para el manejo de funciones:
Existen dos tipos de funciones escalares o tabulares. Las funciones son escalares si la cláusula RETURNS devuelve un valor escalar, y son tabulares si RETURNS incluye la cláusula TABLE.
Dependiendo de la definición del cuerpo de la función, las funciones pueden ser clasificadas como simples o de multisentencia.

Ejemplos de sentencias que se pueden incluir en una función:

Sentencias de asignación.
Sentencias de control de flujo.
Sentencias de declaración de variables y cursores.
Sentencias SELECT para asignar valores a variables locales.
Sentencias de operación de cursores.
Sentencias INSERT, UPDATE y DELETE sobre variables de tipo tabla.
Sentencias EXECUTE contra procedimientos extendidos.

FUNCIONES DE AGREGACIÓN
Las funciones de agregación utilizan un conjunto de resultados, sobre los cuales se ejecuta el cálculo y se devuelve un resultado simple. SQL Server permite crear funciones de agregación definidas por el usuario. Con los siguientes pasos:

Definir la función de agregación como una clase en un lenguaje.NET.
Referenciar en SQL Server al ensamblado creado por el medio de la función de agregación.
Crear la función que referencia al ensamblado.

CODIGO:
CREATE AGGREGATE [ nombre_esquema . ] nombre_agregacion
.… ( nombre_parametro <>)
RETURNS
EXTERNAL NAKE assembly_nombre [ .clase_nombre]
<> :
…. system_escalar_tipo { [ udt_nombre_esquema. ] udt_nombre_tipo }
<> ::=
…. System_escalar_tipo { [ udt_nombre_esquema. ] udt_nombre_tipo }

PARÁMETROS UTILIZADOS EN EL CÓDIGO
nombre_esquema: nombre del esquema al que pertenece la agregación.
nombre_agregacion: es el nombre asignado por el usuario a la función de agregación.
nombre_parametro: parámetro que recibe la función y debe ser provisto por el usuario al momento de invocar la función.
system_escalar_tipo: tipos de datos válidos de SQL que puede contener el valor devuelto por la función.
udt_nombre_esquema: Nombre del esquema al que pertenece el tipo de dato definido por el usuario.
External Name: Nombre del ensamblado que se vinculará a la función de agregación.

Cursores
Las aplicaciones desarrolladas con los lenguajes modernos de programación se orientan al manejo de conjuntos de registros, permitiendo la manipulación de los campos de cada uno de ellos mediante sofisticadas estructuras de control.
SQL Server proporciona los cursores como una herramienta alternativa para el manejo de datos. Estos son un conjunto de resultados navegables en los cuales siempre podemos saber exactamente en qué fila del conjunto estamos posicionados. Su uso nos proporciona el manejo fila a fila de un lote de datos.
Para la utilización de SQL Server debemos:

1) Declararlos mediante la instrucción DECLARE. También se incluye la sentencia SELECT y la clausula ORDER BY de ordenamiento.
2) Abrir el cursor mediante la sentencia OPEN.
3) Obtener las filas del cursor mediante la sentencia FETCH. Donde -1 indicará que no
hay mas filas y -2 que la fila ha desaparecido del cursor.
4) Efectuar cambios sobre la tabla donde está el cursor ubicado.
5) Cerrar el cursor con la sentencia CLOSE.,
6) Des-asignar los recursos asignados al cursor mediante la sentencia DEALLOCATE.

Sentencia de declaración de cursores:
Sintaxis T-SQL extendido
DECLARE nombre_cursor CURSOR [ LOCAL GLOBAL ] [ FORWARD_ONLY SCROLL] [ STATIC KEYSET DYNAMIC FAST_FORWARD ] [ READ_ONLY SCROLL ] SCROLL_LOCKS OPTIMISTIC ] [ TYPE_WARNING ] FOR sentencia_select [ FOR UPDATE [ OF nombre_columna [ ….n ] ] ]
Sintaxis SQL 92
DECLARE nombre_cursor [ INSENSITIVE ] [ SCROLL ] CURSORFOR sentencia_select
[ FOR ( READ ONLY UPDATE [ OF nombre_columna [….n ] ] ] ]


Principales parámetros para el manejo de cursores
INSENSITIVE: define un cursor que crea una copia local de los datos el tempdb.
LOCAL: indica la visibilidad del cursor. Es local al proceso batch o conjunto de sentencias T-SQL. Procedimiento donde el cursor es declarado.
FORWARD_ONLY: indica que el cursor navegara hacia delante.
KEYSET: indica que las filas y el orden del cursor son fijados al momento de abrirlo.
FAST-FORWARD: define un cursor FORWARD_ONLY, READ_ONLY que admite optimizaciones de desempeño.
SCROLL_LOCKS: garantiza la actualización o borrado de filas sobre la fila actual del cursor, mediante la adquisición de bloqueos.
TYPE_WARNING FOR UPDATE: emite una alerta a la aplicación cliente ante un cambio de cursor de un tipo a otro.

Consideraciones sobre el uso de cursores
En la sentencia DECLARE debemos especificar las clausulas READ_ONLY, OPTIMISTIC o SCROLL_LOCKS de la siguiente manera.


Si la sentencia SELECT no admite actualizaciones, el cursor será declarado READ_ONLY.
Los cursores STATIC y FAST-FORWARD se asumen READ_ONLY.
Los DYNAMIC y KEYSET se asumen OPTIMISTIC
Cuando no indicamos el alcance del cursor [LOCAL o GLOBAL] será fijado en la opción default local cursor.

Para seleccionar el tipo adecuado de cursor, se deberán considerar las siguientes pautas:

Si se puede diseñar una solución orientada al manejo de conjuntos de resultados.
Si no queda más remedio que utilizar cursores.
El cursor estático toma una “foto” de los datos al momento de obtener las filas según la instrucción SELECT. Esta se guarda en la tempdb durante el ciclo de vida del cursor, Actúa como si estuviéramos emitiendo la sentencia SELECT por cada clausula FETCH.
La apertura de un cursor con la sentencia OPEN guarda en la variable de sistema @@CURSOR_ROWS la cantidad de filas recuperadas. Si se indicara la clausula INTO en la sentencia FETCH, los datos de las columnas del SELECT se copiarán dentro de variables.
Los valores que puede devolver @@CURSOR_ROWS son:

1: en un cursor dinámico no se puede obtener el valor de la variable.
N: número de filas del cursor.
Opciones de la clausula FETCH para cursores desplazables:
FETCH NEXT: desplaza el puntero del cursor hacia la próxima fila.
FECTCH FIRST: desplaza el puntero del cursor a la primera fila.
FETCHN ABSOLUTE n: devuelve la fila n del conjunto de resultados.
El procedimiento almacenado sp_describe_cursor nos brinda información acerca del cursor o la variable de cursor. Para ejecutar este procedimiento es necesario declarar una variable de salida de tipo cursor, ya que devuelve una fila de datos con todos los campos de información.
Sp_describe_cursor [ @cursor_return =] variable salida_cursor OUTPUT
{ [ , @cursor_source =] N’local’ , [@cursor_identity = ]
N’nombre_cursor}
[ [ , @cursor_source =] N’global’ , [@cursor_identity = ]
N’nombre_cursor_global ‘ ]

[ [ , @cursor_source =] N’variable’ , [@cursor_identity = ]
N’cursor_variable_entrada’ ]
}

Parámetros de procedimiento
Parámetro descripción
@cursor_return Es el nombre de la variable de tipo cursor declarada para contener el cursor devuelto por el procedimiento
@cusor_identity Nombre del cursor

El procedimiento SP_CURSOR_LIST devuelve la misma información que SP_DESCRIBE_CURSOR, para todos los cursores locales, globales o ambos. El procedimiento devuelve una fila por cursor.
El procedimiento SP_DESCRIBE_CURSOR_TABLES informa sobre las tablas a las que accede un cursor en particular y devuelve una fila por tabla.
El procedimiento SP_DESCRIBE_CURSOR_COLUMNS informa sobre los atributos de las columnas del conjunto de resultados de un cursor en particular.

Bloqueos
Las modificaciones de datos deben protegerse mediante el bloqueo de recursos, dado que pueden existir varias conexiones que acceden al mismo recurso al mismo tiempo (concurrencia), por lo cual SQL server adquiere en forma automática distintos tipos de bloqueos según lo considere apropiado o pueden especificarse alguno en particular (mediante HINTS).
Los efectos de la concurrencia no administrada pueden producir:

Actualizaciones perdidas
Dependencia de modificaciones de datos no confirmados (lecturas sucias)
Análisis inconsistente (datos nuevos no considerados)
Lecturas fantasmas (datos que ya no existen)
Para evitar estos problemas, existen 2 tipos de control de concurrencia: pesimista y optimista.

El pesimista asume que habrá muchas modificaciones concurrentes de datos y adquiere bloqueos compartidos para evitar que la transacción lea filas que se están modificando sin confirmar, lo cual ayuda en sistemas con movimientos de actualización considerables.
El optimista asume que habrá pocas modificaciones de datos que afecten las transacciones. Por ello habilita el versionado de filas por lo cual el servidor mantiene versiones de cada fila con sus modificaciones, así cuando una transacción intenta modificar datos, el motor verifica si estos han sido modificados por otra transacción y luego inicia la transacción.
Los beneficios del control pesimista se basan en que no se lleva un versionado de filas modificadas y que las transacciones no se deben de re-ejecutar si algún otro usuario cambia los datos, y sus desventajas están dadas por la necesidad de administrar grandes números de bloqueos y por la aparición de interbloqueos.
Por otra parte, los beneficios del control optimista son que se minimiza la administración de bloqueos y de interbloqueos. La desventaja que tiene es la administración del versionado de filas y la administración de reintentos de ejecución de transacciones por modificaciones en ellas.

Recursos bloqueables

Los recursos de SQL que se pueden bloquear son:

RID (rowid): identificador de la fila. Usado para bloquear una fila en un montón
KEY: bloqueo de fila mediante índice. Se usa para bloquear rangos de filas
PAGE: bloqueo de páginas de 8kb de datos o índices.
EXTENT: bloqueo de grupos continuos de 8 páginas de datos o índices
HOBT: bloqueo de un montón o rama de árbol balanceado de índices
FILE: bloqueo de un archivo de la base completo
APPLICATION: bloqueo de recursos específicos de aplicación
METADATA: bloqueo de metadato
ALLOCATION_UNIT: bloqueo de unidad de alocacion
DATABASE: bloqueo de toda la base de datos

Modos de bloqueo
SQL server puede bloquear recursos de la siguiente manera

Shared (S): usado para operaciones de lectura que no actualizan datos
Update (U): usado en recursos que pueden ser actualizados. Previene los bloqueos mortales que ocurren cuando múltiples sesiones están leyendo, bloqueando y actualizando datos, dejando en claro que un bloqueo mortal ocurre cuando 2 procesos o transacciones están esperando por un recurso y ninguno puede avanzar porque cada uno de ellos bloquea al otro.
Exclusive (X): se utiliza para operaciones de INSERT, UPDATE o DELETE. Aseguran que no se pueden actualizar registros al mismo tiempo.
Intent: se utiliza para establecer una jerarquía de bloqueos. Los tipos pueden ser INTENT SHARED (IS), INTENT EXCLUSIVE (IX) o SHARED con INTENT EXCLUSIVE(SIX)
Schema: se utiliza para operaciones dependientes del esquema de tablas. Los tipos disponibles son: SCHEMA MODIFICACION (sch-M) o SCHEMA STABILITY (sch-S).
Bulk update (BU): usado cuando se hacen inserciones por volcado (BULK) con la especificación TABLOCK.
Key-range: protege un rango de filas leídas por una transacción con aislamiento
Serializable: asegura que otras transacciones no pueden insertar filas que califiquen dentro de las condiciones de búsqueda de filas de la captura.

Transacciones
Una transacción es la que controla cuantas operaciones realiza un proceso batch TSQL antes de confirma los cambios. Las transacciones no serán confirmadas hasta que no se emita la sentencia COMMIT_TRAN[SACTION], o desechadas mediante un ROLLBACK TRAN[SACTION] mientras que la orden de inicio de una transacción se emite con BEGIN TRAN[SACTION].

Begin tran
INSERT INTO FER_EXPOS (strFeria, intidpais)
VALUES(‘feria internacional de maquinas herramientas’, 14)
IF error=0
COMMIT TRAN
ELSE
ROLLBACK TRAN
Para que una transacción sea considerada como tal, el proceso debe de cumplir con las propiedades ACID (atomicidad, consistencia, aislamiento y durabilidad).

Atomicidad: las transacciones son unidades atómicas de trabajo, se ejecutan todas las operaciones en ella definidas o ninguna.
Consistencia: cuando una transacción termina, los datos en la base de datos permanecen en un estado estable, cumpliendo con las reglas de integridad referencial y con todas las estructuras internas estables (ejemplo, arboles de índices).
Aislamiento: los cambios que se realizan dentro de una transacción permanecen aislados a otras transacciones. Todo esto significa que, de alguna manera, se debe mantener el control sobre los elementos que se están modificando (bloqueos) con el propósito de que los mismos no sean alterados por otra transacción mientras la actual se está ejecutando.
Durabilidad: luego de confirmada la transacción, los cambios realizados se mantienen estables en el tiempo.
Es importante tener mecanismos necesarios que aseguren el mantenimiento de estas propiedades en las transacciones, algunos sistemas de bases de datos relacionales empresariales proveen:

Facilidades de boqueo que permiten proveer a la transacción de aislamiento
Facilidades de registro de transacciones que proveen la durabilidad de la información, aun ante fallas severas de hardware
Características para proveer a las transacciones de atomicidad y consistencia. Si una falla se produce durante una transacción, el servidor provee los mecanismos automáticos de ROLLBACK.
Para SQL server existen 3 tipos de transacciones:

Transacciones explicitas: son aquellas transacciones en las cuales se especifica explícitamente, en el proceso por lotes, el inicio y fin de la transacción los cuales se realizan de la siguiente manera:
Begin Transaction: Marca el punto de inicio de una transacción para una conexión
Commit Transaction: sirve para finalizar una transacción en la que no se produjeron errores, haciendo los cambios permanentes en la base de datos
Rollback Transaction: se utiliza para deshacer los cambios sobre los datos en aquellas transacciones donde se han producido errores
Transacciones implícitas: son aquellas que maneja por omisión el servidor SQL server. Cada sentencia individual es confirmada o deshecha.
Transacciones implícitas: cuando una conexión se encuentra trabajando bajo esta modalidad, el servidor inicia una nueva transacción luego de que la actual es confirmada o deshecha.
Nivel de aislamiento de las transacciones
El nivel de aislamiento proporcionado a las transacciones permite controlar el bloqueo de recursos bajo modificación y el comportamiento del versionado de filas de las sentencias T-SQL emitidas por una conexión.
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED
[ READ COMMITTED
[REPEATABLE READ
[SNAPSHOT
[SERIALIZABLE
]
[,].
Donde:
READ UNCOMMITTED: indica que la transacción puede utilizar filas de datos que están siendo modificados por otra transacción aunque estos aun no hayan sido confirmados.
READ COMMITTED: indica que el nivel de aislamiento otorgado a la transacción en curso impedirá leer datos con cambios sin confirmar por otra transacción previniendo las lecturas sucias. El comportamiento de READ COMMITTED depende de cómo se haya prefijado la opción de base de datos READ_COMMITTED_SNAPSHOT.
Si se coloca en OFF (por omisión), el motor adquirirá bloqueos compartidos para prevenir la modificación de datos que están siendo leídos por la transacción actual. El bloqueo es liberado cuando termina la transacción
Si se coloca en ON, el motor utilizara el versionado de filas para presentar cada sentencia con una foto (snapshot) consistente de los datos existentes al comienzo de la transacción. No se adquieren bloques para prevenir el UPDATE de las filas por otras transacciones.
REPEATABLE READ: indica que no se leerán datos que están siendo modificados por otras transacciones y que permanecen sin confirmar.
SNAPSHOT: Indica que los datos capturados para la transacción serán una foto (snapshot) que contiene todos los datos existentes al comienzo de la misma.
SERIALIZABLE:
La transacción no leerá datos que están siendo modificados pero no confirmados por otra transacción
Ninguna otra transacción podrá modificar los datos que han sido leídos por nuestra transacción
Otras transacciones no podrán insertar filas cuyas claves entraran dentro del rango de claves leídas por la transacción en curso hasta que esta termine
Este nivel de aislamiento adquiere bloqueos por rangos de claves que concuerdan con las condiciones de búsqueda de filas capturadas para trabajar, bloqueando cualquier otra transacción que intente insertar o actualizar filas cuyas claves estén en el rango de las filas capturadas.

Observaciones sobre el aislamiento
En primer término, solo un nivel de aislamiento puede ser fijado por transacción.
En segundo lugar, el nivel de aislamiento de las transacciones define que tipos de bloqueos se adquirirán sobre los recursos que utiliza la misma.los bloqueos compartidos (SHARED) adquiridos por READ COMMITED o REPEATABLE READ son, generalmente bloqueos de nivel fila, aunque dependiendo de la cantidad de filas afectadas, los mismos pueden escalar a bloqueos de nivel pagina o tabla.

Estructuras de programación en T-SQL

Variables
Las variables que se declaran en un procedimiento almacenado o dentro de un conjunto de sentencias de T-SQL son locales a los mismos.
Por cuestiones de redundancia en la escritura de procedimientos, se les declara al principio del mismo y se les asigna valores mediante la sentencia SET.
Es posible asignar valores provenientes de una tabla a una variable por medio de la instrucción SELECT.
Sentencias de control de flujo para un proceso BATCH
BEGIN…END Se utiliza para entrecerrar un conjunto de sentencias unidas lógicamente.
IF…ELSE Define una ejecución condicional.
RETURN n Salida incondicional. Termina la ejecución del proceso y devuelve n al proceso que lo invoca.
BREAK Sale de un bucle WHILE interno.
CASE Instrucción condicional de varias ramas, que permite un manejo de condiciones mejor que el IF y aplica a cada fila un conjunto de resultados.

Funciones más utilizada dentro de SQL

NULLIF. Devuelve nulo si ambas expresiónes son iguales.
NULLIF (expresión1, expresión2)
ISNULL. Devuelve TRUE si la expresión es nula, o FALSE si no lo es.
ISNULL (expresión1)
PRINT. Devuelve a la aplicación cliente un mensaje generado en el interior de un proceso.
PRINT
COALESCE. Devuelve la primera expresión no nula de la lista.
COALESCE (expresión1, expresión2 [,…n])
CAST. Convierte explícitamente entre un tipo y otro (compatible) de datos.
CAST (expresión1 AS tipo_dato [(largo)]))
ISNUMERIC. Devuelve TRUE si la expresión es de un tipo numérico válido.
ISNUMERIC (expresión)

SUBSTRING. Devuelve una porción de la cadena, con inicio en el carácter inicio y fin en el carácter fin.
SUBSTRING (expresión, inicio, fin )
CHAR. Convierte un código de carácter ASCII en el carácter.
CHAR (expresión_cadena)
ASCII. Devuelve el código ASCII de un carácter.
CHAR (expresión_cadena)
CHARINDEX. Devuelve la posición de inicio de la cadena indicada (expresión1) en el texto de expresión2, buscando a partir del carácter inicio.
CHARINDEX (expresión1, expresión2 [,inicio])
LEFT. Devuelve los exp_entera caracteres de la izquierda de expresión1.
LEFT (expresión1, exp_entera)
LEN. Devuelve el largo de la expresión1.
LEN (expresión1)
LOWER. Convierte expresión1 a minúsculas.
LOWER (expresión1)
LTRIM. Elimina los espacios blancos a ala izquierda de la expresión1.
LTRIM (expresión1)
REPLACE. Reemplaza en expresión1, la cadena expresión2 con expresión3.
REPLACE (‘expresión1’, ‘expresión2’, ‘expresión3’)
SELECT REPLACE (‘abcdefghicde’, ‘cde’, ‘xxx’)
Abxxxfghixxx
(1 row(s) affected)
REVERSE. Revierten los valores de expresión1.
REVERSE (expresión1)
RIGHT. Devuelve los exp_entera caracteres de la derecha de expresión1.
RIGHT (expresión1, exp_entera)
RTRIM. Elimina los espacios blancos a la derecha de expresión1.
RTRIM (expresión1)
SPACE. Devuelve una cadena de expresión_entera espacios.
SPACE (expresión_entera)
STR. Convierte a cadena una expresión numérica.
STR (flota_expresión [,largo [,decimal]])
UPPER. Convierte expresion1 a mayúscula.
UPPER (character_expression)
DATEADD. Obtiene un valor del tipo datetime Nuevo a partir de sumarle a fecha, número de periodo.
DATEADD (periodo, numero, fecha)
*Suma 21 días a la fecha
SELECT DATEADD (DD, 21, fechaexpo) AS nueva fecha from FER_EXPOS
DATEDIFF. Devuelve el número (en periodos) de diferencia entre inicio y fin.
DATEDIFF (periodo, inicio, fin)
*calcula la diferencia en días entre hoy y otra fecha.
SELECT DATEDIFF (did, fechaexpo, fechainicio,) AS CantDias from FER_EXPOS
DATENAME. Devuelve una cadena especificando el periodo.
DATENAME (periodo, fecha)
DATEPART. Devuelve un entero representando el periodo de una fecha.
DATEPART (datepart, date)
Current date
Feb, 18 1996 11:46 PM
SELECT DATEPART (month, GETDATE ()) AS ‘número de mes’
GO
DAY. Devuelve el número de día de una fecha.
DAY (fecha)
*Devuelve el mismo día de la fecha actual
SELECT DAY (GETDATE ())
MONTH. Devuelve el número de mes de una fecha.
MONTH (fecha)

Funciones de error
La función RAISERROR permite enviar un mensaje al cliente especificado un número de error, un mensaje y un nivel de severidad. Además brinda la posibilidad de grabar los mensajes de error en el servicio de sucesos de Windows, haciéndolos visibles al visor de eventos.
Términos incluidos
Id_mensaje: corresponde a un id de mensaje almacenado en la tabla sysmessages. A esta tabla pueden agregarse los mensajes utilizando el procedimiento almacenado de sistema sp_addmessage.
Cadena_mensaje es un mensaje de error de hasta 399 caracteres. Esta cadena puede contener caracteres de conversión que definen cómo será formateado el mensaje y ubicado como un campo dentro de la cadena.
Bandera: es el código que indica el espaciado y la justificación de la cadena de sustitución
Ancho: es un entero que define el ancho mínimo del campo dentro del cual se ubica el argumento.
Precisión: Para valores del tipo cadena, es el número máximo de caracteres que esta puede tomar.
{h/l} tipo: usado con caracteres del tipo d, i, o, x, X o u.
@variable_local: variable que puede contener el mensaje de error.
Severidad: Es el nivel de severidad definido por el usuario para el error y puede tomar valores entre 0 y 18. Las severidades comprendidas entre 19 y 25 sólo pueden ser utilizadas por miembros del rol sysadmin, requiriéndose, además, el uso de la opción WHIT LOG.
Estado: es un entero arbitrario entre 1 y 127 (valores más grandes o 0 devuelven error). Si el mismo tipo de error definido por el usuario se utiliza en varios lugares, se puede utilizar distintos estados para cada una de las locaciones para identificar mejor dónde se ubica el error.
Argumento: contiene los parámetros de sustitución de variables para formatear el texto de salida del mensaje.

Opciones
LOG: guarda el error en el log de errors de SQL Server.
NOWAIT: Envía el error inmediatamente al cliente
SETERROR: Fija @@ERROR y ERROR_NUMBER al ID del mensaje de error actual

Operadores
Su funcionalidad consiste en que brindan al programador la posibilidad de efectuar comparaciones y otro tipo de operaciones entre variables. Los tipos más importantes:
*Aritméticos
Los operadores aritméticos permiten realizar operaciones matemáticas
*Binarios
Son útiles para mantener indicadores en la base de datos, ya que facilitan la definición de mascaras utilizando una sola columna para ello.
*De asignación
El símbolo = se utiliza tanto para asignar como para comparar.
*De comparación
Tal como su nombre lo indica, sirven para efectuar comparaciones entre variables.
*Lógicos
Algunos de los operadores lógicos con los que trabaja SQL Server:

Operador devuelve
ALL TRUE.- si todos los elementos evaluados en un conjunto son verdaderos
ANY TRUE.- si alguna de las expresiones comparadas son verdaderas
EXISTS TRUE.- si el sub-query devuelve alguna fila de resultados.
LIKE TRUE.- si el operador concuerda con un patrón
OR TRUE.- si alguna de las expresiones es verdadera

Concatenación de cadenas
Para concatenar cadenas, se utiliza el símbolo +.

Captura de errores
Existen dos formas de capturar errores en SQL Server:

Dentro de un bloque CATH con las funciones:
ERROR_MESSAGE (), que devuelve el texto del error que hay que devolver a la aplicación, pudiéndose utilizar las cadenas de sustitución para formatear texto;
ERROR_NUMBER (), que devuelve el número error;
ERROR_SEVERITY (), que devuelve la severidad;
ERROR_STATE (), que devuelve el estado.
Capturando el error en el momento que se produce, con RAISERROR
TRY… CATCH implementa para T-SQL un manejo de excepciones similar al de C# y C++. Un grupo de sentencias se testea dentro del TRY y los errores que allí se produzcan se manejan dentro del bloque CATH

VISTAS
Las vistas Consisten en una tabla virtual definida mediante una consulta SELECT que permite obtener dinámicamente un conjunto de resultados compuestos por filas y columnas. Las vistas simplifican la visión de la base de datos que tiene un usuario.
Ofrecen la posibilidad de:
*Reutilizar instrucciones SELECT complejas: por ejemplo, si tuviéramos varios procedimientos que necesitan recuperar los datos de los clientes y estos datos se obtienen a partir de un SELECT contra las tablas CLIENTES, CLIENTES_DOMICILIOD y CLIENTES_CUENTAS, resultaría mucho mas sencillo crear una vista que recupere los datos de los clientes y utilizarla en todos los procedimientos.
*Aplicar filtros: se pueden utilizar sentencias SELECT contra las vistas de la misma manera que se hace contra otras tablas.
*Actualizar o insertar datos: bajo ciertas condiciones se pueden realizar modificaciones o inserciones de registros a la hora de trabajar con vistas.
*De normalizar datos: por ejemplo, vistas con totales de pedidos por clientes, evitando crear tablas totalizadoras solo para consulta de datos.

Tipos de vistas
Con SQL Server Podemos crear 2 tipos de vistas:
*Vistas indexadas: tiene un índice físico (cluster) sobre uno de sus campos que permite incrementar ampliamente el tiempo de respuesta de la vista.
*Vistas particionadas: une tablas particionadas horizontalmente a través de uno o más servidores, uniendo los datos en una misma instancia de SQL Server.

Creación de vistas
CREATE VIEW [esquema] nombre_vista [ {columna [… n] } ]
{ with <> […….n] }
AS
[WITH <>]
Sentecnia_Select
[ WITH CHECK OPTION ]
[ j ]
<> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }

*Las vistas pueden referenciar hasta 1.0224 columnas.
*Debemos verificar que los objetos que refieren a las vistas existen.
*Si las tablas referenciadas por una vista cambian en su estructura o son eliminadas, la vista deberá recrearse.
Cuando se crea una vista, se almacena su nombre en la tabla de sistema sysobjects, la información acerca de las columnas que componen la vista se almacena en syscolumns, sus dependencias en sysdepends y la definición, en syscommnets, donde el texto será legible a menos que se haya creado la vista con la opción ENCRYPTION.

Modificación e inserción de registros de una vista
SQL Server permite modificar las tablas utilizando sentencias de INSERT, UPDETE y DELETE bajo ciertas condiciones:
*Las modificaciones solicitadas afectaran solo a una tabla subyacente por vez.
*Las modificaciones afectan directamente a campos existentes en la tabla subyacente, no provienen de una función de agregación.
*Las columnas a modificar no se encuentran incluidas en clausulas GROUP BY, HAVING o DITINCT.
*Una inserción de registros fallara a menos que la definición de la vista incluya todos los campos que no aceptan nulos y no tienen asignados valores predeterminados.
*El mismo criterio se utiliza para validar cualquier restricción definida sobre las tablas subyacentes.

Modificación de vistas
La función utilizada para modificar vistas es ALTER VIEW.
Si la vista esta siendo utilizada, SQL Server adquiere bloqueos de esquema sobre la vista.

Eliminación de vistas
La función provista por SQL Server para eliminar las vistas es DROP VIEW.

Conclusión
Podemos concluir en este tema con todo el glosario de términos que nos presenta SQL server, que como se aprecia a lo largo de toda la lectura es muy amplio, nos proporciona códigos para creación, modificación y eliminación de procesos y vistas, herramientas para el mejor aprovechamiento y funcionamiento de nuestras bases de datos.

Bibliografía:
Sql Server
Karina Rosa
2005

Lectura 6 - SQL

INTRODUCCIÓN

A continuación, observaremos para que nos sirve el lenguaje SQL, pues se mencionaran algunas de las estructuras que se deben manejar para la utilización de este lenguaje.
En esta lectura podremos encontrar las diversas sintaxis que se deben manejar para un buen manejo en la creación de las bases de datos, tablas e incluso en las consultas, modificaciones o eliminación de las mismas.
Uno de los beneficios más notorios que veremos en SQL es la manera en que nos ayuda a realizar consultas rápidas en cada una de las bases de datos, tablas, filas, columnas, etc.
Analizaremos algunas de las instrucciones que manejan tanto el DDL como el DML. Nos enfocaremos a las instrucciones más solicitadas por los administradores de la base de datos como son: SELECT, INSERT, DELETE, DROP, CREATE, etc.
Estas son solo algunas de las sentencias que veremos en esta lectura.

¿PARA QUÉ SIRVE SQL?

El lenguaje SQL es un lenguaje con el que se escriben todas las acciones u operaciones que se realizan sobre los motores de bases de datos relacionales. Es un lenguaje que se utiliza para consultas y programación de la base de datos. Existe un estándar para el SQL es el ANSI (American National Standards Institute) en castellano es: Instituto de Normalización Americano.
En esta ocasión utilizaremos Microsoft SQL Server 2000 donde manejaremos el lenguaje SQL. Este se llama Transact SQL ó T-SQL; para esto es necesario decir que se divide en dos partes: DDL Y DML.
· CP es la clave primaria.
· CE es la clave foránea.
Un ejemplo de modelo relacional en:

· DDL.

Se utiliza para definir y administrar objetos de la base de datos, tales como bases de datos, tablas, vistas. Normalmente las instrucciones incluidas en este lenguaje son CREATE, ALTER, DROP aplicadas sobre los diferentes objetos como tablas o vistas.
· CREATE

Para crear una base de datos se utiliza la instrucción CREATE DATABASE con un conjunto de parámetros, esta instrucción puede llevar más parámetros de los que se presentan.
Un ejemplo que utilizaremos será crear una base de datos de nombre SUELDOS dentro del motor SQL Server de 10 Mbyte de tamaño con un tamaño máximo de 50 Mbyte para el archivo de datos (sueldos.mdf) y un tamaño de 5 Mbyte para el archivo registro de transacciones o log y con un tamaño máximo de 25 Mbyte para el archivo de log (sueldos.ldf), en ambos casos con un incremento de 5 Mbyte.


USE master
GO
CREATE DATABASE SUELDOS
ON
(NAME = SUELDOS_dat,
FILENAME = ‘c:\program files\microsoft sql server\mssql\data\sueldosdat.mdf’,
SIZE =10,
MAXSIZE= 50,
FILEGROWTH =5)
LOG ON
(NAME = ‘SUELDOS _log’,
FILENAME = ‘c:\program files\microsoft sql server\mssql\data\sueldoslog.ldf’,
SIZE = 5MB,
MAXSIZE= 25MB,
FILEGROWTH= 5MB)
GO

Esta instrucción indica al motor de la base de datos cual tomar como la base de datos activa, donde se van a ejecutar las instrucciones T-SQL. En este ejemplo se muestra como se da la indicación para usar la base de datos master.
Estamos creando la base de datos SUELDOS en la ruta donde SQL Server guarda sus bases de datos propias para su gestión. Se recomienda guardar las bases de datos en el mismo lugar donde SQL Server lo hace.

Crear una base de datos desde el Enterprise Manager
Enseguida se muestra como crear una base de datos con las instrucciones DDL desde el Enterprise Manager.

Para ello nos posicionamos en el servidor, en nuestro caso NOTEBOOK parados en la carpeta de base de datos, presionamos el botón derecho del mouse y elegimos la opción crear nueva base de datos, como se muestra en la figura.

Luego elegimos su nombre, en nuestro ejemplo, pusimos el nombre SUELDOS_V1 pues la base de datos sueldos ya la habíamos creado con las instrucciones DDL, y presionamos el botón Aceptar y la base de datos será creada.
Si nos movemos a las solapas de Archivos de datos y Archivos de log podemos configurarlos de una manera más sencilla de cómo lo hicimos con las instrucciones de T-SQL.
Ahora veremos cómo utilizar la instrucción CREATE para crear las distintas tablas de nuestra base de datos SUELDOS. Empezaremos por la tabla CATEGORÍAS.

USE SUELDOS
CREATE TABLE CATEGORIAS {
Cod_categoria char (2) NOT NULL,
Desc_Categoria varchar (50) NOT NULL,
CONSTRAINT PK_CATEGORIAS PRIMARY KEY CLUSTERED
{
Cod_Categoria
} ON [PRIMARY]
} ON [PRIMARY]

Como vemos la instrucción CREATE TABLE CATEGORIAS es la instrucción que le indica a T-SQL que debe crear una tabla de nombre CATEGORIAS con los campos Cod_Categoria de tipo entero y que no acepte valores nulos y Desc_Categoria de tipo cadena de caracteres variable de 50 posiciones que tampoco debe aceptar valores nulos. Luego la instrucción CONSTRAINT define una restricción de integridad, en este caso la clave primaria de la tabla CATEGORIAS a la cual le asigna el nombre PK_CATEGORIAS sobre el campo Cod_Categoria y dice que tendrá ella un índice interno de tipo CLUSTERED.

Ahora crearemos la tabla EMPLEADOS

USE SUELDOS
CREATE TABLE EMPLEADOS {
Legajo int NOT NULL,
Apellido varchar (20) NOT NULL,
Dirección varchar (50) NOT NULL,
Tarea varchar (50) NOT NULL,
Cod_Categoria char (2) NOT NULL,
Fecha_Ingreso datetime NOT NULL,
Foto varchar (50) NULL,
Email varchar (50) NULL,
HomePage varchar (50) NULL,
Sueldo_Basico money NOT NULL,
CONSTRAINT PK_EMPLEADOS PRIMARY KEY CLUSTERED
{
Legajo
} ON [PRIMARY] ,
CONSTRAINT PK_EMPLEADOS_CATEGORIAS FOREIGN KEY
{
Cod_Categoria
} REFERENCES CATEGORIA {
Cod_Categoria
}
} ON [PRIMARY]

En esta tabla aparece una nueva instrucción T-SQL de integridad, como que la tabla EMPLEADOS posee una clave foránea con la tabla CATEGORIAS por medio del Cod_Categoria.

Crear una tabla utilizando el Enterprise Manager
En esta ocasión no utilizaremos la instrucción DDL, sino que crearemos una tabla de modo gráficamente. Para ello, nos posicionamos en la base de datos SUELDOS_V1, se presiona botón derecho sobre la carpeta Tablas y elegimos la opción Crear nueva tabla.

Luego aparece la siguiente pantalla en la cual definimos todos los campos con sus respectivos tipos de datos, si se aceptan o no los valores nulos y cuál de ellos será el campo clave, como lo muestra la siguiente figura.

Después de definir los campos con sus respectivos tipos elegiremos guardar, donde se nos pedirá el nombre de la tabla a la cual llamaremos CATEGORIA, daremos aceptar y habremos guardado nuestra tabla.
Para eliminar las tablas de la base de datos, primero se eliminaran las tablas que poseen claves foráneas y luego las tablas de las cuales dependían. En el ejemplo que venimos manejando se debería borrar primero la tabla EMPLEADOS_CONCEPTOS, luego la tabla EMPLEADOS, para por último eliminar la tabla CATEGORIAS. El orden de eliminación es inverso al orden de creado.

· ALTER
Esta instrucción se utiliza para modificar, agregar o quitar propiedades, restricciones o atributos de las bases de datos, tablas vistas, procedimientos almacenados, desencadenadores, funciones entre otros.
Para entender bien esta instrucción daremos un ejemplo de cómo agregarle un atributo o campo a la tabla CATEGORIAS de nombre usuario para registrar el usuario que agregó una categoría a la tabla y luego sacaremos dicho atributo con la misma instrucción como si nada hubiera sucedido.
Para agregar el campo usuario utilizamos la siguiente instrucción T-SQL
USE SUELDOS
ALTER TABLE CATEGORIAS
ADD Usuario varchar (30)


USE SUELDOS
ALTER TABLE CATEGORIAS
DROP COLUMN UsuarioCon esta instrucción la tabla CATEGORIAS ahora si ya posee el campo usuario. Para quitar este campo que recién agregamos utilizaremos el siguiente código:

· DROP
Esta instrucción se utiliza para borrar o eliminar base de datos, tablas, vistas, procedimientos almacenados, funciones, índices, desencadenadores, reglas, atributos, propiedades, restricciones, etc.

La instrucción T-SQL para eliminarla sería:
USE SUELDOS
DROP TABLE CATEGORIAS

· DML
Este lenguaje se utiliza para manipular los datos que se encuentran en los diferentes objetos de la base de datos; es decir consultar los datos (SELECT), insertar nuevos datos (INSERT), modificarlos (UPDATE), o eliminarlos (DELETE) de nuestra base de datos. Cabe mencionar que la manipulación de los datos se hace a nivel de tuplas (filas).

· INSERT
La instrucción INSERT se utiliza para insertar una o más filas en una tabla o en una vista. Un ejemplo sería si deseamos insertar una fila a la tabla CATEGORIAS el código sería el siguiente:
USE SUELDOS
INSERT INTO CATEGORIAS (Cod_Categoria, Desc_Categoria)
VALUES (‘CA’, ‘CADETE’)

En este ejemplo estamos insertando en la tabla CATEGORIAS la fila que posee código de categoría CA cuya descripción para la categoría es CADETE.
Después de INSERT colocamos la palabra INTO (indica donde deseamos realizar la inserción de datos) y después el nombre de la tabla donde queremos agregar la fila de valores, en nuestro caso CATEGORIAS. Luego colocamos el nombre de los campos en su respectivo nombre entre paréntesis y después la palabra VALUE que le indica al motor de base de datos que a partir de ahí todo lo que parezca entre los paréntesis será el conjunto de valores a insertar. Además, los valores CA y CADETE están encerrados entre apóstrofos pues son cadenas de caracteres y esa es la forma de indicárselo al motor de SQL.
Algo que se debe tomar en cuenta a la hora de insertar filas en las tablas de la base de datos, se deberán insertar primero las filas de las tablas que no poseen claves foráneas para luego insertar las filas en las tablas que poseen claves foráneas.

· SELECT
Esta instrucción permite consultar los datos de una o más tablas. Dicha instrucción además se puede utilizar para filtrar los datos mostrados tanto a nivel de atributos (campos) como a nivel de tuplas (filas).
USE SUELDOS
SELECT *
FROM CATEGORIASUn ejemplo para que se muestren todos los datos de la tabla CATEGORIAS.

USE SUELDOS
SELECT Desc_Categoria
FROM CATEGORIAS
WHERE Cod_Categoria =’CA’Ahora si uno quisiera nada más ver la descripción de la categoría cuando esta es el código CA el código sería:


· UPDATE

Se utiliza para modificar (actualizar o cambiar) el valor o valores de una fila o filas de una tabla. Supongamos que deseamos cambiar la descripción de la categoría del código categoría CA de cadete a capataz.
USE SUELDOS
UPDATE CATEGORIAS
SET Desc_Categoria = ‘Capataz’
Where Cod_categoria = ’CA’
· DELETE

Permite eliminar una o más filas de una tabla. Un ejemplo es eliminar todas las filas de la tabla CATEGORIAS.
USE SUELDOS
DELETE FROM CATEGORIAS

O en su defecto
USE SUELDOS
DELETE CATEGORIAS

Ahora bien si se desea eliminar las filas de la tabla categoría, cuyo código de categoría es CA, el código será:
USE SUELDOS
DELETE FROM CATEGORIAS
WHERE Cod_categoria = ‘CA’

CONSULTAS EN SQL SERVER
La sentencia SELECT describe una consulta a la base de datos relacional permitiendo la recuperación de filas y columnas de una o varias tablas. La ejecución de este tipo de consultas no modifica dato alguno. Los resultados de las consultas siempre serán tuplas, es decir conjuntos de resultados ordenados visualmente como la intersección de filas por columnas.
Como ya mencionamos, la instrucción SELECT es la encargada de describir una consulta en la base de datos relacional.

A continuación algunos de los parámetros que se pueden llegar a utilizar junto con SELECT:
· ALL: indica que se admite la aparición de filas duplicadas, y es el valor por omisión.
· DISTINCT: indica la unicidad de las filas de resultados. Los valores nulos son considerados iguales para esta cláusula.
· TOP expresión [PERCENT]: indica la cantidad de filas por recuperar, ya sea por cardinal o porcentaje. La expresión TOP [WITH TIES] limita los resultados a las primeras filas que cumplan con la limitación indicada.
· : representa la lista de columnas (separadas por comas) que contendrá el conjunto de resultados. *indica que se devolverán todas las columnas de la tabla. Nombre_tablanombre_vistaalias_tabla*limita el alcance de* cuando se consultan varias tablas. Es decir que, si hay más de una tabla o vista en cláusula FROM, se deberá usar un alias antecediendo al operador* para indicar a que tabla corresponde aplicar el operador.nombre_columna indica el nombre de la columna para recuperar. Si se desea recuperar el mismo nombre del campo para dos tablas, deberá utilizarse el alias o nombre de objeto (tabla o vista) como predecesor del nombre del campo. Expresión representan un nombre dado a una columna como alias, a una constante, a una función o a una subconsulta.
· $IDENTITY: devuelve la columna que tiene fijada la propiedad identidad. Si más de una tabla involucrada en la consulta tiene definida esta propiedad, deberá anteponerse el alias del objeto o su nombre.
· $ROWGUID: devuelve la columna que tiene fijado el tipo de dato global Unique identifier. Si más de una tabla involucrada en la consulta tiene definido este tipo, deberá anteponerse el alias del objeto o su nombre.
· CLR. Indica un método, o una propiedad o campo de un tipo del CLR. Usar “.” Para una instancia y “::” para el método, propiedad pública del CLR.
Funciones agregadas en la sentencia SELECT.
Existen funciones agregadas generales que nos permiten resumir resultados en una instrucción SELECT. Estas funciones son.
o AVG (exp) devuelve la medida de la expresión. Solo se puede utilizar con valores numéricos e ignora os valores nulos.
o COUNT (expresión) devuelve el número de valores no nulos de la expresión. Si se especifica la cláusula DISTINTC, devuelve la cantidad de valores no nulos exclusivos. Se puede aplicar a expresiones numéricas y no numéricas.
o COUNT (*): tiene como finalidad devolver la cuenta del número de filas de una tabla. Cuenta la totalidad de filas, incluyendo aquellas que poseen valores nulos.
o MAX (expresión) devuelve el valor máximo de la expresión.
o MIN (Expresión) devuelve el valor mínimo de la expresión.
o Estas funcione pueden usarse en columnas numéricas pero no en las de tipo bit.
o SUM (Expresión) devuelve la suma de todos los valores, o de los exclusivos si se utiliza en conjunto con DISTINCT. La función ignora los valores nulos.
Consideraciones particulares de la sentencia SELECT.
Las sentencias SELECT están permitidas en funciones definidas por el usuario, siempre y cuando se utilicen con el objeto de asignar valores a variables locales. Los alias de columna se pueden usar en cláusulas ORDER BY, aunque no pueden utilizarse con WHERE, GROUP BY o

HAVING.
La conjunción y disyunción de cláusulas WHERE mediante las palabras reservadas AND y OR permite restringir más el conjunto de resultados.
FROM y JOIN
La forma correcta de obtener los resultados de la unión de dos tablas es indicar la clave de la relación en la cláusula FROM mediante los órdenes de
JOIN.
La cláusula FROM establece las tablas, vistas, tablas derivadas y uniones mediante JOINS que se utilizan en instrucciones SELECT, INSERT, UPDATE y DELETE. Además, el argumento FROM se requiere siempre, a menos que la lista de SELECT sólo obtenga el resultado de expresiones matemáticas, funciones, constrantes, etc.
Principales parámetros que componen la sentencia FROM:
indica la tabla con la que se vinculará la primera tabla de la lista FROM el tipo de unión y las condiciones en las que se hará la misma.
JOIN: indica las tablas a unir.
CROSS JOIN: india el producto cruzado entre las tablas incluidas en la expresión.
Función_agregación: representa una función de agregación del sistema creada por el usuario. Las funciones utilizadas deben ser invariables ante nulos, es decir, no deben considerarlos en su cálculo, por ejemplo COUNT(*)

SELECT UNION
Permite combinar el resultado de dos o más consultas, combinando columnas de dos tablas en un mismo conjunto de resultados. Las reglas básicas para realizar uniones son:
I. El número y orden de las columnas debe ser el mismo en todas las consultas.
II. Los tipos de datos deben ser compatibles.

WHERE
La cláusula WHERE permite restringir las filas devueltas por una consulta mediante el uso de predicados separados por las cláusulas AND y OR.

PREDICADOS
Los operadores AND, OR y NOT se utilizan para conectar predicados de condiciones de búsqueda en cláusulas WHERE.
El operador AND devuelve verdadero si ambas condiciones son verdaderas.
El operador NOT reversa el resultado de una condición de búsqueda.
El operador OR conecta dos condiciones de búsqueda y basta con que una de ellas sea verdadera para que devuelva verdadero.
Los predicados devuelven tres resultados: TRUE, FALSE o UNKNOW.
A continuación, explicamos los predicados que se pueden utilizar en cláusulas WHERE.

CONTAINS
Es un predicado utilizado para buscar columnas basadas en tipos de datos de caracteres en forma más o menos precisa contra un patrón. Los hitos (aciertos) se pueden buscar por palabras simples frases, por proximidad de palabras o inflexiones.
En resumen se utiliza para buscar:
· Palabras o frases.
· El prefijo de palabras o frases
· Una palabra cerca de otra.
· La inflexión de una palabra a partir de otra.
· Palabras sinónimas

EXISTS
Permite testear el resultado de una subconsulta.

IN
Permite evaluar si una expresión se encuentra en una lista o subconsulta.

IS NULL
Este predicado tiene como funcionalidad evaluar si una determinada expresión es nula o no.

LIKE
Determina cuando o no un determinado carácter conforma un patrón que puede incluir caracteres regulares o caracteres comodines.

FREETEXT
Este predicado provee una forma simple, utilizado el lenguaje natural, de consultar columnas basadas en tipos de caracteres buscando valores de coincidencia por significado más que por palabras o frases.

{SOME/ANY}
Se les utiliza como operadores de comparación en subconsultas. Devuelve TRUE para su predicado si cualquier valor de la subconsulta satisface la comparación.

GROUP BY
La cláusula GROUP BY permite especificar los grupos, dentro de los cuales las filas de resultados serán incorporadas, devolviendo, además, la sumarización de resultados se si ha utilizado una función de agregación en la sentencia SELECT.
Para utilizar esta sentencia, es necesario que cada columna no incluida en una función de agregación sea incluida en la lista GROUP BY en el mismo orden que en la sentencia SELECT.

CURE
Genera un conjunto de resultados multidimensional basado en las columnas que el usuario desea analizar. La lista del SELECT contiene las columnas y funciones de agregación. La cláusula GROUP BY incorpora WITH CUBE para obtener todas las combinaciones posibles de los valores.

ROLLUP
Es útil para generar reportes con totales y subtotales similar a CUBE. Pero a diferencia entre ambos consiste en que CUBE genera agregaciones para todas las combinaciones de valores en las columnas seleccionadas y ROLLUP muestra subtotales por jerarquía de valores en dichas columnas.

FOR XML
Podemos escribir consultas que devuelven resultados con formato XML en lugar de la estructura usual de resultados. Estas consultas se pueden ejecutar dentro de sentencias SELECT en funciones y procedimientos almacenados, adicionando la clausura FOR XML en sus modalidades RAW, AUTO, EXPLICIT o PATH.
RAW: Se encarga de generar un elemento por fila de datos recuperada sin anidación.
AUTO: genera elementos XML anidados basándose en heurísticas sobre conformación de la lista SELECT.
EXPLICIT y PATH: permiten obtener un mayor control sobre los elementos generados requiriendo la asignación de formato, `puesto que se pueden direccionar los resultados a esquemas XML.

ORDER BY
Esta cláusula ordena los resultados por una o más columnas de hasta 8060 bytes. El tipo de ordenamiento lo fijan las claves ASC y DESC. El resultado exacto de la ordenación depende del conjunto de caracteres para los campos de tipo varchar, char, nchar, y nvarchar, pudiéndose especificar uno en particular para el ordenamiento.
Esta cláusula no puede aplicarse a campos de tipo text, ntext, image o xml.

OPERACIONES DE MODIFICACIÓN DE DATOS
SQL Server admite tres operaciones para la manipulación de datos a través de las sentencias:
INSERT
Esta sentencia es utilizada para insertar una o más filas de datos en una tabla.

UPDATE
Esta sentencia es utilizada para modificar una o más filas en una tabla basándose en una condición de búsqueda y restricción de resultados. Si no se especifica la cláusula WHERE, la sentencia afectará a todas las filas de la tabla.

DELETE
Esta sentencia es utilizada para eliminar una o más filas de datos de una tabla basándose en una condición de búsqueda y restricción de resultados. Si no se especifica la cláusula WHERE, la sentencia afectará a todas las filas de la tabla. La opción de DELETE no tiene forma de deshacerse a menos que sea incluida en una transacción, entre las sentencias BEGIN TRAN/COMMIT TRAN.

CONCLUSIÓN
Finalmente como pudimos observar en la estructura de estas instrucciones, hay muchas reglas que deben de cumplir desde el modo en que se crean las bases de datos, como el orden que deben llevar la creación y la eliminación de las tablas.
Cabe aclarar que esto es solo una parte de lo que se puede llegar a hacer con las sintaxis de SQL, pues en la opción de help de SQL se puede apreciar con más detalle cada una de las estructuras y sus funciones, pues en este apartado se encuentran mejor definidas.
Después de esta breve introducción por el lenguaje SQL, ahora si estamos listos para hacer un buen manejo de las bases de datos, aunque ya se mencionó que falta más por conocer de este lenguaje, estas son las instrucciones más esenciales con las que se pueden manejar las bases de datos sin ningún problema.
Para concluir podemos decir que las sintaxis de cada una de las instrucciones son la parte fundamental de SQL, pues con ellas son con las que se manejan todo tipo de consultas, modificaciones, actualizaciones, etc. Pues este tipo de funciones son las que nos ayudarán a destacar cada uno de los procesos que realizará nuestra base de datos.

BIBLIOGRAFÍA:
Rosa (2005). Cap. 7 Consultas
Rozic (2004). Cap. 5 El Lenguaje SQL

Lectura 5 - SQL Interactivo

INTRODUCCIÓN


A continuación veremos una breve descripción de lo que se trata SQL interactivo.
Analizaremos cada una de sus características, funciones, elementos, modos de ejecución, sus palabras reservadas y no reservadas.
Todos y cada uno de los términos que se presentaran son parte fundamental del SQL a la hora de crear las bases de datos, de hacer consultas en estas, de eliminarlas o modificarlas.
Conoceremos las sentencias o palabras claves que serán de gran ayuda para cada una de las situaciones antes mencionadas.
Desde el momento de mencionar las características comprenderemos el por que SQL es llamado interactivo; pues podremos observar la manera en que SQL interactúa con los demás lenguajes que son utilizados para su interpretación.
La manera en que SQL interactivo se comunica es fundamental en su interpretación ya que esta basada en diferentes tipos de caracteres que se darán conocer enseguida.


CONCEPTOS BÁSICOS DEL LENGUAJE DE SQL

CARACTERÍSTICAS

El lenguaje SQL esta formado por tres tipos de caracteres:

· Letras. El alfabeto latino
· Dígitos. Los números.
· Caracteres especiales. Espacios en blanco y los siguientes
“%&()*+,-. ;:<= >[ ]^_#{ } / ­

Los modos de utilización van a depender de la forma de ejecutar, por ejemplo:


I. Ejecución directa o SQL interactivo. Las ordenes SQL se escriben en una línea de comando, o bien se crea mediante un diseño visual que las traduce a una sentencia SQL y así son ejecutadas por el sistema.


II. Incrustado o embebido. Las ordenes SQL se escriben como parte del código de otro lenguaje, delimitando las sentencias SQL mediante separadores especiales, de modo que cada unidad de compilación este formada por código anfitrión y SQL. Una unidad de compilación es un segmento de código ejecutable. Cada lenguaje anfitrión contiene los compiladores SQL propios de la base de datos en la que se almacenan los datos.


III. Módulos SQL-cliente o procedimientos almacenados. Un modulo SQL cliente es un objeto SQL, de modo que las ordenes SQL se compilan aparte del código anfitrión y se enlazan todos los programas, creándose un ejecutable. Un único modulo puede estar asociado a varias unidades de compilación. Los mecanismos de asociación entre módulos y la transferencia del control de unos a otros son definidos por el fabricante.


IV. Dinámico. Las instrucciones SQL a ejecutar se incluyen en un modulo de SQL-cliente o en una unidad de compilación usando SQL incrustado. Los tipos de órdenes empleados en SQL dinámico comprenden las siguientes instrucciones:


* Prepare. Preparan el código SQL generado para su ejecución.
* Deallocate. Quitan la marca de preparación de las órdenes SQL preparadas.
* Describe. Contiene una descripción de los parámetros de entradas y /salidas dinámica de una orden prepare.
* Select. Devuelve un conjunto de columnas como resultado de una consulta dinámica.
* Execute. Asocia los parámetros de entrada a una orden prepare de modo que pueda ejecutarse.
* Declare cursor. Declara un cursor o tabla en memoria.
* Allocate cursor, fetch, delete, update, open, close, get. Son órdenes que pueden ejecutarse sobre cursores.


La ejecución dinámica de órdenes SQL se realiza de modos distintos.
Las órdenes pueden ser preparadas para su ejecución y mas tarde ejecutadas una o más veces, pudiéndose eliminar, mediante dellocate o release, la orden preparada cuando no se necesite ejecutar más. El segundo modo consiste en ejecutar las órdenes directamente, sin prepararlas.

V. Procedimientos invocados externamente
Son rutinas –funciones o procedimientos- SQL, que contiene órdenes de tipo call y una cláusula de nombre de lenguaje que define un lenguaje distinto de SQL.

VI. Rutinas invocadas por SQL y funciones construidas
Una rutina invocada por SQL se define entre dos elementos, el nombre de la rutina y el nombre del lenguaje en que se implementa, siendo relativamente variable su implementación de una base de datos comerciales a otras.

ELEMENTOS DE LAS ORDENES SQL


Las ordenes SQL no poseen un formato especifico, siempre empiezan por una palabra predefinida. Para separar ordenes se emplea el punto y coma (;). Las órdenes están formadas por los siguientes elementos:


* Palabras predefinidas. Son las palabras propias del lenguaje SQL: SELECT, FROM, WHERE, LIKE, CREATE, entre otras.
* Nombres de objetos (tablas, columnas,..). Son los identificadores asignados por los programadores o usuarios a tablas, campos, variables, procedimientos, etc. La mayor parte de lo nombres de objetos se definen mediante una orden CREATE.
* Constantes. Son secuencias de caracteres cuyo valor no varia, siendo independientes de los datos. Son de tipo: numéricas, carácter, temporal, GUID, monetarias e índices.
* Delimitadores. Son un subconjunto de caracteres que separan elementos de una sentencia SQL.
* Operadores. Sirven para crear nuevos datos a partir de los ya existentes.
* Datos y nulos. Un datos es una unidad de información con sentido en si misma que se almacena en un campo, una constante o una variable. Un valor nulo (NULL) es un valor no asignable en especial a un determinado tipo de datos.
* Expresiones y predicados. Es una combinación de operadores y operandos (contenedores de información-datos-) y se ligan unos con otros mediante operadores. Los predicados son expresiones contenidos en ordenes SQL, permiten expresar condiciones en diferentes ordenes, obteniéndose un valor booleano y se clasifican en los siguientes tipos:

- Comparación. Están formados por 3 elementos: una expresión o valor a comparar, un operador de comparación y un valor con el que se compara.
- BETWEEN. Especifica si un valor esta comprendido en un rango.
- IN. Especifica si un valor esta comprendido en una lista de valores declarada.
- LIKE. Especifican si un campo cumple un determinado patrón o secuencia de caracteres con los que se compara.
- NULL. Especifican una prueba para un valor nulo (NULL).
- Cuantificados. Es un tipo especial de comparación en el que en lugar de obtenerse un valor único como resultado de la comparación, se obtiene un conjunto de valores pertenecientes a un campo. El resultado de un predicado cuantificado depende del cuantificador empleado: ALL, SOME y ANY.
- EXISTS. Especifican una prueba para un conjunto de valores.
- UNIQUE. Especifican una prueba para comprobar la ausencia de filas duplicadas.
- MATCH. Especifican una prueba para filas coincidentes.
- OVERLAPS. Especifica si se produce solapes entre dos periodos de tiempo.
- DISTINCT. Especifica si dos valores de fila son distintos.
- TYPE. Especifica una prueba de tipo de datos.


* Predicados compuestos: And, Or, Not. Para obtener el valor de un predicado compuesto de tipo AND, OR, NOT, se evalúa cada parte del predicado compuesto y se siguen las reglas de la lógica proposicional, obteniéndose un resultado verdadero, falso o desconocido.

FUNCIONES


Son operaciones que se pueden realizar sobre un determinado valor o conjunto de valores, devolviendo, a su vez, un nuevo valor, del mismo o distinto tipo. Algunos de los tipos son:

* Funciones del valor numérico. Son funciones que devuelven un valor de tipo numérico.
* Funciones de cadena. Son funciones que devuelven un valor de tipo cadena de caracteres o bit. Son de tres tipos: carácter, BLOB y bit.
* Funciones de fecha /hora e intervalo. Son funciones que devuelven un valor de tipo fecha/hora o intervalo respectivamente.
* Conversión de tipos de datos. CAST (expresión en tipo de dato inicial, tipo de dato al que convertir la expresión). También se emplea la función CONVERT.
* Función de análisis de datos. Devuelven un resultado derivado de realizar una operación sobre cierto numero de filas se dividen en 2 funciones, funciones tabulares y funciones tabulares de ventanas.
* Funciones de agrupamiento de datos. Pueden calificarse con DISTINCT o ALL. La sintaxis de las funciones de agregado es: nombre de la función y una expresión que devuelve un valor de fila.
* Funciones de sistema. Son funciones implementadas por los fabricantes para facilitar tareas a los administradores y programadores de la base de datos, son muy variables de unos fabricantes a otros en cuanto a funcionalidad y sintaxis.
* Objeto de base de datos. Son todos aquellos que se pueden crear en una base de datos empleando la orden CREATE de SQL, los que son definidos por los usuarios y aquellos que permiten la administración del sistema.


PALABRAS RESERVADAS


Las palabras clave empleadas en SQL son de dos tipos: reservadas y no reservadas. La lista de palabras reservadas, en la versión ANSI (1999/2000) es mayor que la definida en la versión ANSI X3.135-1992. Las palabras reservadas se listan para evitar su utilización por los usuarios, ya que pueden llegar a ser empleadas en nuevas versiones de SQL.


TIPOS DE DATOS


Es un conjunto de valores. Loa parámetros SQL, columnas, atributos y variables son elementos de una base de datos cuyos valores pertenecen a un tipo de datos. El lenguaje SQL esta formado por 3 clases de tipos de datos: los datos predefinidos, los datos construidos o compuestos son los cuales se crean a partir de los tipos de datos definidos para esa categoría y los definidos por el usuario se crean a partir de datos existentes, o de aquellos tipos de datos en los que se basan los tipos de datos existentes.
SUBCONJUNTOS ANSI-SQL

DQL

Es el Data Query Language o Lenguaje de Consulta de Datos. La orden que se emplea es SELECT. Aunque la sintaxis de esta orden es muy compleja, es el centro de la gestión de consulta de datos, de modo que se mostraran las formas más usuales de empleo de SELECT sin definirla por completo. Como la orden SELECT permite seleccionar registros de una o más tablas, a la definición de una orden SELECT se le denominan consulta.
Su sintaxis básica es:

DML


Es el Data Manipulation Language o Lenguaje de Manipulación de Datos, entre sus órdenes destacan:


· La orden INSERT crea nuevas filas en una tabla. Su sintaxis es:
INSERT INTO tabla donde se inserta columnas a insertar subconsultas que genera las columnas [ OVERRIDING USER VALUE OVERRIDING SYSTEM VALUE ] DEFAULT VALUES lista de nombres de columna.
· La orden DELETE borra una fila de una tabla. Su sintaxis es:
DELETE FROM tabla de la que se borra WHERE CURRENT OF nombre del cursor.
· La orden UPDATE actualiza una fila de una tabla. Su sintaxis es:
UPDATE tabla SET nombre de columna = valor actualizado.


DDL


Es el Data Definition Lenguage o Lenguaje de Definición de Datos. Entre las órdenes de este lenguaje destacan las siguientes:

· La orden CREATE SCHEMA define un esquema en un catalogo. Su sintaxis es:
CREATE SCHEMA nombre del esquema características del esquema.
· La orden DROP SCHEMA destruye un esquema en un catalogo. Su sintaxis es:
DROP SCHEMA nombre del esquema CASCADE RESTRICT.
· CHECK su sintaxis es:
CHECK (condición de búsqueda).
· CONSTRAINT

DCL


Es el Data Control Language o Lenguaje de Control de Datos. Las órdenes mas usadas son:

· La orden GRANT define privilegios y autorizaciones tanto para usuarios como roles. Su sintaxis es:
GRANT .
· La orden REVOKE elimina autorizaciones y privilegios. Su sintaxis es:
REVOKE [<>] FROM ]> <>]>.

TPL


Es el Transaction Processing Language (Lenguaje de Proceso de Transacciones) o gestor de transacciones. Se emplea como lenguaje de control del DML. Las órdenes de esta parte de SQL son: STARR TRANSACTION, con el sinónimo BEGIN TRANSACTION, SET TRANSACTION, SET CONSTRAINTS MODE, SET SAVEPOINT, RELEASE SAVEPOINT, COMMIT,
ROLLBACK Y LOCK TABLE.

EJEMPLOS DE MANEJO DE BASES DE DATOS POR MEDIO DE LA HERRAMIENTA DE ANSI-SQL:

CREACIÓN DE BASE DE DATOS


Para empezar se pone la contraseña que pusimos cuando instalamos MySQL.
Damos la instrucción CREATE SCHEMA y el nombre de la base de datos a crear.
Con SHOW DATABASES; visualizamos las tablas existentes y la que se acaba de crear

CREACIÓN DE TABLAS

Con USE y el nombre de la BD, estamos eligiendo la BD con la que se trabajará y a la cuál se le creará una tabla

Con SHOW TABLES; se visualizan las tablas que contiene nuestra BD.
Con DESCRIBE y el nombre de la tabla se observan los datos con los que se lleno la tabla

INSERT INTO:VALUESSELECT * FROM

SELECT : AND y OR

ORDER BY ORDER BY DESC

LIKE

COUNT (*)COUNT (*) GROUP BY

CONCLUSIÓN

Finalmente como pudimos observar todas aquellas funciones y palabras claves ya mencionadas son indispe4nsables a la hora de usar SQL, pues con estas podremos manipular la forma en que deseamos construir nuestras bases de datos.
Todas y cada una de las s tencias on las que elaboremos nuestras bases de datos.

BIBLIOGRAFÍA:

Análisis y diseño detallado de aplicaciones informáticas de gestión.
G. Cabrera y G. Montoya.
Mc. Graw Hill 1999