miércoles, 21 de enero de 2009
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
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.
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
0 Comments:
Post a Comment