Curso SQL para Clarion

CLARION + SQL Manual de Aplicaciones Clarion + SQL El origen del manual, es una compilacion d e cursos dictados p or Fernando Cerini / Gustavo Olmedo entre otros en el mundo Clarion Hispano.

[email protected] [email protected]

Contents

3

Tabla de Contenido 0

8

Part I Programando con CLARION + SQL (MS-SQL) 1

Nociones Basicas ................................................................................................................................... 8

2

Microsoft SQL ................................................................................................................................... Server 10

3

Service Manager ..........................................................................................................................................................

10

Administrador .......................................................................................................................................................... Coorporativo

12

DTS - Importación .......................................................................................................................................................... y exportación de datos

13

Gestión de Tablas ..........................................................................................................................................................

14

Introduccion................................................................................................................................... al lenguaje SQL - MSQL-Server 16 Normalización .......................................................................................................................................................... de la base de Datos

16

Primera Forma ......................................................................................................................................................... Normal

19

Segunda ......................................................................................................................................................... Forma Normal

19

Tercera Forma ......................................................................................................................................................... Normal

19

Ejemplo de ......................................................................................................................................................... Normalizacion

20

Integridad de .......................................................................................................................................................... los datos

24

Tipos datos MSSQL ..........................................................................................................................................................

26

Campos Identity .........................................................................................................................................................

28

Lenguaje de Definición .......................................................................................................................................................... de datos (LDD)

30

CREATE .........................................................................................................................................................

30

ALTER

.........................................................................................................................................................

30

DROP

.........................................................................................................................................................

30

TRUNCATE .........................................................................................................................................................

31

Lenguaje de Manipulación .......................................................................................................................................................... de Datos Nivel Básico

31

Recuperar ......................................................................................................................................................... Datos con SELECT

31

FROM

.........................................................................................................................................

33

WHERE y HAVING: ......................................................................................................................................... Filtrar filas

34

ORDER BY

.........................................................................................................................................

36

JOIN: Combinaciones .........................................................................................................................................

36

Utilizar alias de tablas .........................................................................................................................................

44

DISTINCT

.........................................................................................................................................

45

TOP n

.........................................................................................................................................

45

Agregar filas ......................................................................................................................................................... con INSERT

45

Cambiar datos ......................................................................................................................................................... con UPDATE

47

Eliminar filas ......................................................................................................................................................... con DELETE

49

Trabajando con .......................................................................................................................................................... fechas

51

Elementos de.......................................................................................................................................................... sintaxis de Transact-SQL

53

Consultas: Nivel .......................................................................................................................................................... avanzado

58

Usar funciones ......................................................................................................................................................... de agregado en la lista de selección

58

Agrupar filas ......................................................................................................................................................... con GROUP BY

60

Combinar......................................................................................................................................................... resultados con UNION

63

Subconsultas..........................................................................................................................................................

64

Subconsultas ......................................................................................................................................................... con Alias

66

Subconsultas ......................................................................................................................................................... con IN

67

Subconsultas ......................................................................................................................................................... con NOT IN

69

Subconsultas ......................................................................................................................................................... en UPDATE, DELETE e INSERT

69

Subconsultas ......................................................................................................................................................... con operadores de comparación

70

Subconsultas ......................................................................................................................................................... con EXISTS

72

4

Curso SQL + Clarion - www.templatesclarion.com.ar

4

5

Subconsultas ......................................................................................................................................................... con NOT EXISTS

74

Subconsultas ......................................................................................................................................................... usadas en lugar de una expresión

75

Múltiples ......................................................................................................................................................... niveles de anidamiento

75

Procesamiento ......................................................................................................................................................... condicional con CASE

76

Transacciones .......................................................................................................................................................... y control de la concurrencia

77

Bloqueos.........................................................................................................................................................

79

Read Uncommited .........................................................................................................................................................

80

Read Commited .........................................................................................................................................................

81

Repeatable ......................................................................................................................................................... Read

82

Serializable .........................................................................................................................................................

83

Diccionario ................................................................................................................................... de Datos (DCT) 84 Propiedades de .......................................................................................................................................................... Archivos

85

Tipos de Datos .......................................................................................................................................................... Clarion / MS-SQL

87

Usando SQLIdentity .......................................................................................................................................................... en Clarion 6

90

Claves/Indices .......................................................................................................................................................... (Keys)

91

Integridad referencial .......................................................................................................................................................... - Relaciones

92

Creacion Tablas .......................................................................................................................................................... en Motor

93

Utility Template .........................................................................................................................................................

93

Synchronizer .........................................................................................................................................................

94

File Import .........................................................................................................................................................

97

Conexion a una .......................................................................................................................................................... Base de Datos

100

Driver Nativo ......................................................................................................................................................... - SQL Accelerator Drivers

101

ODBC - ......................................................................................................................................................... Open DataBase Conectivity

102

Convertir TPS .......................................................................................................................................................... a SQL

102

"CSTRING" ......................................................................................................................................................... por "STRING"

104

Manejo de ......................................................................................................................................................... Fechas

104

Migrar Datos ......................................................................................................................................................... Existentes

105

Usando SQL ................................................................................................................................... en Aplicaciones (APP) 105 Recomendaciones ..........................................................................................................................................................

106

Ventana de Login .......................................................................................................................................................... Automático

108

Validacion de .......................................................................................................................................................... Datos

108

SQL Embebido .......................................................................................................................................................... en Clarion

109

Prop:SQL

..........................................................................................................................................................

109

Comando BUFFER ..........................................................................................................................................................

111

Prop:SQLFilter ..........................................................................................................................................................

111

Prop:WHERE ..........................................................................................................................................................

112

SetFilter con.......................................................................................................................................................... codigo SQL

113

Filtrando Campos .......................................................................................................................................................... DATE

113

StupidTempTable ..........................................................................................................................................................

114

Codigo Clarion .......................................................................................................................................................... Vs. SQL

116

Manejo de Errores ..........................................................................................................................................................

117

Manejo Set, Next, .......................................................................................................................................................... Previous

117

Clarion y NULLs ..........................................................................................................................................................

118

SQL / VIEWs..........................................................................................................................................................

119

Browses

..........................................................................................................................................................

120

SQL Advanced .......................................................................................................................................................... Tab

122

Process /Reports ..........................................................................................................................................................

125

Transacciones .......................................................................................................................................................... Batch

126

Manejo de Transacciones ..........................................................................................................................................................

126

Stored Procedures ..........................................................................................................................................................

127

Usando TurboSQL ..........................................................................................................................................................

128

Uso del trace..........................................................................................................................................................

128

Cargas de Cabecera .......................................................................................................................................................... - Detalle

131

Contents Creando un .......................................................................................................................................................... Editor de SQL

6

5 132

Links - Material ................................................................................................................................... Recomendado 141 In-Memory Database .......................................................................................................................................................... Driver

143

Ejemplos

143

Indice

..........................................................................................................................................................

147

Parte

I

8

1


Programando con CLARION + SQL (MS-SQL)

Clarion + SQL

Microsoft SQL Server Introduccion al lenguaje SQL - MSQL-Server Diccionario de Datos (DCT) Usando SQL en Aplicaciones (APP) Links - Material Recomendado

1.1

Nociones Basicas Aplicaciones Cliente/Servidor y Base de datos Relacional Un término utilizado con frecuencia en el uso de la base de datos es cliente/servidor. Si Ud. esta usando archivos planos (ej. TPS), ya sea en un servidor o una computadora local, significa que "NO" esta usando cliente/servidor. En el entorno Cliente/servidor, ambas partes poseen cierta inteligencia. Las bases de datos Relacionadas son un ejemplo común del proceso del cliente/del servidor. El Servidor de Bases de datos proporciona destacables ventajas sobre los archivos planos; velocidad, integridad de datos, compatibilidad con otros con productos, facilidad de administración, etc. Base de Datos SQL La mayoría de las aplicaciones Cliente/Servidor actúan como “front-ends” (interfaces) corriendo en una estación de trabajo Cliente y solicitando servicios de datos de un motor de base de datos (Como Oracle, Informix, SyBase, MS-SQL) corriendo en un servidor. La mayoría de estas bases de datos son accedidas utilizando el Lenguaje de Query Consulta Estructurado (Structured Query Languaje o SQL). SQL fue originalmente desarrollado como un lenguaje de consultas para permitir a los usuarios extraer información de una Base de Datos. A lo largo de los años, el SQL fue extendido para permitir programación además del acceso a los datos. SQL se ha convertido en la herramienta estándar de consulta usada para acceder a los motores de bases de datos. Programas SQL son los frecuentemente mas utilizados para el núcleo transaccional de una base de datos. Sin embargo, el SQL no es un lenguaje


9

completo ya que carece de diseño de interfaces, por ende el SQL es “embebido” dentro de un lenguaje con mayor capacidad y generalidad ( como Clarion / C++ ) para crear aplicaciones completas. Los sistemas de gestión de base de datos con soporte SQL más utilizados son, por orden alfabético: ·

DB2

·

Firebird

·

Informix

·

Interbase

·

MySQL

·

Oracle

·

PostgreSQL

·

SQL Server

·

Sybase ASE

SQL (Structured Query Language) El Lenguaje de Consulta Estructurado (SQL) es el Lenguaje para acceder a una base de datos relacional; el mismo fue adoptado como estándar de la industria en 1986. El SQL es un lenguaje de acceso a bases de datos que explota la flexibilidad y potencia de los sistemas relacionales permitiendo gran variedad de operaciones sobre los mismos.

Clarion y SQL La tecnología de los File Drivers de Clarion permite escribir aplicaciones que accedan a bases de datos SQL sin necesidad de escribir ni sola una línea de código SQL. El File Driver de Clarion es el que se comunica con la base de datos generando automáticamente la sentencia SQL que el motor de base de datos requiere a partir de las instrucciones estándares de I/O del lenguaje Clarion. Como estos Drivers SQL están específicamente diseñados para “conversar” con cada base de datos en particular (excepto el ODBC Driver, que por supuesto, es una versión genérica de SQL), el código SQL generado está optimizado para utilizar la sintaxis especifica de ese motor. Esto significa que los usuario Clarion no están obligados a aprender (utilizar SQL) para escribir aplicaciones Cliente/Servidor. El hecho de que un programador Clarion no este obligado a escribir SQL no limita de ninguna manera la capacidad de Clarion para generar programas Cliente/Servidor. Ud. puede embeber su propia sentencia SQL para extender lo que el File Driver hace por usted, o puede tomar el control total sobre la base de datos para realizar cualquier tarea que necesite realizar. Existen varias maneras para embeber sentencias propias de SQL en un programa Clarion, si lo desea. Esto le brinda, al programador Clarion, la flexibilidad de permitir que el File Driver lo haga, o directamente indicar como lo debe hacer.

10


Lenguaje de Definición de datos (DDL) El lenguaje de Definición de datos ( Data Definition Language (DDL)), es el que se encarga de la modificación de la estructura de los objetos de la base de datos. (mas detalle ver Lenguaje de Definición de datos (LDD) ) Lenguaje de Manipulación de Datos (DML) El Lenguaje de Manipulación de Datos ( Data Manipulation Language (DML)), es un lenguaje que nos permite a los usuarios llevar a cabo las tareas de consulta o manipulación de los datos, organizados por el modelo de datos adecuado. Mediante el uso del SQL se recuperaran y manipularan datos en una base de datos relacional. (mas detalle ver Lenguaje de Manipulación de Datos )

1.2

Microsoft SQL Server

· · · ·

1.2.1

Service Manager Enterprise Manager Query Analizer Data Transformation Services (DTS)

Service Manager

SQL Server instala varios servicios en nuestra maquina , el nombre del servicio se puede ver/manejar desde el administrador de servicios de windows: Inicio -> Programas -> Herramientas Administrativas -> Servicios, desde donde se puede parar, arracar y cambiar el usuario que los ejecuta. También podemos utilizar una utilidad que instala SQL Server “El Administrador de Servicios de SQL Server”, este programa se encuentra en la barra de tareas de windows


·

·

·

·

·

11

SQL Server: Es el servicio principal y arranca el SQL Server. Se llama MSSQLSERVER y si hay varias instancias en una misma máquina, el servicio se llama MSSQL$Nombre_de_instancia. SQL Server Agent: Es el Agente de SQL Server. Se utiliza para la programación de tareas, alertas, mail y se llama SQLSERVERAGENT. Similar al anterior si hay multiples instancias el servicio se llama SQLAGENT$Nombre_de_instancia. Coordinador de transacciones distribuidas : DTC, utilizado para tareas entre varios servidores distribuidos. Se llama “Distributed Transaction Coordinator”. Microsoft Seach: Utilizado para la búsqueda de texto completo en las bases de datos e indices de texto, este servicio solo estará si se ha instalado esta utilidad. Se llama “ Microsoft Search”. Asistente del Active Directory.

12

1.2.2


Administrador Coorporativo

Esta herramienta es la base de trabajo del SQL Server 2000. A través de el podemos realizar cualquier tarea relacionada con SQL Server, además podemos administrar no solo nuestro SQL Server sino también servidores SQL Server remotos distribuidos en otras máquinas. El administrador corporativo pertenece a la MMC (Microsoft Management Console) y como tal se puede añadir a las consolas ya existentes. Para ejecutar el administrador corporativo debemos ir a inicio -> programas -> Microsoft SQL Server -> administrador corporativo, una vez ejecutado nos aparece la imagen que visualizamos abajo, a la izquierda nos muestra los datos estructurados en forma de árbol y a la derecha diversas opciones dependiendo de donde estemos navegando por el árbol.


1.2.3

13

DTS - Importación y exportación de datos

Una de las herramientas incorporadas en SQL Server 2000 es el servicios de transformación de datos (DTS – Data Transformation Services). Incluye herramientas y objetos que permiten mover datos entre varios orígenes de datos diferentes o iguales. Que es el DTS? Es una herramienta para mover, copiar, modificar y trabajar con orígenes de datos iguales o diferentes. En este punto vamos a ver la importación y exportación de datos entre dos orígenes de datos . DTS tiene una arquitectura OLE DB por lo que puede copiar y transformar múltiples orí genes de datos, entre ellos: - SQL Server - Oracle - Access, excel, Outlook, archivos de texto - Exchange Server - Orígenes de datos proporcionados por terceros Para ejecutar el asistente de importación/exportación podemos hacerlo de varias formas.

14


Una es desde la consola (cmd) del sistema ejecutando el archivo dtswiz. Otra opción es ir desde el menú Inicio -> programas -> Microsoft SQL Server -> Importación y exportació n de datos y la ultima, por supuesto, desde el administrador corporativo. Se puede ejecutar posicionándonos en una base de datos y con el botón derechos seleccionamos Todas las tareas -> importar datos o exportar datos dependiendo de la tarea que vamos a realizar.

1.2.4

Gestión de Tablas

Para crear tablas SQL Server proporciona varias formas, podemos crear tablas desde el administrador corporativo, o a través de transact-sql con el comando Create Table . Del mismo modo que las creamos las podemos modificar, borrar, renombralas, copiarlas, etc. Las tablas deben tener un nombre como máximo de 128 caracteres y el nombre debe empezar por un carácter alfabético, a excepción de las tablas temporales que se crean con el signo # delante del nombre, para la sesión del usuario actual y con dos ## para las tablas temporales globales, accesibles a todos los usuarios. Para crear una tabla desde el administrador corporativo seleccionamos una base de datos y dentro de estas el nodo tablas, pulsamos con el botón derecho del ratón y seleccionamos “Nueva tabla”:


15

Modificar y/o borrar una tabla: Para realizar estas acciones tenemos que posicionar el rat ón sobre la tabla que queremos borrar o modificar y pulsando con el botón derecho seleccionamos la opción deseada. Las instrucciones de Transact-SQL / Querys para manipular tablas se ejecutan desde el “ Analizador de Consultas” seleccionando la base de datos que se quiere manipular. Para ejecutar el Analizador de Consultas debemos ir a inicio -> programas -> Microsoft SQL Server -> Analizador de Consultas. O tambien desde el Administrador Coorporativo en el menu Herramientas -> Analizador de Consultas.

16

1.3


Introduccion al lenguaje SQL - MSQL-Server Normalización de la base de Datos Integridad de los datos Tipos datos MSSQL Lenguaje de Definición de datos (LDD) Lenguaje de Manipulación de Datos Nivel Básico Trabajando con fechas Elementos de sintaxis de Transact-SQL Consultas: Nivel avanzado Subconsultas Transacciones y control de la concurrencia

1.3.1

Normalización de la base de Datos

Qué es la normalización? La normalización es el proceso mediante el cual se transforman datos complejos a un conjunto de estructuras de datos más pequeñas, que además de ser más simples y más estables, son más fáciles de mantener. También se puede entender la normalización


17

como una serie de reglas que sirven para ayudar a los diseñadores de bases de datos a desarrollar un esquema que minimice los problemas al momento de actualizar y consultar los datos. Cada regla está basada en la que le antecede. Por que normalizar?

La normalización se adoptó porque el viejo estilo de poner todos los datos en un solo lugar, como un archivo o una tabla de la base de datos, era ineficiente y conducía a errores de lógica cuando se trataban de manipular los datos. La normalización también hace las cosas fáciles de entender. Los seres humanos tenemos la tendencia de simplificar las cosas al máximo. Lo hacemos con casi todo, desde los animales hasta con los automóviles. Vemos una imagen de gran tamaño y la hacemos más simple agrupando cosas similares juntas. Las guías que la normalización provee crean el marco de referencia para simplificar una estructura de datos compleja. Otra ventaja de la normalización de base de datos es el consumo de espacio. Una base de datos normalizada ocupa menos espacio en disco que una no normalizada. Hay menos repetición de datos, lo que tiene como consecuencia un mucho menor uso de espacio en disco. El proceso de normalización tiene un nombre y una serie de reglas para cada fase. Esto puede parecer un poco confuso al principio, pero poco a poco se va entendiendo el proceso, así como las razones para hacerlo de esta manera.

Grados de normalización Existen básicamente tres niveles de normalización

Primera Forma Normal (1NF) Segunda Forma Normal (2NF) Tercera Forma Normal (3NF) Cada una de estas formas tiene sus propias reglas. Cuando una base de datos se conforma a un nivel, se considera normalizada a esa forma de normalización. (No siempre es una buena idea tener una base de datos conformada en el nivel más alto de normalización, puede llevar a un nivel de complejidad que pudiera ser evitado si estuviera en un nivel más bajo de normalización). · · ·

Resumen

En la tabla siguiente se describe brevemente en que consiste cada una de las reglas, y posteriormente se explican con más detalle. ·

Primera Forma Normal(1FN): Incluye la eliminación de todos los grupos repetidos.

·

Segunda Forma Normal (2FN): Asegura que todas las columnas que no son llave

·

sean completamente dependientes de la llave primaria (PK). Tercera Forma Normal (3FN): Elimina cualquier dependencia transitiva. Una dependencia transitiva es aquella en la cual las columnas que no son llave son dependientes de otras columnas que tampoco son llave.

18


Ejemplo de Normalizacion ¿Qué tan lejos debe llevar la normalización? En ultima instancia, depende de nosotros.

La siguiente decisión es ¿qué tan lejos debe llevar la normalización? La normalización es una ciencia subjetiva. Determinar las necesidades de simplificación depende de nosotros. Si nuestra base de datos va a proveer información a un solo usuario para un propósito simple y existen pocas posibilidades de expansión, normalizar los datos hasta la 3FN quizá sea algo exagerado. Las reglas de normalización existen como guías para crear tablas que sean fáciles de manejar, así como flexibles y eficientes. A veces puede ocurrir que normalizar los datos hasta el nivel más alto no tenga sentido. La pregunta fundamental

¿Se están dividiendo tablas sólo para seguir las reglas o estas divisiones son en verdad prácticas?. Éstas son el tipo de cosas que nosotros como diseñadores de la base de datos, necesitamos decidir, y la experiencia y el sentido común nos pueden auxiliar para tomar la decisión correcta. La normalización no es una ciencia exacta, más bien subjetiva. Existen seis niveles más de normalización!

Los cuales no se han discutido aquí porque no tienen gran aplicación en casos practicos… Ellos son: Forma Normal Boyce-Codd Cuarta Forma Normal (4NF) Quinta Forma Normal (5NF) o Forma Normal de Proyección-Unión Forma Normal de Proyección-Unión Fuerte Forma Normal de Proyección-Unión Extra Fuerte Forma Normal de Clave de Dominio · · · · · ·

Estas formas de normalización pueden llevar las cosas más allá de lo que necesitamos. Existen para cerrar conceptualmente dentro de la teoria Relacional (para hacer una base de datos realmente relacional). Tienen que ver principalmente con dependencias múltiples y claves relacionales.

En resumen La normalización es una técnica que se utiliza para crear relaciones lógicas apropiadas entre tablas de una base de datos. Ayuda a prevenir errores lógicos en la manipulación de datos. La normalización facilita también agregar nuevas columnas sin romper el esquema actual ni las relaciones.


19

Existen varios niveles de normalización: Primera Forma Normal, Segunda Forma Normal, Tercera Forma Normal, Forma Normal Boyce-Codd, Cuarta Forma Normal, Quinta Forma Normal o Forma Normal de Proyección-Unión, Forma Normal de Proyección-Unión Fuerte, Forma Normal de Proyección-Unión Extra Fuerte y Forma Normal de Clave de Dominio. Cada nuevo nivel o forma nos acerca más a hacer una base de datos verdaderamente relacional. Se discutieron las primeras tres formas. Éstas proveen suficiente nivel de normalización para cumplir con las necesidades de la mayoría de las bases de datos. Normalizar demasiado puede conducir a tener una base de datos ineficiente y hacer a su esquema demasiado complejo para trabajar. Un balance apropiado de sentido común y práctico puede ayudarnos a decidir cuándo normalizar. 1.3.1.1

Primera Forma Normal

La regla de la Primera Forma Normal establece que las columnas repetidas deben eliminarse y colocarse en tablas separadas. Ventajas

Poner la base de datos en la Primera Forma Normal resuelve el problema de los encabezados de columna múltiples. Muy a menudo, los diseñadores de bases de datos inexpertos harán algo similar a la tabla no normalizada. Una y otra vez, crearán columnas que representen los mismos datos. La normalización ayuda a clarificar la base de datos y a organizarla en partes más pequeñas y más fáciles de entender. En lugar de tener que entender una tabla gigantesca y monolítica que tiene muchos diferentes aspectos, sólo tenemos que entender los objetos pequeños y más tangibles, así como las relaciones que guardan con otros objetos también pequeños. 1.3.1.2

Segunda Forma Normal

La regla de la Segunda Forma Normal establece que todas las dependencias parciales se deben eliminar y separar dentro de sus propias tablas. Una dependencia parcial es un término que describe a aquellos datos que no dependen de la llave primaria de la tabla para identificarlos. Ventajas

Una vez alcanzado el nivel de la Segunda Forma Normal, se controlan la mayoría de los problemas de lógica. Podemos insertar un registro sin un exceso de datos en la mayoría de las tablas. 1.3.1.3

Tercera Forma Normal

Una tabla está normalizada en esta forma si todas las columnas que no son llave son funcionalmente dependientes por completo de la llave primaria y no hay dependencias transitivas. Comentamos anteriormente que una dependencia transitiva es aquella en la cual existen columnas que no son llave que dependen de otras columnas que tampoco son llave.

20


Ventajas

Cuando las tablas están en la Tercera Forma Normal se previenen errores de lógica cuando se insertan o borran registros. Cada columna en una tabla está identificada de manera única por la llave primaria, y no deben haber datos repetidos. Esto provee un esquema limpio y elegante, que es fácil de trabajar y expandir. 1.3.1.4

Ejemplo de Normalizacion

Un dato sin normalizar no cumple con ninguna regla de normalización. Para explicar con un ejemplo en que consiste cada una de las reglas, vamos a considerar los datos de la siguiente tabla. ID ORDEN

FECHA

ID CLI

NOM CLIENTE

ESTADO

NUM ITEM

DESC ITEM

CANT

PRECIO

2301 2301 2301 2302 2303 2303

2/23/03 2/23/03 2/23/03 2/25/03 2/27/03 2/27/03

101 101 101 107 110 110

MARTI MARTI MARTI HERMAN WESPORTS WESPORTS

CA CA CA WI MI MI

3786 4011 9132 5794 4011 3141

RED RAQUETA PAQ-3 PAQ-6 RAQUETA FUNDA

3 6 8 4 2 2

35 65 4.75 5.0 65 10

Al examinar estos registros, podemos darnos cuenta que contienen un grupo repetido para NUM_ITEM, DESC_ITEM, CANT y PRECIO. La 1FN prohibe los grupos repetidos, por lo tanto tenemos que convertir a la primera forma normal. Los pasos a seguir son: Tenemos que eliminar los grupos repetidos. Tenemos que crear una nueva tabla con la PK de la tabla base y el grupo repetido. · ·

Los registros quedan ahora conformados en dos tablas que llamaemos ORDENES y ARTICULOS_ORDENES - ORDENES ID_ORDE N

FECHA

ID_CLIENT NOM_CLIENT ESTADO E E

2301 2302 2303

2/23/03 101 2/25/03 107 2/27/03 110

MARTI HERMAN WE-SPORTS

CA WI MI

- ARTICULOS_ORDENES ID_ORDEN NUM_ITEM DESC_ITEM CANT

PRECIO

2301 2301 2301

35 65 4.75

3786 4011 9132

RED RAQUETA PAQ-3

3 6 8


2302 2303 2303

5794 4011 3141

PAQ-6 RAQUETA FUNDA

4 2 2

5.0 65 10

21

22


Ahora procederemos a aplicar la segunda formal normal, es decir, tenemos que eliminar cualquier columna no llave que no dependa de la llave primaria de la tabla. Los pasos a seguir son: Determinar cuáles columnas que no son llave no dependen de la llave primaria de la tabla. Eliminar esas columnas de la tabla base. Crear una segunda tabla con esas columnas y la(s) columna(s) de la PK de la cual dependen. ·

· ·

La tabla ORDENES ya está en 2FN. Cualquier valor único de ID_ORDEN determina un sólo valor para cada columna. Por lo tanto, todas las columnas son dependientes de la llave primaria ID_ORDEN. Por su parte, la tabla ARTICULOS_ORDENES no se encuentra en 2FN ya que las columnas PRECIO y DESC_ITEM son dependientes de NUM_ITEM, pero no son dependientes de ID_ORDEN. Lo que haremos a continuación es eliminar estas columnas de la tabla ARTICULOS_ORDENES y crear una tabla ARTICULOS con dichas columnas y la llave primaria de la que dependen. Las tablas quedan ahora de la siguiente manera. - ARTICULOS_ORDENES ID_ORDEN NUM_ITEM

CANT

2301 2301 2301 2302 2303 2303

3786 4011 9132 5794 4011 3141

3 6 8 4 2 2

- ARTICULOS NUM_ITEM

DESC_ITEM

PRECIO

3786 4011 9132 5794 4011 3141

RED RAQUETA PAQ-3 PAQ-6 RAQUETA FUNDA

35 65 4.75 5.0 65 10

Prog Progra rama mand ndo o con con CLAR CLARIO ION N + SQL SQL (MS(MS-SQ SQL) L)

23

La tercera forma normal nos dice que tenemos que eliminar cualquier columna no llave que sea dependiente de otra columna no llave. Los pasos a seguir son: Determinar las columnas que son dependientes de otra columna no llave. Eliminar esas columnas de la tabla base. Crear una segunda tabla con esas columnas y con la columna no llave de la cual son dependientes. Al observar las tablas que hemos creado, nos damos cuenta que tanto la tabla ARTICULOS, como la tabla ARTICULOS_ORDENES se encuentran en 3FN. Sin embargo la tabla ORDENES no lo está, ya que NOM_CLIENTE y ESTADO son dependientes de ID_CLIENTE, y esta columna no es la llave primaria. · · ·

Para normalizar esta tabla, moveremos las columnas no llave y la columna llave de la cual dependen dentro de una nueva tabla CLIENTES. Las nuevas tablas CLIENTES y ORDENES se muestran a continuación. - ORDENES ID_ORDEN

FECHA

ID_CLIENTE

2301 2302 2303

2/23/03 2/25/03 2/27/03

101 107 110

- CLIENTES ID_CLIENTE

NOM_CLIENTE

ESTADO

101 107 110

MARTI HERMAN WE-SPORTS

CA WI MI

24

1.3. 1.3.2 2


Inte Integr griidad dad de de los los dato datos s

La integridad de los datos garantiza la calidad de los datos de la base de datos. Por ejemplo, si se especifica para un empleado el valor de employee_id (identificador del empleado) 123, la base de datos no debe permitir que ningún otro empleado tenga el mismo valor de identificador. Si tiene una columna employee_rating para la que se prevea valores entre el 1 y el 5, la base de datos no debe aceptar el valor 6. Si en la tabla hay una columna dept_id en la que se almacene el número de departamento del empleado, la base de datos sólo debe permitir valores que correspondan a los números de departamento de la compañía. Dos pasos importantes en el diseño de las l as tablas son la identificación i dentificación de valores válidos para una columna y la determinación de cómo forzar la integridad de los datos en la columna.

Tipos de Integridad Estas son las categorías de integridad de datos: · · · ·

Integridad de entidad Integridad de dominio Integridad referencial Integridad definida por el usuario

Integridad de entidad

La integridad de entidad define una fila como entidad única para una tabla determinada. La integridad de entidad fuerza la integridad de la columna o columnas c olumnas de los identificadores o la clave principal de una tabla (mediante índices, restricciones UNIQUE, restricciones PRIMARY KEY o propiedades IDENTITY). Integridad de dominio

La integridad de dominio viene dada por la validez de las entradas para una columna determinada. Puede forzar la integridad de dominio si restringe el tipo (mediante tipos de datos), el formato (mediante las reglas y las restricciones CHECK), o el intervalo de valores posibles (mediante restricciones FOREIGN KEY, restricciones CHECK, definiciones DEFAULT, definiciones NOT NULL y reglas). Integridad referencial

La integridad referencial protege las relaciones definidas entre las tablas cuando se crean o se eliminan registros. En SQL Server la integridad i ntegridad referencial se basa en las relaciones entre claves externas y claves principales o entre claves externas y claves exclusivas (mediante restricciones FOREIGN KEY, restricciones CHECK). La integridad referencial garantiza que los valores clave sean coherentes en las distintas tablas. Para conseguir esa coherencia, es preciso que no haya referencias a valores inexistentes y que, si cambia el valor de una clave, todas las referencias a ella se cambien en consecuencia en toda la base de datos. Cuando se fuerza la integridad referencial, SQL Server S erver impide a los usuarios:


·

·

·

25

Agregar registros a una tabla relacionada si no hay ningún registro asociado en la tabla principal. Cambiar valores en una tabla principal de manera que queden registros huérfanos en una tabla relacionada. Eliminar registros de una tabla principal cuando hay registros relacionados coincidentes.

Por ejemplo, con las tablas sales (compras) y titles (títulos) de la base de datos pubs, la integridad referencial se basa en la relación entre la clave externa (title_id) de la tabla sales y la clave principal (title_id) de la tabla titles. Integridad definida por el usuario

La integridad definida por el usuario le permite definir reglas de la compañía específicas que no pertenecen a ninguna otra categoría de integridad. Todas las categorías de integridad son compatibles con la integridad definida por el usuario (todas las restricciones en columnas y tablas de CREATE TABLE, procedimientos almacenados y desencadenadores).

26

1.3.3


Tipos datos MSSQL

Tipos de datos numéricos. SQL Server dispone de varios tipos de datos númericos. Cuanto mayor sea el número que puedan almacenar mayor será en consecuencia el espacio utilizado para almacenarlo. Como regla general se recomienda usar el tipo de dato mínimo posible. Todos los dato numéricos admiten el valor NULL. Bit. Una columna o variable de tipo bit puede almacenar el rango de valores de 1 a 0. Tinyint. Una columna o variable de tipo tinyint puede almacenar el rango de valores

de 0 a 255.

SmallInt. Una columna o variable de tipo smallint puede almacenar el rango de

valores -32768 a 32767.

Int. Una columna o variable de tipo int puede almacenar el rango de valores -231 a

231-1 .

BigInt. Una columna o variable de tipo bigint puede almacenar el rango de valores

-263 a 263-1 .

Decimal(p,s). Una columna de tipo decimal puede almacenar datos númericos

decimales sin redondear. Donde p es la precision (número total del dígitos) y s la escala (número de valores decimales) Float. Una columna de datos float puede almacenar el rango de valores

-1,79x-10308 a 1,79x-10308, , si la definimos con el valor máxmo de precisión. La precisión puede variar entre 1 y 53. Real. Sinónimo de float(24). Puede almacenar el rango de valores -3,4x-1038 a

3,4x-1038,

Money. Almacena valores númericos monetarios de -263 a 263-1, con una precisión

de hasta diexz milesimas de la unidad monetaria.

SmallMoney. Almacena valores númericos monetarios de -214.748,3647 a

214.748,3647, con una precisión de hasta diez milesimas de la unidad monetaria. Todos los tipos de datos enteros pueden marcarse con la propiedad identity para hacerlos autonuméricos.

Tipos de datos de caracter. Char(n) . Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando

almacenamos datos en el tipo char, siempre se utilizan los n caracteres indicados, incluso si la entrada de datos es inferior. Por ejemplo, si en un char(5), guardamos el valor 'A', se almacena 'A ', ocupando los cinco bytes. Varchar(n). Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando

almacenamos datos en el tipo varchar, unicamente se utilizan los caracteres necesarios,Por ejemplo, si en un varchar(255), guardamos el valor 'A', se almacena


27

'A', ocupando solo un byte bytes. Varchar(max). Igual que varchar, pero al declararse como max puede almacenar 231

-1 bytes. Nchar(n) .Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es

recomendable utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferente idomas. Nvarchar(n) .Almacena n caracteres en formato UNICODE, dos bytes por cada letra.

Es recomendable utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferente idomas. Nvarchar(max).Igual que varchar, pero al declararse como max puede almacenar 231

-1 bytes.

Tipos de datos de fecha. Datetime. Almacena fechas con una precision de milisegundo. Debe usarse para

fechas muy especificas. SmallDatetime. Almacena fechas con una precision de minuto, por lo que ocupa la

mitad de espacio de que el tipo datetime, para tablas que puedan llegar a tener muchos datos es un factor a tener muy en cuenta. TimeStamp.Se utiliza para marcar un registro con la fecha de inserción - actualizació

n. El tipo timestamp se actualiza automáticamente cada vez que insertamos o modificamos los datos.

Tipos de datos binarios Binary. Se utiliza para almacenar datos binarios de longitud fija, con una longitud má

xima de 8000 bytes. Varbinary. Se utiliza para almacenar datos binarios de longitud variable, con una

longitud máxima de 8000 bytes..Es muy similar a binary, salvo que varbinary utiliza menos espacio en disco. Varbinary(max) .Igual que varbinary, pero puede almacenar 231-1 bytes

Tipo de datos XML. XML.Una de las grandes mejoras que incorpora SQL Server 2005 es el soporte nativo para XML. Como podemos deducir, este tipo de datos se utiliza para almacenar XML.

Ejemplos:

28


DECLARE @bit bit, @tinyint tinyint, @smallint smallint, @int int, @bigint bigint, @decimal decimal(10,3), @real real, @double float(53), @money money set @bit = 1 print @bit

- - 1 0 d i g i t o s , 7 en t e r o s y

- - 3 d e c i ma l e s

set @tinyint = 255 print @tinyint set @smallint = 32767 print @smallint set @int = 642325 print @int set @decimal = 56565.234 print @decimal

--

Pu n t o c o mo s e p ar a d or

d e c i ma l

set @money = 12.34 print @money 1.3.3.1

Campos Identity

SQL Server nos da la posibilidad de generar valores sobre las columnas de la siguiente manera: ·

Con la Propiedad identity

·

Con la función NEWID

·

Con el tipo de dato uniqueidentifier

Propiedad Identity La propiedad identity puede ser utilizada para crear columnas que contienen valores secuenciales que identifican a cada columna insertada en la tabla y frecuentemente estas son utilizadas como llaves primarias. Las ventajas de la propiedad identity es que se reduce el costo y mejora el performance. Simplifica la programación, mantiene las llaves primarias con valores cortos y reduce cuellos de botella en las transacciones. Sintaxis CREATE TABLE table (column_name data_type [ IDENTITY [(seed, increment)]] NOT NULL )

Puntos a considerar en los campos identity ·

Solo se permite un campo identity en una tabla


·

·

·

·

29

Debe ser usada con el tipo de datos integre(int, bigint, smallint o tinyint), numeric o decimal. El tipo de datos numerica y decima deben ser especificados con una escala de 0. No pueden ser actualizados Se puede utilizar IDENTITYCOL en el lugar de la columna en un query. Esto referencia a la columna con la propiedad identity sin la necesidad de conocer el nombre de la columna. No permite el uso de valores nulos

Se puede extraer información sobre la propiedad identity de diferentes formas: ·

·

·

·

Existen dos funciones que obtienen información sobre la definición de un identity: IDENT_SEED(regresa el valor con el que inicia el identity) y IDENT_INCR(regresa el valor con el que se incrementa el identity). Se pueden extraer datos de las columnas identity utilizando la variable global @@identity. Esta contiene el último valor insertado en una columna identity durante una sesión. SCOPE_INDENTITY regresa el último valor identity insertado en una columna en el mismo scop. Un scop puede ser un store procedure, trigger, función o batch. IDENT_CURRENT regresa el último valor del identity generado para una tabla en especifico en cualquier sesión y cualquier scop.

La propiedad identity puede ser manipulada de diferentes formas: ·

·

Se puede permitir insertar valores específicos en los campos identity de una tabla habilitando la opción IDENTITY_INSERT. Cuando esta opción esta en ON, las sentencias insert deben de contener un valor en la columna con identity. Para verificar y corregir el valor actual del identity de una columna, se puede utilizar el DBCC CHECKIDENT. Este te permite comparar los valores actuales del identity con el valor máximo de la columna con identity.

NOTA: La propiedad identity no garantiza que los registros sea únicos, para esto genere

un índice único sobre el campo identity. Ejemplo

En este ejemplo se crea una tabla con dos campos, userid y nombre. La propiedad identity es utilizada en el campo userid para incrementar el valor automáticamente cada vez que se inserte un registro. El inicio del identity es 100 y el incremento 5. De esta manera los valores serían 100, 105, 110, 115, etc. Utilizando un incremento de 5 permite insertar registros entre los valores posteriormente. CREATE TABLE Class (userid int IDENTITY(100, 5) NOT NULL, Name varchar(16))

La función NEWID y el tipo de dato uniqueidentifier La función NEWID y el tipo de dato uniqueidentifier se utilizan de manera conjunta y se deben de utilizar cuando la información que se colecta proviene de muchas tablas, insert

30


ándolas en una sola requiriendo que cada uno de los registros sea único. ·

·

·

El tipo de dato uniqueidentifier almacena un valor único como un string binario de 16 bytes. Este tipo de dato es utilizado para almacenar un identificador global (Global Unique Identifier (GUID)). La función NEWID crea un identificador único GUID que puede ser almacenado en un tipo de dato uniqueidentifier. El tipo de dato uniqueidentifier no genera automáticamente nuevos ID's para los registros insertados como lo hace la propiedad identity. Para obtener un nuevo valor se debe definir la tabla con un constraint que especifique la función NEWID vomo default. Cuando se genere una sentencia insert, se debe especifica la funció n NEWID.

Ejemplo CREATE TABLE Customer (CustID uniqueidentifier NOT NULL DEFAULT NEWID(), CustName char(30) NOT NULL)

1.3.4

Lenguaje de Definición de datos (LDD)

Existen cuatro operaciones básicas dentro de este lenguaje: CREATE , ALTER , DROP y TRUNCATE 1.3.4.1

CREATE

Este comando crea un objeto dentro de la base de datos. Puede ser una tabla, vista, í ndice, trigger, función, procedimiento o cualquier otro objeto que el motor de la base de datos soporte. Por Ejemplo (creación de una tabla): CREATE TABLE TABLA_NOMBRE ( ci integer not null, nombre VARCHAR (50), fecha_nac DATE NOT NULL, PRIMARY KEY (my_field1, my_field2) ) 1.3.4.2

ALTER

Este comando permite modificar la estructura de un objeto. Se pueden agregar/quitar campos a una tabla, modificar el tipo de un campo, agregar/quitar índices a una tabla, modificar un trigger, etc. Por Ejemplo (agregar columna a una tabla): ALTER TABLE TABLA_NOMBRE ( ADD NUEVO_CAMPO INT UNSIGNED ) 1.3.4.3

DROP

Este comando elimina un objeto de la base de datos.Puede ser una tabla, vista, índice, trigger, función, procedimiento o cualquier otro objeto que el motor de la base de datos soporte. Se puede combinar con la sentencia ALTER.


31

Por Ejemplo : DROP TABLE TABLA_NOMBRE 1.3.4.4

TRUNCATE

Este comando trunca todo el contenido de una tabla. La ventaja sobre el comando DELETE, es que si se quiere borrar todo el contenido de la tabla, es mucho más rápido, especialmente si la tabla es muy grande, la desventaja es que TRUNCATE solo sirve cuando se quiere eliminar absolutamente todos los registros, ya que no se permite la clá usula WHERE. Por Ejemplo : TRUNCATE TABLE TABLA_NOMBRE

1.3.5

Lenguaje de Manipulación de Datos Nivel Básico

Existen cuatro operaciones básicasen el lenguaje de Manipulacion de Datos: SELECT, INSERT, UPDATE y DELETE. 1.3.5.1

Recuperar Datos con SELECT

Una consulta es una petición de datos almacenados en el motor SQL. La instrucción SELECT recupera los datos de SQL Server y los presenta al usuario en uno o más conjuntos de resultados. Un conjunto de resultados es una organización tabular de los datos obtenidos de SELECT. Al igual que una tabla de SQL, el conjunto de resultados está formado por columnas y filas. La sintaxis completa de la instrucción SELECT es compleja, aunque la mayor parte de las instrucciones SELECT describen las cuatro propiedades principales de un conjunto de resultados: El número y atributos de las columnas del conjunto de resultados. Estos atributos se definen para cada columna del conjunto de resultados: El tipo de datos de la columna. El tamaño de la columna y, para las columnas numéricas, la precisión y escala. El origen del valor de los datos devueltos en la columna. Las tablas cuyos datos del conjunto de resultados se recuperan y cualquier relación lógica entre las tablas. Las condiciones que deben cumplir las filas de las tablas de origen para satisfacer los requisitos de la instrucción SELECT. Las filas que no cumplen las condiciones se omiten. La secuencia en la que se ordenan las filas del conjunto de resultados. ·

· · · ·

·

·

Por ejemplo esta instrucción SELECT busca el identificador del producto, el nombre y el precio unitario de cualquier producto cuyo precio exceda de 40 dólares: SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice > $40 ORDER BY UnitPrice ASC

Los nombres de columna enumerados después de la palabra clave SELECT (ProductID, ProductName y UnitPrice) forman la lista de selección. Esta lista especifica que el

32


conjunto de resultados tiene tres columnas, y cada columna tiene el nombre, tipo de datos y tamaño de la columna asociada de la tabla Products. Puesto que la cláusula FROM especifica sólo una tabla base, todos los nombres de columna de la instrucción SELECT hacen referencia a las columnas de esa tabla. La cláusula FROM enumera la tabla individual, Products, cuyos datos se van a recuperar. La cláusula WHERE especifica que las únicas filas de Products que cumplen los requisitos de esta instrucción SELECT son aquellas en las que el valor de la columna UnitPrice supera los 40 dólares. La cláusula ORDER BY especifica que el conjunto de resultados debe ordenarse en secuencia ascendente según el valor de la columna UnitPrice.

SELECT: Lista de selección de columnas La lista de selección define las columnas del conjunto de resultados de una instrucción SELECT. La lista de selección es una serie de expresiones separadas por comas. Cada expresión define una columna del conjunto de resultados. Las columnas del conjunto de resultados están en el mismo orden que las expresiones de la lista de selección. Los siguientes atributos de las columnas del conjunto de resultados se definen mediante las expresiones de la lista de selección: El tipo de datos, tamaño, precisión y escala de la columna del conjunto de resultados son los mismos que los de la expresión que define la columna. El nombre de la columna del conjunto de resultados es el nombre asociado con la expresión que define la columna. La palabra clave opcional AS se puede usar para cambiar el nombre o para asignar uno, si la expresión no lo tiene. Los valores de los datos de las columnas del conjunto de resultados se derivan de la evaluación de la expresión para cada fila del conjunto de resultados. La lista de selección puede contener también palabras clave que controlen el formato final del conjunto de resultados. ·

·

·

·

Entre los elementos de la lista de selección se pueden incluir: Una expresión simple: una referencia a una función, una variable local, una constante o una columna de una tabla o vista. Una subconsulta escalar, que es una instrucción SELECT que da como resultado un valor individual para cada fila del conjunto de resultados. Una expresión compleja generada al usar operadores en una o más expresiones simples. La palabra clave *, que especifica que se devolverán todas las columnas de una tabla. La asignación de variables con el formato @local_variable = expresión. La instrucción SET @local_variable se puede usar también para la asignación de variables. La palabra clave IDENTITYCOL, que se resuelve como una referencia a la columna de la tabla que tiene la propiedad IDENTITY. Por ejemplo, la propiedad IDENTITY ha sido definida para la columna OrderID en la tabla Northwind Orders, igual que la expresión Orders. IDENTITYCOL es igual a Orders.OrderID. La palabra clave ROWGUILDCOL, que se resuelve como una referencia a la columna de una tabla que tiene la propiedad ROWGUIDCOL. Crear una nueva columna (con SELECT INTO) que use la propiedad IDENTITY, mediante la sintaxis especificada. Por ejemplo, para crear una nueva columna llamada counter en la tabla authors que sea de tipo int, empiece con el valor 100 y vaya aumentándolo en 1 por cada número sucesivo; use counter = IDENTITY(int, 100, 1). Agregar temporalmente una columna a los resultados de la consulta que designe si la operación CUBE o ROLLUP agregó la fila o no. Usar la palabra clave GROUPING. ·

·

·

· ·

·

·

·

·

En este ejemplo se muestran muchos de los elementos que se pueden encontrar en una


33

lista de selección: SELECT FirstName + ' ' + LastName AS "Employee Name", IDENTITYCOL AS "Employee ID", HomePhone, Region, 10 AS Constant FROM Northwind.dbo.Employees ORDER BY LastName, FirstName ASC

Temas Relacionados:

FROM WHERE y HAVING: Filtrar filas ORDER BY JOIN: Combinaciones DISTINCT TOP n

1.3.5.1.1 FROM

La cláusula FROM es obligatoria en todas las instrucciones SELECT en las que se estén recuperando datos de tablas o vistas. Use la cláusula FROM para enumerar las tablas y vistas que contienen columnas a las que se hace referencia en la lista de selección y en la cláusula WHERE. Los nombres de las tablas y vistas se pueden sustituir con alias mediante la cláusula AS. La cláusula FROM es una lista separada por comas de nombres de tablas, nombres de vistas y cláusulas JOIN. La cláusula FROM puede especificar:

Una o más tablas o vistas. Por ejemplo: SELECT * FROM Shippers

Combinaciones de dos tablas o vistas: SELECT Cst.CustomerID, Cst.CompanyName, Cst.ContactName, Ord.ShippedDate, Ord.Freight FROM Northwind.dbo.Orders AS Ord JOIN Northwind.dbo.Customers AS Cst ON (Cst.CustomerID = Ord.CustomerID)

Una o más tablas derivadas, que son instrucciones SELECT de la cláusula FROM a las que se hace referencia mediante un alias o un nombre definido por el usuario. El conjunto de resultados de la instrucción SELECT de la cláusula FROM forma una tabla usada por la instrucción SELECT externa. Por ejemplo, esta instrucción SELECT usa una tabla derivada para averiguar si alguna tienda tiene todos los títulos de libros de la base de datos pubs: SELECT ST.stor_id, ST.stor_name FROM stores AS ST, (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count

34


FROM sales GROUP BY stor_id ) AS SA WHERE ST.stor_id = SA.stor_id AND SA.title_count = (SELECT COUNT(*) FROM titles)

Instrucciones SELECT sin cláusulas FROM

Las instrucciones SELECT que no necesitan una cláusula FROM son aquéllas que no seleccionan datos de ninguna tabla de la base de datos. Las siguientes son instrucciones SELECT que sólo seleccionan datos de las variables locales o de funciones Transact-SQL que no operan en una columna, por ejemplo: SELECT @MyIntVariable SELECT @@VERSION 1.3.5.1.2 WHERE y HAVING: Filtrar filas

Las cláusulas WHERE y HAVING de una instrucción SELECT controlan qué filas de las tablas de origen se usan para generar el conjunto de resultados. WHERE y HAVING son filtros. Especifican una serie de condiciones de búsqueda y para crear el conjunto de resultados sólo se usan las filas que cumplan los términos de dichas condiciones. Las filas que cumplen las condiciones de búsqueda se dice que están calificadas para participar en el conjunto de resultados. Por ejemplo, la cláusula WHERE de esta instrucción SELECT sólo califica las filas en las que la región sea Washington State: SELECT CustomerID, CompanyName FROM Northwind.dbo.Customers WHERE Region = 'WA'

La cláusula HAVING se usa normalmente junto con la cláusula GROUP BY, aunque se puede especificar sin GROUP BY. La cláusula HAVING especifica otros filtros que se aplican después de los de la cláusula WHERE. Por ejemplo, esta cláusula WHERE sólo califica los pedidos de un producto cuyo precio unitario supere los 100 dólares, y la cláusula HAVING restringe posteriormente el resultado a aquellos pedidos que incluyan más de 100 unidades: SELECT OrdD1.OrderID AS OrderID, SUM(OrdD1.Quantity) AS "Units Sold", SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue FROM [Order Details] AS OrdD1 WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID FROM [Order Details] AS OrdD2 WHERE OrdD2.UnitPrice > $100) GROUP BY OrdD1.OrderID HAVING SUM(OrdD1.Quantity) > 100

Las condiciones de búsqueda o calificaciones de las cláusulas WHERE y HAVING pueden incluir:

Operadores de comparación


35

(como =, < >, < y >). Por ejemplo, en la siguiente consulta se recuperan las filas de la tabla Product SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID = 2 ORDER BY ProductID

Intervalos (BETWEEN y NOT BETWEEN). Por ejemplo, en esta consulta se recuperan las filas de la tabla Products de las categorías 2 a 4. SELECT CategoryID, ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID BETWEEN 2 and 4 ORDER BY CategoryID, ProductID

Listas (IN, NOT IN). Por ejemplo, en esta consulta se recuperan las filas de la tabla Products donde Category ID coincida con algún Id. de la lista: SELECT CategoryID, ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID IN (1,4,5,7) ORDER BY CategoryID, ProductID

Coincidencia de patrones (LIKE y NOT LIKE). Por ejemplo, en esta consulta se recuperan las filas de la tabla Products donde el nombre del producto empiece con las letras Ch. SELECT CategoryID, ProductID, ProductName FROM Northwind.dbo.Products WHERE ProductName LIKE 'Ch%' ORDER BY CategoryID, ProductID

Todos los registros (=ALL, >ALL, <= ALL, ANY). Por ejemplo, en esta consulta se recuperan los Id. de los pedidos y de los productos de la tabla Order Details donde la cantidad del producto enviado sea mayor que la cantidad enviada de cualquier producto de la categoría 1: USE Northwind GO SELECT OrdD1.OrderID, OrdD1.ProductID FROM "Order Details" OrdD1 WHERE OrdD1.Quantity > ALL (SELECT OrdD2.Quantity FROM "Order Details" OrdD2 JOIN Products Prd ON OrdD2.ProductID = Prd.ProductID WHERE Prd.CategoryID = 1) GO

Combinaciones de estas condiciones (AND, OR, NOT). Por ejemplo, en esta consulta se recuperan todos los productos en los que la cantidad de productos almacenados sea inferior a la cantidad de pedidos, o el producto venga del proveedor 15 y sea de la categoría 4: SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE UnitsInStock < ReorderLevel OR (SupplierID = 15 AND CategoryID = 4)

36


1.3.5.1.3 ORDER BY

La cláusula ORDER BY ordena los resultados de una consulta por una o más columnas. Una ordenación puede ser ascendente (ASC) o descendente (DESC). Si no se especifica ninguna, se supone que es ASC.

Ejemplos En la consulta siguiente se devuelven los resultados ordenados de forma ascendente por pub_id. USE pubs SELECT pub_id, type, title_id FROM titles ORDER BY pub_id

Si se nombra más de una columna en la cláusula ORDER BY, las ordenaciones se anidan. La instrucción siguiente ordena las filas de la tabla titles, primero por editor, en orden descendente; después, por tipo, en orden ascendente, dentro de cada editor y, finalmente, por precio (también en orden ascendente, porque no se especifica DESC). USE pubs SELECT pub_id, type, title_id, price FROM titles ORDER BY pub_id DESC, type, price 1.3.5.1.4 JOIN: Combinaciones

Las condiciones de combinación se pueden especificar en las cláusulas FROM o WHERE, aunque se recomienda que se especifiquen en la cláusula FROM. Las cláusulas WHERE y HAVING pueden contener también condiciones de búsqueda para filtrar aún más las filas seleccionadas por las condiciones de combinación.

Tipos de combinaciones Combinaciones internas (la operación de combinación típica, que usa algunos operadores de comparación como = o <>). En este tipo se incluyen las combinaciones equivalentes y las combinaciones naturales. Las combinaciones internas usan un operador de comparación para hacer coincidir las filas de dos tablas según los valores de las columnas comunes de cada tabla. Un ejemplo sería recuperar todas las filas en las que el número de identificación de estudiante es el mismo en las tablas students y courses. ·

·

·

·

Combinaciones externas. Puede ser una combinación externa izquierda, derecha o completa. Las combinaciones externas se especifican en la cláusula FROM con uno de los siguientes conjuntos de palabras clave: LEFT JOIN o LEFT OUTER JOIN: El conjunto de resultados de una combinación externa izquierda incluye todas las filas de la tabla de la izquierda especificada en la cláusula LEFT OUTER, y no sólo aquellas en las que coincidan las columnas combinadas. Cuando una fila de la tabla de la izquierda no tiene filas coincidentes en la tabla de la derecha, la fila asociada del conjunto de resultados contiene valores NULL en todas las columnas de la lista de selección que procedan de la tabla de la derecha. RIGHT JOIN o RIGHT OUTER JOIN: Una combinación externa derecha es el inverso de


37

una combinación externa izquierda. Se devuelven todas las filas de la tabla de la derecha. Cada vez que una fila de la tabla de la derecha no tenga correspondencia en la tabla de la izquierda, se devuelven valores NULL para la tabla de la izquierda. ·

·

FULL JOIN o FULL OUTER JOIN: Una combinación externa completa devuelve todas las filas de las tablas de la izquierda y la derecha. Cada vez que una fila no tenga coincidencia en la otra tabla, las columnas de la lista de selección de la otra tabla contendrán valores NULL. Cuando haya una coincidencia entre las tablas, la fila completa del conjunto de resultados contendrá los valores de datos de las tablas base. Combinaciones cruzadas: Las combinaciones cruzadas devuelven todas las filas de la tabla izquierda y cada fila de la tabla izquierda se combina con todas las filas de la tabla de la derecha. Las combinaciones cruzadas se llaman también productos cartesianos.

Utilizar combinaciones internas Una combinación interna es aquélla en la que los valores de las columnas que se están combinando se comparan mediante un operador de comparación. En el estándar SQL-92, las combinaciones internas se pueden especificar en las cláusulas FROM o WHERE. Éste es el único tipo de combinación que SQL-92 admite en la cláusula WHERE. Las combinaciones internas especificadas en la cláusula WHERE se conocen como combinaciones internas al estilo antiguo. Esta consulta de Transact-SQL es un ejemplo de una combinación interna: USE pubs SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city = p.city ORDER BY a.au_lname DESC

Esta combinación interna se conoce como una combinación equivalente. Devuelve todas las columnas de ambas tablas y sólo devuelve las filas en las que haya un valor igual en la columna de la combinación. El siguiente es el conjunto de resultados: a u _i d ----------2 3 8- 9 5 - 7 7 66 409- 56- 7008 s t at e z i p ----- ----CA 94705 CA 94705

a u _l n a me -------Ca r s o n Be n n e t

a u _f n a me -------Ch e r y l Ab r a h a m

c on t r a c t p u b_ i d -------- -----1 1389 1 1389

p h on e -----------4 1 5 5 4 8- 7 7 23 415 658- 9932

a d dr e s s ci t y ---------------------- 5 8 9 Da r wi n L n . Be r k e l e y 6 2 23 Ba t e ma n St . Be r k e l e y

p ub _n ame ci t y st at e --------------------- -------- ----Al g o d a t a I n f o s y s t e ms Be r k e l e y CA Al g o d a t a I n f o s y s t e ms Be r k e l e y CA

c ou nt r y ------- USA USA

En el conjunto de resultados, la columna city aparece dos veces. Puesto que no tiene sentido repetir la misma información, se puede eliminar una de estas dos columnas idénticas si se cambia la lista de selección. El resultado se llama combinación natural. La consulta anterior de Transact-SQL se puede volver a formular para que forme una combinación natural. Por ejemplo: USE pubs

38


SELECT p.pub_id, p.pub_name, p.state, a.* FROM publishers p INNER JOIN authors a ON p.city = a.city ORDER BY a.au_lname ASC, a.au_fname ASC

El siguiente es el conjunto de resultados: pub_i d -----Al g o da t 1 3 89

p u b _ na me -------- ------a I n f o s y s t e ms CA Al g o da t a I n f o s y s t e ms

st at e au_i d a u _ l n a me a u _ f n a me ------- - ------- ----------- - - - - - - - 1389 4 0 9- 5 6 - 7 0 08 Be n ne t Ab r a h am CA 2 3 8- 9 5 - 7 7 66 Ca r s o n Ch e r y l

p ho n e a dd r e s s ci t y --------------------------- -------4 1 5 6 5 8 - 9 9 3 2 6 2 2 3 Ba t e ma n St . Be r k e l e y 4 1 5 5 4 8 - 7 7 2 3 5 8 9 Da r wi n L n . Be r k e l e y

st at e ----CA CA

zi p ----94705 94705

c on t r a ct --------- 1 1

( 2 r o w( s ) a f f e c t e d )

En este ejemplo, publishers.city no aparece en los resultados.

Combinaciones con operadores distintos de igual También se puede combinar valores de dos columnas que no son iguales. Los mismos operadores y predicados usados para las combinaciones internas se pueden utilizar para las combinaciones no igual (< >). Para obtener más información acerca de los operadores y predicados disponibles que se pueden usar en las combinaciones, consulte Utilizar operadores en expresiones y WHERE. Este ejemplo de Transact-SQL es de una combinación mayor que (>) que busca autores de New Moon que viven en estados cuyo nombre está en orden alfabético después de Massachusetts, donde se encuentra New Moon Books. USE pubs SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state FROM publishers p INNER JOIN authors a ON a.state > p.state WHERE p.pub_name = 'New Moon Books' ORDER BY au_lname ASC, au_fname ASC

El siguiente es el conjunto de resultados: p u b_ n ame --------Ne w Mo o n Ne w Mo o n Ne w Mo o n Ne w Mo o n Ne w Mo o n New Moon

st at e ------- -----Bo o k s MA Bo o k s MA Bo o k s MA Bo o k s MA Bo o k s MA Books MA

a u _l n a me a u _f n a me st at e - -------------------- -------------------- ----Bl o t c h e t - Ha l l s Re g i n a l d OR d e l Ca s t i l l o I nnes MI Gr e e n e Mo r n i n g s t a r TN Pa n t e l e y Sy l v i a MD Ri n g e r Al b e r t UT Ri nger Anne UT

( 6 r o w( s ) a f f e c t e d )

Combinaciones con el operador no igual La combinación no igual (< >) se usa con poca frecuencia. Como regla general, las combinaciones no igual sólo tienen sentido cuando se usan con una autocombinación. Por ejemplo, la combinación no igual de Transact-SQL y la autocombinación siguientes se usan para buscar categorías de dos o más libros baratos (menos de 15 dólares) de distintos precios: USE pubs


39

SELECT DISTINCT t1.type, t1.price FROM titles t1 INNER JOIN titles t2 ON t1.type = t2.type AND t1.price <> t2.price WHERE t1.price < $15 AND t2.price < $15

Nota: La expresión NOT column_name = column_name es equivalente a column_name < > column_name.

En el siguiente ejemplo de Transact-SQL se usa una combinación no igual que se combina con una autocombinación para buscar todas las filas de la tabla titleauthor en la que dos o más filas tengan el mismo title_id pero distintos números de au_id (es decir, libros con más de un autor): USE pubs SELECT DISTINCT t1.au_id, t1.title_id FROM titleauthor t1 INNER JOIN titleauthor t2 ON t1.title_id = t2.title_id WHERE t1.au_id <> t2.au_id ORDER BY t1.au_id

El siguiente es el conjunto de resultados: au_i d ------213- 46267- 41267- 41409- 56427- 17472- 27672- 71722- 51724- 80724- 80756- 30846- 92899- 46899- 46998- 72-

---8915 2394 2394 7008 2319 2349 3249 5454 9391 9391 7391 7186 2035 2035 3567

t i t l e_i d -------- BU1 0 3 2 BU1 1 1 1 T C7 7 7 7 BU1 0 3 2 PC8 8 8 8 T C7 7 7 7 T C7 7 7 7 MC3 0 2 1 BU1 1 1 1 PS1 3 7 2 PS1 3 7 2 PC8 8 8 8 MC3 0 2 1 PS2 0 9 1 PS2 0 9 1

( 1 5 r o w( s ) a f f e c t e d )

Utilizar combinaciones externas Las combinaciones internas sólo devuelven filas cuando hay una fila de ambas tablas, como mínimo, que coincide con la condición de la combinación. Las combinaciones internas eliminan las filas que no coinciden con alguna fila de la otra tabla. Sin embargo, las combinaciones externas devuelven todas las filas de una de las tablas o vistas mencionadas en la cláusula FROM, como mínimo, siempre que tales filas cumplan con alguna de las condiciones de búsqueda de WHERE o HAVING. Todas las filas se recuperarán de la tabla izquierda a la que se haya hecho referencia con una combinación externa izquierda, y de la tabla derecha a la que se haya hecho referencia con una combinación externa derecha. En una combinación externa completa, se devuelven todas las filas de ambas tablas. SQL Server utiliza las siguientes palabras clave de SQL-92 para las combinaciones externas especificadas en una cláusula FROM:

40


LEFT OUTER JOIN o LEFT JOIN RIGHT OUTER JOIN o RIGHT JOIN FULL OUTER JOIN o FULL JOIN

SQL Server admite tanto la sintaxis de combinaciones externas de SQL-92 como la sintaxis heredada para especificar las combinaciones externas que se basan en la utilización de los operadores *= y =* en la cláusula WHERE. Se recomienda utilizar a sintaxis de SQL-92 porque no está sujeta a las ambigüedades que se producen, en ocasiones, en las combinaciones externas heredadas de Transact-SQL.

Utilizar combinaciones exteriores izquierdas Imagine una combinación de la tabla authors y de la tabla publishers en sus columnas city. Los resultados sólo muestran los autores que viven en ciudades donde hay algún editor (en este caso, Abraham Bennet y Cheryl Carson). Para incluir todos los autores en el resultado, con independencia de que un editor se encuentre o no en la misma ciudad, use una combinación exterior izquierda de SQL-92. A continuación se muestra la consulta y los resultados de la combinación externa izquierda de Transact-SQL: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

El siguiente es el conjunto de resultados: a u _ f n a me a u _ l n a me p u b _ n a me -------------------- ------------------------------ ----------------Re gi n al d Bl o t c he t - Ha l l s NUL L Mi c h e l De F r a n c e NUL L I n ne s d e l Ca s t i l l o NUL L An n Du l l NUL L Ma r j o r i e Gr e e n NUL L Mo r n i n g s t a r Gr e e n e NUL L Bu r t Gr i n gl e s by NUL L Sh e r y l Hu n t e r NUL L Li vi a Ka r s e n NUL L Ch a r l e n e Locksl ey NUL L St e a r n s Ma c F e at h e r NUL L He a t h e r Mc B a d d e n NUL L Mi c h a el O' L e ar y NUL L Sy l v i a Pa nt e l e y NUL L Al b er t Ri n ge r NUL L An n e Ri n g e r NUL L Me a n d e r Smi t h NUL L De a n St r a i g h t NUL L Di r k St r i n ge r NUL L Johnson Wh i t e NUL L Ak i k o Yo k o mo t o NUL L Ab r a h am Be n ne t Al g o da t a I n f o s y s t e ms Ch er y l Ca r s o n Al g od at a I n f o s y s t e ms ( 2 3 r o w( s ) a f f e c t e d )


41

LEFT OUTER JOIN incluye en el resultado todas las filas de la tabla authors, tanto si hay una coincidencia en la columna city de la tabla publishers como si no la hay. Observe que, en los resultados, no hay datos coincidentes con la mayor parte de los autores enumerados, con lo que estas filas contienen valores NULL en la columna pub_name.

Utilizar combinaciones exteriores derechas Imagine una combinación de la tabla authors y de la tabla publishers en sus columnas city. Los resultados sólo muestran los autores que viven en ciudades donde haya algún editor (en este caso, Abraham Bennet y Cheryl Carson). El operador de combinación exterior derecha de SQL-92, RIGHT OUTER JOIN, indica que todas las filas de la segunda tabla se deben incluir en los resultados, con independencia de si hay datos coincidentes en la primera tabla. Para incluir todos los editores en los resultados, sin tener en cuenta si hay un editor en la ciudad, use una combinación externa derecha de SQL-92. A continuación se muestra la consulta de Transact-SQL y los resultados de la combinación externa derecha: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

El siguiente es el conjunto de resultados: a u _ f n a me a u _ l n a me p u b _ n a me -------------------- ------------------------ -------------------Ab r a h am Be n ne t Al g o da t a I n f o s y s t e ms Ch er y l Ca r s o n Al g od at a I n f o s y s t e ms NUL L NUL L Bi n n e t & Ha r d l e y NUL L NUL L F i v e L a k e s Pu b l i s h i n g NUL L NUL L GGG&G NUL L NUL L L u c e r n e Pu b l i s h i n g NULL NULL New Moo n Boo k s NUL L NUL L Ra mo n a Pu b l i s h e r s NULL NULL Sco ot ney Book s ( 9 r o w( s ) a f f e c t e d )

Una combinación externa se puede restringir aún más si se utiliza un predicado (como comparar la combinación con una constante). En este ejemplo se muestra la misma combinación externa derecha, pero se eliminan todos los títulos de los que se han vendido menos de 50 copias: USE pubs SELECT s.stor_id, s.qty, t.title FROM sales s RIGHT OUTER JOIN titles t ON s.title_id = t.title_id AND s.qty > 50 ORDER BY s.stor_id ASC

El siguiente es el conjunto de resultados: s t or _ i d qt y t i tl e - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - -( n ul l ) ( n ul l ) Bu t I s I t Us er Fr i e n d l y ? ( n ul l ) ( n ul l ) Co mp ut e r Ph ob i c AND No n- Ph ob i c I n di v i d ua l s : Be ha v i o r Va r i a t i o n s

42


( n ul l ( nul l ( n ul l 7066 ( n ul l ( n ul l ( nul l

) ) )

( n ul ( n ul ( nul ( nul ( nul ( n ul ( n ul ( n ul ( n ul

) ( n ul ) ( n ul ) ( nul ) ( nul ) ( nul ) ( n ul ) ( n ul ) ( n ul ) ( n ul

l l l l l l l l l

( n ul ( nul ( n ul 75 ) ( n ul ) ( nul ) ( nul

l ) Co ok i n g wi t h Co mp ut e r s : Su r r e pt i t i o us Ba l a n ce Sh ee t s l ) Emo t i o n a l Se c ur i t y : A Ne w Al g o r i t h m l ) F i f t y Ye a r s i n Bu ck i n gh a m Pa l a ce Ki t c he ns I s An g e r t h e En e my ? l ) L i f e Wi t h o u t Fe a r l ) Ne t Et i q u e t t e l ) On i o n s , L e e k s , a n d Ga r l i c : Co o k i n g Se c r e t s o f t h e Me di t e r r a ne an l ) Pr o l o ng ed Da t a De pr i v at i o n: Fo ur Ca se St u di e s l ) Se c r e t s of Si l i c on Va l l e y l ) Si l i c on Va l l e y Ga s t r o n o mi c Tr e a t s l ) St r a i g h t Ta l k Ab o u t Co mp u t e r s l ) Su s hi , An y on e ? l ) Th e Bu sy Ex ec ut i v e' s Da t a ba se Gu i d e l ) Th e Go ur me t Mi c r o wa v e l ) Th e Ps y c ho l o gy of Co mp ut e r Co ok i n g l ) Yo u Ca n Co mb at Co mp ut e r St r e ss !

( 1 8 r o w( s ) a f f e c t e d )

Para ver más información acerca de los predicados, consulte WHERE.

Utilizar combinaciones externas completas Para retener la información que no coincida al incluir las filas no coincidentes en los resultados de una combinación, utilice una combinación externa completa. Microsoft® SQL Server™ 2000 proporciona el operador de combinación externa completa, FULL OUTER JOIN, que incluye todas las filas de ambas tablas, con independencia de que la otra tabla tenga o no un valor coincidente. Imagine una combinación de la tabla authors y de la tabla publishers en sus columnas city. Los resultados sólo muestran los autores que viven en ciudades donde hay algún editor (en este caso, Abraham Bennet y Cheryl Carson). El operador de combinación externa completa de SQL-92, FULL OUTER JOIN, indica que todas las filas de ambas tablas se van a incluir en los resultados, con independencia de que haya datos coincidentes en las tablas. Para incluir todos los editores y todos los autores en el resultado, sin tener en cuenta si una ciudad tiene un editor que se encuentra en la misma ciudad, o si un editor se encuentra en la misma ciudad, use una combinación externa completa. A continuación se muestra la consulta y los resultados de la combinación externa completa de Transact-SQL: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

El siguiente es el conjunto de resultados: a u _ f n a me a u _ l n a me p u b _ n a me -------------------- ---------------------------- -------------------Re gi n al d Bl o t c he t - Ha l l s NUL L Mi c h e l De F r a n c e NUL L I n ne s d el Ca st i l l o NUL L An n Du l l NUL L Ma r j o r i e Gr e e n NUL L Mo r n i n g s t a r Gr e e n e NUL L


Bu r t Sh e r y l Li vi a Ch a r l e n e St e a r n s He a t h e r Mi c h a el Sy l v i a Al b er t An n e Me a n d e r De a n Di r k Johnson Ak i k o Ab r a h am Ch er y l NUL L NUL L NUL L NUL L NULL NUL L NULL

Gr i n gl e s by Hu n t e r Ka r s e n Locksl ey Ma c F e at h e r Mc B a d d e n O' L e ar y Pa nt e l e y Ri n ge r Ri n g e r Smi t h St r a i g h t St r i n ge r Wh i t e Yo k o mo t o Be n ne t Ca r s o n NUL L NUL L NUL L NUL L NULL NUL L NULL

43

NUL L NUL L NUL L NUL L NUL L NUL L NUL L NUL L NUL L NUL L NUL L NUL L NUL L NUL L NUL L Al g o da t a I n f o s y s t e ms Al g od at a I n f o s y s t e ms Bi n n e t & Ha r d l e y F i v e L a k e s Pu b l i s h i n g GGG&G L u c e r n e Pu b l i s h i n g New Moo n Boo k s Ra mo n a Pu b l i s h e r s Scoot ney Books

Utilizar combinaciones cruzadas Una combinación cruzada que no tenga una cláusula WHERE produce el producto cartesiano de las tablas involucradas en la combinación. El tamaño del conjunto de resultados de un producto cartesiano es igual al número de filas de la primera tabla multiplicado por el número de filas de la segunda tabla. A continuación se muestra un ejemplo de una combinación cruzada de Transact-SQL: USE pubs SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers ORDER BY au_lname DESC

Utilizar autocombinaciones Una tabla se puede combinar consigo misma en una autocombinación. Por ejemplo, puede usar una autocombinación para averiguar qué autores de Oakland, California, viven en la misma área de código postal. Puesto que esta consulta conlleva una combinación de la tabla authors consigo misma, la tabla authors aparece en dos funciones. Para distinguir estas funciones, debe dar a la tabla authors dos alias distintos (au1 y au2) en la cláusula FROM. Estos alias se usan para calificar los nombres de columna del resto de la consulta. Éste es un ejemplo de la instrucción de autocombinación de Transact-SQL: USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' ORDER BY au1.au_fname ASC, au1.au_lname ASC

El siguiente es el conjunto de resultados: a u _ f n a me

a u _ l n a me

a u _ f n a me

a u _ l n a me

44


-------------------- ------------------- -------------------- --------- De an St r a i g ht De an St r a i g ht De an St r a i g h t Di r k St r i n ge r De an St r a i g ht Li vi a Ka r s e n Di r k St r i n ge r De an St r a i g ht Di r k St r i n g e r Di r k St r i n g e r Di r k St r i n g e r Li v i a Ka r s en Li vi a Ka r s e n De an St r a i g ht Li v i a Ka r s en Di r k St r i n g e r Li vi a Ka r s e n Li v i a Ka r s e n Ma r j o r i e Gr e en Ma r j o r i e Gr e en St e a r n s Ma c Fe a t h e r St e a r n s Ma c Fe a t h e r ( 1 1 r o w( s ) a f f e c t e d )

Para eliminar las filas del conjunto de resultados en las que los autores coinciden consigo mismos y eliminar las filas que son idénticas, salvo en que el orden de los autores está invertido, realice este cambio a la consulta de autocombinación de Transact-SQL: USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' AND au1.state = 'CA' AND au1.au_id < au2.au_id ORDER BY au1.au_lname ASC, au1.au_fname ASC El siguiente es el conjunto de resultados: a u _ f n a me a u _ l n a me a u _ f n a me a u _ l n a me ------------ ----------------- -------------------- -------------------De an St r a i g ht Di r k St r i n ge r De an St r a i g ht Li vi a Ka r s e n Di r k St r i n g e r Li v i a Ka r s en ( 3 r o w( s ) a f f e c t e d )

Ahora queda claro que Dean Straight, Dirk Stringer y Livia Karsen tienen todos el mismo código postal y viven en Oakland, California. 1.3.5.1.5 Utilizar alias de tablas

La legibilidad de una instrucción SELECT se puede mejorar si se proporciona un alias a la tabla, conocido también como una variable de intervalo o nombre de correlación. Un alias de tabla se puede asignar con o sin la palabra clave AS: table_name AS table alias table_name table_alias

En el ejemplo siguiente, el alias p se asigna a publishers. USE pubs SELECT p.pub_id, p.pub_name FROM publishers AS p


45

1.3.5.1.6 DISTINCT

La palabra clave DISTINCT elimina las filas duplicadas de un conjunto de resultados. Por ejemplo, hay muchas filas en la tabla Northwind Orders con el mismo valor para ShipCity. Para obtener una lista de los valores de ShipCity que hayan quitado los duplicados: SELECT DISTINCT ShipCity, ShipRegion FROM Orders ORDER BY ShipCity . 1.3.5.1.7 TOP n

La palabra clave TOP especifica que se devolverán las primeras n filas del conjunto de resultados. Si se especifica ORDER BY, las filas se seleccionan después de que el conjunto de resultados se haya ordenado. n es el número de filas que se va a devolver, a menos que se especifique la palabra clave PERCENT. PERCENT especifica que n es el porcentaje de filas del conjunto de resultados que se devuelve. Por ejemplo, esta instrucción SELECT devuelve las primeras diez ciudades, en secuencia alfabética, de la tabla Orders: SELECT DISTINCT TOP 10 ShipCity, ShipRegion FROM Orders ORDER BY ShipCity 1.3.5.2

Agregar filas con INSERT

La instrucción INSERT agrega una o más filas nuevas a una tabla. Tratada de forma simplificada, INSERT tiene la siguiente forma: INSERT [INTO] table_or_view [(column_list)] data_values

La instrucción hace que data_values se inserte como una o más filas en la tabla o vista que se nombra. El argumento column_list es una lista separada por comas de los nombres de tablas que se pueden utilizar para especificar las columnas para las que se suministran datos. Si no se especifica column_list, todas las columnas de la tabla o vista reciben datos. Cuando una column_list no enumera todas las columnas de la tabla o vista, se inserta un valor de NULL (o el valor predeterminado si se ha definido alguno para la columna) en aquellas columnas que no se hayan enumerado en la lista. Todas las columnas no especificadas en la lista de columnas deben permitir valores NULL o tener un valor predeterminado asignado. Las instrucciones INSERT no especifican valores para los siguientes tipos de columna porque Microsoft® SQL Server™ genera los valores de las columnas de estos tipos: Columnas con una propiedad IDENTITY que genera los valores para la columna. Columnas que tengan un valor predeterminado que utilice la función NEWID para generar un valor GUID exclusivo. Columnas calculadas.

46


Se trata de columnas virtuales que se definieron como una expresión calculada a partir de una o varias columnas en la instrucción CREATE TABLE, como: CREATE TABLE TestTable (ColA INT PRIMARY KEY, ColB INT NOT NULL, ColC AS (ColA + ColB) * 2)

Los valores de datos suministrados deben coincidir con la lista de columnas. El número de valores de datos debe ser el mismo que el número de columnas y el tipo de datos, precisión y escala de cada valor de datos debe coincidir con los de la columna correspondiente. Hay dos maneras de especificar los valores de datos: Utilizar una cláusula VALUES para especificar los valores de datos para una fila: INSERT INTO MyTable (PriKey, Description) VALUES (123, 'A description of part 123.')

Utilizar una subconsulta SELECT para especificar los valores de datos para una o más filas. INSERT INTO MyTable (PriKey, Description) SELECT ForeignKey, Description FROM SomeView

Insertar una fila mediante INSERT...Values La palabra clave VALUES especifica los valores de una fila de una tabla. Los valores se especifican como una lista separada por comas de expresiones escalares cuyos tipos de datos, precisión y escala deben ser iguales, o implícitamente convertibles, a la columna correspondiente de la lista de columnas. Si no se especifica una lista de columnas, los valores deben especificarse en la misma secuencia que las columnas de la tabla o vista. Ejemplo

Por ejemplo, para insertar un nuevo transportista en la tabla Shippers con la cláusula VALUES: INSERT INTO Northwind.dbo.Shippers (CompanyName, Phone) VALUES (N'Snowflake Shipping', N'(503)555-7233')

Para esta inserción se necesita una lista de columnas porque la columna ShipperID tiene la propiedad IDENTITY, con lo que no se pueden insertar valores en la misma.

Insertar filas mediante INSERT...SELECT La subconsulta SELECT de la instrucción INSERT se puede utilizar para agregar valores de una o más tablas o vistas. Utilizar una subconsulta SELECT permite además que se inserte más de una fila al mismo tiempo. Ejemplo

Esta instrucción INSERT inserta en una tabla aparte algunos de los datos de todas las filas de titles cuyo type sea cocina moderna: USE pubs INSERT INTO MyBooks SELECT title_id, title, type FROM titlesFROM titles WHERE type = 'mod_cook'


47

La lista de selección de la subconsulta debe coincidir con la lista de columnas de la instrucción INSERT. Si no se especifica ninguna lista de columnas, la lista de selección debe coincidir con las columnas de la tabla o vista en la que se inserta.

Insertar filas mediante SELECT INTO La instrucción SELECT INTO crea una nueva tabla y la llena con el conjunto de resultados de SELECT. La estructura de la nueva tabla se define con los atributos de las expresiones de la lista de selección Ejemplo SELECT Shippers.*, Link.Address, Link.City, Link.Region, Link.PostalCode INTO NewShippers FROM Shippers JOIN LinkServer.DB.dbo.Shippers AS Link ON (Shippers.ShipperID = Link.ShipperID)

SELECT INTO se puede emplear para combinar datos de varias tablas o vistas en una tabla. También se puede utilizar para crear una nueva tabla que contenga datos seleccionados de un servidor vinculado.

1.3.5.3

Cambiar datos con UPDATE

La instrucción UPDATE puede cambiar los valores de filas individuales, grupos de filas o todas las filas de una tabla o vista. También se puede utilizar para actualizar las filas de un servidor remoto con un nombre de servidor vinculado o las funciones OPENROWSET, OPENDATASOURCE y OPENQUERY, siempre que el proveedor de OLE DB utilizado para obtener acceso al servidor remoto admita actualizaciones. Una instrucción UPDATE que haga referencia a una tabla o vista sólo puede cambiar los datos de una tabla a la vez. La instrucción UPDATE tiene estas cláusulas principales: SET

Contiene una lista separada por comas de las columnas que deben actualizarse y el nuevo valor de cada columna, con el formato column_name = expression. El valor suministrado por las expresiones incluye elementos tales como constantes, valores seleccionados de una columna de otra tabla o vista, o valores calculados por una expresión compleja. FROM

Identifica las tablas o vistas que suministran los valores de las expresiones de la cláusula SET, y las condiciones de combinación opcional entre las tablas o vistas de origen. WHERE

Especifica la condición de búsqueda que define las filas de las tablas y vistas de origen que están calificadas para proporcionar valores para las expresiones de la cláusula SET. Esta instrucción de actualización aumenta el precio de todos los productos de Northwind de la categoría 2 en un 10 por ciento: UPDATE Northwind.dbo.Products SET UnitPrice = UnitPrice * 1.1

48


WHERE CategoryID = 2

Cambiar datos con la cláusula SET SET especifica las columnas que deben cambiarse y sus nuevos valores. Los valores de las columnas especificadas se actualizan con los valores proporcionados en SET en todas las filas que coincidan con la condición de búsqueda de la cláusula WHERE. Si no se especifica una cláusula WHERE, se actualizan todas las filas. Por ejemplo, si todas las casas de publicación de la tabla publishers trasladan sus oficinas principales a Atlanta, Georgia, se utilizaría esta instrucción UPDATE: UPDATE publishers SET city = 'Atlanta', state = 'Georgia'

En una actualización se puede calcular y usar los valores de las columnas calculadas. Por ejemplo, para doblar todos los precios de la tabla titles, la columna price de la tabla titles se puede establecer a price * 2. Ejemplo

Las expresiones utilizadas en la cláusula SET pueden ser también subconsultas que sólo devuelven un valor; por ejemplo, si la base de datos Northwind tuviera una tabla OrderSummary: UPDATE OrderSummary SET Last30Days = (SELECT SUM(OrdDet.UnitPrice * OrdDet.Quantity) FROM [Order Details] AS OrdDet JOIN Orders AS Ord ON (OrdDet.OrderID = Ord.OrderID AND Ord.OrderDate > DATEADD(dd,-30,GETDATE()) ))

Cambiar datos con la cláusula WHERE La cláusula WHERE realiza dos funciones: Especifica las filas que deben actualizarse. Indica las filas de las tablas de origen que se califican para suministrar los valores de la actualización si se especifica también una cláusula FROM. Si no especifica una cláusula WHERE, se actualizan todas las filas de la tabla. Ejemplo

Esta instrucción UPDATE implementa un cambio de nombre para uno de los transportistas (shippers): UPDATE Northwind.dbo.Shippers SET CompanyName = 'United Shippers' WHERE CompanyName = 'United Packages'

Cambiar datos con la cláusula FROM Utilice la cláusula FROM para extraer datos de una o más tablas o vistas, e insertarlos en la tabla que desea actualizar. Por ejemplo, cuando el autor Dirk Stringer consigue un contrato, a su libro, The Psychology of Computer Cooking, se le asigna un número de identificación del título en la tabla titles. La fila de Dirk de la tabla titleauthor se puede actualizar si se agrega un número de identificación de título para este último libro.


49

Ejemplo

En el ejemplo siguiente se actualiza la fila de Dirk Stringer de la tabla titleauthor para agregar el número de identificación de título de su último libro: UPDATE titleauthor SET title_id = titles.title_id FROM titles INNER JOIN titleauthor ON titles.title_id = titleauthor.title_id INNER JOIN authors ON titleauthor.au_id = authors.au_id WHERE titles.title = 'Net Etiquette' AND au_lname = 'Locksley'

Cambiar datos con un cursor Cuando utilice un cursor de servidor de Transact-SQL, podrá actualizar la fila actual con una instrucción UPDATE que incluya una cláusula WHERE CURRENT OF. Los cambios realizados con esta cláusula afectan sólo a la fila en la que esté situado el cursor. Cuando un cursor se basa en una combinación, sólo se modifica el table_name especificado en la instrucción UPDATE. Las demás tablas que participan en el cursor no se ven afectadas. USE Northwind GO DECLARE abc CURSOR FOR SELECT CompanyName FROM Shippers OPEN abc GO FETCH NEXT FROM abc GO UPDATE Shippers SET CompanyName = N'Speedy Express, Inc.' WHERE CURRENT OF abc GO CLOSE abc DEALLOCATE abc GO 1.3.5.4

Eliminar filas con DELETE

La instrucción DELETE quita una o varias filas de una tabla o vista. Una forma simplificada de la sintaxis de DELETE es: DELETE table_or_view FROM table_sources WHERE search_condition

table_or_view nombra la tabla o vista de la que se van a eliminar las filas. Se eliminan todas las filas de table_or_view que reúnan los requisitos de la condición de búsqueda de la cláusula WHERE. Si no se especifica una cláusula WHERE, se eliminan todas las filas de table_or_view. La cláusula FROM especifica tablas o vistas adicionales y condiciones de combinación que pueden ser utilizadas por los predicados de la condición de búsqueda de la cláusula WHERE para calificar las filas que deben ser eliminadas de table_or_view. Las filas no se eliminan de las tablas mencionadas en la cláusula FROM, sólo de la tabla mencionada en table_or_view. Cualquier tabla de la que se hayan quitado todas las filas sigue permaneciendo en la

50


base de datos. La instrucción DELETE sólo elimina filas de la tabla, y ésta se debe quitar de la base de datos mediante la instrucción DROP TABLE.

Para eliminar filas con DELETE Esta secuencia de comandos muestra las tres instrucciones DELETE necesarias para eliminar las filas asociadas con los productos suministrados por la empresa llamada Lyngbysild de la base de datos Northwind. No sería una operación empresarial típica porque conlleva la eliminación de líneas de pedidos existentes, pero muestra una serie de eliminaciones de distinta complejidad. USE NorthwindUSE Northwind GO DELETE [Order Details] FROM Suppliers, Products WHERE Products.SupplierID = Suppliers.SupplierID AND Suppliers.CompanyName = 'Lyngbysild' AND [Order Details].ProductID = Products.ProductID GO DELETE Products FROM Suppliers WHERE Products.SupplierID = Suppliers.SupplierID AND Suppliers.CompanyName = 'Lyngbysild' GO DELETE Suppliers WHERE CompanyName = 'Lyngbysild' GO

Eliminar filas de conjuntos de resultados Las secuencias de comandos, procedimientos almacenados y desencadenadores de Transact-SQL pueden utilizar la cláusula WHERE CURRENT OF en una instrucción DELETE para eliminar la fila de cursor en la que se encuentran posicionados, por ejemplo: DECLARE abc CURSOR FOR SELECT * FROM MyTable OPEN abc FETCH NEXT FROM abc DELETE MyTable WHERE CURRENT OF abc CLOSE abc DEALLOCATE abc

Eliminar todas las filas con TRUNCATE TABLE La instrucción TRUNCATE TABLE es un método rápido y no registrado para eliminar todas las filas de una tabla. Casi siempre es más rápido que una instrucción DELETE sin condiciones porque DELETE registra cada eliminación de fila y TRUNCATE TABLE registra sólo la cancelación de la asignación de páginas completas de datos. TRUNCATE TABLE libera inmediatamente todo el espacio ocupado por los datos e índices de esa tabla. También se liberan las páginas de distribución de todos los índices. Como en el caso de DELETE, la definición de una tabla vaciada con TRUNCATE TABLE permanece en la base de datos, junto con sus índices y sus objetos asociados. Para quitar la definición de la tabla debe utilizarse la instrucción DROP TABLE.


1.3.6

51

Trabajando con fechas

SQL Server tiene los tipos de datos datetime y smalldatetime para almacenar datos de fecha y hora. No hay tipos de datos diferentes de hora y fecha para almacenar sólo horas o sólo fechas. Si sólo se especifica una hora cuando se establece un valor datetime o smalldatetime, el valor predeterminado de la fecha es el 1 de enero de 1900. Si sólo se especifica una fecha, la hora será, de forma predeterminada, 12:00 a.m. (medianoche), es decir, las 00:00.

Tipo de datos Datetime. Datos de fecha y hora comprendidos entre el 1 de enero de 1753 y el 31 de diciembre de 9999, con una precisión de un trescientosavo de segundo, o 3,33 milisegundos. SQL Server rechaza todos los valores que no puede reconocer como fechas entre 1753 y 9999.

Tipo de datos Smalldatetime. Datos de fecha y hora desde el 1 de enero de 1900 al 6 de junio de 2079, con precisión de minutos. Entonces si se utiliza un valor smalldatetime los segundos y milisegundos son siempre 0.

Diferencia entre Datetime y Smalldatetime. SQL Server almacena internamente los valores de tipo de datos datetime como enteros de 4 bytes y los valores smalldatetime como enteros de 2 bytes.

Funciones de fecha y hora (Transact-SQL) Estas funciones escalares realizan una operación sobre un valor de fecha y hora de entrada, y devuelven un valor de cadena, numérico o de fecha y hora:

DATEADD DATEDIFF DATENAME DATEPART GETDATE DAY MONTH YEAR

Trabajando con fechas ...

Con algunos pequeños ejemplos trataremos de resolver los mayores problemas para trabajar con estos tipos de datos. Para ello utilizaremos las funciones CAST y CONVERT. Separando Fecha y Hora.

52


Declare @Fecha datetime Set @Fecha = Getdate() Select Convert(Char(10), @Fecha,112) As SoloFecha, Convert(Char(8), @Fecha, 108) As S SoloFecha SoloHora ---------- -------20010803 07:35:02 (1 row(s) affected)

Otra forma de conseguir el mismo resultado: Declare @Fecha datetime Set @Fecha = Getdate() SELECT Convert(varchar, @Fecha, 3) AS SoloFecha, Convert(varchar, @Fecha, 8)

Operaciones con Fechas

(diferencia entre dos fechas). Obtener diferencia de meses, dias, minutos, etc. entre dos fechas. Para realizar operaciones entre dos fechas MSSQL tiene la función DATEDIFF. Veamos algunos ejemplos de cómo utilizarla: declare @FechaIngreso datetime declare @FechaEgreso datetime select @FechaIngreso = '19981231 15:15' select @FechaEgreso = '20021005 10:10' Select DATEDIFF(dd, @FechaIngreso, @FechaEgreso) AS Dias, DATEDIFF(mm, @FechaIngreso, @FechaEgreso) AS Meses, DATEDIFF(mi, @FechaIngreso, @FechaEgreso) AS Minutos

Para obtener otras diferencias podemos recurrir a la siguiente tabla: Parte de la fecha Abreviaturas año

aa, aaaa

trimestre

tt, t

mes

mm, m

dia del año

da, a

dia

dd, d

semana

sm, ss

hora

Hs

minuto

mi, n

segundo

ss, s

milisegundo

Ms

Otro ejemplo de DATEDIFF en donde recuperados los datos de la ultima semana


53

partiendo de la fecha del día:

SELECT TusDatos FROM TuTabla WHERE DATEDIFF(dd, TuFecha, GetDate()) <= 7

Continuando con las operaciones con las fechas, veamos como podemos hacer para sumar, restar, dí select convert(varchar(12), DATEADD(month, -1, getdate()), 106) as 'un mes atrás' select convert(varchar(12), DATEADD (week, -1, getdate()), 106) as 'una semana atrás' select convert(varchar(12), DATEADD (day, -1, getdate()), 106) as 'ayer'

1.3.7

Elementos de sintaxis de Transact-SQL

Transact-SQL tiene varios elementos de sintaxis que son utilizados por, o influyen en, la mayor parte de las instrucciones: Identificadores

Son los nombres de objetos como tablas, vistas, columnas, bases de datos y servidores. Tipos de datos

Definen el tipo de datos que contienen los objetos de datos como columnas, variables y parámetros. La mayor parte de las instrucciones de Transact-SQL no hacen referencia explícitamente a tipos de datos, sino que sus resultados están influidos por las interacciones entre los tipos de datos de los objetos a los que se hace referencia en la instrucción. Funciones

Son elementos de sintaxis que toman cero, uno o más valores de entrada y devuelven un valor escalar o un conjunto de valores en forma de tabla. Ejemplos de funciones son la función SUM para sumar varios valores, la función DATEDIFF para determinar el número de unidades de tiempo que separan dos fechas, la función @@SERVERNAME para obtener el nombre de una instancia de Microsoft® SQL Server™. Las funciones tambien pueden ser definidas por el usuario. Expresiones

Son unidades de sintaxis que Microsoft SQL Server puede resolver en valores únicos. Ejemplos de expresiones son las constantes, las funciones que devuelven un valor único, una referencia a una columna o una variable. Operadores

Funcionan con una o más expresiones individuales para formar una expresión más compleja. Por ejemplo, combinar el signo menos (-) con la constante 12 da como resultado la constante -12. El signo de multiplicación (*) de la expresión PriceColumn *1.1 aumenta el precio en un 10 por ciento.

54


Comentarios

Son fragmentos de texto insertado en instrucciones o secuencias de comandos de Transact-SQL para explicar el objetivo de la instrucción. Los comentarios no son ejecutados por SQL Server. Palabras clave reservadas

Son palabras reservadas que utiliza SQL Server y no deben emplearse como nombres de objetos de una base de datos. Valores NULL.

Los valores NULL son valores desconocidos. Puede usar valores NULL para indicar que la información se proporcionará posteriormente. Por ejemplo, si la persona de contacto de la empresa Leka Trading cambia y no se conoce la nueva persona de contacto, podría indicar que su nombre es desconocido con un valor NULL. Lenguaje de control de flujo.

El lenguaje de control de flujo permite al código del programa emprender una acción en función de que se cumpla o no alguna condición. Por ejemplo, IF (si) la cantidad de productos pedida es igual o menor que la cantidad de productos almacenada actualmente, THEN (entonces) necesitamos pedir más productos. Esto es fundamental en la creacion de Stored Procedures y Triggers.

Utilizar variables y parámetros Transact-SQL tiene varias formas de pasar datos entre instrucciones Transact-SQL. Entre éstas se encuentran: Variables locales de Transact-SQL

Una variable de Transact-SQL es un objeto de lotes y secuencias de comandos Transact-SQL que puede contener un valor de datos. Una vez que la variable se ha declarado o definido, una instrucción Transact-SQL de un lote puede establecer un valor para la variable y otra instrucción posterior del lote puede obtener el valor de la variable. Por ejemplo: DECLARE @EmpIDVar INT SET @EmpIDVar = 1234 SELECT * FROM Employees WHERE EmployeeID = @EmpIDVar

Parámetros de Transact-SQL

Un parámetro es un objeto usado para pasar datos entre un procedimiento almacenado y el lote o secuencia de comandos que lo ejecuta. Los parámetros pueden ser de entrada o de salida. Por ejemplo: CREATE PROCEDURE ParmSample @EmpIDParm INT AS


55

SELECT * FROM Employees WHERE EmployeeID = @EmpIDParm GO EXEC ParmSample @EmpIDParm = 1234 GO

Control de flujo Transact-SQL proporciona palabras especiales que forman parte del llamado lenguaje de control de flujo con el que se controla el flujo de la ejecución de instrucciones Transact-SQL, bloques de instrucciones y procedimientos almacenados. Estas palabras se pueden usar en las instrucciones Transact-SQL ad hoc, en los lotes y en los procedimientos almacenados. Sin el lenguaje de control de flujo, las instrucciones Transact-SQL independientes se ejecutan secuencialmente, tal como se producen. El lenguaje de control de flujo permite que las instrucciones estén conectadas, se relacionen entre ellas y se hagan interdependientes con construcciones similares a las de la programación. BEGIN...END

Las instrucciones BEGIN y END se usan para agrupar varias instrucciones Transact-SQL en un bloque lógico. Use las instrucciones BEGIN y END en cualquier parte donde una instrucción de control de flujo deba ejecutar un bloque con dos o más instrucciones Transact-SQL. Por ejemplo, cuando una instrucción IF controla la ejecución de una única instrucción Transact-SQL, no se necesita ninguna instrucción BEGIN o END: IF (@@ERROR <> 0) SET @ErrorSaveVariable = @@ERROR

Si @@ERROR es 0, sólo se salta la instrucción individual SET. Use las instrucciones BEGIN y END para hacer que la instrucción IF salte un bloque de instrucciones cuando el resultado de la evaluación sea FALSE (falso): IF (@@ERROR <> 0) BEGIN SET @ErrorSaveVariable = @@ERROR PRINT 'Error encountered, ' + CAST(@ErrorSaveVariable AS VARCHAR(10)) END

Las instrucciones BEGIN y END deben usarse como un par: no se puede usar una sin la otra. La instrucción BEGIN aparece sola en una línea, seguida de un bloque de instrucciones Transact-SQL. Finalmente, la instrucción END aparece sola en una línea para indicar el fin del bloque. Las instrucciones BEGIN y END se usan cuando: Es necesario que un bucle WHILE incluya un bloque de instrucciones. Es necesario que un elemento de una función CASE incluya un bloque de instrucciones. Es necesario que una cláusula IF o ELSE incluya un bloque de instrucciones. · · ·

56


IF...ELSE

La instrucción IF se usa para probar una condición. El control de flujo resultante depende de si se especifica o no la instrucción ELSE opcional: IF especificado sin ELSE: Cuando la instrucción IF da como resultado TRUE (verdadero), se ejecuta la instrucción o bloque de instrucciones que sigue a la instrucción IF. Cuando la instrucción IF da como resultado FALSE (falso), se salta la instrucción o bloque de instrucciones que sigue a la instrucción IF. IF especificado con ELSE: Cuando la instrucción IF da como resultado TRUE, se ejecuta la instrucción o bloque de instrucciones que sigue a la instrucción IF. A continuación, el control salta al punto posterior a la instrucción o bloque de instrucciones que sigue a ELSE. Cuando la instrucción IF da como resultado FALSE, se salta la instrucción o bloque de instrucciones que sigue a IF y se ejecuta la instrucción o bloque de instrucciones que sigue a la instrucción ELSE opcional. ·

·

Por ejemplo, si un procedimiento almacenado ha estado guardando los códigos de error devueltos po IF (@ErrorSaveVariable <> 0) BEGIN PRINT 'Errors encountered, rolling back.' PRINT 'Last error encountered: ' + CAST(@ErrorSaveVariable AS VARCHAR(10)) ROLLBACK END ELSE BEGIN PRINT 'No Errors encountered, committing.' COMMIT END RETURN @ErrorSaveVariable

RETURN

La instrucción RETURN termina incondicionalmente una consulta, procedimiento almacenado o lote. Ninguna de las instrucciones de un procedimiento almacenado o lote que siga a la instrucción RETURN se ejecutará. Cuando se usa en un procedimiento almacenado, la instrucción RETURN puede especificar un valor entero para devolver a la aplicación, lote o procedimiento que realiza la llamada. Si no se especifica ningún valor en la instrucción RETURN, un procedimiento almacenado devuelve el valor 0. La mayor parte de los procedimientos almacenados siguen la convención de usar el código de retorno para indicar si el procedimiento almacenado se ha ejecutado correctamente o no. Los procedimientos almacenados devuelven el valor 0 cuando no se encontraron errores. Cualquier valor distinto de cero indica que se produjo un error. Por ejemplo: USE Northwind GO -- Create a procedure that takes one input parameter -- and returns one output parameter and a return code. CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT, @MaxQuantity INT OUTPUT AS -- Declare and initialize a variable to hold @@ERROR.


57

DECLARE @ErrorSave INT SET @ErrorSave = 0 -- Do a SELECT using the input parameter. SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = @EmployeeIDParm -- Save any nonzero @@ERROR value. IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR -- Set a value in the output parameter. SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details] IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR -- Returns 0 if neither SELECT statement had -- an error, otherwise returns the last error. RETURN @ErrorSave GO

Un lote o procedimiento almacenado de Transact-SQL que ejecuta un procedimiento almacenado puede recuperar el código de retorno en una variable de tipo entero: DECLARE @ReturnStatus INT DECLARE @MaxQtyVariable INT EXECUTE @ReturnStatus = SampleProcedure @EmployeeIDParm = 9, @MaxQtyVariable = @MaxQu -- Show the values returned. PRINT ' ' PRINT 'Return code = ' + CAST(@ReturnStatus AS CHAR(10)) PRINT 'Maximum Quantity = ' + CAST(@MaxQtyVariable AS CHAR(10)) GO

WHILE...BREAK o CONTINUE

La instrucción WHILE repite una instrucción o bloque de instrucciones mientras la condición especificada siga siendo verdadera. Con WHILE se usan normalmente dos instrucciones Transact-SQL: BREAK o CONTINUE. La instrucción BREAK sale del bucle WHILE más profundo, y la instrucción CONTINUE reinicia un bucle WHILE. Un programa podría ejecutar una instrucción BREAK si, por ejemplo, no hubiera más filas que procesar. Una instrucción CONTINUE podría ejecutarse si, por ejemplo, la ejecución del código debiera continuar. Nota: Si una instrucción SELECT se usa como condición de la instrucción WHILE, la instrucción SELECT debe ir entre paréntesis. En este ejemplo se usa una instrucción WHILE para controlar el número de recuperaciones realizadas: USE Northwind GO DECLARE abc CURSOR FOR

58


SELECT * FROM Shippers OPEN abc FETCH NEXT FROM abc WHILE (@@FETCH_STATUS = 0) FETCH NEXT FROM abc CLOSE abc DEALLOCATE abc GO

CASE

La función CASE es una expresión especial de Transact-SQL que permite que se muestre un valor alternativo dependiendo del valor de una columna. Este cambio es temporal, con lo que no hay cambios permanentes en los datos. Por ejemplo, la función CASE puede mostrar California en un conjunto de resultados de una consulta de las filas que tengan el valor CA en la columna state. La función CASE está compuesta de: La palabra clave CASE. El nombre de columna que se va a transformar. Cláusulas WHEN que especifican las expresiones que se van a buscar y cláusulas THEN que especifican las expresiones que las van a reemplazar. La palabra clave END. · · ·

·

Una cláusula AS opcional que define un alias de la función CASE. En este ejemplo se muestra, en el conjunto de resultados de la consulta, el nombre completo del estado en el que vive cada autor: SELECT au_fname, au_lname, CASE state WHEN 'CA' THEN 'California' WHEN 'KS' THEN 'Kansas' WHEN 'TN' THEN 'Tennessee' WHEN 'OR' THEN 'Oregon' WHEN 'MI' THEN 'Michigan' WHEN 'IN' THEN 'Indiana' WHEN 'MD' THEN 'Maryland' WHEN 'UT' THEN 'Utah' END AS StateName FROM pubs.dbo.authors ORDER BY au_lname

1.3.8

Consultas: Nivel avanzado

Usar funciones de agregado en la lista de selección Agrupar filas con GROUP BY Combinar resultados con UNION 1.3.8.1

Usar funciones de agregado en la lista de selección

Las funciones de agregado (SUM, AVG, COUNT, COUNT(*), MAX y MIN) generan valores de resumen en los conjuntos de resultados de las consultas. Una función de agregado (con la excepción de COUNT(*)) procesa todos los valores seleccionados en una única


59

columna para generar un único resultado. Las funciones de agregado se pueden aplicar a todas las filas de una tabla, a un subconjunto de la tabla especificado por una cláusula WHERE o a uno o varios grupos de filas de la tabla. Cuando se aplica una función de agregado, se genera un valor individual por cada conjunto de filas.

COUNT(*) COUNT(*) no requiere el parámetro expression porque no utiliza información acerca de ninguna columna concreta. Cuenta el número total de filas que cumplen las calificaciones de la consulta. COUNT(*) devuelve el número de filas que coinciden con las condiciones de búsqueda especificadas en la consulta, sin eliminar los duplicados. Cuenta todas las filas, incluidas las que contienen valores NULL. En esta consulta se busca el número total de libros de la tabla titles: USE pubs SELECT COUNT(*) FROM titles

El siguiente es el conjunto de resultados: ------------------ 1 8 ( 1 r o w( s ) a f f e c t e d )

COUNT(*) se puede combinar con otras funciones de agregado. Esta consulta muestra COUNT(*) combinado con una función AVG en la que ambas funciones de agregado sólo agregan los datos de las filas que cumplen la condición de búsqueda de la cláusula WHERE. USE pubs SELECT COUNT(*), AVG(price) FROM titles WHERE advance > $1000

El siguiente es el conjunto de resultados: ----------- -----15 14. 42

DISTINCT La palabra clave DISTINCT es opcional con SUM, AVG y COUNT. Cuando se usa DISTINCT, los valores duplicados se eliminan antes de que se calcule la suma, el promedio o el recuento. Si usa DISTINCT, la expresión sólo debe constar del nombre de una columna. No puede incluir una expresión aritmética. En la consulta siguiente se devuelven los precios promedio de los libros de empresa (sin los valores duplicados). USE pubs SELECT AVG(DISTINCT price) FROM titles WHERE type = 'business'

60


El siguiente es el conjunto de resultados: ------------------ 1 4. 6 4 ( 1 r o w( s ) a f f e c t e d )

Sin DISTINCT, la función AVG busca el precio promedio de todos los títulos de empresa. USE pubs SELECT AVG(price) FROM titles WHERE type = 'business

El siguiente es el conjunto de resultados: ------------------ 1 3. 7 3 ( 1 r o w( s ) a f f e c t e d )

1.3.8.2

Agrupar filas con GROUP BY

La cláusula GROUP BY se usa para producir valores de agregado para cada fila del conjunto de resultados. Cuando se usan sin una cláusula GROUP BY, las funciones de agregado sólo devuelven un valor de agregado para la instrucción SELECT. En este ejemplo se devuelve el número de unidades vendidas de cada producto de la categoría 2: USE Northwind SELECT OrdD.ProductID AS ProdID, SUM(OrdD.Quantity) AS AmountSold FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID

El siguiente es el conjunto de resultados: Pr o d I D Amo u n t So l d ----------- ----------3 328 4 453 5 298 6 301 8 372 15 122 44 601 61 603 63 445 65 745 66 239 77 791 ( 1 2 r o w( s ) a f f e c t e d )

A continuación de las palabras clave GROUP BY se coloca una lista de columnas,


61

conocidas como las columnas de agrupamiento. La cláusula GROUP BY restringe las filas del conjunto de resultados; sólo hay una fila por cada valor distinto de la columna o columnas de agrupamiento. Cada fila del conjunto de resultados contiene los datos de resumen relacionados con el valor específico de sus columnas de agrupamiento.

GROUP BY y la cláusula WHERE Puede usar una cláusula WHERE en una consulta que contenga una cláusula GROUP BY. Las filas que no cumplan las condiciones de la cláusula WHERE son eliminadas antes de que se realice el agrupamiento. Por ejemplo: USE pubs SELECT type, AVG(price) FROM titles WHERE advance > $5000 GROUP BY type

El siguiente es el conjunto de resultados: t y pe -----------b us i n es s mo d _ c o o k p o pu l a r _ c o mp p s y c ho l o gy t r a d_ c oo k

-------------------------2. 99 2. 99 21. 48 1 4. 3 0 1 7. 9 7

( 5 r o w( s ) a f f e c t e d )

Sólo las filas con adelantos superiores a 5.000 dólares se incluyen en los grupos que se muestran en los resultados de la consulta.

Elegir filas con la cláusula HAVING La cláusula HAVING establece las condiciones de la cláusula GROUP BY de la misma forma que WHERE interactúa con SELECT. Mientras que las condiciones de búsqueda de WHERE se aplican antes de que se produzca la operación de agrupamiento, las condiciones de búsqueda de HAVING se aplican después. La sintaxis de la cláusula HAVING es similar a la de la cláusula WHERE, con la diferencia de que HAVING puede contener funciones de agregado. Las cláusulas HAVING pueden hacer referencia a cualquiera de los elementos que aparecen en la lista de selección. En esta consulta se buscan publicadores cuyas ventas anuales hasta la fecha hayan sido superiores a 40.000 dólares. USE pubs SELECT pub_id, total = SUM(ytd_sales) FROM titles GROUP BY pub_id HAVING SUM(ytd_sales) > 40000

El siguiente es el conjunto de resultados: pub_i d t ot al ------ ----------0877 44219 ( 1 r o w( s ) a f f e c t e d )

62


Para asegurarse de que hay como mínimo seis libros involucrados en los cálculos para cada publicador, este ejemplo utiliza HAVING COUNT(*) > 5 para eliminar los publicadores que devuelven totales con menos de seis libros. USE pubs SELECT pub_id, total = SUM(ytd_sales) FROM titles GROUP BY pub_id HAVING COUNT(*) > 5

El siguiente es el conjunto de resultados: pub_i d t ot al ------ ----------0877 44219 1389 24941 ( 2 r o w( s ) a f f e c t e d )

La comprensión de la secuencia correcta en la que se aplican las cláusulas WHERE, GROUP BY y HAVING ayuda a codificar consultas que sean eficientes: La cláusula WHERE se utiliza para filtrar las filas que resultan de las operaciones especificadas en la cláusula FROM. La cláusula GROUP BY se usa para agrupar el resultado de la cláusula WHERE. La cláusula HAVING se usa para filtrar las filas del resultado agrupado. Es más eficiente especificar en la cláusula WHERE las condiciones de búsqueda que se pueden establecer antes o después de la operación de agrupamiento. Esto reduce el número de filas que tienen que agruparse. Las únicas condiciones de búsqueda que se deben especificar en la cláusula HAVING son aquellas que se deben aplicar una vez que se hayan realizado las operaciones de agrupamiento. El optimizador de consultas de Microsoft® SQL Server™ 2000 puede tratar la mayor parte de estas condiciones. Si el optimizador determina que una condición de búsqueda HAVING se puede aplicar antes de la operación de agrupamiento, lo hará. Puede que el optimizador no reconozca todas las condiciones de búsqueda de HAVING que se pueden aplicar antes de la operación de agrupamiento. Se recomienda colocar todas estas condiciones de búsqueda en la cláusula WHERE en lugar de en la cláusula HAVING. En la consulta siguiente se muestra la cláusula HAVING con una función de agregado. Agrupa por tipos las filas de la tabla titles y elimina los grupos que incluyen solamente un libro: USE pubs SELECT type FROM titles GROUP BY type HAVING COUNT(*) > 1

El siguiente es el conjunto de resultados:


63

t y pe ------------------ b us i n es s mod_c ook p o pu l a r _ c o mp p s y c ho l o gy t r a d_ c oo k ( 5 r o w( s ) a f f e c t e d )

Cuando en HAVING se incluyen varias condiciones, se combinan mediante AND, OR o NOT. En el ejemplo siguiente se muestra cómo agrupar titles por publicadores, incluyendo sólo aquellos cuyo número de identificación sea superior a 0800, hayan pagado más de 15.000 dólares en adelantos totales y vendan libros por un promedio de menos de 20 dólares. SELECT pub_id, SUM(advance) AS AmountAdvanced, AVG(price) AS AveragePrice FROM pubs.dbo.titles WHERE pub_id > '0800' GROUP BY pub_id HAVING SUM(advance) > $15000 AND AVG(price) < $20

ORDER BY se puede usar para ordenar el resultado de una cláusula GROUP BY. En este ejemplo se muestra la utilización de la cláusula ORDER BY para definir el orden en el que se devuelven las filas de una cláusula GROUP BY: SELECT pub_id, SUM(advance) AS AmountAdvanced, AVG(price) AS AveragePrice FROM pubs.dbo.titles WHERE pub_id > '0800' AND price >= $5 GROUP BY pub_id HAVING SUM(advance) > $15000 AND AVG(price) < $20 ORDER BY pub_id DESC

1.3.8.3

Combinar resultados con UNION

El operador UNION permite combinar los resultados de varias instrucciones SELECT en un único conjunto de resultados. Todos los conjuntos de resultados combinados mediante UNION deben tener la misma estructura. Deben tener el mismo número de columnas y las columnas del conjunto de resultados correspondientes deben tener tipos de datos compatibles Instrucciones para utilizar UNION

Cuando utilice operadores UNION, siga estas directrices: ·

Todas las listas de selección de las instrucciones que se combinan con UNION deben tener el mismo número de expresiones (nombres de columna, expresiones aritméticas, funciones de agregado, etc.).

64


·

·

1.3.9

Las columnas correspondientes del conjunto de resultados combinado con UNION, o cualquier subconjunto de columnas que se use en consultas individuales, deben ser del mismo tipo de datos, tener la posibilidad de convertir los tipos de datos de forma implícita o suministrar una conversión explícita. Por ejemplo, no es posible utilizar UNION entre una columna de un tipo de datos datetime y una de tipo de datos binary, a menos que se suministre una conversión explícita, mientras sí que se puede aplicar UNION entre una columna de tipo de datos money y una del tipo int porque pueden convertirse implícitamente. Las columnas del conjunto de resultados de las instrucciones individuales que se combinan con UNION deben estar en el mismo orden, porque UNION compara las columnas de una en una en el orden que se indica en las consultas individuales.

Subconsultas

Una subconsulta es una consulta SELECT que devuelve un valor único y está anidada en una instrucción SELECT, INSERT, UPDATE o DELETE, o dentro de otra subconsulta. Una subconsulta se puede utilizar en cualquier parte en la que se permita una expresión. En este ejemplo se utiliza una subconsulta como una expresión de columna llamada MaxUnitPrice en una instrucción SELECT. SELECT Ord.OrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM Northwind.dbo.[Order Details] AS OrdDet WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice FROM Northwind.dbo.Orders AS Ord

Se llama también subconsulta a una consulta o selección interna, mientras que la instrucción que contiene una subconsulta también es conocida como consulta o selección externa. Muchas de las instrucciones Transact-SQL que incluyen subconsultas se pueden formular también como combinaciones. Otras preguntas se pueden formular sólo con subconsultas. En Transact-SQL, normalmente no hay diferencias de rendimiento entre una instrucción que incluya una subconsulta y una versión semánticamente equivalente que no lo haga. Sin embargo, en algunos casos en los que se debe comprobar la existencia de algo, una combinación produce mejores resultados. De lo contrario, la consulta anidada debe ser procesada para cada resultado de la consulta externa con el fin de asegurar la eliminación de los duplicados. En tales casos, la utilización de combinaciones produciría los mejores resultados. Esto es un ejemplo que muestra una subconsulta SELECT y una combinación SELECT que devuelve el mismo conjunto de resultados: / * SEL ECT s t a t e me n t

bui l t

us i ng a s ubquer y .

* /

SELECT ProductName FROM Northwind.dbo.Products WHERE UnitPrice = (SELECT UnitPrice FROM Northwind.dbo.Products WHERE ProductName = 'Sir Rodney''s Scones') / * SEL ECT s t a t e me n t b u i l t u s i n g a j o i n t h a t t h e s ame r e s ul t s e t . * /

r e t u r n s

SELECT Prd1.ProductName FROM Northwind.dbo.Products AS Prd1 JOIN Northwind.dbo.Products AS Prd2


65

ON (Prd1.UnitPrice = Prd2.UnitPrice) WHERE Prd2.ProductName = 'Sir Rodney''s Scones'

La consulta SELECT de una subconsulta se incluye siempre entre paréntesis. No puede incluir una cláusula COMPUTE o FOR BROWSE y sólo puede incluir una cláusula ORDER BY cuando se especifica también una cláusula TOP. Una subconsulta puede anidarse dentro de la cláusula WHERE o HAVING de una instrucción externa SELECT, INSERT, UPDATE o DELETE, o dentro de otra subconsulta. Se puede disponer de hasta 32 niveles de anidamiento, aunque el límite varía dependiendo de la memoria disponible y de la complejidad del resto de las expresiones de la consulta. Las consultas individuales puede que no admitan anidamientos por encima de 32 niveles. Una subconsulta puede aparecer en cualquier parte en la que se pueda usar una expresión, si devuelve un valor individual. Si una tabla aparece sólo en una subconsulta y no en la consulta externa, las columnas de esa tabla no se pueden incluir en el resultado (la lista de selección de la consulta externa).

Tipos de Subconsultas Las instrucciones que incluyen una subconsulta normalmente tienen uno de estos formatos: WHERE expression [NOT] IN (subquery) WHERE expression comparison_operator [ANY | ALL] (subquery) WHERE [NOT] EXISTS (subquery) · · ·

Hay tres tipos básicos de subconsultas: Operan en listas incorporadas con IN, o en aquéllas que modificó un operador de comparación mediante ANY o ALL. Se introducen con un operador de comparación sin modificar y deben devolver un valor individual. Son pruebas de existencia que se introducen con EXISTS. ·

·

·

Reglas de las subconsultas Una subconsulta está sujeta a una serie de limitaciones: La lista de selección de una subconsulta presentada con un operador de comparación sólo puede incluir una expresión o nombre de columna (excepto EXISTS e IN, que operan en SELECT * o en una lista, respectivamente). Si la cláusula WHERE de una consulta externa incluye un nombre de columna, debe ser compatible en cuanto a combinación con la columna nombrada en la lista de selección de la subconsulta. Los tipos de datos ntext, text e image no están permitidos en las listas de selección de subconsultas. Puesto que deben devolver un valor individual, las subconsultas presentadas por un operador de comparación sin modificar (uno no seguido de la palabra clave ANY o ALL) no pueden incluir cláusulas GROUP BY y HAVING. La palabra clave DISTINCT no se puede usar con subconsultas que incluyan GROUP BY. No se pueden especificar las cláusulas COMPUTE e INTO. Sólo se puede especificar ORDER BY si se especifica también TOP. Una vista creada con una subconsulta no se puede actualizar. La lista de selección de una subconsulta presentada con EXISTS consta, por convención, de un asterisco (*) en lugar de un nombre de columna individual. Las ·

·

·

·

· · · · ·

66


reglas de una subconsulta presentada con EXISTS son idénticas a las de una lista de selección estándar porque una subconsulta presentada con EXISTS constituye una prueba de existencia y devuelve TRUE o FALSE en lugar de datos. Temas Relacionados:

Subconsultas con Alias Subconsultas con IN Subconsultas con NOT IN Subconsultas en UPDATE, DELETE e INSERT Subconsultas con operadores de comparación Subconsultas con EXISTS Subconsultas con NOT EXISTS Subconsultas usadas en lugar de una expresión Múltiples niveles de anidamiento Procesamiento condicional con CASE 1.3.9.1

Subconsultas con Alias

Muchas instrucciones en las que la subconsulta y la consulta externa hacen referencia a la misma tabla se pueden indicar como autocombinaciones (combinar una tabla con ella misma). Por ejemplo, si usa una subconsulta, puede buscar autores que viven en la misma ciudad que Livia Karsen: USE pubs SELECT au_lname, au_fname, city FROM authors WHERE city IN (SELECT city FROM authors WHERE au_fname = 'Livia' AND au_lname = 'Karsen')

El siguiente es el conjunto de resultados: a u _l n a me - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - Gr e en St r a i g ht St r i n ge r Ma c Fe a t h e r Ka r s e n

a u _f n a me ci t y -------------------- ---------Ma r j o r i e Oa k l a nd De an Oa k l a nd Di r k Oa kl a nd St e a r n s Oa k l a n d Li vi a Oa k l a nd

( 5 r o w( s ) a f f e c t e d )

O bien, puede usar una autocombinación: USE pubs SELECT au1.au_lname, au1.au_fname, au1.city FROM authors AS au1 INNER JOIN authors AS au2 ON au1.city = au2.city AND au2.au_lname = 'Karsen' AND au2.au_fname = 'Livia'

Los alias de tabla son necesarios porque la tabla que se está combinando consigo misma aparece en dos funciones distintas. Los alias se pueden usar también en las consultas anidadas que hacen referencia a la misma tabla en una consulta interna y externa.


67

USE pubs SELECT au1.au_lname, au1.au_fname, au1.city FROM authors AS au1 WHERE au1.city in (SELECT au2.city FROM authors AS au2 WHERE au2.au_fname = 'Livia' AND au2.au_lname = 'Karsen')

Los alias explícitos indican claramente que las referencias a authors de la subconsulta no significan lo mismo que las referencias de la consulta externa. 1.3.9.2

Subconsultas con IN

El resultado de una subconsulta presentada con IN (o con NOT IN) es una lista de cero o más valores. Una vez que la consulta devuelve los resultados, la consulta externa hace uso de ellos. En esta consulta se buscan los nombres de los editores que han publicado libros de empresa. USE pubs SELECT pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')

El siguiente es el conjunto de resultados: pub_ name ---------------------------------------Al g od at a I n f o sy s t e ms New Moo n Boo ks ( 2 r o w( s ) a f f e c t e d )

Esta instrucción se evalúa en dos pasos. Primero, la consulta interna devuelve los números de identificación de los editores que han publicado libros de empresa (1389 y 0736). Segundo, estos valores se sustituyen en la consulta externa, que busca los nombres asociados con los números de identificación en publishers. USE pubs SELECT pub_name FROM publishers WHERE pub_id in ('1389', '0736')

Una diferencia de la utilización de una combinación en lugar de una subconsulta, para este problema y otros similares, es que la combinación permite mostrar, en el resultado, columnas de más de una tabla. Por ejemplo, si desea incluir en el resultado el título de los libros de empresa, debe usar combinaciones. USE pubs SELECT pub_name, title

68


FROM publishers INNER JOIN titles ON publishers.pub_id = titles.pub_id AND type = 'business'

El siguiente es el conjunto de resultados: p ub _n ame t i t l e - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - Al g od at a I n f o s y s t e ms Th e Bu s y Ex e c ut i v e ' s Da t a ba s e Gu i d e Al g od at a I n f o sy s t e ms Co ok i n g wi t h Co mp ut e r s : Su r r e pt i t i o us Ba l a nc e Sh ee t s Ne w Mo o n Bo o k s Al g od at a I n f o sy s t e ms

Yo u Ca n Co mb a t Co mp u t e r St r e s s ! St r a i g ht Ta l k Ab ou t Co mp ut e r s

( 4 r o w( s ) a f f e c t e d )

Esta consulta muestra que la combinación produce cuatro filas, no dos como en la subconsulta anterior. Este es otro ejemplo de una consulta que se puede formular con una subconsulta o con una combinación. Esta consulta busca los nombres de todos los segundos autores que viven en California y que reciben menos del 30 por ciento de los derechos de autor de un libro. USE pubs SELECT au_lname, au_fname FROM authors WHERE state = 'CA' AND au_id IN (SELECT au_id FROM titleauthor WHERE royaltyper < 30 AND au_ord = 2)

El siguiente es el conjunto de resultados: El siguiente es el conjunto de resultados: a u _ l n a me - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - Ma c Fe a t h e r

a u _ f n a me -------------------St ear ns

( 1 r o w( s ) a f f e c t e d )

Se evalúa la consulta interna y da como resultado los números de Id. de los tres autores que cumplen las calificaciones de la subconsulta. Después se evalúa la consulta externa. Observe que puede incluir más de una condición en la cláusula WHERE tanto de las consultas internas como de las externas. Mediante una combinación, la misma consulta se expresa de esta forma: USE pubs SELECT au_lname, au_fname FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id WHERE state = 'CA' AND royaltyper < 30 AND au_ord = 2

Una combinación se puede expresar siempre como una subconsulta. Una subconsulta se


69

puede expresar a menudo, aunque no siempre, como una combinación. Esto se debe a que las combinaciones son simétricas: puede combinar las tablas A y B en cualquier orden y obtener la misma respuesta. Lo mismo no es cierto si se implica a una subconsulta. 1.3.9.3

Subconsultas con NOT IN

Las subconsultas presentadas con la palabra clave NOT IN devuelven también una lista de cero o más valores. Esta consulta busca los nombres de los editores que no han publicado libros de empresa. USE pubs SELECT pub_name FROM publishers WHERE pub_id NOT IN (SELECT pub_id FROM titles WHERE type = 'business')

La consulta es exactamente la misma que la de las subconsultas con IN, excepto en que NOT IN se sustituye por IN. Sin embargo, esta instrucción no se puede convertir a una combinación. La combinación análoga "no igual" tiene un significado distinto: busca los nombres de los editores que han publicado algún libro que no sea de empresa. 1.3.9.4

Subconsultas en UPDATE, DELETE e INSERT

Las subconsultas se pueden anidar en las instrucciones UPDATE, DELETE e INSERT, así como en las instrucciones SELECT. La siguiente consulta dobla el precio de todos los libros publicados por New Moon Books. La consulta actualiza la tabla titles; su subconsulta hace referencia a la tabla publishers. UPDATE titles SET price = price * 2 WHERE pub_id IN (SELECT pub_id FROM publishers WHERE pub_name = 'New Moon Books')

Ésta es una instrucción UPDATE equivalente que usa una combinación: UPDATE titles SET price = price * 2 FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books'

Puede quitar todos los registros de ventas de los libros de empresa con esta consulta anidada: DELETE sales WHERE title_id IN (SELECT title_id FROM titles WHERE type = 'business')

70


Ésta es una instrucción DELETE equivalente que usa una combinación: DELETE sales FROM sales INNER JOIN titles ON sales.title_id = titles.title_id AND type = 'business' 1.3.9.5

Subconsultas con operadores de comparación

Las subconsultas se pueden presentar con uno de los operadores de comparación (=, < >, >, > =, <, ! >, ! < o < =). Una subconsulta presentada con un operador de comparación sin modificar (un operador de comparación no seguido de ANY o ALL) debe devolver un valor individual en lugar de una lista de valores, como las subconsultas presentadas con IN. Si tal subconsulta devuelve más de un valor, Microsoft® SQL Server™ muestra un mensaje de error. Para usar una subconsulta presentada con un operador de comparación sin modificar, debe estar suficientemente familiarizado con los datos y con la naturaleza del problema para saber que la subconsulta devolverá exactamente un valor. Por ejemplo, si supone que cada editor se encuentra en una sola ciudad, y desea encontrar el nombre de los autores que viven en la ciudad en la que se encuentra Algodata Infosystems, puede escribir una instrucción con una subconsulta presentada con el operador de comparación simple =. USE pubs SELECT au_lname, au_fname FROM authors WHERE city = (SELECT city FROM publishers WHERE pub_name = 'Algodata Infosystems')

El siguiente es el conjunto de resultados: a u _ l n a me -------Ca r s o n Be n n e t

a u _ f n a me -------- Ch e r y l Ab r a h a m

( 2 r o w( s ) a f f e c t e d )

Sin embargo, si Algodata Infosystems estuviera ubicada en varias ciudades, se produciría un mensaje de error. En lugar del operador de comparación =, se podría usar una formulación IN (= ANY funciona también). Las subconsultas presentadas con operadores de comparación sin modificar incluyen, a menudo, funciones agregadas, puesto que éstas devuelven un valor individual. Por ejemplo, esta instrucción busca los nombres de todos los libros con un precio superior al precio actual mínimo. USE pubs SELECT DISTINCT title FROM titles


71

WHERE price > (SELECT MIN(price) FROM titles)

El siguiente es el conjunto de resultados: t i t l e ---------------------------------------------------------- Bu t I s I t Us er Fr i e n d l y ? Co mp ut e r Ph ob i c a nd No n- Ph ob i c I n di v i d ua l s : Be ha v i o r Va r i a t i o ns Co ok i n g wi t h Co mp ut e r s : Su r r e pt i t i o us Ba l a nc e Sh ee t s Emo t i o na l Se cu r i t y : A Ne w Al g or i t h m Fi f t y Ye ar s i n Bu ck i n gh am Pa l a c e Ki t c he ns I s An g e r t h e En e my ? L i f e Wi t h ou t Fe ar On i o ns , L ee k s, a nd Ga r l i c : Co ok i n g Se cr e t s of t h e Me di t e r r a ne an Pr o l o ng ed Da t a De pr i v at i o n: Fo ur Ca s e St u di e s Se c r e t s of Si l i c on Va l l e y Si l i c on Va l l e y Ga st r o no mi c Tr e at s St r a i g ht Ta l k Ab ou t Co mp ut e r s Su s h i , An y o n e? T he Bu s y Ex e c u t i v e ' s Da t a b as e Gu i d e ( 1 4 r o w( s ) a f f e c t e d )

Puesto que las subconsultas presentadas con operadores de comparación sin modificar deben devolver un valor individual, no pueden incluir cláusulas GROUP BY o HAVING a menos que sepa que la propia cláusula GROUP BY o HAVING devuelve un valor individual. Por ejemplo, esta consulta busca los libros con un precio superior al del libro con el menor precio que sea del tipo 'trad_cook'. USE pubs SELECT DISTINCT title FROM titles WHERE price > (SELECT MIN(price) FROM titles GROUP BY type HAVING type = 'trad_cook')

El siguiente es el conjunto de resultados: ti tl e ------------------------------------------------------------------------ Bu t I s I t Us er Fr i e n d l y ? Co mp ut e r Ph ob i c AND No n- Ph ob i c I n di v i d ua l s : Be ha v i o r Va r i a t i o ns On i o ns , L ee k s, a nd Ga r l i c : Co ok i n g Se cr e t s o f t h e Me di t e r r a ne an Pr o l o ng ed Da t a De pr i v at i o n: Fo ur Ca s e St u di e s Se c r e t s of Si l i c on Va l l e y Si l i c on Va l l e y Ga st r o no mi c Tr e at s St r a i g ht Ta l k Ab ou t Co mp ut e r s Su s h i , An y o n e? T he Bu s y Ex e c u t i v e ' s Da t a b as e Gu i d e ( 9 r o w( s ) a f f e c t e d )

72

1.3.9.6


Subconsultas con EXISTS

Cuando se presenta una subconsulta con la palabra clave EXISTS, funciona como una prueba de existencia. La cláusula WHERE de la consulta externa comprueba la existencia de las filas devueltas por la subconsulta. La subconsulta en realidad no produce ningún dato, devuelve el valor TRUE o FALSE. Una subconsulta presentada con EXISTS tiene la siguiente sintaxis: WHERE [NOT] EXISTS (subquery)

En esta consulta se busca el nombre de todos los editores que publican libros de empresa. USE pubs SELECT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business')

El siguiente es el conjunto de resultados: pu b_n ame -------------------- New Moo n Boo k s Al g od at a I n f o sy s t e ms ( 2 r o w( s ) a f f e c t e d )

Para determinar los resultados de esta consulta, considere el nombre de cada editor de uno en uno. ¿Este valor hace que la subconsulta devuelva una fila, como mínimo? En otras palabras, ¿hace que la prueba de existencia dé como resultado TRUE? En este caso, el primer nombre de editor es Algodata Infosystems, con número de identificación 1389. ¿Hay filas en la tabla titles cuyo pub_id sea 1389 y cuyo type sea empresa? Si lo hay, Algodata Infosystems debe ser uno de los valores seleccionados. El mismo proceso se repite para cada uno de los restantes nombres de editor. Observe que las subconsultas presentadas con EXISTS son ligeramente distintas de las restantes subconsultas en los aspectos siguientes: La palabra clave EXISTS no se precede de un nombre de columna, constante u otra expresión. La lista de selección de una subconsulta presentada por EXISTS casi siempre consta de un asterisco (*). No hay razón para enumerar los nombres de las columnas porque simplemente se está comprobando la existencia de filas que cumplan las condiciones especificadas en la subconsulta. La palabra clave EXISTS es importante porque a veces no hay una formulación alternativa de la subconsulta. Aunque algunas consultas formuladas con EXISTS no se pueden expresar de ninguna otra forma, todas las consultas que usan IN o un operador


73

de comparación modificado por ANY o ALL se pueden expresar con EXISTS. A continuación se muestran algunos ejemplos de consultas que usan EXISTS y sus alternativas equivalentes. Se muestran dos formas de buscar autores que vivan en la misma ciudad que un editor: USE pubs SELECT au_lname, au_fname FROM authors WHERE city =ANY (SELECT city FROM publishers) -- Or USE pubs SELECT au_lname, au_fname FROM authors WHERE exists (SELECT * FROM publishers WHERE authors.city = publishers.city)

Éste es el conjunto de resultados de las consultas: a u _ l n a me -------Ca r s o n Be n n e t

a u _ f n a me -------- Ch e r y l Ab r a h a m

( 2 r o w( s ) a f f e c t e d )

Estas dos consultas buscan títulos de libros publicados por algún editor que se encuentre en una ciudad que comience con la letra B. USE pubs SELECT title FROM titles WHERE pub_id IN (SELECT pub_id FROM publishers WHERE city LIKE 'B%') -- Or USE pubs SELECT title FROM titles WHERE EXISTS (SELECT * FROM publishers WHERE pub_id = titles.pub_id AND city LIKE 'B%')

Éste es el conjunto de resultados de las consultas: t i t l e ---------------------------------------------------- T he Bu s y Ex e c u t i v e ' s Da t a b as e Gu i d e Co ok i n g wi t h Co mp ut e r s : Su r r e pt i t i o us Ba l a nc e Sh ee t s Yo u Ca n Co mb a t Co mp u t e r St r e s s ! St r a i g ht Ta l k Ab ou t Co mp ut e r s

74


Bu t I s I t Us er Fr i e n d l y ? Se c r e t s of Si l i c on Va l l e y Ne t Et i q u e t t e I s An g e r t h e En e my ? L i f e Wi t h ou t Fe ar Pr o l o ng ed Da t a De pr i v at i o n: Fo ur Ca s e St u di e s Emo t i o na l Se cu r i t y : A Ne w Al g or i t h m ( 1 1 r o w( s ) a f f e c t e d )

1.3.9.7

Subconsultas con NOT EXISTS

NOT EXISTS funciona igual que EXISTS, salvo por el hecho de que la cláusula WHERE en la que se utiliza se cumple si la subconsulta no devuelve ninguna fila. En el ejemplo siguiente se buscan nombres de editores que no publiquen libros de empresa: USE pubs SELECT pub_name FROM publishers WHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business')

El siguiente es el conjunto de resultados: pub_ name ---------------------------------------Bi n ne t & Ha r d l e y Fi v e L ak es Pu bl i s hi n g Ra mo n a Pu b l i s h e r s GGG&G Sc o o t n e y Bo o k s L uc er n e Pu bl i s h i n g ( 6 r o w( s ) a f f e c t e d )

En esta consulta se buscan los títulos de los que no ha habido ninguna venta. USE pubs SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id = titles.title_id)

El siguiente es el conjunto de resultados: t i t l ---T he Ne t

e ------------------------------ Ps y c h o l o g y o f Co mp u t e r Co o k i n g Et i q u e t t e

( 2 r o w( s ) a f f e c t e d )


1.3.9.8

75

Subconsultas usadas en lugar de una expresión

En Transact-SQL, una subconsulta se puede usar allí donde se pueda usar una expresión en las instrucciones SELECT, UPDATE, INSERT y DELETE, excepto en una lista ORDER BY. El siguiente ejemplo muestra cómo podría usar esta mejora. Esta consulta busca el precio de un libro conocido de informática, el promedio de todos los libros y la diferencia entre el precio del libro y el precio promedio de todos los libros. USE pubs SELECT title, price, (SELECT AVG(price) FROM titles) AS average, price-(SELECT AVG(price) FROM titles) AS difference FROM titles WHERE type='popular_comp'

El siguiente es el conjunto de resultados: t i tl ---Bu t Se cr Ne t

e pr i c e a v er a g e di f f er enc e --------------------------------- -------------- ---------------I s I t Us er Fr i e nd l y ? 2 2 . 9 5 14 . 7 7 8. 18 e t s o f Si l i c on Va l l e y 2 0 . 0 0 14 . 7 7 5. 23 Et i q u e t t e ( n ul l ) 14. 77 ( nul l )

( 3 r o w( s ) a f f e c t e d )

1.3.9.9

Múltiples niveles de anidamiento

Una subconsulta puede incluir en sí una o varias subconsultas. En una instrucción se puede anidar cualquier número de subconsultas. Esta consulta busca el nombre de los autores que han participado en la escritura de, como mínimo, un libro conocido de informática. USE pubs SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE type = 'popular_comp'))

El siguiente es el conjunto de resultados: a u _ l n a me - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - Ca r s o n Du l l L oc k s l e y Hu nt e r

a u _ f n a me -------------------Ch e r y l An n Ch ar l e ne Sh er y l

( 4 r o w( s ) a f f e c t e d )

La consulta más interna devuelve los números de Id. de título PC1035, PC8888 y PC9999. La consulta del siguiente nivel superior se evalúa con estos Id. de título y devuelve los números de Id. del autor. Finalmente, la consulta externa usa los Id. de

76


autor para buscar el nombre de los autores. También puede expresar esta consulta como una combinación: USE pubs SELECT au_lname, au_fname FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id JOIN titles ON titleauthor.title_id = titles.title_id WHERE type = 'popular_comp' 1.3.9.10 Procesamiento condicional con CASE

La función CASE se utiliza para evaluar varias condiciones y devolver un valor único para cada condición. Una utilización normal de la función CASE es sustituir códigos o abreviaturas por valores más legibles. En la siguiente consulta se usa la función CASE para cambiar el nombre a las categorías de libros, de forma que sean más comprensibles. USE pubs SELECT CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking' ELSE 'Not yet categorized' END AS Category, CONVERT(varchar(30), title) AS "Shortened Title", price AS Price FROM titles WHERE price IS NOT NULL ORDER BY 1

El siguiente es el conjunto de resultados: c at e g o r y ------------------Bu s i n es s Bu s i n es s Bu s i n es s Bu s i n e s s Mo de r n Co ok i n g Mo d e r n Co o k i n g Po pu l a r Co mp ut i n g Po pu l a r Co mp ut i n g Ps y c h o l o g y Ps y c h ol o gy Ps y c h o l o g y Ps y c ho l o gy Ps y c h ol o gy Tr a di t i o na l Co ok i n g Tr a di t i o na l Co ok i n g Tr a di t i o na l Co ok i n g

s ho r t e n e d t i t l e -----------------------------Co ok i n g wi t h Co mp ut e r s : Su r r e p St r a i g ht Ta l k Ab ou t Co mp ut e r s Th e Bu s y Ex e c ut i v e ' s Da t a ba s e Yo u Ca n Co mb a t Co mp u t e r St r e s s Si l i c o n Va l l e y Ga s t r o no mi c Tr e T he Go u r me t Mi c r o wa v e Bu t I s I t Us er Fr i e nd l y ? Se cr e t s o f Si l i c on Va l l e y Co mp u t e r Ph o b i c AND No n - Ph o b i c Emo t i o na l Se c ur i t y : A Ne w Al g o I s An g er t h e En e my ? L i f e Wi t h ou t Fe ar Pr o l o ng ed Da t a De pr i v a t i o n: Fo Fi f t y Ye ar s i n Bu ck i n gh am Pa l a On i o ns , L ee ks , a nd Ga r l i c : Co o Su sh i , An y on e?

Pr i c e ------1 1. 9 5 1 9. 9 9 1 9. 9 9 2. 99 1 9. 9 9 2. 99 2 2. 9 5 2 0. 0 0 21. 59 7. 99 10. 95 7. 00 1 9. 9 9 11 . 9 5 2 0. 9 5 1 4. 9 9

( 1 6 r o w( s ) a f f e c t e d )

CASE también se utiliza para clasificar datos. La siguiente consulta utiliza la función


77

CASE para clasificar precios. SELECT CASE WHEN price IS NULL THEN 'Not yet priced' WHEN price < 10 THEN 'Very Reasonable Title' WHEN price >= 10 and price < 20 THEN 'Coffee Table Title' ELSE 'Expensive book!' END AS "Price Category", CONVERT(varchar(20), title) AS "Shortened Title" FROM pubs.dbo.titles ORDER BY price

El siguiente es el conjunto de resultados: Pr i c e Ca t e go r y ---------------No t y e t p r i c e d No t y et p r i c ed Ve r y Re a s o n a b l e Ve r y Re a s on a bl e Ve r y Re as o na bl e Ve r y Re as on ab l e Co f f e e Ta bl e Ti t Co f f e e Ta bl e Ti t Co f f e e Ta b l e Ti t Co f f e e Ta bl e Ti t Co f f e e Ta bl e Ti t Co f f e e Ta bl e Ti t Co f f e e Ta b l e Ti t Co f f e e Ta bl e Ti t Ex pe ns i v e b oo k! Ex p e ns i v e b o ok ! Ex p e n s i v e b o o k ! Ex pe ns i v e b oo k !

Sh or t e ne d Ti t l e ----- -------------------Th e Ps y c h ol o gy o f Co Ne t Et i q ue t t e T i t l e Yo u Ca n Co mb a t Co mp u Ti t l e Th e Go u r me t Mi c r o wa v Ti t l e L i f e Wi t h ou t Fe ar Ti t l e Emo t i o na l Se cu r i t y : l e I s An ge r t h e En emy ? l e Co ok i n g wi t h Co mp ut e l e Fi f t y Ye a r s i n Bu c k i l e Su s hi , An yo ne ? l e Th e Bu s y Ex e cu t i v e ' s l e St r a i g ht Ta l k Ab ou t l e Si l i c on Va l l e y Ga s t r l e Pr o l o ng ed Da t a De pr i Se c r e t s o f Si l i c o n V On i o n s , L e ek s , a n d G Co mp u t e r Ph o b i c AND Bu t I s I t Us er Fr i e n

( 1 8 r o w( s ) a f f e c t e d )

1.3.10 Transacciones y control de la concurrencia

Los motores SQL son sistemas concurrentes, o sea que admiten la ejecución concurrente de consultas. Ejemplo: Sistema de venta de boletos de avión. Por tanto, son necesarios modelos de procesos concurrentes para admitir operaciones concurrentes que preserven la integridad de los datos.

Transacción Una transacción es una unidad de la ejecución de un programa. Puede consistir en varias operaciones de acceso a la base de datos. Está delimitada por comandos que indican el inicio, el fin exitoso o la cancelacion de la misma. Propiedades A.C.I.D. Atomicidad

Es la propiedad de las transacciones que permite observarlas como operaciones atómicas: ocurren totalmente o no ocurren.

78


Consistencia

La ejecución aislada de la transacción conserva la consistencia de la base de datos.

Aislamiento (Isolation) Para cada par de transacciones que puedan ejecutarse concurrentemente Ti y Tj, se cumple que para los efectos de Ti: - Tj ha terminado antes de que comience Ti - Tj ha comenzado después de que termine Ti Las transacciones son independientes entre sí. Niveles de aislamiento Se puede ajustar el nivel de aislamiento entre las transacciones y determina para una transacción el grado de aceptación de datos inconsistentes. A mayor grado de aislamiento, mayor precisión, pero a costa de menor concurrencia. El nivel de aislamiento para una sesión SQL establece el comportamiento de los bloqueos para las instrucciones SQL. Niveles de aislamiento: Lectura no comprometida. Menor nivel. Asegura que no se lean datos corruptos físicamente. Lectura comprometida. Sólo se permiten lecturas de datos comprometidos. Lectura repetible. Las lecturas repetidas de la misma fila para la misma transacción dan los mismos resultados. Secuenciable. Mayor nivel de aislamiento. Las transacciones se aíslan completamente. ·

· ·

·

Comportamiento concurrente de las transacciones. Lectura sucia. Lectura de datos no comprometidos. (Retrocesos) Lectura no repetible. Se obtienen resultados inconsistentes en lecturas repetidas. Lectura fantasma. Una lectura de una fila que no existía cuando se inició la transacción. · · ·

Ni v el d e a i s l a mi e n t o

Lec t ur a s uc i a

L ec t u r a no r epet i bl e

Lec t ur a f a nt a s ma

Lectura no comprometida

Sí

Sí

Sí

Lectura comprometida

No

Sí

Sí

Lectura repetible

No

No

Sí

Secuenciable

No

No

No

SQL Server permite todos estos niveles, Oracle sólo permite la lectura comprometida y secuenciable. Los niveles se pueden establecer en ambos para cada transacción. Durabilidad

El sistema gestor de bases de datos asegura que los cambios realizados por una transacción que termina con éxito perduran.


79

Estados de una transacción · · · ·

Activa: Durante su ejecución Parcialmente comprometida: Después de ejecutar su última instrucción. Fallida: Imposible de continuar su ejecución normal. Abortada: Transacción retrocedida y base de datos restaurada al estado anterior a su ejecución. Se puede reiniciar o cancelar.

Transacciones anidadas

Una transacción anidada o multinivel T consiste en un conjunto T = {t1, t2, . . ., tn} de subtransacciones y en un orden parcial P sobre T. Cada ti de T puede abortar sin obligar a que T aborte. Puede que T reinicie ti o simplemente no ejecute ti. Si se compromete ti, esa acción no hace que ti sea permanente, sino que ti se compromete con T, y puede que aborte si T aborta. La ejecución de T no debe violar el orden parcial P. Es decir, si aparece un arco ti ? tj en el grafo de precedencia, tj ? ti no debe estar en el cierre transitivo de P. Temas Relacionados:

Bloqueos Read Commited Read Uncommited Repeatable Read Serializable 1.3.10.1 Bloqueos

Un bloqueo es una información del tipo de acceso que se permite a un elemento. El motor SQL impone los bloqueos necesarios en cada momento. El gestor de acceso a los datos implementa las restricciones de acceso. En algunos sistemas se permite que el usuario pueda indicar el bloqueo más adecuado (locking hints).

Tipos de bloqueo con respecto a la operación read-locks: sólo permite lectura write-locks: permite lectura y escritura El gestor de bloqueos almacena los bloqueos en una tabla de bloqueos: (, , )=(E,B,T) La transacción T tiene un tipo de bloqueo B sobre el elemento E. Normalmente, E es clave, aunque no siempre, porque varias transacciones pueden bloquear el mismo elemento de forma diferente. · ·

Niveles de bloqueo Especifica la granularidad del bloqueo Fila: Fila individual Clave: Fila de un índice Página: Páginas (8KB) Extent: Extensión (grupo de 8 páginas contiguas de datos o índices) Table: Tabla completa Database: Base de datos completa · · · · · · ·

Modos de bloqueo Especifica el modo en que se bloquea un elemento

80


·

·

·

·

· · · · ·

Compartido: para operaciones sólo de lectura. Se permiten lecturas concurrentes, pero ninguna actualización. Actualización: para operaciones que pueden escribir. Sólo se permite que una transacción adquiera este bloqueo. Si la transacción modifica datos, se convierte en exclusivo, en caso contrario en compartido. Exclusivo. para operaciones que escriben datos. Sólo se permite que una transacción adquiera este bloqueo. Intención: se usan para establecer una jerarquía de bloqueo. Por ejemplo, si una transacción necesita bloqueo exclusivo y varias transacciones tienen bloqueo de intención, no se concede el exclusivo. Intención compartida. Bloqueo compartido. Intención exclusiva. Bloqueo exclusivo. Compartido con intención exclusivo. Algunos bloqueos compartidos y otros exclusivos. Esquema. Para operaciones del DDL (modificacion de estructuras de datos). Actualización masiva. En operaciones de actualización masiva.

Livelock

Espera indefinida de una transacción por un bloqueo que no se llega a conceder porque se cede a otras transacciones. Una solución (sistemas operativos): estrategia first-come-first-served (se atiende al primero que llega). Deadlock

T1: LOCK A; LOCK B; UNLOCK A; UNLOCK B; T2: LOCK B; LOCK A; UNLOCK B; UNLOCK A; T1 y T2 bloquean A y B => Espera indefinida de T1 y T2. Soluciones (sistemas operativos): 1- Concesión simultánea de todos los bloqueos de una transacción. 2- Asignar un orden lineal arbitrario a los elementos y requerir que las transacciones pidan los bloqueos en este orden. 3- Permitir los deadlocks y analizar cada cierto tiempo si existen. Este es el caso del SQL Server, por lo tanto es nuestra responsabilidad evitar los DeadLocks. 1.3.10.2 Read Uncommited

Lecturas no confirmadas, nivel más bajo de aislamiento solamente protege de lecturas de datos físicamente dañados. Es el que produce mayor nivel de concurrencia (nunca hay un bloqueo), pero también el que no garantiza en absoluto la coherencia de los datos. Veámoslo con un ejemplo; Juan, director comercial de una firma está pensando en incrementar los precios de ciertos artículos en un 50%, por eso ejecuta una instrucción en la que incrementa el precio de todos los artículos en ese 50%. El precio de una camiseta que costaba 30,00 $ pasa a ser en este instante de 45,00 $, aunque como no lo tiene muy claro todavía no lo ha confirmado. En ese instante Luis, comercial de la misma compañía, crea una factura a su cliente en la que le vende la camiseta de 30,00 $ a 45,.00 $. Después, nuestro indeciso Juan deshace la transacción esperando a que el gerente vuelva de vacaciones para cambiar los precios, mientras que Luis confirma el pedido. El resultado, Luis ha vendido una camiseta a un precio que nunca ha existido en la base de datos.


-- Script 1 -- PASO 1 use Northwind go set transaction isolation level read uncommitted begin tran update products set UnitPrice=UnitPrice*1.5 -- Ejecutar hasta aquí y pasar -- al segundo script -- PASO 3 -- Ahora desharemos la transacción rollback tran -- Volvamos a comprobar el precio -- en nuestro segundo script.

81

--Script 2 use Northwind go -- PASO 2 set transaction isolation level read uncommitted begin tran --Ejecutamos antes de confirmar -- o validar la transación de la -- conexión 1 select UnitPrice from Products where ProductId=7 -- El resultado es 45.00 commit tran -- ahora volvemos al script uno -- y deshacemos la transacción -- PASO 4 select UnitPrice from Products where ProductId=7 -- Ahora el resultado es 30.00

1.3.10.3 Read Commited

Lecturas confirmadas, en Sql-Server es el nivel de aislamiento por defecto, y supera el problema de nuestro comercial Luis. En este caso Luis no habría leído 45,00 $ como precio sino que permanecería a la espera de que Juan confirme o rechace los datos para obtener un precio. Si probamos el script anterior cambiando SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED por SET TRANSACTION ISOLATION LEVEL READ COMMITTED, el resultado será bien diferente. En nuestro paso 2 obtendremos un bloqueo, podemos comprobar el bloqueo ejecutando el procedimiento almacenado del sistema sp_lock (naturalmente desde la conexión 1) y obtendremos que hay varios bloqueos de tipo X (Exclusivos) y que un proceso tiene estado (WAIT) esperando. Si inmediatamente después de ejecutar el paso 2 ejecutamos el paso 3 comprobaremos que la segunda conexión nos estará devolviendo el precio del artículo 7, pero esta vez no nos dirá en ningún momento 45,00 sino que el precio proporcionado será 30,00. El nivel de aislamiento ‘Lecturas confirmadas’ proporciona un nivel de concurrencia muy bueno y previene del problema de las lecturas sucias, pero no previene de otros fenómenos como las lecturas no repetibles y datos fantasma. Las lecturas no repetibles implican que varias instrucciones ‘Select’ dentro de la misma transacción devuelve datos diferentes. Veamos un ejemplo. -- Script 3 -- PASO 1 use northwind go SET TRANSACTION ISOLATION LEVEL READ COMMITTED Begin tran select ProductName From Products where ProductId=77 -- resultado 'Original Frankfurter grüne Soße'

82


El nivel de aislamiento puede especificarse mediante el comando SET o bien mediante los HINTS de SQL es decir indicando en la propia instrucción SELECT el nivel de aislamiento deseado, por ejemplo para establecer el mínimo nivel de aislamiento además de con SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED podríamos hacerlo en nuestro ejemplo mediante el comando ‘select UnitPrice from Products with(NOLOCK) where ProductId=7’ y el resultado sería 45,00 independientemente del nivel de aislamiento indicado con la instrucción SET. -- Vayamos al Script 4, -- Paso 3 -- Dentro de la misma transacción, -- ejecutamos la misma instrucción select ProductName From Products where ProductId=77 Commit tran -- Se llama de otra forma!!! -- Volvamos al Script 6, Paso 4

-- Script 4 -- PASO 2 use northwind go Begin Tran Update Products Set Productname='Producto 77' where ProductId=77 commit tran --Volvamos al scrit 5 paso 3 -- Paso 4 -- Volvemos a actualizar al valor correcto. Begin Tran Update Products Set Productname='Original Frankfurter grüne Soße' where ProductId=77 commit tran

1.3.10.4 Repeatable Read

Lecturas repetibles, este nivel de aislamiento evita el problema que mostraba el script anterior, es decir si cambiamos el nivel de aislamiento a lecturas repetibles, en nuestro segundo script el intento de actualización quedará bloqueado a la espera de que nuestro primer script confirme o rechace la transacción. No obstante este nivel de aislamiento no soluciona el problema de los datos fantasma. Los datos fantasma son registros que aparecen y desaparecen dentro de nuestra transacción, es decir, un usuario dentro de una transacción busca el artículo 1500, y no está, sin embargo varias instrucciones después, pero dentro de la misma transacción vuelve a buscarlo y si existe. Veamos el ejemplo:


-- Script 5 -- PASO 1 use northwind go SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Begin tran select * From Products where ProductId=1500 -- resultado (0 Filas) -- Vayamos al Script 6, -- Paso 3 -- Dentro de la misma transacción, -- ejecutamos la misma instrucción select * From Products where ProductId=1500 -- Ahora aparece un registro!!!! -- Volvamos al Script 6, Paso 4 -- Paso 5 -- validamos la transacción commit tran -- en este instante se termina el -- bloqueo y se borra el registro -- (en la otra conexión)

83

-- Script 6 -- PASO 2 use northwind go Begin Tran -- Product Id es un campo identity -- en la B.D. Northwind, por eso -- debemos usar este comando si -- queremos insertar un valor explicito. set identity_insert Products on insert into Products (ProductId,ProductName) values (1500,'Producto de pruebas') set identity_insert Products off commit tran --Volvamos al scrit 5 paso 3 -- Paso 4 -- Borraremos el registro.. begin tran delete from products where ProductId=1500 commit tran -- Obtendremos un bloqueo, ya que -- este dato ha sido leido, por lo que -- no puede ser eliminado

1.3.10.5 Serializable

El nivel de aislamiento serializable es el que más se acomoda a lo que significa la definición ACID, pero por el contrario no permite una alta escalabilidad y si se usa mucho puede provocar numerosos bloqueos e interbloqueos. En este caso lo que queda garantizado es que dos instrucciones ‘Select’ en la misma transacción con nivel serializable van a devolver el mismo conjunto de datos. Esto implica bloquear registros que incluso ¡No existen todavía!, con esto evita el problema de los registros fantasma. Lo que complica poderosamente su implementación. Un hecho que me gustaría resaltar para que si usamos este nivel de aislamiento lo hagamos con precaución es lo sencillo que resulta producir un ‘deadlock’ o abrazo mortal con este nivel de aislamiento. Probemos este script

84


-- Script 7 -- Paso 1 use northwind go SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN -- Obtengamos el nombre del producto 77 SELECT ProductName FROM Products Where ProductId=77 -- En este nivel de aislamiento hemos -- creado un bloqueo de tipo compartido. -- (No confundir con la granularidad -- del bloqueo, es decir con qué -- es lo que se bloquea) -- hagamos lo mismo en el script número 8 -- Ahora actualizaremos el nombre del -- producto update Products set ProductName ='Producto 77' where ProductId=77 -- Obtenemos un bloqueo al intentar -- convertir el bloqueo compartido en exclusivo, ya que nuestra -- segunda conexión también tiene un -- bloqueo compartido sobre este -- registro. -- Pasemos al segundo script e intentemos -- tambiénactualizar el nombre, tampoco -- es tan raro que dos usuarios -- quisieran cambiar el nombre a un -- artículo (por una falta de -- ortografía por ejemplo)

1.4

Diccionario de Datos (DCT)

Propiedades de Archivos Tipos de Datos Clarion / MS-SQL Usando SQLIdentity en Clarion 6 Claves/Indices (Keys) Integridad referencial - Relaciones Creacion Tablas en Motor Conexion a una Base de Datos Convirtiendo ISAM (Tps) a SQL

-- Script 8 -- Paso 2 use northwind go SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN -- Obtengamos el nombre del -- producto 77 SELECT ProductName FROM Products Where ProductId=77 -- Hasta aquí ningún problema,.. -- volvamos al script 7.. -- Ahora actualizaremos el nombre -- del producto update Products set ProductName ='Producto 77' where ProductId=77 rollback tran -- El resultado: que la primera -- conexión obtiene un mensaje de -- error indicandole que ha sido -- víctima de un abrazo mortal


1.4.1

85

Propiedades de Archivos

Tab General

DataBase Driver: Este cambio es el mas obvio, necesitamos cambiar de “TopSpeed” a

"Microsoft SQL" .

Driver Options: Si presionamos el Boton Ellipsis (Tres puntos) nos vamos a encontrar

con una lista de parametros soportados por cada driver, por ej. a continuacion mostramos una imagen del driver de Microsoft SQL:

86


Full PathName: El Pathname se utiliza para definir el nombre físico de la tabla mientras

que se identifica en el servidor.

No RECLAIM : Los drives SQLs no soportan el atributo RECLAIM.

Tab Options


87

Esta Seccion nos permite colocar opciones como por ej.. algunas propiedades de de la tabla que nos permita crear determinado código en la generacion de templates. Por ejemplo hay una opcion importante a partir del del Clarion 6.3 a : IsIdentity :

Esta opcion es a nivel Campo/Columna del DCT y nos permite activar la recuperacion de un valor auto-incrementing del lado del servidor . De esta forma evitamos insertar a nivel codigo "SELECT @@IDENTIty.... @@IDENTIty....." ." o "select Scope_Identity() Scope_Identity() ..." para recuperar el ultimo numero del lado del motor. El valor a insertar seria Isidentity =1 en User options. Es valido aclarar que la columna debera contener el valor "Identity" en la Base de datos..

1.4. 1.4.2 2

Tipo Tipos s de de Dat Datos os Clar Clario ion n / MS-S MS-SQL QL

Si utilizamos el "File Import Wizard" Wizard" o el "Dictionary Synchronizer" automaticamente automaticamente nos va a completar las propiedades de las columnas basandose en cada una de las caracteristicas de las mismas. Sin embargo, hay algunos "switches" especiales en las características de la columna que a futuro se pueden configurar/enviar al "SQL Accelerator Driver" que es el que tiene el acceso a los datos. Estos "switches" son descritos abajo y pueden ser aplicados a todos los "SQL

88


accelerators"con excepcion de ORACLE:

External Name

En primer lugar es valido aclarar que el atributo "External Name" se utiliza tambien tambien para agregar algun nombre externo a la columna columna (Campo). Este tipo de caso se puede dar donde el nombre del campo de la tabla del dct es diferente al nombre de la columna de la tabla de la base de datos. Por ejemplo si ud. esta accediendo a una tabla de una base de datos que permiten nombres de columnas mas largos en longitud que Clarion, Clar ion, ud. puede colocar el nombre que quiere manejar en clarion en el Atributo "Column Name" del campo y en External Name colocar el nombre de la columna de la tabla del motor de basedatos. "Switches" aplicados a "SQL accelerators" en el "External Name": | NOWHERE

Colocando NOWHERE le estamos diciendo al driver que excluya el campo de cualquier clausula WHERE que se le envie desde clarion hacia el motor.

| READONLY

Adicionando READONLY le estamos diciendo al driver que no escriba en este campo cuando el registro es actualizado. | BINARY

Adicionando BINARY le estamos diciendo al driver que almacene los datos de este campo en formato Binario. Esto es útil al almacenar imágenes o caracteres no-imprimibles | WRITABLE

WRITABLE es lo contrario contrario a READONLY. Algunos "SQL backends" backends" incorrectamente les


89

informan al driver que el campo es " read only" only" y deberia deberia no ser actualizado. Este switch informa al driver que puede escribir sobre este campo. | ZERONOTNULL

ZERONOTNULL es usado en combinacion con el atributo "/ZERODATE=NULL " del "driver string" . Este switch le informa al driver que este campo fecha en particular sea actualizado en cero (0) si el mismo mismo es 0.

Tabla Comparativa En caso de que actualicemos las tablas primero en el dct y luego realizar las modificaciones en el Motor, o bien realicemos una conversion de TPS a Motor, les proporcionamos una tabla comparativa/equivalencia entre los tipos de datos MS-SQL y Clarion: Categoria

Tipo Dato MS-SQL

Equivalencia Clarion

Numericos Exactos:

Bigint Decimal Int Numeric Smallint Money Tinyint Smallmoney B it

DECIMAL DECIMAL(19) LONG LONG SHORT DECIMAL(19,4) BYTE DECIMAL(11,4) BYTE

Numericos Aproximados: Float Real

REAL REAL

Fecha y Hora:

Smalldatetime

STRING(8) with GROUP(DATE and TIME) STRING(8) with GROUP(DATE and TIME)

Caracteres Strings:

Char Text Varchar

STRING BLOB CSTRING(51)

Unicode Caracteres Strings:

Nchar

CSTRING

Ntext Nvarchar

BLOB CSTRING

Binary Strings:

Binary Image Varbinary

STRING BLOB,BINARY STRING

Otros Tipos de datos:

Cursor

n/a

Datetime

90


Timestamp sql_variant Uniqueidentifier Table Xml

STRING(8) STRING(8016) CSTRING(37) n/a STRING(65535)

Tambien es muy importante al momento de crear/modificar campos en el dct tener en cuenta Manejo de Fechas y Campos "CSTRING" por "STRING".

1.4.3

Usando SQLIdentity en Clarion 6

A partir de la version 6.3 de Clarion (build 9051) tenemos la posibilidad de recuperar el valor Identity generado por el motor (Back-End) de una forma muy practica. Como se hace esto? :

Mediante Codigo Embebido Muy simple, diciendole al File Driver de Clarion cual/cuales son los campos identity de la estructura de un archivo. Esto lo hacemos mediante la propiedad " FILE{PROP:ServerAutoInc, n} = 1 " donde n es el numero de campo dentro de nuestra estructura de archivo. Ejemplo: MyFile FILE,DRIVER('MSSQL')... RECORD ID STRING(26) ! c ol u mn a de f i n i d a Data STRING(200)

c omo i d en t i t y en el

CODE OPEN(MyFile) MyFile{PROP: ServerAutoInc} ! a ct i v amo s MyFile{PROP:ServerAutoInc, 1} = 1 ! l e

s er v i d or

el " s er v er s i d e a ut o i n c" de c i mo s al d r i v e el n r o d e c o l u mn a e n l a qu e s e co nt i

Para que funcione esto obviamente necesitamos que el SQL-Server contenga la correcta definicion de

Mediante IDE de Clarion (Dct) Otra forma de hacerlo es configurando la columna Identity en las propiedades del campo de la tabla en el Diccionario de Datos(DCT).


91

En este caso, colocamos la Propiedad "IsIdentity" con un valor "true" en la lengueta "options" de la columna / campo de la tabla.

1.4.4

Claves/Indices (Keys)

Al momento de generar una aplicacion "Clarion SQL" es muy importante en el diseño del diccionario el manejo de los indices/Claves. Para ello le proporcionamos determinadas consideraciones al momento de crearlas/modificarlas.

92


Consideraciones: 1. NO usar tipos de datos "GROUP" en las Claves. Se pueden usar campos que formen parte de grupos como componentes de una clave, pero no se puede utilizar el campo "GROUP" como componente. Si en el dct de tps tenemos claves de con este tipo de dato, deberiamos colocar cada campo que utiliza ese GROUP. 2. Clave Unica (Primaria) Asegurarnos que todas las tablas tengan una clave unica. En SQL no existe el concepto de puntero de archivo o numero de Registro, entonces para que el DRIVER pueda actualizar el registro necesitamos tener identificado univocamente cada registro. Para esto necesitamos tener al menos una clave unica por cada Archivo. Tengamos en cuenta que el PUT/ACCESS:File.Update() es convertido por el driver a una sentencia SQL: UPDATE TABLA SET CAMPO = VALOR WHERE ClaveUnica = Valor

3. CASE SENSITIVE: La mayoria de las base de datos usan el concepto del sensible a mayusculas a nivel de toda la base de datos. Por tal motivo no se puede especificar una llave sensible y otra NO. En realidad tambien si usamos claves sin CASE SENSITIVE puede degradar la performance del motor, ya que las consultas generadas van a usar la funcion UCASE. 4. IGUALAR CLAVES DE CLARION CON INDICES DEL MOTOR DE BASE DE DATOS: Con SQL no es necesario que los indices del DCT sean exactamente igual a los del SQL. Se pueden crear tantos indices en el dct como indices en el SQL independientemente. Si hay diferencias NO va a a arrojar el error 47 el aplicativo. El driver utiliza los componentes de las claves para generar el ORDER BY en los Querys. El problema es que si no existe la clave en el SQL que coincida con ese ORDER BY , la consulta va a funcionar mucho mas lenta de lo que pensamos. 5. "CSTRING" x "STRING"

1.4.5

Integridad referencial - Relaciones

Integridad referencial significa que, por cada relación 1:Muchos existe siempre un padre para cada hijo ( Es decir, que no se permiten hijos huérfanos)


93

Las reglas de RI son manejadas mas eficientemente si son definidas en el motor de base de datos, usualmente utilizando Triggers, Stored Procedures, o sentencias de integridad. Mediante esto, la base de datos puede automáticamente administrar la integridad referencial,, optimizando el tráfico de datos, sin necesidad de enviar a los registros hijos a través de la red. (que sería el comportamiento por defecto de Clarion). Como el servidor de base de datos será el que maneje la funcionalidad de RI, la mejor opción es especificar “Ninguna Acción” La integridad Referencial de los datos es recomendable que la maneje el servidor y no Clarion. Para ello se utiliza las siguientes configuraciones a nivel Dct:

Restrict (Server)

El servidor va a prevenir que el usuario desde un "Delete" o "update" pueda cambiar o borrar un campo si el mismo es usado en una foreign Key

Cascade (Server)

El servidor va a actualizar/Borrar registros de un campo que es utilizado en una foreign Key.

Clear (Server)

El servidor va a actualizar a null o ceros aquellos campos que forman parte de la foreign Key.

Estos seteos de tipo "server" tienen un mismo comportamiento que el "No Action", no generan codigo desde clarion, sin embargo nos permiten conocer desde el lado de clarion cuales son las relaciones y pueden ser usados por algun template de generacion de script para el server.

Ver mas detalle en Normalizacion_de_la_base_de_Datos 1.4.6

Creacion Tablas en Motor Utility Template Synchronizer File Import

1.4.6.1

Utility Template

Una de las formas de crear una tabla en una base de datos es mediante la creacion de un script SQL. Existen varios UTILITY templates circulando gratis dentro de la comunidad de usuarios que nos permiten generar ese script. Por ejemplo uno de los mas utilizados es el de Roberto Artigas DCT2SQL. Una vez que hayamos generado el archivo *.SQL deberiamos ejecutar ese script. Para esto utilizaremos el SQL Query Analyzer, o el winSQL o cualquier otra herramienta que

94


nos permita ejecutar scripts SQL en el motor.

1.4.6.2

Synchronizer

Típicamente, la forma de desarrollar aplicaciones SQL es importando una estructura de tabla ya definida en el motor de base de datos. Aunque es posible editar manualmente las estructuras de las tablas en el Editor de Diccionario de datos para tablas SQL, recomendamos firmemente importar la definición de la estructura de la tabla. Importar las estructuras de las tablas, minimiza la posibilidad de introducir un error en el diccionario y garantiza la correcta especificación de los tipos de datos , estructuras de las claves, etc. Este método asume que las tablas ya estén predefinidas en el motor de base de datos. Mediante el Synchronizer , Clarion nos permite importar y sincronizar tablas desde el motor hacia el dct. El sincronizador del diccionario es probablemente la herramienta más importante a dominar al momento de manejar las tabla entre el motor y el dct. Para acceder al mismo dentro del Editor del diccionario se debera ir al menu File --> Dictionary Synchronizer. Los pasos principales a realizar son los siguientes: ·

Conexion: En este paso se debera establecer los parametros de conexion a la base de datos a sincronizar.


·

·

95

Direccion: En este paso le diremos al synchronizer desde donde se hara la comparacion desde Dct a Base o Base a Dct .

Seleccion de Tablas: Seleccionamos con que tablas vamos a trabajar en esta sesion de Synchronizer .

En el ejemplo de abajo vemos como seleccionamos las tablas "clientes" y "clientes_telef" que estan en la base y no se encuentran en el dct.

96


·

Resolver las diferencias entre la base de datos y el dct: Estas diferencias entre las características de la base de datos/DCT necesitan siempre ser resueltas para ser procesadas:

Una de las principales razones para utilizar el "Synchronizer" es para importar la definicion de multiples tablas desde la base hacia el dct. En el ejemplo que vemos abajo, visualizamos que las tablas "clientes" y "clientes_telef" no se encuentran en el Dct y si en la base de datos (Source). Si presionamos el boton derecho del mouse en el registro del archivo, veremos un menu popup con acciones a tomar/decidir. En este caso le decimos que adicione la tabla a "ejemplo.dct" y de esta manera quedaria resuelto esta diferencia ya que automaticamente al presionar OK nos va a crear la definicion en el dct importandola desde la Base de datos.


1.4.6.3

97

File Import

Otras de las posibilidades que tenemos para la creacion de tablas en el dct es importandola desde el motor. Para ellos Clarion nos provee de una herramienta en el editor del Dct, a la que podemos

98


llegar seleccionando el menu File --> Import Table.

Primeramente debemos seleccionar el Driver..

El proximo paso es proporcionarle los datos para la conexion a la Base de datos:


Y por ultimo seleccionamos la tabla a importar...

99

100


En este caso le decimos que adicione la tabla "Clientes_Telef" a nuestro dct y de esta manera automaticamente nos va a crear la definicion en el dct importandola desde la Base de datos.

1.4.7

Conexion a una Base de Datos

Owner Name Para la conexion a una base de datos, Clarion utiliza el atributo Owner para conectarse a una base de datos. Esto indica cómo conectar con la base de datos donde se localizan las tablas. Todas las tablas "SQL" requieren un OWNER (“Connect String“) y para esto es recomendable siempre utilizar una Variable Global de tipo CSTRING (255) para setear el String de conexion hacia el motor. La conexion con el Server se realiza la primera vez que se abre un archivo. Por mas que ese archivo se cierre inmediatamente y el aplicativo quede abierto, la conexion se mantendra abierta hasta el cierre de la aplicacion, o bien hasta que se llame a prop:disconnet.


101

Los parametros de conexion del MSSQL son: servername, database,,

servernam El nombre del Server o la direccion IP del Servidor e database Nombre de la Base de datos uid

Usuario

pwd

Password

Driver Nativo - SQL Accelerator Drivers ODBC - Open DataBase Conectivity

1.4.7.1

Driver Nativo - SQL Accelerator Drivers

Database Driver Lo mas recomendable para aplicaciones de Clarion con MS-SQL es utilizar el driver Nativo "Microsoft SQL" en Database Driver de las propiedades de la tabla en el dct. El "SQL Accelerator Driver" es el que nos proporciona el acceso a los datos de la base de datos.

.

Si importamos las tablas directamente desde el motor (Synchronizer /File Import ) vamos a ver que ya nos viene configurado el driver. En el caso que creemos una tabla desde cero en el dct debemos seleccionar esta opcion. Los SQL Accelerator Drivers comparten una base de código común y muchas características como por ejemplo la tecnología de Buffering, los Driver Strings , etc. Sin embargo, su objetivo primario es traducir los comandos Clarion en eficientes sentencias SQL para sus respectivos motores de bases de datos y manejar el resultado que ellos devuelvan. Los Accelerator Drivers convierten las sentencias de acceso a disco de Clarion ( SET, GET, NEXT, etc.) a sentencias SQL optimizadas. Esto significa que puede usar el mismo código fuente para acceder a tablas SQL y a otros tipos de bases como por ejemplo Archivos TopSpeed. Además, los SQL Accelerator Drivers permiten enviar SQL propios que son enviados directamente al motor e interpretan el resultado para ser procesado de manera estándar usando los comandos NEXT y PREVIOUS.

102

1.4.7.2


ODBC - Open DataBase Conectivity

ODBC El ODBC (Open DataBase Conectivity) es una interfase estratégica de Windows para acceder a los datos desde diferentes motores de base de datos (RDBMS) a través de una variedad de redes y plataformas. Es un método alternativo a los Drivers nativos. Su funcionamiento es mediante la utilización de DLLs para cada una de las Bases de Datos. ODBC es una excelente elección en un entorno de desarrollo Cliente Servidor, especialmente si el Servidor es un motor de base de datos relacionales con SQL nativo (Pervasive, Informix, Oracle, AS400, SyBase, etc..). Permite crear aplicaciones con soporte Cliente Servidor sin hacer mucho más que cambiar el Driver. Con ODBC el Servidor puede manejar la mayor parte del trabajo con las bases de datos, especialmente si se usan sentencias SQL complejas. Un buen diseño orientado a Cliente Servidor puede incrementar considerablemente la performance de las aplicaciones. Los Drivers ODBC existentes actualmente, cubren la mayoría de los tipos de bases de datos. Hay Drivers ODBC disponibles para las cuales Clarion aun no posee Drivers nativos. (Access, Excel, Notes, etc..) Cabe mencionar que existe el driver ODBC para tablas tps que nos permite utilizar, entre otras cosas, algún producto de reportes como Crystal Reports, o algun gestor de consultas como WinSQL. ODBC es un estándar muy difundido actualmente, y la tendencia es que todas las bases de datos ofrezcan acceso ODBC. En muchos casos tenemos disponibles varios tipos de Drivers para la misma base. ODBC es independiente de la plataforma. Uno de los principales objetivos de Microsoft al establecer ODBC fue el de soportar un fácil acceso a los entornos corporativos en los cuales las bases de datos se encuentran distribuidas en diversas plataformas o múltiples motores de base de datos. Ventajas:

Desventajas: ODBC agrega una capa –el ODBC Driver Manager- entre su aplicación y la

base de datos. Cuando se accede a datos en su disco local, esto generalmente deteriora la performance. El Driver Manager debe traducir cada llamada al API ODBC de la aplicación a SQL, antes de que ocurra cualquier acceso a los datos. • La información requerida por el ODBC para conectarse a una fuente de datos (data Source) varia de un Driver a otro. Al contrario que la selección del Driver nativo de Clarion en el cual la operación es virtualmente transparente; Ud. necesitará conseguir información específica de configuración de cada Driver. • ODBC no está incluido en Windows. Cuando distribuya su aplicación, Ud. necesitará instalar los correspondientes Drivers junto con el Administrador de ODBC en el sistema del usuario final. Los Drivers ODBC pueden ser configurados desde el Administrador de ODBC del Panel de Control. Es muy común que el usuario final utilice incorrectamente esta herramienta, y casi cualquier cambio en la configuración de los data sources hará que nuestra aplicación deje de funcionar por resultarle imposible conectarse a la fuente de datos, o peor aun hara que se conecte de manera ineficiente degradando la performance o provocando su mal funcionamiento 1.4.8

Convertir TPS a SQL

Para convertir una Aplicacion "TPS" a "Client Server", se deben tener en cuenta determinados tips que vamos a detallar a continuacion...


103

CREACION DE DCT

Primeramente para realizar las modificaciones al DCT de Clarion debemos hacerlo a partir de un nuevo diccionario y tenemos tres posibilidades de hacerlo: 1. Copiando el archivo "*.dct" o haciendo un " Save As" en el diccionario original. 2.Exportando el diccionario a un TXD e importando en un nuevo Dct. 3.Creando un nuevo diccionario y haciendo Copy/Paste de cada uno de los archivos de dct origen hacia el nuevo. Las desventajas de esto son que las relaciones no se van a copiar, y el tiempo que se insume si el diccionario es demasiado grande. Una vez creado el nuevo Diccionario, se debera realizar los siguientes cambios al Dct:

DataBase Driver: Este cambio es el mas obvio, necesitamos cambiar de “TopSpeed” a

"Microsoft SQL" .

Si se utilizo metodo de "TXD" para la creacion del nuevo diccionario , se podria hacer sobre el mismo antes de importarlo una busqueda "'TopSpeed')" y reemplazar con "'MSSQL'),OWNER(GLO:OwnerName)".

"CSTRING" por "STRING" Manejo de Fechas Migrar Datos Existentes

104


No RECLAIM : Los drives SQLs no soportan el atributo RECLAIM, por ende debemos

sacar este atributo para las aplicaciones SQL.

(Si se utilizo metodo de "TXD" para la creacion del nuevo diccionario , se podria realizar una busqueda "RECLAIM," y reemplazar con "")

TIPOS DE DATOS

Otro de los grandes cambios a considerar dentro de la migracion de un aplicativo de tps a Motor son los tipos de Datos. LONG a DATE (Fecha) / TIME (Hora)

En los sistemas TPS, generalmente los datos de fecha y Hora se almacenan como LONG. Estos se deben convertir a DATE y TIME ya que desde clarion se van a serguir trabajando sin problemas, pero para que los datos sean accesibles desde herramientas externas a Clarion como por ej. Crystal R eports, Reporting Services, etc. es necesario realizar la conversion. ( ver manejo de Fechas) MEMO a CSTRING :

Los drivers SQLs no soportan campos con tipos de datos MEMOs, por tal motivo es necesario cambiar a tipo de dato CSTRING. Ver Tipos de Datos Clarion / MS-SQL para mas detalle con respecto a los tipos de datos Clarion / MS-Sql.

CLAVES (keys):

Recomendamos revisar Claves/Indices (Keys) de este manual para realizar las modificaciones correspondientes al manejo de Claves/indices de una tabla.

1.4.8.1

"CSTRING" por "STRING"

En la mayoría de las bases de datos SQL, los espacios en blanco al final son importantes al momento de comparar igualdad entre campos. Por ejemplo "Cliente " no es lo mismo que "Cliente" para los motores. Este concepto es muy importante para aquellos campos que son componentes de claves. Si se utilizan campos de tipo "CSTRING" Clarion trata los espacios de igual manera que los motores. Al utilizar este tipo de campo se debera incrementar el tamaño de la columa en uno, ya que el CSTRING tiene un caracter adicional para indicar el fin de la cadena.

1.4.8.2

Manejo de Fechas

En sistemas con archivos TPS, generalmente los datos de fecha y de la hora se almacenan como tipo de datos LONG. Estos se deben convertir a Campos DATE y TIME, para que los mismos sean facilmente accesibles desde herramientas de terceros. O sea si


105

dejamos los campos como LONG (INT en el Motor) lo vamos a seguir trabajando sin problemas desde Clarion, pero no se van a ver correctamente por ej. desde el SQLEditor o desde Cristal Report, Reporting Services,etc..

Microsoft utiliza dos tipos de datos para identificar informacion de fecha y hora : SMALLDATETIME y DATETIME. En ambos casos se manejan de la misma manera en clarion: Aud_Fecha STRING(8) GFecha GROUP,Over(Aud_Fecha) Aud_Fecha_Date DATE ! Ud. debe modificar este campo (fecha) Aud_Fecha_Time TIME ! Ud. debe modificar este campo (Hora) END

Un dato muy importante, es que si Ud. no necesita manejar la Hora dentro de su campo de Fecha , puede cambiar la definicion de arriba a: Aud_Fecha 1.4.8.3

DATE

Migrar Datos Existentes

Si Ud. esta convertiendo un aplicativo TPS a Motor, es muy factible que quiera migrar sus datos. Les comentamos algunas opciones al respecto: · · ·

1.5

Realizando un procedimiento de conversion para cada una de las tablas a migrar. Utilizando el DTS de Ms-Sql conjuntamente con el driver ODBC de "Topspeed " Exportando las tablas Tps a ASCII de tipo CSV (delimitado por comas) y e importarlos a traves del DTS - Importación y exportación de datos

Usando SQL en Aplicaciones (APP) Recomendaciones Ventana de Login Automático Validacion de Datos SQL Embebido en Clarion Prop:SQL Comando BUFFER Prop:SQLFilter Prop:WHERE SetFilter con codigo SQL Filtrando Campos DATE

106


StupidTempTable Codigo Clarion Vs. SQL Manejo de Errores Manejo Set, Next, Previous Clarion y NULLs SQL / VIEWs Browses SQL Advanced Tab Process /Reports Transacciones Batch Manejo de Transacciones Stored Procedures Usando TurboSQL Uso del trace Cargas de Cabecera - Detalle Creando un Editor de SQL

1.5.1

Recomendaciones

Este es un resumen de las consideraciones a tener en cuenta al desarrollar una aplicacion Cliente Servidor. Estas consideraciones tienen como objetivo evitar problemas tanto de performance (lentitud) como de errores en el funcionamiento de nuestra aplicación. El objetivo de una aplicación Cliente Servidor es distribuir coherentemente el trabajo entre la terminal Cliente y el Servidor de Base de Datos. Mas que nada hay que tratar de evitar la sobrecarga de trabajo en el servidor y minimizar el volumen de trafico por la red.

Lista de items a considerar Uso de SQL Embebido En Clarion no es obligatorio usar SQL Embebido para todo, ya que los drivers generan

automaticamente el SQL necesario para resolver los comandos Clarion (ADD, PUT, etc..) Sin embargo, siempre que necesitemos que un proceso funcione lo mas rapidamente posible, deberemos recurrir al SQL embebido. Esto es fundamentalmente critico en los procesos. Tengamos en cuenta que un Process de clarion recorre cada unos de los registros y realiza las actualizaciones “de a una”. Un solo comando SQL puede hacer todo el trabajo de nuestro proceso 10 veces mas rapido. Mejor todavia, si usamos un Stored Procedure, va a funcionar mas rapido porque ya queda precompilado en el motor. Transacciones (LOGOUT) en cargas de Cabecera – Detalle

Siempre que agregamos o modificamos datos dentro de un LOGOUT estos datos quedan Lockeados. Si algun browse o reporte intenta leer estos datos va a quedar “colgado”.Por esto las transacciones deben ser siempre lo mas cortas posible. Nunca se debe dejar una transaccion abierta con algun mensaje o intervencion pendiente del usuario. Un ejemplo claro de mala practica es iniciar una transaccion (LOGOUT) en el Init de una carga de cabecera / detalle, un COMMIT en el TakeCompleted y un ROLLBACK en el Cancel. Todos los registros que se agreguen estan lockeados hasta que el usuario no se decida, generando lockeos con otras terminales. Lo que debe hacerse es trabajar todo en memoria o tablas auxiliares y confirmar todo el procesamiento transaccional en el minimo tiempo posible (generalmente en el


107

TakeCompleted). Claves Primarias en las Tablas

En SQL no existe el concepto de “puntero” del archivo. Para actualizar un registro se necesita obligatoriamente algun campo o grupo de campos que definan univocamente el registro. Esto es: Una clave primaria. Para que el driver convierta los PUT o DELETE en sus correspondientes sentencias SQL, tiene que especificar la sentencia Where sobre la que se va a realizar la actualizacion. Por ejemplo ( Pr i me r o p os i c i o n ar s e c o n NEXT ,

o GET)

ORD:Saldo = 0 PUT(Ordenes) ó ACCESS:Ordenes.Update() Se t r a d u c e c o mo :

UPDATE Ordenes SET Saldo=0 WHERE NumeroOrden = 234 Do n d e Nu me r o Or d e n es e l

c a mp o d e l a Cl a v e Pr i ma r i a

Uso de Claves Sensitivas

El uso de CASE sensitive es obligatorio para todas las claves que contengan algun campo alfanumerico. Si no ponemos el check de Case Sensitive el Driver (correctamente) le va a poner UPPER en el ORDER BY por esos componentes. Esto hace que el SQL no pueda usar la clave y que funcione muy lento. Igualar las claves de Clarion con los indices SQL

Con SQL no es necesario que los indices en el DCT sean exactamente iguales a los del SQL. Se pueden crear tantos indices en el DCT como indices en el SQL independientemente. Por mas que tengamos diferencias no va a dar Error 47. El driver simplemente utiliza los componentes de la Clave para generar el ORDER BY. El problema es que si no existe la clave en el SQL que coincida con ese ORDER BY, la consulta va a funcionar igual pero mucho mas lento. Uso del OWNER Name

Es fundamental que asignemos una variable global ¡GLO:Connexion en el Owner name de todas las tablas. Esto es asi porque es muy comun que el nombre del server, de la base o del usuario cambien. Recordemos que el formato de esa variable de Conexión deberia ser server,base,usuario,password (si se usa ODBC, consultar el Anexo) Uso del Full PathName

En SQL, al importar la tabla nos queda usuario.tabla en el full pathname (generalmente dbo.tabla) Es recomendable dejar solo el nombre de la tabla, para que sea independiente del usuario. Autonumeracion

Lo mejor es usar un campo identity siempre que se pueda. Y en lo posible tratar de no usar la autonumeracion estandar de Clarion. Revisar el capitulo de Autonumeracion…

108


Campos Cstring

Cambiar todos los campos STRING(n) del DCT por CSTRING (n+1). Los campos STRING dan muchos problemas, mas que nada en las claves y busquedas… Definir solo los campos que se usan

En algunos casos no es necesario tener en el DCT todos los campos de la tabla. Por ejemplo podemos tener campos adicionales de auditoria en las tablas con valores por default que no es necesario esten en el DCT. Transacciones en procesos

Siempre que necesitemos hacer un proceso y no podamos usar SQL, es conveniente encerrar todo el procesamiento en un bloque LOGOUT – COMMIT. De esta manera el SQL confirmara todo el lote de comandos cuando llegue al COMMIT. Siendo el proceso mucho mas rapido. Evitar los Relation Trees

El Relation Tree es un control Template que consume bastantes recursos. Hay que evitar usarlo y mas aun si el primer nivel del arbol es muy grande ya que siempre carga los niveles completos en memoria. Acerca de los Browses

1.5.2

Ventana de Login Automático

Los SQL Accelerator Drivers automáticamente buscan por el Nombre del Usuario y Password al acceder a las tablas. Si el nombre de usuario y el Password fueron ingresados entonces el Driver usa esos valores. Si no se han ingresado estos datos, entonces el Driver trae la ventana automática de Login. Se recomienda abrir una tabla al inicio del programa, así el tiempo de conexión con el servidor ocurre solamente al abrir la aplicación; para realizar esto ud. puede agregar una tabla SQL en el File Schematic del procedimiento main. Esto automáticamente genera código de conexión y apertura de la tabla. Excepto por el ODBC Accelerator Driver, el dialogo automático de Login le permite al usuario especificar Nombre de Usuario, Password, y Base de datos. 1.5.3

Validacion de Datos

La validación de los datos se refiere al cumplimiento de las reglas del negocio (especificadas por el programador). En particular, los valores que cada campo pueda tomar en la base de datos. Algunas validaciones típicas fuerzan cosas como : un campo no puede ser dejado en blanco (o cero) , el campo debe contener un valor 1 ó 0 , el campo debe estar en un determinado rango, el campo debe existir en una clave primaria de otra tabla. Estas reglas de validación pueden ser especificadas tanto en el Cliente como en el Servidor. La mejor manera de implementar este tema es minimizando el tráfico de red, por ende la mejor solución sería resolverlo en el cliente. Alternativamente, puede duplicarse esta lógica y estar definida en ambos lados logrando así el siguiente esquema: Al declarar las reglas de validación en el Cliente se asegura que los datos enviados por el Cliente sean válidos. Ya que el servidor va a recibir siempre datos válidos (de esta aplicación) entonces nunca generará mensajes de error. El efecto es que esta ·


·

109

situación reduce el tráfico en la red. Al declarar las reglas de validación en el Servidor se asegura que los datos son siempre válidos, sin importar que aplicación sea la que este accediendo a la base de datos en ese momento. Al interactuar con herramientas SQL un usuario podría corromper los datos.

Forzando estas reglas en ambos lados pareciera aumentar el trabajo de programación. Sin embargo, el Editor de Diccionario de Datos nos permite seleccionar de manera sencilla las reglas mas comunes a partir de opciones prefijadas, afectando la definición del campo. Al hacer esto, la aplicación generará el código necesario para forzar el cumplimiento de estas reglas en toda la aplicación. Definición de las reglas del Negocio

La Definición de las reglas del Negocio puede hacerse tanto en el cliente como en el servidor, por supuesto que definiéndola del lado del cliente obtengo un nivel mucho mas alto de flexibilidad ya que el nivel de expresión del lenguaje de desarrollo de aplicaciones en el cliente supera ampliamente lo que puedo lograr en el servidor. De todas maneras definiendo las reglas en el servidor mediante views, stored procedures y triggers centralizo el problema, y generalmente se logra un gran incremento en la performance. Al momento de analizar que lógica va del lado del cliente y cual del lado del servidor generalmente se opta por la solución que minimice el trafico de la red y los tiempos de respuesta. Es similar al problema de la validación de datos, pero normalmente el tipo de lógica involucrada es mas compleja. Por Ejemplo: a los clientes cuya categoría de iva es "Resp. Inscripto" se les confecciona factura del tipo "A" 1.5.4

SQL Embebido en Clarion

La tecnología de los File Drivers de Clarion le permite al programador Clarion tener que aprender y usar solamente la sintaxis de los comandos I/O del Lenguaje Clarion, sin importar sobre que base de datos esté trabajando. El File Driver automáticamente convertirá esa instrucción a una instrucción SQL válida. Es por eso que al utilizar un Driver basado en motores SQL, es el Driver en si mismo el que genera los querys SQL necesarios para retribuir los datos desde el servidor. Usando toda la información que el File Driver puede obtener de la estructura de la tabla, la estructura del VIEW (vista) y del comando BUFFER, el SQL generado puede llegar a ser bastante eficiente. Pero sin embargo, existen casos en donde un experimentado programador SQL querrá extender esta funcionalidad o tomar total control sobre el SQL a generar. Algunas operaciones como borrar un bloque entero de registros no es realizado por los File Drivers por lo cual en ese caso también es recomendable tomar el control del SQL a generar. Para estos casos, el lenguaje Clarion provee un mecanismo para enviar SQL directo al motor de base de datos : Prop:SQL. 1.5.5

Prop:SQL Prop:SQL es la propiedad de una Tabla o una Vista que permite enviar cualquier SQL

directo al motor de base de datos.

La sintaxis de Prop:SQL es la misma que cualquier otra propiedad del lenguaje Clarion. El target (des MiTabla{Prop:SQL} = ‘ SELECT * FROM UnaTabla’

110


Esta sentencia SQL es enviada al motor de base de datos. Notar que el Target (destinatario) de la propiedad es MiTabla mientras que el query se refiera a UnaTabla. Debido a que un SELECT retorna un grupo de registros entonces hay que utilizar el comando NEXT() para retribuir un registro a la vez. La estructura de la tabla MiTabla deberá tener la misma cantidad de campos que UnaTabla. El ejemplo anterior no demuestra el método optimo ya que los File Drivers generarían un mejor query, y además automático, para resolver esta consulta. La verdadera potencia de PROP:SQL es realizar tareas y/o funciones que no tengan su respectiva contrapartida en el Lenguaje Clarion. Un ejemplo sería la creación de tablas en el servidor de base de datos. Aunque el comando CREATE crea las tablas este está limitado por la información guardada en la estructura de la tabla. Es mucho mejor utilizar en este caso PROP:SQL para crear la tabla ya que podemos agregar mas información como por ejemplo validación de datos , restricciones, etc. Por ejemplo, el siguiente código permite crear la tabla de Estudiantes: MiVista{PROP:SQL} = ‘CREATE TABLE ‘(CodEstudiante ‘Apellido ‘Nombre ‘CodCarrera ‘PRIMARY KEY ‘FOREIGN KEY ‘REFERENCES ‘ON DELETE

Estudiantes ’ INTEGER NOT NULL, ’ VARCHAR(25) NOT NULL, ’ VARCHAR(25) NOT NULL’ INTEGER, ’ (CodEstudiante), ’ CURSA (CodCarrera), ’ Carreras, ’ RESTRICT) ’

&| &| &| &| &| &| &| &| &|

Este SQL especifica dos cosas que son imposibles de hacer usando el CREATE de Clarion. Primero, el atributo NOT NULL especifica que deben existir datos en esa columna. Segundo, la condición de integridad referencial especifica la relación entre la tabla de Estudiantes y Carreras indicando que no se puede borrar una carrera si esta tiene estudiantes. Otra utilización de PROP:SQL es el llamado a Store Procedures (Procedimientos Creados en la base de datos utilizando SQL extendido). En la mayoría de bases de datos basados en SQL los Store Procedures contienen una versión pre-compilada de sentencias SQL . Debido a que los Store Procedures están guardados en la base de datos nos permite una mejor integración y reusabilidad entre distintas aplicaciones. El uso de SQL embebido es particularmente eficiente en procesos de actualización masivos de la base de datos, y es en este punto donde podemos utilizarlo para marcar una gran diferencia en los tiempos de respuesta entre el código Clarion nativo y el SQL. Por ejemplo si queremos aumentar en un 5% los precios de los productos de determinado rubro se puede utilizar la siguiente sentencia SQL en lugar de un proceso Clarion. MiTabla{PROP:SQL} = ’UPDATE Productos SET ‘&| ‘precio=precio * 1.05 WHERE rubro=’’X’’’

Nótese que los nombres usados en las sentencias SQL son los del servidor, no los de Clarion. O sea ponemos rubro (o tabla.rubro) en lugar de PRO:RUBRO.


1.5.6

111

Comando BUFFER

El comando BUFFER de Clarion puede tener un impacto tremendo en la performance de aplicaciones Cliente/Servidor. BUFFER le indica al Driver que utilice un buffer para almacenar registros previamente leídos como así también un buffer para registros “read-ahead” (Lectura Anticipada) . También permite especificar el tiempo de time-out de una página. Es decir, el tiempo de vencimiento de una página de datos. Luego de vencerse este tiempo el Driver vuelve a leer esta página al buffer. Cuando el File Driver sabe que tiene buffers para guardar múltiples registros puede optimizar los querys para aprovechar esta capacidad. Esto permite que el servidor devuelva varios registros a la vez (grupo de registros) en lugar de regresar de a uno por vez. Esta técnica se la conoce con el nombre de “Fat Fetches” (Lecturas Gordas). El efecto neto de este cambio es que ahora en lugar de tener muchos paquetes pequeños viajando por la red tenemos uno mas grande (haciendo mas eficiente el tráfico de la red). El lugar mas común para la utilización del comando BUFFER sería en procedimientos que permitan al usuario realizar un Browse de Registros de la base. Al utilizar buffers para guardar registros ya leídos, el cliente al necesitar nuevamente estos registros los trae del buffer de memoria en lugar de necesitar realizar otro query y esperar su resultado, logrando así una gran reducción de tráfico en la red. Al utilizar buffers de Lectura Anticipada, la aplicación cliente se anticipa y trae a memoria la próxima página de registros mientras el usuario está leyendo la primer página. De esta manera, cuando el usuario realmente pide la próxima página esos registros ya se encuentran en el buffer dándole al usuario la sensación de retribución instantánea. Nota especial con PROP:SQL

La siguiente nota es un extracto de una documentacion de Rick Hoffman's.: Cuan se utiliza un prop:sql con el comando BUFFER(TableName, 0) antes del PROP:SQL le estamos i BUFFER(TableName, 0) TableName{PROP:SQL} = 'SELECT Field1, Field2, FROM TableName' NEXT(TableName)

Ahora bien, tambien podemos utilizar BUFFER(TableName, 20) y de esta forma le estamos diciendo Por Ejemplo: BUFFER(TableName, 20) TableName{PROP:SQL} = 'SELECT Field1, Field2, FROM TableName' NEXT(TableName).

1.5.7

Prop:SQLFilter Prop:SQLFilter es la propiedad que permite agregar un filtro propio a la estructura

WHERE de una sentencia SQL enviada al motor de base de datos. Esta propiedad permite especificar un filtro para una estructura Tabla y/o Vista utilizando la sintaxis del servidor en lugar de la sintaxis de Clarion. Por defecto, Prop:SQLFilter reemplaza cualquier expresión en el atributo Filter. Sin embargo, al comenzar el filtro con el símbolo +, el File Driver adicionará este filtro al filtro ya existente de acuerdo al atributo FILTER del SQL generado.

112


La ventaja de utilizar Prop:SQLFilter es la posibilidad de utilizar las capacidades del motor en cuanto a la sintaxis del filtro. Por ejemplo: El código siguiente usará el atributo FILTER para limitar la vista a los estudiantes de Computación y se agregará un Filtro usando Prop:SQLFilter para que retorne únicamente aquellos estudiantes cuyos nombre comiencen con “Al” ( Como por ejemplo Aldo, Alfredo, Alfonso ) MiVista VIEW(Estudiantes),FILTER(‘Est:Carrera=’’Computació n’’’) PROJECT(Est:Apellido,Est:Nombre,Est:Carrera) END CODE OPEN(MiVista) MiVista{PROP:SQLFilter} = ‘+Estudiantes.Apellido LIKE ‘‘Al%’’’

Esto hubiera sido muy costoso de realizar de no ser por el PROP:SQLFilter. Nótese que en el atributo FILTER se utiliza la nomenclatura de los campos segun su definicion en el DCT, mientras que en la propiedad PROP:SQLFilter se utiliza la nomenclatura de los campos segun su definicion en el motor de base de datos. Otro Ejemplo: Supongamos que queremos ver en el browse solo los Clientes que tienen Ordenes, esto es muy dificil de hacer con codigo Clarion, pero en SQL queda: BRW1.Reset PROCEDURE CODE BRW1.view{PROP:SqlFilter} =

'+EXISTS (SELECT * FROM ordenes B

WHERE A.CodCliente

.... PARENT.Reset()

1.5.8

Prop:WHERE Prop:Where es la propiedad que permite agregar un filtro propio a la lectura secuencial de un archivo (conjuncion de LOOP NEXT (FILE), etc ), utilizando la sintaxis del servidor

en lugar de la sintaxis de Clarion. PROP:WHERE puede setearse tambien en el diccionario como una propiedad del "driver string", pero esto no va a afectar el comportamiento de los Views. Ejemplo: Supongamos que queremos procesar todas las compras de un Cliente del año actual: Clear(Ord:Record) Ord:CustomerId = 100 Set(Ord:K_CustomerId,Ord:K_CustomerId) . Orders{Prop:Where}='Year(OrderDate)=Year(GetDate())' Loop Until Access:Orders.Next() !. . . . .. . . .

p r o c e s ami e n t o de da t o s

END

Esto genera el siguiente query SQL: select ...


113

from Orders where CustomerId >= 100 and Year(OrderDate)=Year(GetDate())

Nótese que PROP:Where debe estar ubicado luego del SET y antes del LOOP. Tenga en cuenta que l

1.5.9

SetFilter con codigo SQL

Otra posibilidad de adicionar un filtro para una estructura Tabla y/o Vista, es utilizando la sintaxis 'SQL()' dentro del metodo SetFilter() de la Clase ViewManager. Por Ejemplo supongamos que queremos filtrar un browse de Provincias cuyo codigo (Idprovincia) se encuentre en la tabla Clientes: Brw1.SetFilter('SQL(Idprovincia IN(Select idprovincia from Clientes))') ThisWindow.Reset(1)

1.5.10 Filtrando Campos DATE

Cuando manejamos campos de tipo " DATE" en un archivo, al momento de realizarle un filtro y comparar con este tipo de datos, debemos considerar el "Format" del mismo para su correcto funcionamiento. En SQL-Server el picture del FORMAT debe ser "@d12-" al momento de generar el "Query string". En caso de que no utilice el FORMAT() con un picture valido es muy factible que al momento de ejecutar el query en clarion nos arroje el siguiente error:

Ejemplo:

A continuacion describimos un ejemplo de un query en el que se filtran todas aquellas ventas que ser realizaron en un dia determinado (Variable Loc:Fecha). En el mismo notamos que la fecha es tratada como tipo de dato STRING, o sea con comillas dobles al momento de comparar - Shippeddate = ''' & FORMAT(Loc:Fecha,@d12-)& ''''. En este caso tambien observamos que al momento de recibir y guardar el dato de Tipo DATE (OrderDate/R:Campo4) tambien se le realiza un DEFORMAT() con picture "@d10-" para visualizar la fecha correctamente. Loc:Query = 'select customerId,shipName,ShipAddress,OrderDate from orders ' Where Shippeddate = ''' & FORMAT(Loc:Fecha,@d12-) & '''' ResSQL{prop:sql} = Loc:Query

!!

e nv i a mo s el

q ue r y a l

Mo t o r

'&|

114


IF FILEERRORCODE() ! ! ma pe amo s e l e r r o r SETCLIPBOARD(Loc:query) ! ! c ol o ca mo s al Cl i p bo ar d el q ue r y p or s i q ue r e mo s MESSAGE('Atencion Ocurrio un error ='&FILEERROR(),'ERROR',ICON:HAND) return LEVEL:Fatal END FREE(QClientes) Loop Until Access:ResSQL.Next() QC:IdCliente = R:Campo1 QC:Razon_Social = R:Campo2 QC:Direccion = R:Campo3 QC:Fecha = DEFORMAT(R:Campo4,@d10-) ADD(QClientes) ! ! Ca r g a mo s un qu eu e p ar a v i s ua l i z ar l END

o en un Co nt r o l

Li st

! ! ! Re s ul t a d o d e l a c on s ul t a

1.5.11 StupidTempTable

Para que necesitamos esta teoria? Esta teoria se utiliza para los siguientes casos: Para obtener y manipular facilmente campos calculados por el server. (Esto se puede ·

ej e cu t a r l o d


·

·

115

aplicar a funciones como sum, avg, max, etc como tambien a la llamada de stored procedures) Para no tener que establecer relaciones en el diccionario por cada JOIN que se nos ocurra usar. Para no necesitar definir una clave por cada ordenamiento que necesitemos de las tablas, independientemente de las relaciones entre las mismas.

Como funciona esta teoria? En el server existe una tabla de validacion (StupidTempTable) con un campo por cada tipo de dato que usemos en nuestra aplicacion. Esta tabla no va a existir en nuestro diccionario de Clarion, sera definida en la DATA SECTION de cada procedimiento. Al definir la tabla local en el procedimiento se le asigna el atributo NAME tanto de la tabla como de cada uno de los campos que se desea retornar. El Driver de Clarion no verifica el orden, o el tamaño, solo que el nombre del campo exista en el SQL y tenga el mismo tipo de datos. Se pueden definir y abrir tantas tablas locales como se necesiten, cada una de ellas haciendo referencia a la misma StupidTempTable, obviamente todas ellas tendran diferente nombre, pero el mismo atributo NAME (Asegurese de cerrarlas una vez que termine de usarlas) Se pueden tener tantos campos en la tabla temporal como se necesiten con el mismo atributo NAME, obviamente sin repetir sus nombres.

Tecnica de la Tabla Temporal Creacion de Tabla de Validacion en el server:

Solo es necesario una.

StupidTempTable X_INT INT X_VARCHAR VARCHAR(255) X_CHAR CHAR(255) X_DECIMAL DECIMAL(12,2) X_MONEY MONEY X_TEXT TEXT ETc..

Creacion de Tabla Local en el procedimiento:

En el embed DATA SECTION. TempTable FILE,DRIVER(MSSQL) NAME('StupidTempTable'), OWNER(GLO:Owner),PRE(Stt) Record RECORD Id_Proveedor LIKE(Pro:IdProveedor),NAME('X_INT') Razon_Social LIKE(Pro:RazonSocial),NAME('X_VARCHAR') Nombre LIKE(Pro:Nombre),NAME('X_VARCHAR') Credito DECIMAL(12,2),NAME('X_DECIMAL') Distancia LONG,NAME('X_INT')! Calculado por el Server (Query)

END END

Obteniendo Informacion y asignacion de valores a una variable:

116


TempTable{prop:sql} = 'Select p.idproveedor,p.RazonSocial,p.Apellido_Nombres,LimiteCr ' Round(sqrt(power(p.XCoordeanda - a.XCoordenada,2)+power(p.YCoordeanda - a.YCoordena ' from proveedores p, posiciones a where estado = <39>A<39> and p.idproveedor = a.idp !Procesamiento de Lectura de Registros. LOOP NEXT(TempTable) IF ERRORCODE() THEN BREAK. L:Variable_Codigo_Proveedor = Stt:Id_Proveedor L:Variable_Nombre = Stt:Nombre L:Variable_Distancia .... etc.

= Stt:Distancia

END

Tecnica de la Tabla Temporal SIMPLIFICADA Si bien es muy poderosa la tecnica anterior, el problema que tiene es que resulta bastante engorroso declarar las tablas temporales en cada procedimiento. Una opcion mas sencilla es crear en el diccionario una tabla con campos de tipo CSTRING. Por Ejemplo:

Respuesta FILE, DRIVER('MSSQL'),OWER(Glo:Owner),NAME('dbo.SQL_Temp'),PRE(SQL),CREATE, Record RECORD,PRE() C1 CSTRING(255) C2 CSTRING(255) C3 CSTRING(255) ...... C20 CSTRING(255) END END

Cuantos Campos?

Le creamos tantos campos de Tipo de datos CSTRING como necesitemos para las consultas de nuestra aplicacion. No hay ningun inconveniente en crear varias tablas temporales en el diccionario para que se ajusten a nuestros requerimientos. Por ejemplo es mas rapido tener una tabla temporal solo con un campo de tipo LONG para realizar sumatorias, maximos, etc. desde la base. Por que Tipo de Datos CSTRING?

Porque el tipo de dato CSTRING mapea automaticamente contra cualquier tipo de datos, por lo tanto cualquier cosa que pongamos en el select se va a asignar en el mismo orden a los campos CSTRING, sin importar el tipo de dato. Los datos nos van a llegar formateados como alfanumericos, en algunos casos es necesarios aplicarles un DEFORMAT para poder manipularlos mejor (especialmente las fechas). 1.5.12 Codigo Clarion Vs. SQL

Ventajas de cada tecnica Clarion: Codigo generico. Funciona para cualquier Driver (TPS / SQL) SQL Embebido: Rapido. Actualiza desde el server cada sentencia SQL (UPDATE, · ·


·

117

DELETE, ...) Stored Procedure: Muy Rapido. Ejecuta dentro del server muchas sentencias SQL complejas. Pre compilado y optimizado. Utiliza SQL extendido (LOOP, IF, ....)

Desventajas de cada tecnica Clarion: Muy lento en Batch. Actualiza desde el Cliente cada registro SQL Embebido: No es generico. A veces Depende del Server. Solo funciona con motores Stored Procedure: Complejo. Siempre depende del server. Agrega complejidad al proyecto. Dificil de debuggear y mantener · ·

·

1.5.13 Manejo de Errores

Al ejecutar una operación de I/O existe la posibilidad que se produzca un error. No importa que base de datos se esté utilizando, el File Driver de Clarion mapea los errores mas comunes al apropiado ERRORCODE(). Sin embargo, existen siempre algunos errores que no tienen equivalencia con los errores de Clarion. Cuando ocurren este tipo de errores , que no tienen equivalencia con los errores estándares de Clarion, se retorna el ERRORCODE()=90 y la función ERROR() devuelve el texto “File Driver Error’. Para determinar el verdadero error y el texto de error hay que utilizar las funciones FILEERRORCODE() Y FILEERROR() respectivamente. Las funciones FILEERRORCODE() Y FILEERROR() contienen el código de error del motor de base de datos y el texto que esta devuelva. Tip: Estos códigos de error figuran en la documentación del producto de la base de datos y no en la documentación de Clarion.

1.5.14 Manejo Set, Next, Previous

Procesamiento de SET/NEXT y SET/PREVIOUS (SELECT/ORDER BY) Una sentencia SET seguida de un Next en una estructura LOOP es el método mas común de procesar datos secuencialmente en Clarion. Cuando el SQL Accelerator Driver encuentra una combinación SET/NEXT, genera una sentencia SQL SELECT con la cláusula ORDER BY basada en los componentes de la clave. Los campos componentes de la clave se determinan al momento del SET. Ejemplo:

Ord:Nombre = ‘PEREZ’ SET(Ord:Nombre,Ord:Nombre) LOOP NEXT(Ordenes) !... Proceso END

Esto genera el siguiente SQL

118


SELECT FROM Ordenes WHERE (Nombre >= ‘PEREZ’) ORDER BY Nombre

1.5.15 Clarion y NULLs

Un concepto común en las bases de datos SQL es el concepto de nulo (NULL). El concepto de valor nulo en un campo de una tabla o una vista indica que el usuario no ha ingresado datos a ese campo. Null significa “valor no conocido” para el campo. Esto es completamente distinto que un cero o un blanco, y permite detectar y diferenciar los campos que nunca han sido ingresados con diferencia de los que ciertamente tienen un cero o un blanco. El lenguaje Clarion soporta el manejo de valores Nulos mediante la utilización de los comandos NULL,SETNULL and SETNONULL. En las expresiones, Null no es equivalente a un blanco o a cero. Por ende, cualquier expresión que compare el valor del campo de una tabla (o vista) con cualquier otro valor siempre devolverá falso cuando el campo sea nulo. Este es válido hasta cuando el valor de ambos elementos sean NULL. Es decir que si tenemos Tabla:Campo1 = Tabla:Campo2 va a evaluar a Verdadero si y solo si ambos campos tienen valores conocidos. Si ambos campos tienen NULL entonces esa expresión igualmente evaluará a FALSO. Conocido = Conocido Conocido = Desconocido Desconocido = Desconocido Desconocido <> 10 1 + Desconocido

Evalúa a Verdadero o a Falso Evalúa a Desconocido Evalúa a Desconocido Evalúa a Desconocido Evalúa a Desconocido

Las únicas excepciones a esta reglas son las operaciones booleanas que utilicen los operadores OR y AND donde parte de la expresión es desconocida y la otra porción cumple con el criterio: Desconocido OR True True OR Desconocido Desconocido AND False False AND Desconocido

Evalúa a Verdadero Evalúa a Verdadero Evalúa a False Evalúa a False

El soporte de valores nulos es dependiente del File Driver. La mayoría de los motores de bases de datos soportan el concepto de valores nulos, mientras que la mayoría de las bases de datos no-sql no lo soportan. Puede usar el comando NULL() para detectar si un valor es nulo o no. Para cualquier campo que deba permanecer nulo al escribir en la tabla, el programador deberá explícitamente setear el campo a nulo utilizando SETNULL(). Por ejemplo, NEXT(MiTabla) FlagMiCampo = NULL(MiCampo) … (mas código) …


119 119

IF FlagMiCampo AND NOT MiCampo ! si el campo era nulo y no fue modificado SETNULL(MiCampo) ¡ volver a setearlo a NULL END!IF PUT(MiTabla)

1.5. 1.5.16 16 SQL SQL / VIEW VIEWs s

La estructura VIEW (Vista) es una estructura de datos que automáticamente define dos operaciones relacionales estándares : el PROJECT y y el JOIN . Una vista también automáticamente filtrara (FILTER) y ordenara (ORDER) el Set de datos. El servidor de base de datos es el que realiza las operaciones, retornando únicamente el Set datos que la aplicación Cliente necesita para realizar su trabajo. Un View se definiria con un concepto similar al VIEW de SQL, partiendo de la base que a partir de la union de varias tablas podemos podemos crear una nueva tabla (VIEW). Los VIEWs de Clarion se crean automaticamente a partir de las relaciones existentes en el diccionario o joins customizados. En teoria el mismo codigo generado a partir de un VIEW de Clarion Cl arion deberia funcionar tanto para SQL y TPS, sin si n efectuar ningun cambio, sin embargo existe una diferencia importante entre ambas plataformas: ·

·

En un VIEW SQL se retornan solamente las columnas que ud. ha colocado en el project (Por ej. en el caso de un Browse lo que figure en el "List Box Format" mas lo informado en el "Hot Fields" del mismo) por cada c ada tabla del View. En un VIEW ISAM (TPS) se retornan todas las columnas por cada una de las tablas que figura en el Join.

Esta diferencia es valida a tener en cuenta porque quizas si migramos un aplicativo de TPS a SQL, nos vamos a encontrar que hay cosas que antes SI funcionaban y luega de tal migracion NO.

PROJECT Una operación Project relacional notifica al servidor de base de datos que solamente utilice un subgrupo de las columnas (campos) de la tabla (reduciendo el tráfico). Por ejemplo, la vista siguiente devolverá solamente algunos de los campos de la tabla de Estudiantes. MiVista VIEW(Estudiantes) PROJECT(Est:Nombre,Est:Apellido,Est:CodCarrera) END

JOIN Un Join relacional automáticamente une (Join) renglones (registros) de múltiples tablas y los convierte en un único resultado que el servidor retorna al Cliente. La estructura VIEW asume que se desea realizar un LEFT OUTER JOIN ( unión que devuelve todo el Set de la izquierda con sus uniones de la derecha . Es decir que la cantidad de registros totales del resultado es siempre igual a la cantidad de registros de la tabla primaria). A partir del atributo INNER es posible cambiar el modo en que se deban realizar los JOINS. Por ejemplo, la siguiente estructura VIEW devolverá todos los estudiantes con el Nombre de la carrera que están estudiando.

120


MiVista VIEW(Estudiantes) PROJECT(Est:Nombre,Est:Apellido,Est:CodCarrera) JOIN(Car:CodCarrera,Est:CodCarrera) PROJECT(Maj:NombreCarrera) END END

Al agregar el atributo INNER el Join la vista devolverá solamente aquellos estudiantes que estén anotados en alguna carrera. Aquellos que no estén anotados en ninguna carrera no serán incluidos en la vista (Notar que en los OUTER Join J oin si son incluidos) MiVista VIEW(Estudiantes) PROJECT(Est:Nombre,Est:Apellido,Est:CodCarrera) JOIN(Car:CodCarrera,Est:CodCarrera),INNER PROJECT(Maj:NombreCarrera) END END

FILTER El atributo Filter (Filtro) en una vista permite especificar una expresión filtro para indicar que registros quiero ver. Esto generará un WHERE en el SQL generado. Por ejemplo, la siguiente vista solamente devolverá aquellos estudiantes cuyo nombre sea “Aldo”. MiVista VIEW(Estudiantes),FILTER(‘Est:Nombre= ``Aldo’’’ ) PROJECT(Est:Nombre,Est:Apellido,Est:CodCarrera) END

ORDER El atributo Order (Orden) en una vista permite especificar como debe estar ordenado el resultado del query. Esto generará un ORDER BY en el SQL generado. Por ejemplo, la siguiente vista devolverá a los estudiantes ordenados en forma descendente por apellido y en forma ascendente por nombre MiVista VIEW(Estudiantes),ORDER(‘-Est:Apellido,+Est:Nombre’) VIEW(Estudiantes),ORDER(‘-Est:Apellido,+Est:N ombre’) PROJECT(Est:Nombre,Est:Apellido,Est:CodCarrera) END

Naturalmente todos estos conceptos pueden ser combinados (usados simultáneamente) para lograr Filtrar,Ordenar,Projectar y Unir obteniendo así el resultado deseado. 1.5.1 5.17 Brow rowses

A continuacion detallamos algunas reglas basicas que se debe conocer al momento de Configurar los Lo mejor seria que los browses siempre se muestren filtrados lo mas posible y por los componentes de la clave. Si un browse no se abre instantaneamente , entonces es que tenemos un problema... y cuanto mas crezca esa tabla el problema va a ir en aumento.

Evitar Table Schematics complicados En general hay que tratar de nunca enlazar mas de un nivel de tablas en el Table Schematic. Cuantas mas tablas mas complejo sera el JOIN que debera resolver el motor para mostrar el browse. Si de todas maneras hay que usar un Table Schematic complicado, entonces ver si se

Prog Progra rama mand ndo o con con CLAR CLARIO ION N + SQL SQL (MS (MS-S -SQL QL))

121 121

puede usar INNER en las propiedades de todas las relaciones, ya que por defecto Clarion genera un JOIN del tipo OUTER que es mucho mas costoso de resolver.

No poner demasiados campos en el Browse En caso que se deseen mostrar muchos datos de un cliente por ejemplo, lo mejor es poner todos los campos en un panel al costado del browse e ir a buscar todos los datos de esa tabla o las relacionadas r elacionadas que se necesiten en el metodo TakeNewSelection del browse. De esta manera tendremos los locators y datos basicos en el browse y el resto de la informacion en un panel que se actualizara solo cuando encuentre el registro que estoy buscando Hacer depuracion con volumenes de datos reales Hay que tratar de generar siempre datos de prueba de tal manera que al probar la funcionalidad del sistema estemos tratando con volumenes de datos que sean representativos del volumen real. Si no hacemos esto lo mas probable es que a nosotros todo nos funcione muy rapido, pero en un par de meses de puesto en marcha el sistema van a haber cosas que funcionaran demasiado lento. Esto ademas produce un efecto domino ya que un “browse lento” implica que el server esta haciendo mucho esfuerzo por resolverlo, dejando mas lento tambien los otros procesos y otras conexiones de usuarios. Uso de Claves Unicas Todas las claves que se van a usar en los Browses deberian ser unicas, el problema que podemos tener si la clave no es unica, es que el browse pierde el puntero de donde esta posicionado y repite los registros. Esto produce un efecto sumanente confuso para el usuario. La primera opcion es agregar el componente de la clave como ultimo componente de la clave duplicada. La otra opcion es poner el componente de la clave en Aditional Sort Fields en los browses que asi lo necesiten. Siempre que sea posible, se recomienda definir campos autonumbers en las claves principales (PK). De esta forma es mas sencillo "unificar" aquellas claves que no son unicas utilizadas en los browses. Ejemplo: Supongamos que tenemos tenemos dos claves en una tabla Clientes: Por_Codigo y Por_Descripcion, al adicionarle el campo de clave unica (IdAutonumber) al final de la clave estariamos unificando la misma: · ·

Por_Codigo Por_Descripcion

= Codigo + IdAutonumber = Descripcion + IdAutonumber

De esta forma nos estamos asegurando que el browse va a ser muy rapido al momento de ejecutarse el mismo. Otro efecto anomalo de "NO Utilizar Claves Unicas" que nos puede aparecer en los browses es la duplicacion de registros al momento de visualizarlos.

Locators Filter , no Incremental En general toda la filosofia de Browses y Locators hay que tratar de evitarla en aplicaciones altamente transaccionales.

122


Pero como eso es virtualmente imposible, tratemos de usar siempre locators Entry. Si es absolutamente necesario usar Locators que se refresquen con cada tecla, entonces es preferible Filtered que Incremental. Tengamos en cuenta que con este tipo de Locators se prepara y ejecuta una sentencia SQL y vuelve un conjunto de datos por cada tecla que presiona el usuario! Usando locators Filter en sus Browses en lugar de locators Step o Incremental puede reducir el volumen de datos enviados entre el cliente y el servidor. Mientras los locators Step o Incremental producen trafico SQL del tipo: SELECT campos ... WHERE CampoLocator >= "VALOR"

El subconjunto de datos retribuido es mucho mayor que en la sentencia generada por un locator Filter: SELECT campos ... WHERE CampoLocator LIKE “VALOR*”

Tener en cuenta que la sentencia LIKE es muy ineficiente si no existe un índice por ese campo, y que además la mayoría de los motores no soportan un LIKE sobre campos numéricos.

Fixed Thumbs y Movable Thumbs en las Barras de Desplazamiento Por defecto, el código generado por los Wizards de Clarion usan Fixed Thumbs cuando se hace un browse de tablas SQL porque los Movable Thumbs pueden disminuir mucho la performance en tablas SQL grandes. Por eso se recomienda usar Fixed Thumbs en los Browses armados manualmente. En las propiedades del List Box seleccionar Scroll Bar Behavior. En el Scroll Bar Type seleccionar fixed Thumb. 1.5.18 SQL Advanced Tab

En Clarion6 fue introducido en el procedimiento template Browse el Tab " SQL Advanced". Esta nueva funcionalidad puede ser utilizada para mostrar campos calculados dentro de un browse como por ej. realizando un SUM, AVG, COUNT.


123

La idea de este template es bastante simple: Configurar una lista de campos a ser mostrados en el VIEW y especificar como se cargan esos campos. Con los valores especificados arriba, el template generara el codigo SQL adecuado para cada campo. Para ello utiliza el comando PROP:Name para sustituir un campo en la declaracion del Select. Ejemplo: En un Browse quiero mostrar el total vendido por producto. En este ejemplo vamos a utilizar la tabla Order_details de la base de datos northwind. Esta tabla tiene el detalle de lo vendido por productos, con lo cual necesitamos hacer un SUM(TOTAL) de la misma por productos. Si lo hariamos desde el motor la sintaxis correcta seria: SELECT a.ProductID, b.ProductName, SUM(a.UnitPrice * a.Quantity - a.UnitPrice * a.Qua FROM [Order Details] a INNER JOIN Products b ON b.ProductID = a.ProductID GROUP BY a.ProductID, b.ProductN

Si observamos el file schematic , en este ejemplo vemos que accedemos directamente a la tabla Order_details realizando un JOIN con products.

124


En el "list Box Format" del browse, mostramos el codigo + Nombre del Producto (Products) y el campo de UnitPrice (Order_details).

Si observamos el tab "SQL Advanced" vamos a ver que hay 2 campos con asignaciones de datos. La razon por la cual Ord:ProductId tiene un valor '1' es porque los mismo se


125

linkean con otras tablas en el Project de clarion. La propiedad PROP:NAME conjuntamente PROP:GROUPBY y PROP:HAVING son las que permiten a Clarion (a partir de clarion 6) extender la funcionalidad del VIEW. Si miramos el codigo interno generado por esta consulta es el siguiente: v{'Ord:UnitPrice', PROP:Name} = 'Sum((a.UnitPrice*a.Quantity)-((a.UnitPrice*a.Quant v{'Ord:ProductID', PROP:Name} = '1' v{PROP:GroupBy} = 'b.ProductId,b.ProductName'

En la imagen de abajo visualizamos la consulta funcionando:

1.5.19 Process /Reports

Cuando se trabaja con los procedimientos de tipo Process y Reports, se le debe indicar al motor cuales son los campos que va a necesitar trabajar. Esa lista de campos debera colocarla en los Tab "Hot Fields ". Con TPS el registro entero esta siempre disponible, en cambio con SQL en los Browse/procedure/report unicamente van a ser negociados con el Motor aquellos campos que se encuentren en el VIEW generado por los procedimientos.

126


Cantidad de registros aproximado (Approximate Record Count) Por defecto, los Templates de Clarion generan código para contar el numero total de registros a ser procesados en un report o process. Esto permite que se muestre una barra de progreso con el porcentaje real de registros procesados sobre el total durante la generación del reporte. Sin embargo para tablas SQL el SELECT COUNT(*) generado automáticamente por el Driver puede ser lento si son tablas muy grandes. Por eso, se recomienda poner un numero de registros aproximado para evitar la generación del SELECT COUNT (*). Ejemplo: Abrir el dialogo Report Properties y en el campo Record Filter, poner 1 para habilitar el Aprox. Record Count. Otro filtro que se puede usar es ClavePrimaria > 0, en algunos casos esto puede acelerar el reporte forzando el acceso a través de la clave primaria. En el campo Aprox. Record Count, poner un contador aproximado para ese reporte. Esto hará que la barra de proceso no muestre el porcentaje exacto de registros procesados por el reporte, pero es mas rápido ya que se evita el conteo de registros por parte del Server. 1.5.20 Transacciones Batch

La mayoría de las bases de datos SQL operan en modo auto commit. Esto significa que cualquier operación que actualice la tabla (ADD, PUT, DELETE) ejecuta un COMMIT implícito. Esto puede ser muy lento para actualizaciones en lotes o masivas (batch). Para optimizarlo podemos encerrar todo el procesamiento en una transacción, esto es entre un LOGIN y un COMMIT. Ejemplo: LOGOUT(.1,OrderDetail) ! Be gi n Tr a n sa ct i o n DO ErrHandler ! Si e mp r e c h eq ue ar e r r o r e s LOOP X# = 1 TO RECORDS(DetailQue) ! Pr o ce sa r l o s GET(DetailQue,X#) ! Tr a i g o e l r e g i s t r o d e l a c ol a DO ErrHandler ! Er r o r e s ? Det:Record = DetailQue ! As i g no e l r e c or d Bu f f e r ADD(OrderDetail) ! y l o a g r e g o al a r c hi v o DO ErrHandler ! Er r o r e s ? END COMMIT ! Te r mi n o l a t r a ns a cc i o n s i n p r o b l e ma s

r e gi s t r o s de u na co l a

ErrHandler ROUTINE ! Er r o r r o ut i n e IF NOT ERRORCODE() THEN EXIT. ! To do OK, c o nt i n ua ROLLBACK ! Si h ub o u n p r o bl e ma en t o nc e s de s ha go l a t r a ns a c c i MESSAGE(‘Error en la Transaccion - ‘ & ERROR()) RETURN 884

on

1.5.21 Manejo de Transacciones

El comando LOGOUT() realiza el comienzo transaccional para un conjunto especificado de archivos. El LOGOUT() le informa al file driver que una transacción está comenzando y al mismo se encarga que los archivos sean bloqueados para el tratamiento transaccional. Solamente un LOGOUT() puede estar activo a la vez; un segundo LOGOUT() sin un anterior COMMIT o ROLLBACK genera el errorcode() = 56.


127

Siempre que agregamos o modificamos datos dentro de un LOGOUT estos datos quedan Lockeados. Si algun browse o reporte intenta leer estos datos va a quedar “colgado”.Por esto las transacciones deben ser siempre lo mas cortas posible. Nunca se debe dejar una transaccion abierta con algun mensaje o intervencion pendiente del usuario. Un ejemplo claro de mala practica es iniciar una transaccion (LOGOUT) en el Init de una carga de cabecera / detalle, un COMMIT en el TakeCompleted y un ROLLBACK en el Cancel. Todos los registros que se agreguen estan lockeados hasta que el usuario no se decida, generando lockeos con otras terminales. Lo que debe hacerse es trabajar todo en memoria o tablas auxiliares y confirmar todo el procesamiento transaccional en el minimo tiempo posible (generalmente en el TakeCompleted).

1.5.22 Stored Procedures Hay tres caminos principales de comunicacion con una base de datos de Microsoft SQL usando Clarion: 1. Permitiendo que los templates y el lenguaje Clarion trabaje por nosotros. 2. Usando PROP:SQL en determinados embebidos enviando el codigo SQL directamente. 3. Usando PROP:SQL con un Stored Procedure.

Un stored procedure es un programa (o función) el cual está físicamente almacenado en una base de datos. La ventaja de un stored procedure es que cuando está en acción, en respuesta a una petición de usuario, corre directamente en el motor de bases de datos, aumentando con ello generalmente, la rapidez de proceso. No Solamente un programa Clarion puede acceder a un stored procedure, sino que se pueden acceder desde otr aplicativo o desde un editor de SQL. Desde Clarion hay dos formas de ejecutar un Stored Procedure:

1) Llamando a un stored que nos devuelve un pool de datos: Ej e mp l o :

Loc:Query = 'CALL sp_Nombre_Store('&Parametro1&' ,'&Parametro&')' ResSQL{prop:sql} = Loc:Query

!!

e nv i a mo s el

q ue r y a l

Mo t o r

IF FILEERRORCODE() ! ! ma pe amo s e l e r r o r SETCLIPBOARD(Loc:query) ! ! c ol o ca mo s al Cl i p bo ar d el q ue r y p or s i q ue r e mo s MESSAGE('Atencion Ocurrio un error ='&FILEERROR(),'ERROR',ICON:HAND) ELSE LOOP Until Access:ResSQL.Next() !!

Pr o ce s amo s l o s da t o s de v ue l t o s po r e l

END END

2) Llamando a un stored que no devuelve datos :

s t o r e d Pr o c ed ur e

ej e cu t a r l o d

128


Ej e mp l o :

Loc:Query = 'NORESULTCALL sp_Nombre_Store('&Parametro1&' ,'&Parametro&')' ResSQL{prop:sql} = Loc:Query

!!

e nv i a mo s el

q ue r y a l

Mo t o r

IF FILEERRORCODE() ! ! ma pe amo s e l e r r o r SETCLIPBOARD(Loc:query) ! ! c ol o ca mo s al Cl i p bo ar d el q ue r y p or s i q ue r e mo s MESSAGE('Atencion Ocurrio un error ='&FILEERROR(),'ERROR',ICON:HAND) END

ej e cu t a r l o d

1.5.23 Usando TurboSQL

Desde Clarion 6.3 en adelante, los drivers SQL (excepto Oracle) soportan el driver string /TURBOSQL. Si esta seteado en TRUE, el driver no verificara que todas las columnas existan en el server al hacer el OPEN. Es decir, que el driver no verifica que la estructura de la tabla definida en el dct coincida con la estructura de la tabla fisica, razon por la cual nunca se va a dar el error "invalid record declaration". Se puede usar este driver string para declarar un buffer en la aplicacion para recibir los resultados de las ejecuciones de prop:sql sin la necesidad de definir una tabla exactamente igual en el motor SQL. Es muy util tambien para los casos en los que no tenemos acceso del tipo administrativo al motor SQL y/o no podemos acceder a modificar la estructura de las tablas definidas. Una de las cosas a tener en cuenta al utilizar esta propiedad, es que debemos tener sumo cuidado con el control de errores. Si el sistema asume la existencia de una columna que fue eliminada en el motor, es probable que esto provoque la caida del sistema o nos emita un "GPF" (Error de Windows). Otra de las cosas a tener en cuenta es que esta propiedad esta pensada para los casos en donde se reciben resultados desde sentencias PROP:SQL enviadas al motor. Por lo tanto no es aconsejable utilizarlo en tablas que requieran actualizaciones. Para setearlo puede hacerlo desde el diccionario poniendo '/TURBOSQL=1' en 'Driver Options' o en por source con el comando SEND 1.5.24 Uso del trace

Usar el trace

Siempre que tengamos una duda de “que esta pasando” el programa \Clarion6\bin\trace.exe nos puede ser de gran ayuda para mostranos exactamente lo que le esta llegando al motor, como estan los datos del buffer , etc.. Esta utilidad genera un archivo log con informacion que es muy importante a modo de debug, acerca del diálogo que se esta llevando a cabo entre el driver y el motor de base de datos (como por ejemplo los queries sql generados, los codigos devueltos, etc) o tambien con cualquier otro tipo de archivo (isam, ado, etc)


129

A continuacion vamos a ver un ejemplo con el driver ODBC:

Estas opciones se almacenan en el archivo WIN.INI, siendo para este ejemplo los siguientes valores: [CWODBC] Trace=1 TraceFile=c:\temp\log_fb_test_01.txt Profile=1 Details=1 Flush=1 Profile=1 (Show Clarion Statements) indica que se debe incluir información respecto

de los comandos de Clarion sobre la lectura/escritura de archivos

Details=1 (Show record buffer contents) indica que se debe incluir información sobre el contenido de los registros (buffers) de los archivos. Sólo se habilita si Profile=1

. En caso de que el archivo se encuentre encriptado, para poder ver el contenido del registro también hace falta activar la propiedad PROP:AllowDetails sobre el driver ('/ALLOWDETAILS =TRUE' )

Trace=1 (Trace calls to back end) indica que se debe incluir información sobre el

código sql generado hacia el motor, con su correspondiente código de retorno desde el motor En File to store tracing in (TraceFile) se indica el nombre del archivo que se va a

generar; si desea redireccionar la información generada hacia el debug, debe tipear la palabra reservada DEBUG

El siguiente es un fragmento de un archivo log generado con Profile=1: 05BCH(2) 19:57:13.326 QUERY_KEY(COUNTRY:016AAA8H) Time Taken:0.00 secs 05BCH(2) 19:57:13.326 BUFFER(COUNTRY:016AAA8H) Time Taken:0.00 secs

130


05BCH(2) 19:57:13.326 QUERY_KEY(COUNTRY:016AAA8H) Time Taken:0.00 secs 05BCH(2) 19:57:13.326 GET_PROPERTY(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.326 GET_PROPERTY(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.326 BUFFER(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.326 SET_PROPERTY(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.326 SETview(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.346 NEXT(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.02 secs 05BCH(2) 19:57:13.346 COU:COUNTRY : 'Australia' 05BCH(2) 19:57:13.346 COU:CURRENCY : 'ADollar' 05BCH(2) 19:57:13.346 POSITIONfile(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.346 NEXT(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.346 COU:COUNTRY : 'Austria' 05BCH(2) 19:57:13.346 COU:CURRENCY : 'Schilling' 05BCH(2) 19:57:13.346 POSITIONfile(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.346 NEXT(VIEW:187AB8:COUNTRY:0187A68H) Time Taken:0.00 secs 05BCH(2) 19:57:13.346 COU:COUNTRY : 'Belgium' 05BCH(2) 19:57:13.346 COU:CURRENCY : 'BFranc' El siguiente es un fragmento de un archivo log generado con Trace=1: 0B18H(2) 19:46:57.962 Setting Cursor Type to Forward Only for Statement 0C72498H Time Taken:0.00 secs 0B18H(2) 19:46:57.962 Resetting Parameters Statement 0C72498H Time Taken:0.00 secs 0B18H(2) 19:46:57.962 Preparing Statement 0C72498H : SELECT A.COUNTRY, A.CURRENCY FROM COUNTRY A ORDER BY A.COUNTRY Time Taken:0.02 secs Importante: no se olvide de apagar el trace manualmente, ya que el mismo no se

apaga automaticamente al cerrar la ventana (si queda encendido se dara cuenta porque la aplicacion corre mas lenta) Ademas del trace tradicional mediante la pantalla que hemos detallado, también se puede controlar la información de manera condicional, mediante el uso de propiedades enviadas al driver Por ejemplo: SYSTEM{PROP:DriverTracing} = '' SYSTEM{PROP:DriverTracing} = '1' tabla{PROP:Profile}=Pathname

!desactiva el log para todos los drivers !activa el log nuevamente !setea Profile=1


tabla{PROP:Profile}='' LOC:Nombre = tabla{PROP:Profile} tabla{PROP:Log}='test' tabla{PROP:Details}=1 tabla{PROP:Details}=0

131

!setea Profile=0 !consulta el nombre del archivo log !escribe el 'test' en el archivo log !setea Details=1 !setea Details=0

1.5.25 Cargas de Cabecera - Detalle

Este es un tema que siempre se presta a discusiones en todos los cursos. Hay muchas formas de resolverlo, pero les voy a hacer un resumen de cual es la manera mejor y mas facil que he encontrado.

Puntos a tener en cuenta Supongamos que vamos a cargar comprobantes, Facturas por ejemplo. No vamos a usar autonumeracion de Clarion para la tabla Facturas. Generacion del Numero de Transaccion

Al abrir el Form de Facturas genero un nro unico que identifique esta transaccion, se puede hacer de varias maneras, como ya se explico en el tema "Campos autonumerados". Carga del Detalle

El detalle lo cargo en una tabla auxiliar. Tambien podria hacerse en un Queue pero en general es mas complicado. La ventaja de usar un DetalleAUX es que se usa direcamente un browse con ABM o EIP, como siempre. Tambien puede usarse el In Memory Driver para esta carga, y de hecho es ideal para eso. El browse de DetalleAUX lo filtro (rango) por el NroTransaccion y obviamente este dato lo uso como enganche entre la cabecera y DetalleAUX. Cada nuevo registro en DetalleAUX le asigno este NroTransaccion. Confirmacion de la Transaccion

En el Take Completed, antes del Parent Call: - abro una transaccion - calculo el Numero oficial del comprobante, mediante una tabla de numeraciones. - inserto la cabecera - muevo todos los registros desde DetalleAux a la tabla real de Detalle (borrando DetalleAUX) - cierro la transaccion Esto seria ideal hacerlo en un Stored Procedure ya que tendriamos mejor control de la transaccion y seria mucho mas rapido. Importante: Recuerden que todo el tiempo que este la transaccion abierta estamos provocando lockeos que pueden afectar browses o reportes que trabajen sobre la tabla facturas. En el Cancel borro los registros de la transaccion en DetalleAux.

132


Si hay colgadas, puede ir quedando basura (inofensiva) en la tabla DetalleAux, la cual seria bueno borrar cada tanto. La tabla DetalleAux debe identificar el usuario en una columna, o bien crear una nueva tabla temporal por cada usuario. Lo mas aconsejable es el In-Memory Driver 1.5.26 Creando un Editor de SQL

Apliquemos lo anterior para crear un editor de SQL sencillo.

Primero necesitamos crear un Diccionario con la variable Global de conexión.(tambien podria estar en el app, claro)

Y la tabla SQL. En este caso crearemos solo 5 campos…

Recordemos que siempre se debe usar un String de conexión variable en el Owner de las tablas.


Creamos un app con este DCT

133

134


En global Embeds – Program setup inicializamos el String de Conexión. En este caso desde un INI, pero tambien se podria llamar a una simple pantalla de conexión que nos pida los datos de server, base, usuario y pasword.

Para simplificar el ejemplo usaremos codigo basico CLARION, no ABC. Por lo tanto deshabilitamos el Defer Open Files. Si no lo hicieramos, la tabla SQL podria no estar abierta cuando quisieramos recorrerla con NEXT. (En general yo “siempre” deshabilito ese check.)

Creamos una ventana para nuestro procedimiento principal.


Elegimos Simple Window, sera la mas indicada en este caso.

135

136


En el Table Schematic agregamos nuestra tabla SQL

En Local Data definiremos una variable TextoSQL donde poder escribir las sentencias SQL a ejecutar y una cola de memoria donde vamos a guardar los resultados. A los campos de la cola de memoria le ponemos los mismos nombres que a la tabla SQL para poder hacer directamente una asignacion profunda :=:


Diseñamos una pantalla con este formato

137

138



139

Creamos un control listbox, NO un template browse. Elegimos los campos de la cola de memoria en el mismo orden. (Tambien podriamos usar el atributo Field Number si quisieramos cambiar el orden de los campos en el list, pero en este caso no tiene sentido)

140


No olvidarse de setear la propiedad From del List

Este es todo el codigo que va en el Boton. En SQL usamos siempre FileError() en vez de Error(). FileError() devuelve el error detallado que reporta el motor SQL.


Ya tenemos nuestro propio Editor SQL!

1.6

Links - Material Recomendado In-Memory Database Driver

141

142


Microsoft - SQL Server http://www.mug.org.ar Libros en Pantalla Funciones SQL http://www.mssqltips.com http://www.programacion.com Clarion Magazine publicacion de Dave Harms. Muy buen recurso CLARION SQL Server Magazine http://sqlzoo.net/ Strings de Conexion icetips Productos Templates Clarion


143

Ejemplos

1.6.1

In-Memory Database Driver

SoftVelocity ha desarrollado un driver de archivos que nos permite utilizar la misma tecnología de los drivers de Clarion para el acceso a base de datos, pero utilizando tablas "en memoria" (In-Memory driver o IMDD). Esto significa que podemos utilizar los mismos comandos con los que estamos familiarizados para acceder y actualizar tablas con cualquier otro driver de archivos. Los datos seran almacenados en memoria RAM, lo que nos brinda una serie de ventajas. Soporta la suma de tipos de datos de todos los drivers de archivos. Elimina el uso de tablas temporales. Se puede utilizar el comando copy para duplicar tablas en memoria de manera instantanea. Es compatible con cualquier template o producto de terceras partes En aplicaciones SQL se puede utilizar como buffer para recibir el resultado de los queries sql. Una vez que la tabla en memoria recibe el conjunto de datos se pueden aplicar ordenamientos, filtros, queries QBE/QBF y demás utilizando los comandos y procedimientos estandar de Clarion (por ejemplo Browse). Esto reduciria notablemente el trafico en la red y los recursos del servidor. 1.6.2

Ejemplos

El curso de SQL, cuenta con dos ejemplo de Clarion (App/Dct) . Los mismos fueron desarrollados en Clarion6 cuyo dct se realizaron importando la Base Northwind del Motor MS-SQL a un dct. Luego de la instalacion del manual, se encuentran ubicados en el sub-directorio "ejemplos" del directorio de instalacion del mismo (por defecto: "C:\ClarionCursoSQL")

Northwind.app Este app contiene ejemplos de:

Uso de SQL Advanced Tab

Aplicacion de Uso de SQL Advanced Tab en un Reporte

Uso de Identity en Clarion

Uso de Prop:Where

144


Uso de queues y filtros con fechas

Uso de Stored Procedures.

sqlEditor.app Este app contiene el ejemplo del Editor de sql.


145

Index

Integridad 92 Integridad de los datos

Index

-K-

-A-

Keys

Advanced Tab Archivos 85

-LLogin

Batch 126 Browses Lentos? BUFFER 111

91

122

-B-

Campos Date 113 Cargas de Cabecera - Detalle Clarion vs. SQL 116 Claves 91 Conexion 100 Consultas 58 creacion 93 CSTRING x STRING 104

108

-M-

120

Manejo Fechas 104 Manipulación de Datos Migracion 105

-C131

-NNext 117 Normalización Nulls 118

-DDatabase Driver 101 Definición de datos 30 DTS 13

-P-

-EEditor SQL 132 Errores 117 External Name 87

100

Previous 117 Process 125 Prop:SQL 109 Prop:SQLFilter 111 Prop:Where 112 Propiedades 85

-R-

-F51

-IIndices

16

-OODBC 102 Owner Name

fecha

24

91

Recomendaciones Relaciones 92 Reports 125

106

31

147

Curso SQL para Clarion

Recommend Documents