El parámetro "futureonly" es opcional, especifica que si existen campos (de cualquier tabla) con este tipo de dato, no se asocien al valor predeterminado; si creamos una nueva tabla con este tipo de dato, si estará asociado al valor predeterminado. Si no se especifica este parámetro, todos los campos de este tipo de dato, existentes o que se creen posteriormente (de cualquier tabla), quedan asociados al valor predeterminado. Si asocia un valor predeterminado a un tipo de dato definido por el usuario que tiene otro valor predeterminado asociado, el último lo reemplaza. Para quitar la asociación, empleamos el mismo procedimiento almacenado que aprendimos cuando quitamos asociaciones a campos: sp_unbindefault 'TIPODEDATODEFINIDOPORELUSUARIO'; Debe tener en cuenta que NO se puede aplicar una restricción "default" en un campo con un tipo de datos definido por el usuario si dicho campo o tipo de dato tienen asociado un valor predeterminado. Si un campo de un tipo de dato definido por el usuario tiene una restricción "default" y luego se asocia un valor predeterminado al tipo de dato, el valor predeterminado no queda asociado en el campo que tiene la restricción "default". Un tipo de dato definido por el usuario puede tener un solo valor predeterminado asociado. Cuando obtenemos información del tipo da dato definido por el usuario ejecutando "sp_help", en la columna "default_name" se muestra el nombre del valor predeterminado asociado a dicho tipo de dato; muestra "none" cuando no tiene ningún valor predeterminado asociado. 90 - Tipo de dato definido por el usuario (eliminar)
Podemos eliminar un tipo de dato definido por el usuario con el procedimiento almacenado "sp_droptype": exec sp_droptype TIPODEDATODEFINIDOPORELUSUARIO; Eliminamos el tipo de datos definido por el usuario llamado "tipo_documento": exec sp_droptype tipo_documento; Si intentamos eliminar un tipo de dato inexistente, aparece un mensaje indicando que no existe. Los tipos de datos definidos por el usuario se almacenan en la tabla del sistema "systypes". Podemos averiguar si un tipo de dato definido por el usuario existe para luego eliminarlo: if exists (select *from systypes where name = 'NOMBRETIPODEDATODEFINIDOPORELUSUARIO')
exec sp_droptype TIPODEDATODEFINIDOPORELUSUARIO; Consultamos la tabla "systypes" para ver si existe el tipo de dato "tipo_documento", si es así, lo eliminamos: if exists (select *from systypes where name = 'tipo_documento') exec sp_droptype tipo_documento; No se puede eliminar un tipo de datos definido por el usuario si alguna tabla (u otro objeto) hace uso de él; por ejemplo, si una tabla tiene un campo definido con tal tipo de dato. Si eliminamos un tipo de datos definido por el usuario, desaparecen las asociaciones de las reglas y valores predeterminados, pero tales reglas y valores predeterminados, no se eliminan, siguen existiendo en la base de datos. 91 - Subconsultas
Una subconsulta (subquery) es una sentencia "select" anidada en otra sentencia "select", "insert", "update" o "delete" (o en otra subconsulta). Las subconsultas se emplean cuando una consulta es muy compleja, entonces se la divide en varios pasos lógicos y se obtiene el resultado con una única instrucción y cuando la consulta depende de los resultados de otra consulta. Generalmente, una subconsulta se puede reemplazar por combinaciones y estas últimas son más eficientes. Las subconsultas se DEBEN incluir entre paréntesis. Puede haber subconsultas dentro de subconsultas, se admiten hasta 32 niveles de anidación. Se pueden emplear subconsultas: - en lugar de una expresión, siempre que devuelvan un solo valor o una lista de valores. - que retornen un conjunto de registros de varios campos en lugar de una tabla o para obtener el mismo resultado que una combinación (join). Hay tres tipos básicos de subconsultas: 1. las que retornan un solo valor escalar que se utiliza con un operador de comparación o en lugar de una expresión. 2. las que retornan una lista de valores, se combinan con "in", o los operadores "any", "some" y "all". 3. los que testean la existencia con "exists". Reglas a tener en cuenta al emplear subconsultas:
- la lista de selección de una subconsulta que va luego de un operador de comparación puede incluir sólo una expresión o campo (excepto si se emplea "exists" y "in"). - si el "where" de la consulta exterior incluye un campo, este debe ser compatible con el campo en la lista de selección de la subconsulta. - no se pueden emplear subconsultas que recuperen campos de tipos text o image. - las subconsultas luego de un operador de comparación (que no es seguido por "any" o "all") no pueden incluir cláusulas "group by" ni "having". - "distinct" no puede usarse con subconsultas que incluyan "group by". - no pueden emplearse las cláusulas "compute" y "compute by". - "order by" puede emplearse solamente si se especifica "top" también. - una vista creada con una subconsulta no puede actualizarse. - una subconsulta puede estar anidada dentro del "where" o "having" de una consulta externa o dentro de otra subconsulta. - si una tabla se nombra solamente en un subconsulta y no en la consulta externa, los campos no serán incluidos en la salida (en la lista de selección de la consulta externa). 92 - Subconsultas como expresión
Una subconsulta puede reemplazar una expresión. Dicha subconsulta debe devolver un valor escalar (o una lista de valores de un campo). Las subconsultas que retornan un solo valor escalar se utiliza con un operador de comparación o en lugar de una expresión: select CAMPOS from TABLA where CAMPO OPERADOR (SUBCONSULTA); select CAMPO OPERADOR (SUBCONSULTA) from TABLA; Si queremos saber el precio de un determinado libro y la diferencia con el precio del libro más costoso, anteriormente debíamos averiguar en una consulta el precio del libro más costoso y luego, en otra consulta, calcular la diferencia con el valor del libro que solicitamos. Podemos conseguirlo en una sola sentencia combinando dos consultas:
select titulo,precio, precio-(select max(precio) from libros) as diferencia from libros where titulo='Uno'; En el ejemplo anterior se muestra el título, el precio de un libro y la diferencia entre el precio del libro y el máximo valor de precio. Queremos saber el título, autor y precio del libro más costoso: select titulo,autor, precio from libros where precio= (select max(precio) from libros); Note que el campo del "where" de la consulta exterior es compatible con el valor retornado por la expresión de la subconsulta. Se pueden emplear en "select", "insert", "update" y "delete". Para actualizar un registro empleando subconsulta la sintaxis básica es la siguiente: update TABLA set CAMPO=NUEVOVALOR where CAMPO= (SUBCONSULTA); Para eliminar registros empleando subconsulta empleamos la siguiente sintaxis básica: delete from TABLA where CAMPO=(SUBCONSULTA); Recuerde que la lista de selección de una subconsulta que va luego de un operador de comparación puede incluir sólo una expresión o campo (excepto si se emplea "exists" o "in"). No olvide que las subconsultas luego de un operador de comparación (que no es seguido por "any" o "all") no pueden incluir cláusulas "group by". 93 - Subconsultas con in
Vimos que una subconsulta puede reemplazar una expresión. Dicha subconsulta debe devolver un valor escalar o una lista de valores de un campo; las subconsultas que retornan una lista de valores reemplazan a una expresión en una cláusula "where" que contiene la palabra clave "in".
El resultado de una subconsulta con "in" (o "not in") es una lista. Luego que la subconsulta retorna resultados, la consulta exterior los usa. La sintaxis básica es la siguiente: ...where EXPRESION in (SUBCONSULTA); Este ejemplo muestra los nombres de las editoriales que ha publicado libros de un determinado autor: select nombre from editoriales where codigo in (select codigoeditorial from libros where autor='Richard Bach'); La subconsulta (consulta interna) retorna una lista de valores de un solo campo (codigo) que la consulta exterior luego emplea al recuperar los datos. Podemos reemplazar por un "join" la consulta anterior: select distinct nombre from editoriales as e join libros on codigoeditorial=e.codigo where autor='Richard Bach'; Una combinación (join) siempre puede ser expresada como una subconsulta; pero una subconsulta no siempre puede reemplazarse por una combinación que retorne el mismo resultado. Si es posible, es aconsejable emplear combinaciones en lugar de subconsultas, son más eficientes. Se recomienda probar las subconsultas antes de incluirlas en una consulta exterior, así puede verificar que retorna lo necesario, porque a veces resulta difícil verlo en consultas anidadas. También podemos buscar valores No coincidentes con una lista de valores que retorna una subconsulta; por ejemplo, las editoriales que no han publicado libros de un autor específico: select nombre from editoriales where codigo not in (select codigoeditorial from libros where autor='Richard Bach');
94 - Subconsultas any - some - all
"any" y "some" son sinónimos. Chequean si alguna fila de la lista resultado de una subconsulta se encuentra el valor especificado en la condición. Compara un valor escalar con los valores de un campo y devuelven "true" si la comparación con cada valor de la lista de la subconsulta es verdadera, sino "false". El tipo de datos que se comparan deben ser compatibles. La sintaxis básica es: ...VALORESCALAR OPERADORDECOMPARACION ANY (SUBCONSULTA); Queremos saber los títulos de los libros de "Borges" que pertenecen a editoriales que han publicado también libros de "Richard Bach", es decir, si los libros de "Borges" coinciden con ALGUNA de las editoriales que publicó libros de "Richard Bach": select titulo from libros where autor='Borges' and codigoeditorial = any (select e.codigo from editoriales as e join libros as l on codigoeditorial=e.codigo where l.autor='Richard Bach'); La consulta interna (subconsulta) retorna una lista de valores de un solo campo (puede ejecutar la subconsulta como una consulta para probarla), luego, la consulta externa compara cada valor de "codigoeditorial" con cada valor de la lista devolviendo los títulos de "Borges" que coinciden. "all" también compara un valor escalar con una serie de valores. Chequea si TODOS los valores de la lista de la consulta externa se encuentran en la lista de valores devuelta por la consulta interna. Sintaxis: VALORESCALAR OPERADORDECOMPARACION all (SUBCONSULTA); Queremos saber si TODAS las editoriales que publicaron libros de "Borges" coinciden con TODAS las editoriales que publicaron libros de "Richard Bach": select titulo
from libros where autor='Borges' and codigoeditorial = all (select e.codigo from editoriales as e join libros as l on codigoeditorial=e.codigo where l.autor='Richard Bach'); La consulta interna (subconsulta) retorna una lista de valores de un solo campo (puede ejecutar la subconsulta como una consulta para probarla), luego, la consulta externa compara cada valor de "codigoeditorial" con cada valor de la lista, si TODOS coinciden, devuelve los títulos. Veamos otro ejemplo con un operador de comparación diferente: Queremos saber si ALGUN precio de los libros de "Borges" es mayor a ALGUN precio de los libros de "Richard Bach": select titulo,precio from libros where autor='Borges' and precio > any (select precio from libros where autor='Bach'); El precio de cada libro de "Borges" es comparado con cada valor de la lista de valores retornada por la subconsulta; si ALGUNO cumple la condición, es decir, es mayor a ALGUN precio de "Richard Bach", se lista. Veamos la diferencia si empleamos "all" en lugar de "any": select titulo,precio from libros where autor='borges' and precio > all (select precio from libros where autor='bach'); El precio de cada libro de "Borges" es comparado con cada valor de la lista de valores retornada por la subconsulta; si cumple la condición, es decir, si es mayor a TODOS los precios de "Richard Bach" (o al mayor), se lista. Emplear "= any" es lo mismo que emplear "in". Emplear "<> all" es lo mismo que emplear "not in".
Recuerde que solamente las subconsultas luego de un operador de comparación al cual es seguido por "any" o "all") pueden incluir cláusulas "group by". 95 - Subconsultas correlacionadas
Un almacén almacena la información de sus ventas en una tabla llamada "facturas" en la cual guarda el número de factura, la fecha y el nombre del cliente y una tabla denominada "detalles" en la cual se almacenan los distintos items correspondientes a cada factura: el nombre del artículo, el precio (unitario) y la cantidad. Se necesita una lista de todas las facturas que incluya el número, la fecha, el cliente, la cantidad de artículos comprados y el total: select f.*, (select count(d.numeroitem) from Detalles as d where f.numero=d.numerofactura) as cantidad, (select sum(d.preciounitario*cantidad) from Detalles as d where f.numero=d.numerofactura) as total from facturas as f; El segundo "select" retorna una lista de valores de una sola columna con la cantidad de items por factura (el número de factura lo toma del "select" exterior); el tercer "select" retorna una lista de valores de una sola columna con el total por factura (el número de factura lo toma del "select" exterior); el primer "select" (externo) devuelve todos los datos de cada factura. A este tipo de subconsulta se la denomina consulta correlacionada. La consulta interna se evalúa tantas veces como registros tiene la consulta externa, se realiza la subconsulta para cada registro de la consulta externa. El campo de la tabla dentro de la subconsulta (f.numero) se compara con el campo de la tabla externa. En este caso, específicamente, la consulta externa pasa un valor de "numero" a la consulta interna. La consulta interna toma ese valor y determina si existe en "detalles", si existe, la consulta interna devuelve la suma. El proceso se repite para el registro de la consulta externa, la consulta externa pasa otro "numero" a la consulta interna y SQL Server repite la evaluación. 96 - Exists y No Exists
Los operadores "exists" y "not exists" se emplean para determinar si hay o no datos en una lista de valores. Estos operadores pueden emplearse con subconsultas correlacionadas para restringir el resultado de una consulta exterior a los registros que cumplen la subconsulta (consulta interior). Estos operadores retornan "true" (si las subconsultas retornan registros) o "false" (si las subconsultas no retornan registros).
Cuando se coloca en una subconsulta el operador "exists", SQL Server analiza si hay datos que coinciden con la subconsulta, no se devuelve ningún registro, es como un test de existencia; SQL Server termina la recuperación rec uperación de registros cuando por lo menos un registro cumple la condición "where" de la subconsulta. La sintaxis básica es la siguiente: ... where exists (SUBCONSULTA); En este ejemplo se usa una subconsulta correlacionada correlacionada con un operador "exists" en la cláusula "where" para devolver una lista de clientes que compraron el artículo "lapiz": select cliente,numero from facturas as f where exists (select *from Detalles as d where f.numero=d.numerofactura f.numero=d.numerofactura and d.articulo='lapiz'); d.articulo='lapiz'); Puede obtener el mismo resultado empleando una combinación. Podemos buscar los clientes que no han adquirido el artículo "lapiz" empleando "if not exists": select cliente,numero from facturas as f where not exists (select *from Detalles as d where f.numero=d.numerofactura f.numero=d.numerofactura and d.articulo='lapiz'); d.articulo='lapiz');
97 - Subconsulta simil autocombinación
Algunas sentencias en las cuales la consulta interna y la externa emplean la misma tabla pueden reemplazarse por una autocombinación. Por ejemplo, queremos una lista de los libros que han sido publicados por distintas editoriales. select distinct l1.titulo from libros as l1 where l1.titulo in (select l2.titulo from libros as l2 where l1.editorial <> l2.editorial);
En el ejemplo anterior empleamos una subconsulta correlacionada y las consultas interna y externa emplean la misma tabla. La subconsulta devuelve una lista de valores por ello se emplea "in" y sustituye una expresión en una cláusula "where". Con el siguiente "join" se obtiene el mismo resultado: select distinct l1.titulo from libros as l1 join libros as as l2 on l1.titulo=l1.titulo and l1.autor=l2.autor where l1.editorial<>l2.editorial; l1.editorial<>l2.editorial; Otro ejemplo: Buscamos todos los libros que tienen el mismo precio que "El aleph" empleando subconsulta: select titulo from libros where titulo<>'El aleph' and precio = (select precio from libros where titulo='El aleph'); La subconsulta retorna un solo valor. Buscamos los libros cuyo precio supere el precio promedio de los libros por editorial: select l1.titulo,l1.editorial,l1.precio l1.titulo,l1.editorial,l1.precio from libros as l1 where l1.precio > (select avg(l2.precio) avg(l2.precio) from libros as l2 where l1.editorial= l2.editorial); Por cada valor de l1, se evalúa la subconsulta, si el precio es mayor que el promedio. 98 - Subconsulta en lugar de una tabla
Se pueden emplear subconsultas que retornen un conjunto de registros de varios campos en lugar de una tabla. Se la denomina tabla derivada y se coloca en la cláusula "from" para que la use un "select" externo. La tabla derivada debe ir entre paréntesis y tener un alias para poder referenciarla. La sintaxis básica es la siguiente:
select ALIASdeTABLADERIVADA.CAMPO ALIASdeTABLADERIVADA.CAMPO from (TABLADERIVADA) as ALIAS; La tabla derivada es una subsonsulta. Podemos probar la consulta que retorna la tabla derivada y luego agregar el "select" externo: select f.*, (select sum(d.precio*cantidad) sum(d.precio*cantidad) from Detalles as d where f.numero=d.numerofactura) f.numero=d.numerofactura) as total from facturas as f; La consulta anterior contiene una subconsulta correlacionada; correlacionada; retorna todos los datos de "facturas" y el monto total t otal por factura de "detalles". Esta consulta retorna varios registros y varios campos y será la tabla derivada que emplearemos en la siguiente consulta: select td.numero,c.nombre,td.total td.numero,c.nombre,td.total from clientes as c join (select f.*, (select sum(d.precio*cantidad) sum(d.precio*cantidad) from Detalles as d where f.numero=d.numerofactura) f.numero=d.numerofactura) as total from facturas as f) as td on td.codigocliente=c.codigo; td.codigocliente=c.codigo; La consulta anterior retorna, de la tabla derivada (referenciada con "td") el número de factura y el monto total, y de la tabla "clientes", el nombre del cliente. Note que este "join" no emplea 2 tablas, sino una tabla propiamente dicha y una tabla derivada, que es en realidad una subconsulta. 99 - Subconsulta (update - delete)
Dijimos que podemos emplear subconsultas en sentencias "insert", "update", "delete", además de "select". La sintaxis básica para realizar actualizaciones con subconsulta es la siguiente: update TABLA set CAMPO=NUEVOVALOR where CAMPO= (SUBCONSULTA); Actualizamos el precio de todos los libros de editorial "Emece": update libros set precio=precio+(precio*0.1) precio=precio+(precio*0.1) where codigoeditorial= codigoeditorial=
(select codigo from editoriales where nombre='Emece'); La subconsulta retorna un único valor. También podemos hacerlo con un join. La sintaxis básica para realizar eliminaciones con subconsulta es la siguiente: delete from TABLA where CAMPO in (SUBCONSULTA); Eliminamos todos los libros de las editoriales que tiene publicados libros de "Juan Perez": delete from libros where codigoeditorial in (select e.codigo from editoriales as e join libros on codigoeditorial=e.codigo where autor='Juan Perez'); La subconsulta es una combinación que retorna una lista de valores que la consulta externa emplea al seleccionar los registros para la eliminación. 100 - Subconsulta (insert)
Aprendimos que una subconsulta puede estar dentro de un "select", "update" y "delete"; también puede estar dentro de un "insert". Podemos ingresar registros en una tabla empleando un "select". La sintaxis básica es la siguiente: insert into TABLAENQUESEINGRESA (CAMPOSTABLA1) select (CAMPOSTABLACONSULTADA) from TABLACONSULTADA; Un profesor almacena las notas de sus alumnos en una tabla llamada "alumnos". Tiene otra tabla llamada "aprobados", con algunos campos iguales a la tabla "alumnos" pero en ella solamente almacenará los alumnos que han aprobado el ciclo. Ingresamos registros en la tabla "aprobados" seleccionando registros de la tabla "alumnos":
insert into aprobados (documento,nota) select (documento,nota) from alumnos; Entonces, se puede insertar registros en una tabla con la salida devuelta por una consulta a otra tabla; para ello escribimos la consulta y le anteponemos "insert into" junto al nombre de la tabla en la cual ingresaremos los registros y los campos que se cargarán (si se ingresan todos los campos no es necesario listarlos). La cantidad de columnas devueltas en la consulta debe ser la misma que la cantidad de campos a cargar en el "insert". Se pueden insertar valores en una tabla con el resultado de una consulta que incluya cualquier tipo de "join". 101 - Crear tabla a partir de otra (select - into)
Podemos crear una tabla e insertar datos en ella en una sola sentencia consultando otra tabla (o varias) con esta sintaxis: select CAMPOSNUEVATABLA into NUEVATABLA from TABLA where CONDICION; Es decir, se crea una nueva tabla y se inserta en ella el resultado de una consulta a otra tabla. Tenemos la tabla "libros" de una librería y queremos crear una tabla llamada "editoriales" que contenga los nombres de las editoriales. La tabla "editoriales", que no existe, contendrá solamente un campo llamado "nombre". La tabla libros contiene varios registros. Podemos crear la tabla "editoriales" con el campo "nombre" consultando la tabla "libros" y en el mismo momento insertar la información: select distinct editorial as nombre into editoriales from libros; La tabla "editoriales" se ha creado con el campo "nombre" seleccionado del campo "editorial" de "libros". Los campos de la nueva tabla tienen el mismo nombre, tipo de dato y valores almacenados que los campos listados de la tabla consultada; si se quiere dar otro nombre a los campos de la nueva tabla se deben especificar alias.
Entonces, luego de la lista de selección de campos de la tabla a consultar, se coloca "into" seguido del nombre de la nueva tabla y se sigue con la consulta. Podemos emplear "group by", funciones de agrupamiento y "order by" en las consultas. También podemos emplear "select... into" con combinaciones, para crear una tabla que contenga datos de 2 o más tablas. 102 - go
Esto solo se aplica cuando instale el SQL Server en su máquina. "go" es un signo de finalización de un lote de sentencias. No es una sentencia, es un comando. El lote de sentencias está compuesto por todas las sentencias antes de "go" o todas las sentencias entre dos "go". Las sentencias no deben ocupar la misma linea en la que está "go". Habrá notado que no se puede ejecutar un procedimiento almacenado luego de otras sentencias a menos que se incluya "execute" (o "exec"). Por ejemplo, si tipeamos: select *from empleados; sp_helpconstraint empleados; muestra un mensaje de error porque no puede procesar ambas sentencias como un solo lote. Para que no ocurra debemos tipear: select *from empleados; exec sp_helpconstraint empleados; o separar los lotes con "go": select *from empleados; go sp_helpconstraint empleados; Las siguientes sentencias no pueden ejecutarse en el mismo lote: create rule, create default,create view, create procedure, create trigger. Cada una de ellas necesita ejecutarse separándolas con "go". Por ejemplo: create table.... go create rule... go
Recuerde que si coloca "go" no debe incluir el "punto y coma" (;) al finalizar una instrucción. No está de más recordar que esto solo se aplica cuando instale el SQL Server en su máquina y ejecute los comandos desde el Query Analyzer. 103 - Vistas
Una vista es una alternativa para mostrar datos de varias tablas. Una vista es como una tabla virtual que almacena una consulta. Los datos accesibles a través de la vista no están almacenados en la base de datos como un objeto. Entonces, una vista almacena una consulta como un objeto para utilizarse posteriormente. Las tablas consultadas en una vista se llaman tablas base. En general, se puede dar un nombre a cualquier consulta y almacenarla como una vista. Una vista suele llamarse también tabla virtual porque los resultados que retorna y la manera de referenciarlas es la misma que para una tabla. Las vistas permiten: - ocultar información: permitiendo el acceso a algunos datos y manteniendo oculto el resto de la información que no se incluye en la vista. El usuario opera con los datos de una vista como si se tratara de una tabla, pudiendo modificar tales datos. - simplificar la administración de los permisos de usuario: se pueden dar al usuario permisos para que solamente pueda acceder a los datos a través de vistas, en lugar de concederle permisos para acceder a ciertos campos, así se protegen las tablas base de cambios en su estructura. - mejorar el rendimiento: se puede evitar tipear instrucciones repetidamente almacenando en una vista el resultado de una consulta compleja que incluya información de varias ta blas. Podemos crear vistas con: un subconjunto de registros y campos de una tabla; una unión de varias tablas; una combinación de varias tablas; un resumen estadístico de una tabla; un subconjunto de otra vista, combinación de vistas y tablas. Una vista se define usando un "select". La sintaxis básica parcial para crear una vista es la siguiente: create view NOMBREVISTA as SENTENCIASSELECT from TABLA; El contenido de una vista se muestra con un "select":
select *from NOMBREVISTA; En el siguiente ejemplo creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 4 campos: create view vista_empleados as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados as e join secciones as s on codigo=seccion Para ver la información contenida en la vista creada anteriormente tipeamos: select *from vista_empleados; Podemos realizar consultas a una vista como si se tratara de una tabla: select seccion,count(*) as cantidad from vista_empleados; Los nombres para vistas deben seguir las mismas reglas que cualquier identificador. Para distinguir una tabla de una vista podemos fijar una convención para darle nombres, por ejemplo, colocar el sufijo “vista” y luego el n ombre de las tablas
consultadas en ellas. Los campos y expresiones de la consulta que define una vista DEBEN tener un nombre. Se debe colocar nombre de campo cuando es un campo calculado o si hay 2 campos con el mismo nombre. Note que en el ejemplo, al concatenar los campos "apellido" y "nombre" colocamos un alias; si no lo hubiésemos hecho aparecería un mensaje de error porque dicha expresión DEBE tener un encabezado, SQL Server no lo coloca por defecto. Los nombres de los campos y expresiones de la consulta que define una vista DEBEN ser únicos (no puede haber dos campos o encabezados con igual nombre). Note que en la vista definida en el ejemplo, al campo "s.nombre" le colocamos un alias porque ya había un encabezado (el alias de la concatenación) llamado "nombre" y no pueden repetirse, si sucediera, aparecería un mensaje de error. Otra sintaxis es la siguiente: create view NOMBREVISTA (NOMBRESDEENCABEZADOS) as SENTENCIASSELECT from TABLA; Creamos otra vista de "empleados" denominada "vista_empleados_ingreso" que almacena la cantidad de empleados por año:
create view vista_empleados_ingreso (fecha,cantidad) as select datepart(year,fechaingreso),count(*) from empleados group by datepart(year,fechaingreso) La diferencia es que se colocan entre paréntesis los encabezados de las columnas que aparecerán en la vista. Si no los colocamos y empleamos la sintaxis vista anteriormente, se emplean los nombres de los campos o alias (que en este caso habría que agregar) colocados en el "select" que define la vista. Los nombres que se colocan entre paréntesis deben ser tantos como los campos o expresiones que se definen en la vista. Las vistas se crean en la base de datos activa. Al crear una vista, SQL Server verifica que existan las tablas a las que se hacen referencia en ella. Se aconseja probar la sentencia "select" con la cual definiremos la vista antes de crearla para as egurarnos que el resultado que retorna es el imaginado. Existen algunas restricciones para el uso de "create view", a saber: - no puede incluir las cláusulas "compute" ni "compute by" ni la palabra clave "into"; - no se pueden crear vistas temporales ni crear vistas sobre tablas temporales. - no se pueden asociar reglas ni valores por defecto a las vistas. - no puede combinarse con otras instrucciones en un mismo lote. Se pueden construir vistas sobre otras vistas. 104 - Vistas (información)
Las vistas son objetos, así que para obtener información de ellos pueden usarse los siguientes procedimientos almacenados del sistema: "sp_help" sin parámetros nos muestra todos los objetos de la base de datos seleccionada, incluidas las vistas. En la columna "Object_type" aparece "view" si es una vista. Si le enviamos como argumento el nombre de una vista, obtenemos la fecha de creación, propietario, los campos y demás información. "sp_helptext" seguido del nombre de una vista nos muestra el texto que la define, excepto si ha sido encriptado. Ejecutando "sp_depends" seguido del nombre de un objeto, obtenemos 2 resultados:
- nombre, tipo, campos, etc. de los objetos de los cuales depende el objeto nombrado y - nombre y tipo de los objetos que dependen del objeto nombrado. Si ejecutamos el procedimiento "sp_depends" seguido del nombre de una vista: sp_depends vista_empleados; aparecen las tablas (y demás objetos) de las cuales depende la vista, es decir, las tablas referenciadas en la misma. Si ejecutamos el procedimiento seguido del nombre de una tabla: sp_depends empleados; aparecen los objetos que dependen de la tabla, vistas, restricciones, etc. También se puede consultar la tabla del sistema "sysobjects": select *from sysobjects; Nos muestra nombre y varios datos de todos los objetos de la base de datos actual. La columna "xtype" indica el tipo de objeto, si es una vista, aparece 'V'. Si queremos ver todas las vistas creadas por nosotros, podemos tipear: select *from sysobjects where xtype='V' and-- tipo vista name like 'vista%';--búsqueda con comodín
105 - vistas (encriptar)
Podemos ver el texto que define una vista ejecutando el procedimiento almacenado del sistema "sp_helptext" seguido del nombre de la vista: sp_helptext NOMBREVISTA; Podemos ocultar el texto que define una vista empleando la siguiente sintaxis al crearla: create view NOMBREVISTA with encryption as SENTENCIASSELECT from TABLA;
"with encryption" indica a SQL Server que codifique las sentencias que definen la vista. Creamos una vista con su definición oculta: create view vista_empleados with encryption as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados as e join secciones as s on codigo=seccion Si ejecutamos el procedimiento almacenado del sistema "sp_helptext" seguido del nombre de una vista encriptada, aparece un mensaje indicando tal situación y el texto no se muestra. 106 - Vistas (eliminar)
Para quitar una vista se emplea "drop view": drop view NOMBREVISTA; Si se elimina una tabla a la que hace referencia una vista, la vista no se elimina, hay que eliminarla explícitamente. Solo el propietario puede eliminar una vista. Antes de eliminar un objeto, se recomienda ejecutar el procedimiento almacenado de sistema "sp_depends" para averiguar si hay objetos que hagan referencia a él. Eliminamos la vista denominada "vista_empleados": drop view vista_empleados;
107 - Vistas (with check option)
Es posible obligar a todas las instrucciones de modificación de datos que se ejecutan en una vista a cumplir ciertos criterios. Por ejemplo, creamos la siguiente vista: create view vista_empleados
as select apellido, e.nombre, sexo, s.nombre as seccion from empleados as e join secciones as s on seccion=codigo where s.nombre='Administracion' with check option; La vista definida anteriormente muestra solamente algunos de los datos de los empleados de la sección "Administracion". Además, solamente se permiten modificaciones a los empleados de esa sección. Podemos actualizar el nombre, apellido y sexo a través de la vista, pero no el campo "seccion" porque está restringuido. 108 - Vistas (modificar datos de una tabla a través de vistas)
Si se modifican los datos de una vista, se modifica la tabla base. Se puede insertar, actualizar o eliminar datos de una t abla a través de una vista, teniendo en cuenta lo siguiente, las modificaciones que se realizan a las vistas: - no pueden afectar a más de una tabla consultada. Pueden modificarse datos de una vista que combina varias tablas pero la modificación solamente debe afectar a una sola tabla. - no se pueden cambiar los campos resultado de un cálculo. - pueden generar errores si afectan a campos a las que la vista no hace referencia. Por ejemplo, si se ingresa un registro en una vista que consulta una tabla que tiene campos not null que no están incluidos en la vista. - la opción "with check option" obliga a todas las instrucciones de modificación que se ejecutan en la vista a cumplir ciertos criterios que se especifican al definir la vista. - para eliminar datos de una vista solamente UNA tabla puede ser listada en el "from" de la definicion de la misma. 109 - Vistas modificar (alter view)
Para modificar una vista puede eliminarla y volver a crearla o emplear "alter view". Con "alter view" se modifica la definición de una vista sin afectar los procedimientos almacenados y los permisos. Si elimina una vista y vuelve a crearla, debe reasignar los permisos asociados a ella. Sintaxis básica para alterar una vista:
alter view NOMBREVISTA with encryption--opcional as SELECT En el ejemplo siguiente se altera vista_empleados para agregar el campo "domicilio": alter view vista_empleados with encryption as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos,domicilio from empleados as e join secciones as s on codigo=seccion Si creó la vista con "with encryption" y quiere modificarla manteniendo la encriptación, debe colocarla nuevamente, en caso de no hacerlo, desaparece. Si crea una vista con "select *" y luego agrega campos a la estructura de las tablas involucradas, los nuevos campos no aparecerán en la vista; esto es porque los campos se seleccionan al ejecutar "create view"; debe alterar la vista. 110 - Lenguaje de control de flujo (case)
La función "case" compara 2 o más valores y devuelve un resultado. La sintaxis es la siguiente: case VALORACOMPARAR when VALOR1 then RESULTADO1 when VALOR2 then RESULTADO2 ... else RESULTADO3 end Por cada valor hay un "when" y un "then"; si encuentra un valor coincidente en algún "where" ejecuta el "then" correspondiente a ese "where", si no encuentra ninguna coincidencia, se ejecuta el "else"; si no hay parte "else" retorna "null". Finalmente se coloca "end" para indicar que el "case" ha finalizado. Un profesor guarda las notas de sus alumnos de un curso en una tabla llamada "alumnos" que consta de los siguientes campos: - nombre (30 caracteres), - nota (valor entero entre 0 y 10, puede ser nulo).
Queremos mostrar los nombres, notas de los alumnos y en una columna extra llamada "resultado" empleamos un case que testee la nota y muestre un mensaje diferente si en dicho campo hay un valor: - 0, 1, 2 ó 3: 'libre'; - 4, 5 ó 6: 'regular'; - 7, 8, 9 ó 10: 'promocionado'; Esta es la sentencia: select nombre,nota, resultado= case nota when 0 then 'libre' when 1 then 'libre' when 2 then 'libre' when 3 then 'libre' when 4 then 'regular' when 5 then 'regular' when 6 then 'regular' when 7 then 'promocionado' when 8 then 'promocionado' when 9 then 'promocionado' when 10 then 'promocionado' end from alumnos; Note que cada "where" compara un valor puntual, por ello los valores devueltos son iguales para algunos casos. Note que como omitimos la parte "else", en caso que el valor no encuentre coincidencia con ninguno valor "when", retorna "null". Podemos realizar comparaciones en cada "where". La sintaxis es la siguiente: case when VALORACOMPARAR OPERADOR VALOR1 then RESULTADO1 when VALORACOMPARAR OPERADOR VALOR2 then RESULTADO2 ... else RESULTADO3 end Mostramos los nombres de los alumnos y en una columna extra llamada "resultado" empleamos un case que teste si la nota es menor a 4, está entre 4 y 7 o supera el 7: select nombre, nota, condicion= case when nota<4 then 'libre' when nota >=4 and nota<7 then 'regular' when nota>=7 then 'promocionado'
else 'sin nota' end from alumnos; Puede utilizar una expresión "case" en cualquier lugar en el que pueda utilizar una expresión. También se puede emplear con "group by" y funciones de agrupamiento. 111 - Lenguaje de control de flujo (if)
Existen palabras especiales que pertenecen al lenguaje de control de flujo que controlan la ejecución de las s entencias, los bloques de sentencias y procedimientos almacenados. Tales palabras son: begin... end, goto, if... else, return, waitfor, while, break y continue. - "begin... end" encierran un bloque de sentencias para que sean tratados como unidad. - "if... else": testean una condición; se emplean cuando un bloque de sentencias debe ser ejecutado si una condición se cumple y si no se cumple, se debe ejecutar otro bloque de sentencias diferente. - "while": ejecuta repetidamente una instrucción siempre que la condición sea verdadera. - "break" y "continue": controlan la operación de las instrucciones incluidas en el bucle "while". Veamos un ejemplo. Tenemos nuestra tabla "libros"; queremos mostrar todos los títulos de los cuales no hay libros disponibles (cantidad=0), si no hay, mostrar un mensaje indicando tal situación: if exists (select *from libros where cantidad=0) (select titulo from libros where cantidad=0) else select 'No hay libros sin stock'; SQL Server ejecuta la sentencia (en este caso, una subconsulta) luego del "if" si la condición es verdadera; si es falsa, ejecuta la sentencia del "else" (si existe). Podemos emplear "if...else" en actualizaciones. Por ejemplo, queremos hacer un descuento en el precio, del 10% a todos los libros de una determinada editorial; si no hay, mostrar un mensaje: if exists (select *from libros where editorial='Emece') begin update libros set precio=precio-(precio*0.1) where editorial='Emece' select 'libros actualizados' end
else select 'no hay registros actualizados'; Note que si la condición es verdadera, se deben ejecutar 2 sentencias. Por lo tanto, se deben encerrar en un bloque "begin...end". En el siguiente ejemplo eliminamos los libros cuya cantidad es cero; si no hay, mostramos un mensaje: if exists (select *from libros where cantidad=0) delete from libros where cantidad=0 else select 'No hay registros eliminados;
112 - Variables de usuario
Las variables nos permiten almacenar un valor y recuperarlo más adelante para emplearlos en otras sentencias. Las variables de usuario son específicas de cada conexión y son liberadas automáticamente al abandonar la conexión. Las variables de usuario comienzan con "@" (arroba) seguido del nombre (sin espacios), dicho nombre puede contener cualquier caracter. Una variable debe ser declarada antes de usarse. Una variable local se declara así: declare @NOMBREVARIABLE TIPO colocando "declare" el nombre de la variable que comienza con el símbolo arroba (@) y el tipo de dato. Ejemplo: declare @nombre varchar(20) Puede declarar varias variables en una misma sentencia: declare @nombre varchar(20), @edad int No existen variables globales en SQL Server. Una variable declarada existe dentro del entorno en que se declara; debemos declarar y emplear la variable en el mismo lote de sentencias, porque si declaramos una variable y luego, en otro bloque de sentencias pretendemos emplearla, dicha variable ya no existe. Por ejemplo, si ejecutamos estas sentencias en diferentes lotes:
declare @variable varchar(10); select @variable; aparece un mensaje indicando que la variable "@variable" debe ser declarada. Debemos tipear: declare @variable varchar(10) select @variable; Disponemos punto y coma solo al final de la última instrucción del lote. Una variable a la cual no se le ha asignado un valor contiene "null". Se le asigna un valor inicial con "set": set @edad=45 Para almacenar un valor en una variable se coloca el signo igual (=) entre la variable y el valor a asignar. Si le asignamos un valor resultado de una consulta, la sintaxis es: select @nombre = autor from libros where titulo='Uno' Podemos ver el contenido de una variable con: select @nombre; Una variable puede tener comodines: declare @patron varchar(30) set @patron='B%' select autor from libros where autor like @patron; La utilidad de las variables consiste en que almacenan valores para utilizarlos en otras consultas. Por ejemplo, queremos saber todos los datos del libro con mayor precio de la tabla "libros" de una librería. Para ello podemos emplear una variable para almacenar el precio más alto: declare @mayorprecio select @mayorprecio:=max(precio) from libros y luego mostrar todos los datos de dicho libro empleando la variable anterior:
select *from libros where precio=@mayorprecio; Es decir, declaramos la variable y guardamos en ella el precio más alto y luego, en otra sentencia, mostramos los datos de todos los libros cuyo precio es igual al valor de la variable. Una variable puede ser definida con cualquier tipo de dato, excepto text, ntext e image; incluso de un tipo de dato definido por el usuario. 113 - Tipos de datos text, ntext y image
Los tipos de datos "ntext", "text" e "image" representan tipos de datos de longitud fija y variable en los que se pueden guardar gran cantidad de información, caracteres unicode y no unicode y datos binarios. "ntext" almacena datos unicode de longitud variable y el máximo es de aproximadamente 1000000000 caracteres, en bytes, el tamaño es el doble de los caracteres ingresados (2 GB). "text" almacena datos binarios no unicode de longitud variable, el máximo es de 2000000000 caracteres aprox. (2 GB). No puede emplearse en parámetros de procedimientos almacenados. "image" es un tipo de dato de longitud variable que puede contener de 0 a 2000000000 bytes (2 GB) aprox. de datos binarios. Se emplea para almacenar gran cantidad de información o gráficos. Se emplean estos tipos de datos para almacenar valores superiores a 8000 caracteres. Ninguno de estos tipos de datos admiten argumento para especificar su longitud, como en el caso de los tipos "char", o "varchar". Como estos tipos de datos tiene gran tamaño, SQL Server los almacena fuera de los registros, en su lugar guarda un puntero (de 16 bytes) que apunta a otro sitio que contiene los datos. Para declarar un campo de alguno de estos tipos de datos, colocamos el nombre del campo seguido del tipo de dato: ... NOMBRECAMPO text .... Otras consideraciones importantes: - No pueden definirse variables de estos tipos de datos. - Los campos de estos tipos de datos no pueden emplearse para índices. - La única restricción que puede aplicar a estos tipos de datos es "default".
- Se pueden asociar valores predeterminados pero no reglas a campos de estos tipos de datos. - No pueden alterarse campos de estos tipos con "alter table". 114 - Tipo de dato text - ntext e image (punteros)
Explicamos anteriormente que como estos tipos de datos tiene gran tamaño, SQL Server almacena los datos fuera de los registros; en el registro guarda un puntero (de 16 bytes) que apunta a otro sitio, que contiene la dirección en la cual se guardan los datos propiamente dichos. La función "textptr" devuelve el valor del puntero a texto que corresponde al campo text, ntext o image; tal valor puede emplearse para manipular los datos de este tipo, con las funciones para leer, escribir y actualizar. Sintaxis: textptr(CAMPO); El campo debe ser tipo text, ntext o image. En el campo de tipo "text" no se almacenan los datos sino la dirección en la cual se encuentran los datos. Podemos ver esa dirección tipeando la siguiente sentencia: select titulo, textptr(sinopsis) from libros; La función "textptr" retorna un puntero a texto (valor binario de 16). Si el campo no tiene texto, retorna un puntero a null; por ello se debe usar la función "textvalid" para confirmar si el puntero a texto existe. Si la consulta retorna más de un registro, "textptr" retorna un puntero a texto del último registro devuelto. La funcion "textvalid" controla si un puntero a texto es válido. Sintaxis: textvalid ('TABLA.CAMPO', PUNTEROATEXTO); Los argumentos son: el nombre de la tabla y campo y el nombre del puntero a texto que se va a controlar. Retorna 1 si el puntero es válido y 0 si no lo es. No se puede emplear "updatetext", "writetext" y "readtext" si el puntero no es válido. La siguiente consulta muestra si los punteros son válidos en cada registro del campo "sinopsis" de la tabla "libros": select titulo, textvalid('libros.sinopsis', textptr(sinopsis)) as 'Puntero valido' from libros;
En el siguiente ejemplo, declaramos una variable de tipo "varbinary" a la cual le asignamos el valor del puntero a texto de un registro y luego vemos si dicho puntero es válido, empleando la variable: declare @puntero varbinary(16) select @puntero = textptr(sinopsis) from libros where titulo= 'Ilusiones' select textvalid('libros.sinopsis', @puntero); Solo disponemos punto y coma al final para que SQL Server ejecute todas las instrucciones en un solo lote y exista la variable @puntero. Si al insertar registros se ingresa un valor "null" en un campo "text", "ntext" o "image" o no se ingresa valor, no se crea un puntero válido. Para crear un puntero a texto válido ejecute un "insert" o "update" con datos que no sean nulos para el campo text, ntext o image. 115 - Tipo de dato text - ntext e image (leer)
La función "readtext" lee valores de un campo text, ntext o image, comenzando desde una posición y leyendo un específico número de bytes. Sintaxis: readtext TABLA.CAMPO PUNTEROATEXTO DESPLAZAMIENTO CANTIDAD; Analicemos la sintaxis: - PUNTEROATEXTO: puntero a texto válido, binary(16). - DESPLAZAMIENTO: número de bytes (para text o image) o caracteres (ntext) que se mueve el puntero antes de comenzar a leer. - CANTIDAD: número de bytes o caracteres a leer desde la posición indicada por DESPLAZAMIENTO. Si es 0, se leen 4KB bytes o hasta el final. Leemos la información almacenada en el campo "sinopsis" de "libros" del registro con código 2, desde la posición 9, 50 caracteres: declare @puntero varbinary(16) select @puntero=textptr(sinopsis) from libros where codigo=2 readtext libros.sinopsis @puntero 9 50;
Si al insertar registros se ingresa un valor "null" en un campo "text", "ntext" o "image" o no se ingresan datos, no se crea un puntero válido y al intentar leer dicho campo ocurre un error, porque la función "readtext" requiere un puntero válido. Para evitarlo podemos chequear el puntero antes de pasárselo a la función de lectura: declare @puntero varbinary(16) select @puntero=textptr(sinopsis) from libros where codigo=1 if (textvalid('libros.sinopsis', @puntero)=1) readtext libros.sinopsis @puntero 9 50 else select 'puntero invalido';
116 - Tipo de dato text - ntext e image (escribir)
La función "writetext" sobreescribe (reemplaza) el texto de un campo "text", "ntext" o "image". No puede emplearse en vistas. Sintaxis: writetext TABLA.CAMPO PUNTEROATEXTO DATO; Luego de "writetext" se coloca el nombre de la tabla y el campo (text, ntext o image) a actualizar. "PUNTEROATEXTO" es el valor que almacena el puntero a texto del dato de tipo "text", "ntext" o "image", tal puntero debe ser válido. "DATO" es el texto que almacena, puede ser una variable o un literal. Este ejemplo coloca el puntero a texto en una variable "@puntero" y luego "writetext" almacena el nuevo texto en el registro apuntado por "@puntero": declare @puntero binary(16) select @puntero = textptr (sinopsis) from libros where codigo=2 writetext libros.sinopsis @puntero 'Este es un nuevo libro acerca de PHP escrito por el profesor Molina que aborda todos los temas necesarios para el aprendizaje desde cero de este lenguaje.'; Recuerde que si al insertar registros se ingresa un valor "null" en un campo "text", "ntext" o "image" o no se ingresan datos, no se crea un puntero válido y al intentar escribir dicho campo ocurre un error, porque la función "writetext" requiere un puntero válido. Para evitarlo podemos chequer el puntero antes de pasárselo a la función de escritura: declare @puntero varbinary(16) select @puntero=textptr(sinopsis) from libros where codigo=1
if (textvalid('libros.sinopsis', @puntero)=1) writetext libros.sinopsis @puntero 'Trata de una gaviota que vuela más alto que las demas.' else select 'puntero invalido, no se actualizó el registro';
117 - Tipo de dato text - ntext e image (actualizar)
Aprendimos que la función "writetext" sobreescribe, reemplaza el contenido completo de un campo de tipo "text", "ntext" o "image". Para actualizar campos de estos tipos también empleamos "updatetext", que permite cambiar una porción del campo (o todo el campo). La sintaxis básica es la siguiente: updatetext TABLA.CAMPO PUNTEROATEXTO DESPLAZAMIENTODELPUNTERO LONGITUDDEBORRADO DATOAINSERTAR; Analizamos la sintaxis: - TABLA.CAMPO: campo y tabla que se va a actualizar. - PUNTEROATEXTO: valor del puntero, retornado por la función "textptr", que apunta al dato text, ntext o image que se quiere actualizar. - DESPLAZAMIENTODELPUNTERO: indica la posición en que inserta el nuevo dato. Especifica la cantidad de bytes (para campos text e image) o caracteres (para campos ntext) que debe moverse el puntero para insertar el dato. Los valores pueden ser: 0 (el nuevo dato se inserta al comienzo), "null" (coloca el puntero al final), un valor mayor a cero y menor o igual a la longitud total del texto (inserta el nuevo dato en la posición indicada) y un valor mayor a la longitud total del campo (genera un mensaje de error). Es importante recordar que cada caracter ntext ocupa 2 bytes. - LONGITUDDEBORRADO: indica la cantidad de bytes (para text e image) o caracteres (para ntext) a borrar comenzando de la posición indicada por el parámetro DESPLAZAMIENTODELPUNTERO. Si colocamos el valor 0 (no se borra ningún dato), "null" (borra todos los datos desde la posición indicada por el parámetro DESPLAZAMIENTODELPUNTERO hasta el final), un valor mayor que cero y menor o igual a la longitud del texto (borra tal cantidad) y un valor inválido, es decir, mayor a la longitud del texto (genera un mensaje de error). Es importante recordar que cada caracter "ntext" ocupa 2 bytes. - DATOAINSERTAR: el dato que va a ser insertado en el campo. Puede ser char, nchar, varchar, nvarchar, binary, varbinary, text, ntext, image, un literal o una variable. Si el dato es un campo text, ntext o image de otra tabla, se debe indicar el nombre de la tabla junto con el campo y el valor del puntero que apunta al tipo de dato text, ntext o image (retornado por la función "textptr"), de esta forma:
TABLA.CAMPO PUNTERO; Tenemos la tabla libros, con un campo de tipo text llamado "sinopsis"; hay un registro cargado con el siguiente texto: "Para aprender PHP a paso." Necesitamos agregar antes de "a paso" el texto "paso " para que el texto completo sea "Para aprender PHP paso a paso", tipeamos: declare @puntero binary(16) select @puntero = textptr(sinopsis) from libros where titulo='Aprenda PHP' updatetext libros.sinopsis @puntero 18 0 'paso '; Entonces, declaramos una variable llamada "@puntero"; guardamos en la variable el valor del puntero, obtenido con la función "textptr(sinopsis)", tal puntero apunta al campo "sinopsis" del libro "Aprenda PHP". Luego actualizamos el campo, colocando el puntero en la posición 18, no borramos ningún byte y colocamos el t exto a agregar; el campo ahora contendrá "Para aprencer PHP paso a paso". Es posible guardar en un campo "text" de una tabla el contenido del campo "text" de otra tabla; para ello debemos utilizar 2 punteros, uno para obtener la dirección del campo que queremos actualizar y otro para obtener la dirección del campo del cual extraemos la información. En el siguiente ejemplo guardamos en una variable el valor del puntero a texto al campo "sinopsis" del libro "Aprenda PHP" de la tabla "libros"; en otra variable guardamos el valor del puntero a texto al campo "sinopsis" del libro con código 1 de la tabla "ofertas"; finalmente actualizamos el registro de "ofertas" con el texto de "libros". declare @puntero1 binary(16) select @puntero1 = textptr(sinopsis) from libros where titulo='Aprenda PHP' declare @puntero2 binary(16) select @puntero2 = textptr(sinopsis) from ofertas where titulo='Aprenda PHP' updatetext ofertas.sinopsis @puntero2 0 null libros.sinopsis @puntero1; Entonces, se emplea "updatetext" para modificar datos de campos de tipo text, ntext e image, pudiendo cambiar una porción del texto. 118 - Tipo de dato text - ntext e image (funciones)
Las siguientes son otras funciones que pueden emplearse con estos tipos de datos: - datalenght(CAMPO): devuelve el número de bytes de un determinado campo. Retorna "null" si el campo es nulo. Ejemplo: select titulo, datalength(sinopsis) as longitud from libros order by titulo; - patindex ('PATRON',CAMPO): retorna el comienzo de la primera ocurrencia de un patrón de la expresión especificada, si el patrón no se encuentra, retorna cero. El patrón es una cadena que puede incluir comodines. Ejemplo: select patindex('%PHP%', sinopsis) from libros; Con este tipo de datos también puede utilizarse "like", pero "like" solamente puede incluirse en la cláusula "where". - substring (TEXTO,INICIO,LONGITUD): devuelve una parte del texto especificado como primer argumento, empezando desde la posición especificada por el segundo argumento y de tantos caracteres de longitud como indica el tercer argumento. Ejemplo: select titulo,substring(sinopsis,1,20) from libros;
119 - Procedimientos almacenados
Vimos que SQL Server ofrece dos alternativas para asegurar la integridad de datos, la integridad: 1) DECLARATIVA, mediante el uso de restricciones (constraints), valores predeterminados (defaults) y reglas (rules) y 2) PROCEDIMENTAL, mediante la implementación de procedimientos almacenados y desencadenadores (triggers). Nos detendremos ahora en procedimientos almacenados. Un procedimiento almacenado es un conjunto de instrucciones a las que se les da un nombre, que se almacena en el servidor. Permiten encapsular tareas repetitivas. SQL Server permite los siguientes tipos de procedimientos almacenados: 1) del sistema: están almacenados en la base de datos "master" y llevan el prefijo "sp_"; permiten recuperar información de las tablas del sistema y pueden ejecutarse en cualquier base de datos.
2) locales: los crea el usuario (próximo tema). 3) temporales: pueden ser locales, cuyos nombres comienzan con un signo numeral (#), o globales, cuyos nombres comienzan con 2 signos numeral (##). Los procedimientos almacenados temporales locales están disponibles en la sesión de un solo usuario y se eliminan automáticamente al finalizar la sesión; los globales están disponibles en las sesiones de todos los usuarios. 4) extendidos: se implementan como bibliotecas de vínculos dinámicos (DLL, Dynamic-Link Libraries), se ejecutan fuera del entorno de SQL Server. Generalmente llevan el prefijo "xp_". No los estudiaremos. Al crear un procedimiento almacenado, las instrucciones que contiene se analizan para verificar si son correctas sintácticamente. Si no se detectan errores, SQL Server guarda el nombre del procedimiento almacenado en la tabla del sistema "sysobjects" y su contenido en la tabla del sistema "syscomments" en la base de datos activa. Si se encuentra algún error, no se crea. Un procedimiento almacenados puede hacer referencia a objetos que no existen al momento de crearlo. Los objetos deben existir cuando se ejecute el procedimiento almacenado. Ventajas: - comparten la lógica de la aplicación con las otras aplicaciones, con lo cual el acceso y las modificaciones de los datos se hacen en un solo sitio. - permiten realizar todas las operaciones que los usuarios necesitan evitando que tengan acceso directo a las tablas. - reducen el tráfico de red; en vez de enviar muchas instrucciones, los usuarios realizan operaciones enviando una única instrucción, lo cual disminuye el número de solicitudes entre el cliente y el servidor. 120 - Procedimientos almacenados (crear - ejecutar)
Los procedimientos almacenados se crean en la base de datos seleccionada, excepto los procedimientos almacenados temporales, que se crean en la base de datos "tempdb". En primer lugar se deben tipear y probar las instrucciones que se incluyen en el procedimiento almacenado, luego, si se obtiene el resultado esperado, se crea el procedimiento. Los procedimientos almacenados pueden hacer referencia a tablas, vistas, a funciones definidas por el usuario, a otros procedimientos almacenados y a tablas temporales. Un procedimiento almacenado pueden incluir cualquier cantidad y tipo de instrucciones, excepto:
create default, create procedure, create rule, create trigger y create view. Se pueden crear otros objetos (por ejemplo índices, tablas), en tal caso deben especificar el nombre del pr opietario; se pueden realizar inserciones, actualizaciones, eliminaciones, etc. Si un procedimiento almacenado crea una tabla temporal, dicha tabla sólo existe dentro del procedimiento y desaparece al finalizar el mismo. Lo mismo sucede con las variables. Hemos empleado varias veces procedimientos almacenados del sistema ("sp_help", "sp_helpconstraint", etc.), ahora aprenderemos a crear nuestros propios procedimientos almacenados. Para crear un procedimiento almacenado empleamos la instrucción "create procedure". La sintaxis básica parcial es: create procedure NOMBREPROCEDIMIENTO as INSTRUCCIONES; Para diferenciar los procedimientos almacenados del sistema de los procedimientos almacenados locales use un prefijo diferente a "sp_" cuando les de el nombre. Con las siguientes instrucciones creamos un procedimiento almacenado llamado "pa_libros_limite_stock" que muestra todos los libros de los cuales hay menos de 10 disponibles: create proc pa_libros_limite_stock as select *from libros where cantidad <=10; Entonces, creamos un procedimiento almacenado colocando "create procedure" (o "create proc", que es la forma abreviada), luego el nombre del procedimiento y seguido de "as" las sentencias que definen el procedimiento. "create procedure" debe ser la primera sentencia de un l ote. Para ejecutar el procedimiento almacenado creado anteriormente tipeamos: exec pa_libros_limite_stock; Entonces, para ejecutar un procedimiento almacenado colocamos "execute" (o "exec") seguido del nombre del procedimiento. Cuando realizamos un ejercicio nuevo, siempre realizamos las mismas tareas: eliminamos la tabla si existe, la creamos y luego ingresamos algunos registros. Podemos crear un procedimiento almacenado que contenga todas estas instrucciones: create procedure pa_crear_libros as if object_id('libros')is not null
drop table libros; create table libros( codigo int identity, titulo varchar(40), autor varchar(30), editorial varchar(20), precio decimal(5,2), primary key(codigo) ); insert into libros values('Uno','Richard Bach','Planeta',15); insert into libros values('Ilusiones','Richard Bach','Planeta',18); insert into libros values('El aleph','Borges','Emece',25); insert into libros values('Aprenda PHP','Mario Molina','Nuevo siglo',45); insert into libros values('Matematica estas ahi','Paenza','Nuevo siglo',12); insert into libros values('Java en 10 minutos','Mario Molina','Paidos',35); Y luego lo ejecutamos cada vez que comenzamos un nuevo ejercicio y así evitamos tipear tantas sentencias: exec pa_crear_libros;
122 - Procedimientos almacenados (parámetros de entrada)
Los procedimientos almacenados pueden recibir y devolver información; para ello se emplean parámetros, de entrada y salida, respectivamente. Veamos los primeros. Los parámetros de entrada posibilitan pasar información a un procedimiento. Para que un procedimiento almacenado admita parámetros de entrada se deben declarar variables como parámetros al crearlo. La sintaxis es: create proc NOMBREPROCEDIMIENTO @NOMBREPARAMETRO TIPO =VALORPORDEFECTO as SENTENCIAS; Los parámetros se definen luego del nombre del procedimiento, comenzando el nombre con un signo arroba (@). Los parámetros son locales al procedimiento, es decir, existen solamente dentro del mismo. Pu eden declararse varios parámetros por procedimiento, se separan por comas. Cuando el procedimiento es ejecutado, deben explicitarse valores para cada uno de los parámetros (en el orden que fueron definidos), a menos que se haya definido un valor por defecto, en tal caso, pueden omitirse. Pueden ser de cualquier tipo de dato (excepto cursor).
Luego de definir un parámetro y su tipo, opcionalmente, se puede especificar un valor por defecto; tal valor es el que asume el procedimiento al ser ejecutado si no recibe parámetros. Si no se coloca valor por defecto, un procedimiento definido con parámetros no puede ejecutarse sin valores para ellos. El valor por defecto puede ser "null" o una constante, también puede incluir comodines si el procedimiento emplea "like". Creamos un procedimiento que recibe el nombre de un autor como parámetro para mostrar todos los libros del autor solicitado: create procedure pa_libros_autor @autor varchar(30) as select titulo, editorial,precio from libros where autor= @autor; El procedimiento se ejecuta colocando "execute" (o "exec") seguido del nombre del procedimiento y un valor para el parámetro: exec pa_libros_autor 'Borges'; Creamos un procedimiento que recibe 2 parámetros, el nombre de un autor y el de una editorial: create procedure pa_libros_autor_editorial @autor varchar(30), @editorial varchar(20) as select titulo, precio from libros where autor= @autor and editorial=@editorial; El procedimiento se ejecuta colocando "execute" (o "exec") seguido del nombre del procedimiento y los valores para los parámetros separados por comas: exec pa_libros_autor_editorial 'Richard Bach','Planeta'; Los valores de un parámetro pueden pasarse al procedimiento mediante el nombre del parámetro o por su posición. La sintaxis anterior ejecuta el procedimiento pasando valores a los parámetros por posición. También podemos emplear la otra sintaxis en la cual pasamos valores a los parámetros por su nombre: exec pa_libros_autor_editorial @editorial='Planeta', @autor='Richard Bach'; Cuando pasamos valores con el nombre del parámetro, el orden en que se colocan puede alterarse.
No podríamos ejecutar el procedimiento anterior sin valores para los parámetros. Si queremos ejecutar un procedimiento que permita omitir los valores para los parámetros debemos, al crear el procedimiento, definir valores por defecto para cada parámetro: create procedure pa_libros_autor_editorial2 @autor varchar(30)='Richard Bach', @editorial varchar(20)='Planeta' as select titulo, autor,editorial,precio from libros where autor= @autor and editorial=@editorial; Podemos ejecutar el procedimiento anterior sin enviarle valores, usará los predeterminados. Si enviamos un solo parámetro a un procedimiento que tiene definido más de un parámetro sin especificar a qué parámetro corresponde (valor por posición), asume que es el primero. Es decir, SQL Server asume que los valores se dan en el orden que fueron definidos, no se puede interrumpir la secuencia. Si queremos especificar solamente el segundo parámetro, debemos emplear la sintaxis de paso de valores a parámetros por nombre: exec pa_libros_autor_editorial2 @editorial='Paidos'; Podemos emplear patrones de búsqueda en la consulta que define el procedimiento almacenado y utilizar comodines como valores por defecto: create proc pa_libros_autor_editorial3 @autor varchar(30) = '%', @editorial varchar(30) = '%' as select titulo,autor,editorial,precio from libros where autor like @autor and editorial like @editorial; La sentencia siguiente ejecuta el procedimiento almacenado "pa_libros_autor_editorial3" enviando un valor por posición, se asume que es el primero. exec pa_libros_autor_editorial3 'P%'; La sentencia siguiente ejecuta el procedimiento almacenado "pa_libros_autor_editorial3" enviando un valor para el segundo parámetro, para el primer parámetro toma el valor por defecto: exec pa_libros_autor_editorial3 @editorial='P%';
También podríamos haber tipeado: exec pa_libros_autor_editorial3 default, 'P%';
121 - Procedimientos almacenados (eliminar)
Los procedimientos almacenados se eliminan con "drop procedure". Sintaxis: drop procedure NOMBREPROCEDIMIENTO; Eliminamos el procedimiento almacenado llamado "pa_libros_autor": drop procedure pa_libros_autor; Si el procedimiento que queremos eliminar no existe, aparece un mensaje de error, para evitarlo, podemos emplear esta sintaxis: if object_id('NOMBREPROCEDIMIENTO') is not null drop procedure NOMBREPROCEDIMIENTO; Eliminamos, si existe, el procedimiento "pa_libros_autor", si no existe, mostramos un mensaje: if object_id('pa_libros_autor') is not null drop procedure pa_libros_autor else select 'No existe el procedimiento "pa_libros_autor"'; "drop procedure" puede abreviarse con "drop proc". Se recomienda ejecutar el procedimiento almacenado del sistema "sp_depends" para ver si algún objeto depende del procedimiento que deseamos eliminar. Podemos eliminar una tabla de la cual dependa un procedimiento, SQL Server lo permite, pero luego, al ejecutar el procedimiento, aparecerá un mensaje de error porque la tabla referenciada no existe. 123 - Procedimientos almacenados (parámetros de salida)
Dijimos que los procedimientos almacenados pueden devolver información; para ello se emplean parámetros de salida. El valor se retorna a quien realizó la llamada con parámetros de salida. Para que un procedimiento almacenado devuelva un valor se debe declarar una variable con la palabra clave "output" al crear el procedimiento:
create procedure NOMBREPROCEDIMIENTO @PARAMETROENTRADA TIPO =VALORPORDEFECTO, @PARAMETROSALIDA TIPO=VALORPORDEFECTO output as SENTENCIAS select @PARAMETROSALIDA=SENTENCIAS; Los parámetros de salida pueden ser de cualquier tipo de datos, excepto text, ntext e image. Creamos un procedimiento almacenado al cual le enviamos 2 números y retorna el promedio: create procedure pa_promedio @n1 decimal(4,2), @n2 decimal(4,2), @resultado decimal(4,2) output as select @resultado=(@n1+@n2)/2; Al ejecutarlo también debe emplearse "output": declare @variable decimal(4,2) execute pa_promedio 5,6, @variable output select @variable; Declaramos una variable para guardar el valor devuelto por el procedimiento; ejecutamos el procedimiento enviándole 2 valores y mostramos el resultado. La instrucción que realiza la llamada al procedimiento debe contener un nombre de variable para almacenar el valor retornado. Creamos un procedimiento almacenado que muestre los títulos, editorial y precio de los libros de un determinado autor (enviado como parámetro de entrada) y nos retorne la suma y el promedio de los precios de todos los libros del autor enviado: create procedure pa_autor_sumaypromedio @autor varchar(30)='%', @suma decimal(6,2) output, @promedio decimal(6,2) output as select titulo,editorial,precio from libros where autor like @autor select @suma=sum(precio) from libros where autor like @autor
select @promedio=avg(precio) from libros where autor like @autor; Ejecutamos el procedimiento y vemos el contenido de las variables en las que almacenamos los parámetros de salida del procedimiento: declare @s decimal(6,2), @p decimal(6,2) execute pa_autor_sumaypromedio 'Richard Bach', @s output, @p output select @s as total, @p as promedio;
124 - Procedimientos almacenados (return)
La instrucción "return" sale de una consulta o procedimiento y todas las instrucciones posteriores no son ejecutadas. Creamos un procedimiento que muestre todos los libros de un autor determinado que se ingresa como parámetro. Si no se ingresa un valor, o se ingresa "null", se muestra un mensaje y se sale del procedimiento: create procedure pa_libros_autor @autor varchar(30)=null as if @autor is null begin select 'Debe indicar un autor' return end; select titulo from libros where autor = @autor; Si al ejecutar el procedimiento enviamos el valor "null" o no pasamos valor, con lo cual toma el valor por defecto "null", se muestra un mensaje y se sale; en caso contrario, ejecuta la consulta luego del "else". "return" puede retornar un valor entero. Un procedimiento puede retornar un valor de estado para indicar si se ha ejecutado correctamente o no. Creamos un procedimiento almacenado que ingresa registros en la tabla "libros". Los parámetros correspondientes al título y autor DEBEN ingresarse con un valor distinto de "null", los demás son opcionales. El procedimiento retorna "1" si la inserción se realiza, es decir, si se ingresan valores para título y autor y "0", en caso que título o autor sean nulos: create procedure pa_libros_ingreso @titulo varchar(40)=null, @autor varchar(30)=null,
@editorial varchar(20)=null, @precio decimal(5,2)=null as if (@titulo is null) or (@autor is null) return 0 else begin insert into libros values (@titulo,@autor,@editorial,@precio) return 1 end; Para ver el resultado, debemos declarar una variable en la cual se almacene el valor devuelto por el procedimiento; luego, ejecutar el procedimiento asignándole el valor devuelto a la variable, finalmente mostramos el contenido de la variable: declare @retorno int exec @retorno=pa_libros_ingreso 'Alicia en el pais...','Lewis Carroll' select 'Ingreso realizado=1' = @retorno exec @retorno=pa_libros_ingreso select 'Ingreso realizado=1' = @retorno; También podríamos emplear un "if" para controlar el valor de la variable de retorno: declare @retorno int; exec @retorno=pa_libros_ingreso 'El gato con botas','Anónimo' if @retorno=1 print 'Registro ingresado' else select 'Registro no ingresado porque faltan datos';
125 - Procedimientos almacenados (información)
Los procedimientos almacenados son objetos, así que para obtener información de ellos pueden usarse los siguientes procedimientos almacenados del sistema y las siguientes tablas: - "sp_help": sin parámetros nos muestra todos los objetos de la base de datos seleccionada, incluidos los procedimientos. En la columna "Object_type" aparece "stored procedure" si es un procedimiento almacenado. Si le enviamos como argumento el nombre de un procedimiento, obtenemos la fecha de creación e i nformación sobre sus parámetros. - "sp_helptext": seguido del nombre de un procedimiento almacenado nos muestra el texto que define el procedimiento, excepto si ha sido encriptado. - "sp_stored_procedures": muestra todos los procedimientos almacenados, los propietarios, etc. Este procedimiento almacenado puede recibir 3 parámetros: @sp_name (nombre, nvarchar, admite c omodines para búsqueda de patrones),
@sp_owner (propietario, nvarchar, admite comodines) y @qualifier (nombre de la base de datos). Por ejemplo, podemos ver todos los procedimientos almacenados creados por nosotros con esta sentencia: sp_stored_procedures @sp_name='pa_%'; - "sp_depends": seguido del nombre de un objeto, nos devuelve 2 resultados: 1) nombre, tipo, campos, etc. de los objetos de los cuales depende el objeto enviado y 2) nombre y tipo de los objetos que dependen del objeto nombrado. Por ejemplo, ejecutamos "sp_depends" seguido del nombre de un procedimiento: sp_depends pa_autor_promedio; aparecen las tablas (y demás objetos) de las cuales depende el procedimiento, es decir, las tablas referenciadas en el mismo. Podemos ejecutar el procedimiento seguido del nombre de una tabla: sp_depends libros; aparecen los procedimientos (y demás objetos) que dependen de ella. - La tabla del sistema "sysobjects": muestra nombre y varios datos de todos los objetos de la base de datos actual. La columna "xtype" indica el tipo de objeto. Si es un procedimiento almacenado, muestra "P". Ejemplo: select *from sysobjects; Si queremos ver todos los procedimientos almacenados creados por nosotros, podemos tipear: select *from sysobjects where xtype='P' and-- tipo procedimiento name like 'pa%';--búsqueda con comodín
126 - Procedimientos almacenados (encriptado)
Dijimos que SQL Server guarda el nombre del procedimiento almacenado en la tabla del sistema "sysobjects" y su contenido en la tabla "syscomments". Si no quiere que los usuarios puedan leer el contenido del procedimiento podemos indicarle a SQL Server que codifique la entrada a la tabla "syscomments" que contiene el texto. Para ello, debemos colocar la opción "with encryption" al crear el procedimiento: create procedure NOMBREPROCEDIMIENTO PARAMETROS with encryption as INSTRUCCIONES;
Esta opción es opcional. Creamos el procedimiento almacenado "pa_libros_autor" con la opción de encriptado: create procedure pa_libros_autor @autor varchar(30)=null with encryption as select *from libros where autor=@autor; Si ejecutamos el procedimiento almacenado del sistema "sp_helptext" para ver su contenido, no aparece. 127 - Procedimientos almacenados (modificar)
Los procedimientos almacenados pueden modificarse, por necesidad de los usuarios o por cambios en la estructura de las tablas que referencia. Un procedimiento almacenado existente puede modificarse con "alter procedure". Sintaxis: alter procedure NOMBREPROCEDIMIENTO @PARAMETRO TIPO = VALORPREDETERMINADO as SENTENCIAS; Modificamos el procedimiento almacenado "pa_libros_autor" para que muestre, además del título, la editorial y precio: alter procedure pa_libros_autor @autor varchar(30)=null as if @autor is null begin select 'Debe indicar un autor' return end else select titulo,editorial,precio from libros where autor = @autor; Si quiere modificar un procedimiento que se creó con la opción "with encryption" y quiere conservarla, debe incluirla al alterarlo. 128 - Procedimientos almacenados (insertar)
Podemos ingresar datos en una tabla con el resultado devuelto por un procedimiento almacenado. La instrucción siguiente crea el procedimiento "pa_ofertas", que ingresa libros en la tabla "ofertas": create proc pa_ofertas as select titulo,autor,editorial,precio from libros where precio<50; La siguiente instrucción ingresa en la tabla "ofertas" el resultado del procedimiento "pa_ofertas": insert into ofertas exec pa_ofertas; Las tablas deben existir y los tipos de datos deben coincidir. 129 - Procedimientos almacenados (anidados)
Un procedimiento almacenado puede llamar a otro procedimiento almacenado. El procedimiento que es invocado por otro debe existir cuando creamos el procedimiento que lo llama. Es decir, si un procedimiento A llama a otro procedimiento B, B debe existir al crear A. Los procedimientos almacenados pueden anidarse hasta 32 niveles. Creamos un procedimiento almacenado que reciba 2 números enteros y nos retorne el producto de los mismos: create procedure pa_multiplicar @numero1 int, @numero2 int, @producto int output as select @producto=@numero1*@numero2; Creamos otro procedimiento que nos retorne el factorial de un número, tal procedimiento llamará al procedimiento "pa_multiplicar": create procedure pa_factorial @numero int as declare @resultado int declare @num int
set @resultado=1 set @num=@numero while (@num>1) begin exec pa_multiplicar @resultado,@num, @resultado output set @num=@num-1 end select rtrim(convert(char,@numero))+'!='+convert(char,@resultado);
Cuando un procedimiento (A) llama a otro (B), el segundo (B) tiene acceso a todos los objetos que cree el primero (A). 130 - Procedimientos Almacenados (recompilar)
La compilación es un proceso que consiste en analizar el procedimiento almacenado y crear un plan de ejecución. Se realiza la primera vez que se ejecuta un procedimiento almacenado o si el procedimiento almacenado se debe volver a compilar (recompilación). SQL Server recompila automáticamente un procedimiento almacenado si se realiza algún cambio en la estructura de una tabla (o vista) referenciada en el procedimiento (alter table y alter view) y cuando se modifican las claves (insert o delete) de una tabla referenciada. Un procedimiento almacenado puede recompilarse explícitamente. En general se recomienda no hacerlo excepto si se agrega un índice a una tabla referenciada por el procedimiento o si los datos han variado mucho desde la última compilación. SQL Server ofrece tres métodos para recompilar explícitamente un procedimiento almacenado: 1) Se puede indicar, al crear el procedimiento, que SQL Server no guarde en la caché un plan de ejecución para el procedimiento sino que lo compile cada vez que se ejecute. En este caso la sintaxis es la siguiente: create procedure NOMBREPROCEDIMIENTO PARAMETROS with recompile as SENTENCIAS; 2) Podemos especificar "with recompile" al momento de ejecutarlo: exec NOMBREPROCEDIMIENTO with recompile; 3) Podemos ejecutar el procedimiento almacenado del sistema "sp_recompile". Este procedimiento vuelve a compilar el procedimiento almacenado (o desencadenador) que se especifica. La sintaxis es:
exec sp_recompile NOMBREOBJETO; El parámetro enviado debe ser el nombre de un procedimiento, de un desencadenador, de una tabla o de una vista. Si es el nombre de una tabla o vista, todos los procedimientos almacenados que usan tal tabla (o vista) se vuelven a compilar. 131 - Procedimientos Almacenados (con join)
Hasta ahora, hemos creado procedimientos que incluyen una sola tabla o pocas instrucciones para aprender la sintaxis, pero la funcionalidad de un procedimiento consiste básicamente en que contengan muchas instrucciones o instrucciones complejas y así evitar tipear repetidamente dichas instrucciones; además si no queremos que el usuario conozca la estructura de las tablas involucradas, los procedimientos permiten el acceso a ellas. Podemos crear procedimientos que incluyan combinaciones (join), subconsultas, varias instrucciones y llamadas a otros procedimientos. Podemos crear todos los procedimientos que necesitemos para que realicen todas las operaciones y consultas. 132 - Tablas temporales
Las tablas temporales son visibles solamente en la sesión actual. Las tablas temporales se eliminan automáticamente al acabar la sesión o la función o procedimiento almacenado en el cual fueron definidas. Se pueden eliminar con "drop table". Pueden ser locales (son visibles sólo en la sesión actual) o globales (visibles por todas las sesiones). Para crear tablas temporales locales se emplea la misma sintaxis que para crear cualquier tabla, excepto que se coloca un signo numeral (#) precediendo el nombre. create table #NOMBRE( CAMPO DEFINICION, ... ); Para referenciarla en otras consultas, se debe incluir el numeral(#), que es parte del nombre. Por ejemplo: insert into #libros default values; select *from #libros; Una tabla temporal no puede tener una restricción "foreign key" ni ser indexada, tampoco puede ser referenciada por una vista.
Para crear tablas temporales globales se emplea la misma sintaxis que para crear cualquier tabla, excepto que se coloca un signo numeral doble (##) precediendo el nombre. create table ##NOMBRE( CAMPO DEFINICION, ... ); El (o los) numerales son parte del nombre. Así que puede crearse una tabla permanente llamada "libros", otra tabla temporal local llamada "#libros" y una tercera tabla temporal global denominada "##libros". No podemos consultar la tabla "sysobjects" para ver las tablas temporales, debemos tipear: select *from tempdb..sysobjects;
133 - Funciones
SQL Server ofrece varios tipos de funciones para realizar distintas operaciones. Hemos visto y empleado varias de ellas. Se pueden emplear las funciones del sistema en cualquier lugar en el que se permita una expresión en una sentencia "select". Las funciones pueden clasificarse en: - deterministicas: siempre retornan el mismo resultado si se las invoca enviando el mismo valor de entrada. Todas las funciones de agregado y string son deterministicas, excepto "charindex" y "patindex". - no deterministicas: pueden retornar distintos resultados cada vez que se invocan con el mismo valor de entrada. Las siguientes son algunas de las funciones no deterministicas: getdate, datename, textptr, textvalid, rand. Todas las funciones de configuración, cursor, meta data, seguridad y estadísticas del sistema son no deterministicas. SQL Server provee muchas funciones y además permite que el usuario pueda definir sus propias funciones. Sabemos que una función es un conjunto de sentencias que operan como una unidad lógica, una rutina que retorna un valor. Una función tiene un nombre, acepta parámetros de entrada y retorna un valor escalar o una tabla. Los parámetros de entrada pueden ser de cualquier tipo, excepto timestamp, cursor y table. Las funciones definidas por el usuario no permiten parámetros de salida.
No todas las sentencias SQL son válidas dentro de una función. NO es posible emplear en ellas funciones no determinadas (como getdate()) ni sentencias de modificación o actualización de tablas o vistas. Si podemos emplear sentencias de asignación, de control de flujo (if), de modificación y eliminación de variables locales. SQL Server admite 3 tipos de funciones definidas por el usuario clasificadas según el valor retornado: 1) escalares: retornan un valor escalar; 2) de tabla de varias instrucciones (retornan una tabla) y 3) de tabla en línea (retornan una tabla). Las funciones definidas por el usuario se crean con la instrucción "create function" y se eliminan con "drop function". 134 - Funciones (drop)
Las funciones definidas por el usuario se eliminan con la instrucción "drop function": Sintaxis: drop function NOMBREPPROPIETARIO.NOMBREFUNCION; Se coloca el nombre del propietario seguido del nombre de la función. Si la función que se intenta eliminar no existe, aparece un mensaje indicándolo, para evitarlo, podemos verificar su existencia antes de solicitar su eliminación (como con cualquier otro objeto): if object_id('NOMBREPROPIETARIO.NOMBREFUNCION') is not null drop function NOMBREPROPIETARIO.NOMBREFUNCION; Eliminamos, si existe, la función denominada "f_fechacadena": if object_id('dbo.f_fechacadena') is not null drop function dbo.f_fechacadena;
135 - Funciones escalares (crear y llamar)
Una función escalar retorna un único valor. Como todas las funciones, se crean con la instrucción "create function". La sintaxis básica es: create function NOMBRE (@PARAMETRO TIPO=VALORPORDEFECTO)
returns TIPO begin INSTRUCCIONES return VALOR end; Luego del nombre se colocan (opcionalmente) los parámetros de entrada con su t ipo. La cláusula "returns" indica el tipo de dato retornado. El cuerpo de la función, se define en un bloque "begin...end" que contiene las instrucciones que retornan el valor. El tipo del valor retornado puede ser de cualquier tipo, excepto text, ntext, image, cursor o timestamp. Creamos una simple función denominada "f_promedio" que recibe 2 valores y retorna el promedio: create function f_promedio (@valor1 decimal(4,2), @valor2 decimal(4,2) ) returns decimal (6,2) as begin declare @resultado decimal(6,2) set @resultado=(@valor1+@valor2)/2 return @resultado end; Entonces, luego de "create function" y el nombre de la función, se deben especificar los parámetros de entrada con sus tipos de datos (entre paréntesis), el tipo de dato que retorna luego de "returns", luego de "as" comienza el bloque "begin...end" dentro del cual se encuentran las instrucciones de procesamiento y el valor retornado luego de "return". En el ejemplo anterior se declara una variable local a la función (desaparece al salir de la función) que calcula el resultado que se retornará. Al hacer referencia a una función escalar, se debe especificar el propietario y el nombre de la función: select dbo.f_promedio(5.5,8.5); Cuando llamamos a funciones que tienen definidos parámetros de entrada DEBEMOS suministrar SIEMPRE un valor para él. Si llamamos a la función anterior sin enviarle los valores para los parámetros: select dbo.f_promedio(); SQL Server muestra un mensaje de error indicando que necesita argumentos.
Creamos una función a la cual le enviamos una fecha y nos retorna el nombre del mes en español: create function f_nombreMes (@fecha datetime='2007/01/01') returns varchar(10) as begin declare @nombre varchar(10) set @nombre= case datename(month,@fecha) when 'January' then 'Enero' when 'February' then 'Febrero' when 'March' then 'Marzo' when 'April' then 'Abril' when 'May' then 'Mayo' when 'June' then 'Junio' when 'July' then 'Julio' when 'August' then 'Agosto' when 'September' then 'Setiembre' when 'October' then 'Octubre' when 'November' then 'Noviembre' when 'December' then 'Diciembre' end--case return @nombre end; Analicemos: luego de "create function" y el nombre de la función, especificamos los parámetros de entrada con sus tipos de datos (entre paréntesis). El parámetro de entrada tiene definido un valor por defecto. Luego de los parámetros de entrada se indica el tipo de dato que retorna luego de "returns"; luego de "as" comienza el bloque "begin...end" dentro del cual se encuentran las instrucciones de procesamiento y el valor retornado luego de "return". Las funciones que retornan un valor escalar pueden emplearse en cualquier consulta donde se coloca un campo. Recuerde que al invocar una función escalar, se debe especificar el propietario y el nombre de la función: select nombre, dbo.f_nombreMes(fechaingreso) as 'mes de ingreso' from empleados; No olvide que cuando invocamos funciones que tienen definidos parámetros de entrada DEBEMOS suministrar SIEMPRE un valor para él.
Podemos colocar un valor por defecto al parámetro, pero al invocar la función, para que tome el valor por defecto DEBEMOS especificar "default". Por ejemplo, si llamamos a la función anterior sin enviarle un valor: select dbo.f_nombreMes(); SQL Server muestra un mensaje de error indicando que necesita argumento. Para que tome el valor por defecto debemos enviar "default" como argumento: select dbo.f_nombreMes(default); La instrucción "create function" debe ser la primera sentencia de un lote. 136 - Funciones de tabla de varias instrucciones
Hemos visto el primer tipo de funciones definidas por el usuario, que retornan un valor escalar. Ahora veremos las funciones con varias instrucciones que retornan una tabla. Las funciones que retornan una tabla pueden emplearse en lugar de un "from" de una consulta. Este tipo de función es similar a un procedimiento almacenado; la diferencia es que la tabla retornada por la función puede ser referenciada en el "from" de una consulta, pero el resultado de un procedimiento almacenado no. También es similar a una vista; pero en las vistas solamente podemos emplear "select", mientras que en funciones definidas por el usuario podemos incluir sentencias como "if", llamadas a funciones, procedimientos, etc. Sintaxis: create function NOMBREFUNCION (@PARAMETRO TIPO) returns @NOMBRETABLARETORNO table-- nombre de la tabla --formato de la tabla (CAMPO1 TIPO, CAMPO2 TIPO, CAMPO3 TIPO ) as begin insert @NOMBRETABLARETORNO select CAMPOS from TABLA where campo OPERADOR @PARAMETRO RETURN
end Como cualquier otra función, se crea con "create function" seguida del nombre de la función; luego (opcionalmente) los parámetros de entrada con su tipo de dato. La cláusula "returns" define un nombre de variable local para la tabla que r etornará, el tipo de datos a retornar (que es "table") y el formato de la misma (campos y tipos). El cuerpo de la función se define también en un bloque "begin... end", el cual contiene las instrucciones que insertan filas en la variable (tabla que será retornada) definida en "returns". "return" indica que las filas insertadas en la variable son retornadas; no puede ser un argumento. El siguiente ejemplo crea una función denominada "f_ofertas" que recibe un parámetro. La función retorna una tabla con el codigo, título, autor y precio de todos los libros cuyo precio sea inferior al parámetro: create function f_ofertas (@minimo decimal(6,2)) returns @ofertas table-- nombre de la tabla --formato de la tabla (codigo int, titulo varchar(40), autor varchar(30), precio decimal(6,2) ) as begin insert @ofertas select codigo,titulo,autor,precio from libros where precio < @minimo return end; Las funciones que retornan una tabla pueden llamarse sin especificar propietario: select *from f_ofertas(30); select *from dbo.f_ofertas(30); Dijimos que este tipo de función puede ser referenciada en el "from" de una consulta; la siguiente consulta realiza un join entre la tabla "libros" y la tabla retornada por la función "f_ofertas": select *from libros as l join dbo.f_ofertas(25) as o on l.codigo=o.codigo;
Se puede llamar a la función como si fuese una tabla o vista listando algunos campos: select titulo,precio from dbo.f_ofertas(40);
138 - Funciones (modificar)
Las funciones de SQL Server no pueden ser modificadas, las funciones definidas por el usuario si. Las funciones definidas por el usuario pueden modificarse con la instrucción "alter function". Sintaxis general: alter function PROPIETARIO.NOMBREFUNCION NUEVADEFINICION; Sintaxis para modificar funciones escalares: alter funtion PROPIETARIO.NOMBREFUNCION (@PARAMETRO TIPO=VALORPORDEFECTO) returns TIPO as begin CUERPO return EXPRESIONESCALAR end Sintaxis para modificar una función de varias instrucciones que retorna una tabla: alter function NOMBREFUNCION (@PARAMETRO TIPO=VALORPORDEFECTO) returns @VARIABLE table (DEFINICION DE LA TABLA A RETORNAR) as begin CUERPO DE LA FUNCION return end Sintaxis para modificar una función con valores de tabla en línea alter function NOMBREFUNCION (@PARAMETRO TIPO)
returns TABLE as return (SENTENCIAS SELECT) Veamos un ejemplo. Creamos una función que retorna una tabla en línea: create function f_libros (@autor varchar(30)='Borges') returns table as return ( select titulo,editorial from libros where autor like '%'+@autor+'%' ); La modificamos agregando otro campo en el "select": alter table f_libros (@autor varchar(30)='Borges') returns table as return ( select codigo,titulo,editorial from libros where autor like '%'+@autor+'%' );
138 - Funciones (modificar)
Las funciones de SQL Server no pueden ser modificadas, las funciones definidas por el usuario si. Las funciones definidas por el usuario pueden modificarse con la instrucción "alter function". Sintaxis general: alter function PROPIETARIO.NOMBREFUNCION NUEVADEFINICION; Sintaxis para modificar funciones escalares: alter funtion PROPIETARIO.NOMBREFUNCION
(@PARAMETRO TIPO=VALORPORDEFECTO) returns TIPO as begin CUERPO return EXPRESIONESCALAR end Sintaxis para modificar una función de varias instrucciones que retorna una tabla: alter function NOMBREFUNCION (@PARAMETRO TIPO=VALORPORDEFECTO) returns @VARIABLE table (DEFINICION DE LA TABLA A RETORNAR) as begin CUERPO DE LA FUNCION return end Sintaxis para modificar una función con valores de tabla en línea alter function NOMBREFUNCION (@PARAMETRO TIPO) returns TABLE as return (SENTENCIAS SELECT) Veamos un ejemplo. Creamos una función que retorna una tabla en línea: create function f_libros (@autor varchar(30)='Borges') returns table as return ( select titulo,editorial from libros where autor like '%'+@autor+'%' ); La modificamos agregando otro campo en el "select": alter table f_libros (@autor varchar(30)='Borges') returns table
as return ( select codigo,titulo,editorial from libros where autor like '%'+@autor+'%' );
139 - Funciones (encriptado)
Las funciones definidas por el usuario pueden encriptarse, para evitar que sean leídas con "sp_helptext". Para ello debemos agregar al crearlas la opción "with encryption" antes de "as". En funciones escalares: create function NOMBREFUNCION (@PARAMETRO TIPO) returns TIPO with encryption as begin CUERPO return EXPRESION end En funciones de tabla de varias sentencias se coloca luego del formato de la tabla a retornar: create function NOMBREFUNCION (@PARAMETRO TIPO) returns @NOMBRETABLARETORNO table-- nombre de la tabla --formato de la tabla (CAMPO1 TIPO, CAMPO2 TIPO, CAMPO3 TIPO ) with encryption as begin insert @NOMBRETABLARETORNO select CAMPOS from TABLA where campo OPERADOR @PARAMETRO RETURN
end En funciones con valores de tabla en línea: create function NOMBREFUNCION (@PARAMETRO TIPO=VALORPORDEFECTO) returns table with encryption as return (SELECT); Veamos un ejemplo: create function f_libros (@autor varchar(30)='Borges') returns table with encryption as return ( select titulo,editorial from libros where autor like '%'+@autor+'%' ); Si ejecutamos el procedimiento almacenado del sistema "sp_helptext" seguido del nombre de la función creada anteriormente, SQL Server mostrará un mensaje indicando que tal función está encriptada. 140 - Funciones (información)
Las funciones son objetos, así que para obtener información de ellos pueden usarse los siguientes procedimientos almacenados del sistema y las siguientes tablas: - "sp_help": sin parámetros nos muestra todos los objetos de la base de datos seleccionada, incluidas las funciones definidas por el usuario. En la columna "Object_type" aparece "scalar function" si es una función escalar, "table function" si es una función de tabla de varias sentencias y "inline function" si es una función de tabla en línea. Si le enviamos como argumento el nombre de una función definida por el usuario, obtenemos el propietario, el tipo de función y la fecha de creación; si es una función de tabla, los campos de la tabla retornada. - "sp_helptext": seguido del nombre de una función definida por el usuario nos muestra el texto que define la función, excepto si ha sido encriptado. - "sp_stored_procedures": muestra todos los procedimientos almacenados y funciones definidas por el usuario.
- "sp_depends": seguido del nombre de un objeto, nos devuelve 2 resultados: 1) nombre, tipo, campos, etc. de los objetos de los cuales depende el objeto enviado (referenciados por el objeto) y 2) nombre y tipo de los objetos que dependen del objeto nombrado (que lo referencian). Por ejemplo, ejecutamos "sp_depends" seguido del nombre de una función definida por el usuario: sp_depends pa_libroslistado; aparecen las tablas (y demás objetos) de las cuales depende el procedimiento, es decir, las tablas (y campos) referenciadas en la misma. No aparecen objetos que dependan de la función porque no existe ningún objeto que la referencie. Podemos ejecutar el procedimiento seguido del nombre de una tabla: sp_depends libros; aparecen las funciones (y demás objetos) que dependen de ella (que la referencian). No aparecen objetos de los cuales depende porque la tabla no los tiene. - La tabla del sistema "sysobjects": muestra nombre y varios datos de todos los objetos de la base de datos actual. La columna "xtype" indica el tipo de objeto. Si es una función definida por el usuario escalar, muestra "FN", si es una función de tabla de varias sentencias, muestra "TF" y si es una función de tabla en linea muestra "IF". Si queremos ver el nombre, tipo y fecha de creación de todas las funciones definidas por el usuario, podemos tipear: select name,xtype as tipo,crdate as fecha from sysobjects where xtype in ('FN','TF','IF');
141 - Disparadores (triggers)
Un "trigger" (disparador o desencadenador) es un tipo de procedimiento almacenado que se ejecuta cuando se intenta modificar los datos de una tabla (o vista). Se definen para una tabla (o vista) específica. Se crean para conservar la integridad referencial y la coherencia entre los datos entre distintas tablas. Si se intenta modificar (agregar, actualizar o eliminar) datos de una tabla en la que se definió un disparador para alguna de estas acciones (inserción, actualización y eliminación), el disparador se ejecuta (se dispara) en forma automática. Un trigger se asocia a un evento (inserción, actualización o borrado) sobre una tabla. La diferencia con los procedimientos almacenados del sistema es que los triggers:
- no pueden ser invocados directamente; al intentar modificar los datos de una tabla para la que se ha definido un disparador, el disparador se ejecuta automáticamente. - no reciben y retornan parámetros. - son apropiados para mantener la integridad de los datos, no para obtener resultados de consultas. Los disparadores, a diferencia de las restricciones "check", pueden hacer referencia a campos de otras tablas. Por ejemplo, puede crearse un trigger de inserción en la tabla "ventas" que compruebe el campo "stock" de un artículo en la tabla "articulos"; el disparador controlaría que, cuando el valor de "stock" sea menor a la cantidad que se intenta vender, la inserción del nuevo registro en "ventas" no se realice. Los disparadores se ejecutan DESPUES de la ejecución de una instrucción "insert", "update" o "delete" en la tabla en la que fueron definidos. Las restricciones se comprueban ANTES de la ejecución de una instrucción "insert", "update" o "delete". Por lo tanto, las restricciones se comprueban primero, si se infringe alguna restricción, el desencadenador no llega a ejecutarse. Los triggers se crean con la instrucción "create trigger". Esta instrucción especifica la tabla en la que se define el disparador, los eventos para los que se ejecuta y las instrucciones que contiene. Sintaxis básica: create triggre NOMBREDISPARADOR on NOMBRETABLA for EVENTO- insert, update o delete as SENTENCIAS Analizamos la sintaxis: - "create trigger" junto al nombre del disparador. - "on" seguido del nombre de la tabla o vista para la cual se establece el trigger. - luego de "for", se indica la acción (evento, el tipo de modificación) sobre la tabla o vista que activará el trigger. Puede ser "insert", "update" o "delete". Debe colocarse al menos UNA acción, si se coloca más de una, deben separarse con comas. - luego de "as" viene el cuerpo del trigger, se especifican las condiciones y acciones del disparador; es decir, las condiciones que determinan cuando un intento de inserción, actualización o borrado provoca las acciones que el trigger realizará. Consideraciones generales: - "create trigger" debe ser la primera sentencia de un bloque y sólo se puede aplicar a una tabla.
- un disparador se crea solamente en la base de datos actual pero puede hacer referencia a objetos de otra base de datos. - Las siguientes instrucciones no están permitidas en un desencadenador: create database, alter database, drop database, load database, restore database, load log, reconfigure, restore log, disk init, disk resize. - Se pueden crear varios triggers para cada evento, es decir, para cada tipo de modificación (inserción, actualización o borrado) para una misma tabla. Por ejemplo, se puede crear un "insert trigger" para una tabla que ya tiene otro "insert trigger". A continuación veremos la creación de un disparador para el suceso de inserción: "insert triger". 142 - Disparador de inserción (insert trigger)
Podemos crear un disparador para que se ejecute siempre que una instrucción "insert" ingrese datos en una tabla. Sintaxis básica: create triggre NOMBREDISPARADOR on NOMBRETABLA for insert as SENTENCIAS Analizamos la sintaxis: "create trigger" junto al nombre del disparador; "on" seguido del nombre de la tabla para la cual se establece el trigger. Luego de "for" se coloca el evento (en este caso "insert"), lo que indica que las inserciones sobre la tabla activarán el trigger. Luego de "as" se especifican las condiciones y acciones, es decir, las condiciones que determinan cuando un intento de inserción provoca las acciones que el trigger realizará. Creamos un trigger sobre la tabla "ventas" para el evento se inserción. Cada vez que se realiza un "insert" sobre "ventas", el disparador se ejecuta. El disparador controla que la cantidad que se intenta vender sea menor o igual al stock del libro y actualiza el campo "stock" de "libros", restando al valor anterior la cantidad vendida: create trigger DIS_ventas_insertar on ventas for insert as declare @stock int
select @stock= stock from libros join inserted on inserted.codigolibro=libros.codigo where libros.codigo=inserted.codigolibro if (@stock>=(select cantidad from inserted)) update libros set stock=stock-inserted.cantidad from libros join inserted on inserted.codigolibro=libros.codigo where codigo=inserted.codigolibro else begin raiserror ('Hay menos libros en stock de los solicitados para la venta', 16, 1) rollback transaction end Entonces, creamos el disparador ("create trigger") dándole un nombre ("DI_ventas_insertar") sobre ("on") una tabla específica ("ventas") para ("for") el suceso de inserción ("insert"). Luego se "as" colocamos las sentencias, las acciones que el trigger realizará cuando se ingrese un registro en "ventas" (en este caso, controlar que haya stock y disminuir el stock de "libros"). Cuando se activa un disparador "insert", los registros se agregan a la tabla del disparador y a una tabla denominada "inserted". La tabla "inserted" es una tabla virtual que contiene una copia de los registros insertados; tiene una estructura similar a la tabla en que se define el disparador, es decir, la tabla en que se intenta la acción. La tabla "inserted" guarda los valores nuevos de los registros. Dentro del trigger se puede acceder a esta tabla virtual "inserted" que contiene todos los registros insertados, es lo que hicimos en el disparador creado anteriormente, lo que solicitamos es que se le reste al "stock" de "libros", la cantidad ingresada en el nuevo registro de "ventas", valor que recuperamos de la tabla "inserted". "rollback transaction" es la sentencia que deshace la transacción, es decir, borra todas las modificaciones que se produjeron en la última transacción restableciendo todo al estado anterior. "raiserror" muestra un mensaje de error personalizado. Para identificar fácilmente los disparadores de otros objetos se recomienda usar un prefijo y darles el nombre de la tabla para la cual se crean junto al tipo de acción. La instrucción "writetext" no activa un disparador. 143 - Disparador de borrado (delete trigger)
Podemos crear un disparador para que se ejecute siempre que una instrucción "delete" elimine datos en una tabla.
Sintaxis básica: create triggre NOMBREDISPARADOR on NOMBRETABLA for delete as SENTENCIAS Analizamos la sintaxis: "create trigger" junto al nombre del disparador; "on" seguido del nombre de la tabla para la cual se establece el trigger. Luego de "for" se coloca el evento (en este caso "delete"), lo que indica que las eliminaciones sobre la tabla activarán el trigger. Luego de "as" se especifican las condiciones que determinan cuando un intento de eliminación causa las acciones que el trigger realizará. El disparador del siguiente ejemplo se crea para la tabla "ventas", para que cada vez que se elimine un registro de "ventas", se actualice el campo "stock" de la tabla "libros" (por ejemplo, si el comprador devuelve los libros comprados): create trigger DIS_ventas_borrar on ventas for delete as update libros set stock= libros.stock+deleted.cantidad from libros join deleted on deleted.codigolibro=libros.codigo; Entonces, creamos el disparador ("create trigger") dándole un nombre ("DI_ventas_borrar") sobre ("on") una t abla específica ("ventas") para ("for") el evento de borrado ("delete"). Luego de "as" colocamos las sentencias, las acciones que el trigger realizará cuando se elimine un registro en "ventas" (en este caso, aumentar el stock de "libros"). Cuando se activa un disparador "delete", los registros eliminados en la tabla del disparador se agregan a una tabla llamada "deleted". La tabla "deleted" es una tabla virtual que conserva una copia de los registros eliminados; tiene una estructura similar a la tabla en que se define el disparador, es decir, la tabla en que se intenta la acción. Dentro del trigger se puede acceder a esta tabla virtual "deleted". El siguiente disparador se crea para controlar que no se elimine más de un registro de la tabla "libros". El disparador se activa cada vez que se elimina un registro o varios, controlando la cantidad de registros que se están eliminando; si se está eliminando más de un registro, el disparador retorna un mensaje de error y deshace la transacción: create trigger DIS_libros_borrar
on libros for delete as if (select count(*) from deleted) > 1 begin raiserror('No puede borrar más de un libro',16,1) rollback transaction end; Si se ejecuta un "delete" sobre "libros" que afecte a varios registros, se activa el disparador y evita la transacción. Si se ejecuta el siguiente "delete", que afecta a un solo registro, se activa el disparador y permite la transacción: delete from libros where codigo=5; La sentencia "truncate table" no puede incluirse en un disparador de borrado (delete trigger). 144 - Disparador de actualización (update trigger)
Podemos crear un disparador para que se ejecute siempre que una instrucción "update" actualice los datos de una tabla. Sintaxis básica: create triggre NOMBREDISPARADOR on NOMBRETABLA for update as SENTENCIAS Analizamos la sintaxis: "create trigger" junto al nombre del disparador; "on" seguido del nombre de la tabla para la cual se establece el trigger. Luego de "for" se coloca el evento (en este caso "update"), lo que indica que las actualizaciones sobre la t abla activarán el trigger. Luego de "as" se especifican las condiciones y acciones, es decir, las condiciones que determinan cuando un intento de modificación provoca las acciones que el trigger realizará. El siguiente disparador de actualización se crea para evitar que se modifiquen los datos de la tabla "libros": create trigger DIS_libros_actualizar on libros
for update as raiserror('Los datos de la tabla "libros" no pueden modificarse', 10, 1) rollback transaction Entonces, creamos el disparador ("create trigger") dándole un nombre ("DI_libros_actualizar") sobre una tabla específica ("libros") para ("for") el suceso de actualización ("update"). Luego de "as" colocamos las sentencias, las acciones que el trigger realizará cuando se intente actualizar uno o varios registros en "libros" (en este caso, impedir las modificaciones). Cuando se ejecuta una instrucción "update" en una tabla que tiene definido un disparador, los registros originales (antes de ser actualizados) se mueven a la tabla virtual "deleted" y los registros actualizados (con los nuevos valores) se copian a la tabla virtual "inserted". Dentro del trigger se puede acceder a estas tablas. En el cuerpo de un trigger se puede emplear la función "update(campo)" que recibe un campo y retorna verdadero si el evento involucra actualizaciones (o inserciones) en ese campo; en caso contrario retorna "false". Creamos un disparador que evite que se actualice el campo "precio" de la tabla "libros": create trigger DIS_libros_actualizar_precio on libros for update as if update(precio) begin raiserror('El precio de un libro no puede modificarse.', 10, 1) rollback transaction end; Empleamos "if update()" para que el trigger controle la actualización del campo "precio"; así, cuando el disparador detecte una actualización en tal campo, realizará las acciones apropiadas (mostrar un mensaje y deshacer la actualización); en caso que se actualice otro campo, el disparador se activa, pero permite la transacción. Creamos un disparador de actualización que muestra el valor anterior y nuevo valor de los registros actualizados: create trigger DIS_libros_actualizar2 on libros for update as if (update(titulo) or update(autor) or update(editorial)) and not (update(precio) or update(stock)) begin select d.codigo, (d.titulo+'-'+ d.autor+'-'+d.editorial) as 'registro anterior', (i.titulo+'-'+ i.autor+'-'+i.editorial) as 'registro actualizado' from deleted as d
join inserted as i on d.codigo=i.codigo end else begin raiserror('El precio y stock no pueden modificarse. La actualización no se realizó.', 10, 1) rollback transaction end; Empleamos "if update" para que el trigger controle si la actualización se realiza en ciertos campos permitidos (titulo, autor y editorial) y no en los campos prohibidos (precio y stock)); si se modifican los campos permitidos y ninguno de los no permitidos, mostrará los antiguos y nuevos valores consultando las tablas "deleted" e "inserted", en caso que se actualice un campo no permitido, el disparador muestra un mensaje y deshace la transacción. Note que el disparador no controla los intentos de actualización sobre el campo "codigo", esto es porque tal campo, no puede modificarse porque está definido "identity", si intentamos modificarlo, SQL Server muestra un mensaje de error y el trigger no llega a dispararse. 145 - Disparadores (varios eventos)
Hemos aprendido a crear disparadores para diferentes eventos (insert, update y delete). Dijimos que un disparador puede definirse para más de una acción; en tal caso, deben separarse con comas. Creamos un trigger para evitar que se inscriban socios que deben matrículas y no permitir que se eliminen las inscripciones de socios deudores. El trigger se define para ambos eventos en la misma sentencia de creación. create trigger dis_inscriptos_insert_delete on inscriptos for insert,delete as if exists (select *from inserted join morosos on morosos.documento=inserted.documento) begin raiserror('El socio es moroso, no puede inscribirse en otro curso', 16, 1) rollback transaction end else if exists (select *from deleted join morosos on morosos.documento=deleted.documento) begin raiserror('El socio debe matriculas, no puede borrarse su inscripcion', 16, 1) rollback transaction end
else if (select matricula from inserted)='n' insert into morosos select documento from inserted; El trigger controla: - si se intenta ingresar una inscripción de un socio moroso, se deshace la transacción; - si se intenta eliminar una inscripción de un socio que está en "morosos", se deshace la transacción; - si se ingresa una nueva inscripción y no se paga la matrícula, dicho socio se ingresa a la tabla "morosos". 148 - Disparador (información)
Los triggers (disparadores) son objetos, así que para obtener información de ellos pueden usarse los siguientes procedimientos almacenados del sistema y las siguientes tablas: - "sp_help": sin parámetros nos muestra todos los objetos de la base de datos seleccionada, incluidos los triggers. En la columna "Object_type" aparece "trigger" si es un disparador. Si le enviamos como argumento el nombre de un disparador, obtenemos el propietario, el tipo de objeto y la fecha de creación. - "sp_helptext": seguido del nombre de un disparador nos muestra el texto que define el trigger, excepto si ha sido encriptado. - "sp_depends": retorna 2 resultados: 1) el nombre, tipo, campos, etc. de los objetos de los cuales depende el objeto enviado (referenciados por el objeto) y 2) nombre y tipo de los objetos que dependen del objeto nombrado (que lo referencian). Por ejemplo, ejecutamos "sp_depends" seguido del nombre de un disparador: sp_depends dis_inscriptos_insertar; Aparece una tabla similar a la siguiente: name type updated column ----------------------------------------------------------------dbo.condicionales user table yes codigocurso dbo.condicionales user table yes fecha dbo.inscriptos user table yes numerocurso
dbo.inscriptos dbo.condicionales dbo.cursos dbo.cursos dbo.inscriptos
user table yes fecha user table yes documento user table no numero user table no cantidadmaxima user table yes documento
En la columna "name" nos muestra las tablas (y demás objetos si hubiese) de las cuales depende el trigger, es decir, las tablas referenciadas en el mismo; el tipo de objeto en la columna "type" (en este caso, todas tablas); la columna "update" indica si el objeto es actualizado o no (note que la tabla "cursos" no se actualiza, solamente se consulta); la columna "column" muestra el nombre del campo que se referencia. No aparecen objetos que dependen del trigger porque no existe ningún objeto que lo referencie. También podemos ejecutar el mismo procedimiento seguido del nombre de una tabla: sp_depends inscriptos; aparecen los objetos que dependen de ella (que la referencian). En este ejemplo: 1 solo objeto, su nombre y tipo (trigger). No aparecen objetos de los cuales depende porque la tabla no los tiene. - Para conocer los disparadores que hay en una tabla específica y sus acciones respectivas, podemos ejecutar el procedimiento del sistema "sp_helptrigger" seguido del nombre de la tabla o vista. Por ejemplo: sp_helptrigger inscriptos; Nos muestra la siguiente información: trigger_name trigger_owner isupdate isdelete isinsert isinsteadof -----------------------------------------------------------------------------------------------------------dis_inscriptos_insertar dbo 0 0
isafter
1
El nombre del trigger, su propietario; en las 3 columnas siguientes indica para qué evento se ha definido (un valor 1 indica que está definido para tal evento); las 2 últimas columnas indican el momento de disparo (un valor 1 se interpreta como verdadero y un 0 como falso). En el ejemplo, el disparador "dis_inscriptos_insertar" está definido para el evento de inserción (valor 1 en "isinsert") y es "instead of" (valor 1 en "isinsteadof"). - La tabla del sistema "sysobjects": muestra nombre y varios datos de todos los objetos de la base de datos actual. La columna "xtype" indica el tipo de objeto. Si es un trigger muestra "TR". Si queremos ver el nombre, tipo y fecha de creación de todos los disparadores, podemos tipear: select name,xtype as tipo,crdate as fecha from sysobjects where xtype = 'TR';
0
1
149 - Disparador (modificar)
Los triggers pueden modificarse y eliminarse. Al modificar la definición de un disparador se reemplaza la definición existente del disparador por la nueva definición. La sintaxis general es la siguiente: alter trigger NOMBREDISPARADOR NUEVADEFINICION; Asumiendo que hemos creado un disparador llamado "dis_empleados_borrar" que no permitía eliminar más de 1 registro de la tabla empleados; alteramos el disparador, para que cambia la cantidad de eliminaciones permitidas de 1 a 3: alter trigger dis_empleados_borrar on empleados for delete as if (select count(*) from deleted)>3--antes era 1 begin raiserror('No puede borrar mas de 3 empleados',16, 1) rollback transaction end; Se puede cambiar el evento del disparador. Por ejemplo, si creó un disparador para "insert" y luego se modifica el evento por "update", el disparador se ejecutará cada vez que se actualice la tabla. 150 - disparador (deshabilitar y habilitar)
Se puede deshabilitar o habilitar un disparador específico de una tabla o vista, o todos los disparadores que tenga definidos. Si se deshabilita un disparador, éste sigue existiendo, pero al ejecutar una instrucción "insert", "update" o "delete" en la tabla, no se activa. Sintaxis para deshabilitar o habilitar un disparador: alter table NOMBRETABLA ENABLE | DISABLE trigger NOMBREDISPARADOR;
El siguiente ejemplo deshabilita un trigger: alter table empleados disable trigger dis_empleados_borrar; Se pueden deshabilitar (o habilitar) varios disparadores en una sola sentencia, separando sus nombres con comas. El siguiente ejemplo deshabilitamos dos triggers definidos sobre la tabla empleados: alter table empleados disable trigger dis_empleados_actualizar, dis_empleados_insertar; Sintaxis para habilitar (o deshabilitar) todos los disparadores de una tabla específica: alter table NOMBRETABLA ENABLE | DISABLE TRIGGER all; La siguiente sentencia habilita todos los triggers de la tabla "empleados": alter table empleados enable trigger all;
151 - Disparador (with encryption)
Hasta el momento hemos aprendido que un trigger se crea sobre una tabla (o vista), especificando el momento de ejecución (after o instead of), para un evento (inserción, eliminación o actualización). Podemos encriptar los triggers para evitar que sean leídos con "sp_helptext". Para ello debemos agregar al crearlos la opción "with encryption" luego del nombre de la tabla o vista: create triggre NOMBREDISPARADOR on NOMBRETABLAoVISTA with encryption MOMENTODEDISPARO--after o instead of ACCION-- insert, update, delete as SENTENCIAS El siguiente disparador se crea encriptado: create trigger DIS_empleados_insertar on empleados with encryption
after insert as if (select seccion from inserted)='Gerencia' begin raiserror('No puede ingresar empleados en la sección "Gerencia".', 16, 1) rollback transaction end; Si ejecutamos el procedimiento almacenado del sistema "sp_helptext" seguido del nombre del trigger creado anteriormente, SQL Server mostrará un mensaje indicando que tal disparador ha sido encriptado. 152 - Disparador (condicionales)
Una instrucción "insert", "update" o "delete" que invoque a un disparador puede afectar a varios registros. En tales casos, un trigger rechaza o acepta cada transacción de modificación como una totalidad. Podemos optar por: 1) procesar todos los registros: todos los registros afectados deberán cumplir los criterios del disparador para que se produzca la acción, o 2) permitir acciones condicionales: puede definir un disparador que controle si cada registro afectado cumple con la condición; si algún registro no la cumple, la acción no se produce para tal registro pero si para los demás que si la cumplen. Veamos un ejemplo. Tenemos la tabla "libros". Creamos un disparador de actualización sobre la tabla "libros". Se permite actualizar el stock de varios libros a la vez; pero ningún "stock" debe tener un valor negativo. Entonces, si algún "stock" queda con un valor negativo, no debe cambiar, los demás si: create trigger dis_libros_actualizar on libros after update as if exists (select *from inserted where stock<0) begin update libros set stock=deleted.stock from libros join deleted on deleted.codigo=libros.codigo join inserted on inserted.codigo=libros.codigo where inserted.stock<0; end;
