Curso Informix-SQL

CURSO DE INFORMIX-SQL

Convenciones utilizadas

En función de los posibles manejadores de base de datos Informix disponibles en el mercado, Informix Standard Engine o Informix-OnLine, las descripciones de los comandos tendrán la forma:

COMANDO

Descripción

Descripción del propósito general del comando.

Sintaxis:

SE Sintaxis del comando para el manejador Standard Engine

OL Sintaxis del comando para el manejador OnLine

Informix es una marca registrada de Informix Software, Inc.

Introducción

Estructura del menú de SQL

El entorno de trabajo provisto por Informix-SQL consta de una serie de menúes que permiten ejecutar todas las operaciones posibles desde el ambiente. Se presenta a continuación la estructura del menú principal:

Query-language Report Form

New Run Run

Run Modify Modify

Modify Generate Generate

UserEditor New New

Output Compile Compile

Choose Drop Drop

Save Exit Exit

Info

Drop

Exit

Informix-SQL

Database UserMenu Table

Select Run Create

Create Modify Alter

Drop Exit Info

Exit Drop

Exit

Tipos de Datos en SQL

Cada columna de una base de datos debe tener asignado uno de los siguientes tipos de datos:

CHAR(n) SE y OL	String de caracteres de longitud n, (1<=n<=32.767) Ocupa n bytes
VARCHAR(max[,min]) OL	String de longitud variable de hasta max caracteres (1<=max<=255). Si está presente, min garantiza el número mínimo de caracteres a reservar para esa columna. El espacio que ocupa es el que insume cada fila de datos más 1 byte para la longitud.
SMALLINT SE y OL	Entero entre -32.767 y 32.767 inclusive. Ocupa 2 bytes
INTEGER SE y OL	Entero entre -2.147.483.647 y 2.147.483.647 inclusive. Ocupa 4 bytes.
DECIMAL[(m[,n])] SE y OL	Punto flotante decimal con m dígitos significativos (la precisión y n dígitos a la derecha del punto decimal (la escala). m<=32 y n<=m. Default: DECIMAL(16)
SMALLFLOAT SE y OL	Punto flotante equivalente a DECIMAL(8)
FLOAT SE y OL	Punto flotante equivalente a DECIMAL(16)
MONEY[(m[,n])] SE y OL	Equivalente al tipo DECIMAL. Cuando una columna de este tipo es mostrada es precedida por el símbolo definido en la variable de ambiente DBMONEY. El default para n es 2, y el default es MONEY(16,2)
SERIAL[(n)] SE y OL	A una columna con este tipo se le asigna automáticamente un entero secuencial. Si está presente, n indica el número inicial de la secuencia. El default para n es 1. Ocupa 4 bytes.
DATE SE y OL	Se utiliza para almacenar fechas. Al momento de la asignación, se debe hacerlo mediante las comillas. El modo default para mostrar una columna de este tipo es de la forma mm dd yyyy. Las fechas son dadas internamente en notación juliana, es decir, el número de días desde el 1-Ene-1900. Ocupa 4 bytes.

DATETIME c1 TO c2 SE y OL	Permite almacenar un instante de tiempo con mucha más granularidad que un campo DATE. Su precisión puede variar de años a fracciones de segundo. c1 y c2 pueden ser: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND o FRACTION(n), con n indicando la cantidad de dígitos significativos con un default de 3.
INTERVAL c1 TO c2 SE y OL	Permite almacenar una diferencia de instantes de tiempo con mucha granularidad. Por razones de implementación hay dos tipos de intervalos posibles. Así: c1 y c2 pueden ser YEAR to MONTH o bien DAY, HOUR, MINUTE, SECOND o FRACTION(n) con n indicando la cantidad de dígitos significativos con un default de 3.
TEXT OL	Es uno de los dos posibles tipos de BLOBs (Binary Large Objects), streams de bytes de hasta 2 GBytes de capacidad, que permite almacenar información basada en caracteres como documentos de procesadores de palabras o archivos ASCII. Se almacenan en un espacio separado de la tabla.
BYTE OL	Es el otro tipo posible de BLOBs (Binary Large Objects) que permite almacenar información no basada en caracteres como imágenes digitalizadas o patrones de voz. Se almacenan en un espacio separado de la tabla.

Comandos de SQL

Podemos agrupar las sentencias del Informix-SQL en cinco tipos:

1 - DDL (Lenguaje de Definición de Datos)

Base de Datos	Tablas	Vistas	Sinónimos	Indices	Generales
CREATE DATABASE	CREATE TABLE	CREATE VIEW	CREATE SYNONYM	CREATE INDEX	UPDATE STATISTICS
DATABASE	ALTER TABLE	DROP VIEW	DROP SYNONYM	ALTER INDEX	RENAME COLUMN
CLOSE DATABASE	RENAME TABLE			DROP INDEX
DROP DATABASE	DROP TABLE

2 - DML (Lenguaje de Manejo de Datos)

SELECT

INSERT

UPDATE

DELETE

3 - Acceso a los datos

Permisos sobre tablas o base de datos	Locking de tablas
GRANT	LOCK TABLE
REVOKE	UNLOCK TABLE
	SET LOCK MODE

4 - Integridad de los datos

Transacciones	Tablas
BEGIN WORK	CREATE AUDIT
COMMIT WORK	DROP AUDIT
ROLLBACK WORK	RECOVER TABLE
START DATABASE
ROLLFORWARD DATABASE

5 - Sentencias Auxiliares

CHECK TABLE	LOAD	INFO
REPAIR TABLE	UNLOAD	OUTPUT

Lenguaje de Definición de Datos - DDL

En el DDL se incluyen las sentencias que nos permiten crear y/o eliminar una base de datos y sus tablas, vistas e índices y aquellas que cambian o renombran tablas y columnas. Todas ellas trabajan sobre la base de datos actual -salvo, claro está, las que crean, seleccionan o eliminan una base de datos-.

CREATE DATABASE

Descripción

Esta sentencia se utiliza para crear una nueva Base de Datos.

Sintaxis:

SE CREATE DATABASE Nombre-Base-De-Datos

[WITH LOG in "pathname" [MODE ANSI]]

OL CREATE DATABASE Nombre-Base-De-Datos

[IN Nombre-DBSpace]

[WITH [BUFFERED] LOG]

[WITH LOG MODE ANSI]

· En Standard Engine, la base se crea a partir del directorio actual de trabajo, y se puede especificar el nombre del path donde ubicar el transaction log file.

· En OnLine la base se crea en el root dbspace, salvo que se indique lo contrario por la cláusula IN.

· Si se usa el modo BUFFERED para el transaction log file, se mejora la performance a cambio de la posibilidad de perder las últimas operaciones previas a una caída.

· En ambos casos, el modo ANSI adiciona las siguientes prestaciones:

þ Todas las sentencias son tratadas como transacciones automáticamente, en modo unbuffered

þ Las referencias a las tablas, vistas, sinónimos, índices, etc. deben ser hechas con el nombre del propietario, salvo que sea éste quien las invoque.

þ Medidas de seguridad: los usuarios no reciben por default el privilegio de Public sobre las tablas y sinónimos.

· La base de datos creada queda como la base de datos activa.

DATABASE

Descripción

Esta sentencia se utiliza para seleccionar una Base de Datos accesible como Base de Datos activa.

Sintaxis

DATABASE Nombre-Base-De-Datos [EXCLUSIVE]

· Se puede seleccionar una base de datos de otro server, especificando el nombre del mismo.

· Si se está trabajando con una base de datos y se usa esta sentencia, se cierra la anterior, a menos que la misma esté en otro server, en cuyo caso reporta un error

· La cláusula EXCLUSIVE se utiliza para impedir que otros usuarios ingresen a la base de datos. Si alguien la está usando, reporta un error.

CLOSE DATABASE

Descripción

Esta sentencia se utiliza para cerrar la Base de Datos activa.

Sintaxis

CLOSE DATABASE

· Si se está trabajando con transacciones, hay que dejarlas cerradas antes de cerrar la base de datos

DROP DATABASE

Descripción

Esta sentencia se utiliza para borrar una Base de Datos completa, incluyendo todos los catálogos del sistema, índices y datos.

Sintaxis

DROP DATABASE Nombre-Base-De-Datos

· Para poder usar esta sentencia, es necesario ser el usuario informix o tener el privilegio DBA. En este caso no solicita confirmación.

· No se puede borrar una tabla que está siendo utilizada por otros usuarios

· En Standard Engine, borrar una base de datos implica borrar el transaction log file.

· En Standard Engine se remueve el subdirectorio que creó para la base de datos, a menos que el usuario hubiera agregado allí archivos que no correspondan a la base.

CREATE TABLE

Descripción

Esta sentencia se utiliza para crear una nueva tabla en la Base de Datos, especificando restricciones de integridad sobre las columnas, diseñar el tamaño de los extents inicial y subsiguientes y especificar cómo será el locking de esa tabla.

Sintaxis

SE CREATE [TEMP] TABLE Nombre-Tabla

(Nombre-Columna Tipo-Dato [Cláusula Default] [NOT NULL],...

[Cláusula CONSTRAINT])

[IN "pathname"]

OL CREATE [TEMP] TABLE Nombre-Tabla

(Nombre-Columna Tipo-Dato [Cláusula Default] [NOT NULL],...

[Cláusula CONSTRAINT])

[IN Nombre-DBSpace]

[EXTENT SIZE Primeros k bytes [NEXT SIZE Próximos k bytes]]

· Los nombres de las tablas deben ser únicos dentro de la base de datos. En una base ANSI, la combinación Propietario.Nombre-Tabla debe ser único.

· Una tabla temporaria dura por la sesión de trabajo, y su nombre no puede coincidir con ninguna tabla, vista o sinónimo de la base de datos.

· Al menos una columna debe figurar al crear la tabla.

· En las tablas temporarias se puede especificar una cláusula WITH NO LOG para indicar que no registre en el transaction log file las operaciones sobre esa tabla.

· Se puede indicar dónde crear la tabla: en el caso del Standard Engine, se puede indicar un path diferente del que está la base de datos; en OnLine, se puede indicar un dbspace diferente al que está la base de datos. Más aún, se puede especificar el tamaño del primer extent y el de los siguientes (debe hacerse en páginas, con un mínimo de 4)

A nivel de las tablas se pueden definir constraints, para indicar que la columna tenga un valor por default, sean clave primaria, clave externa, o que los datos que se ingresen respondan a determinados chequeos. Para ello, se incluyen las formas generales de las diferentes cláusulas:

La forma general de una cláusula Constraint es:

[UNIQUE | PRIMARY KEY (Columna 1,...,Columna n)]

[FOREIGN KEY (Columna 1,...,Columna n) REFERENCES Nombre-Tabla

(Lista-Columnas)]

[CHECK (Condición)]

CONSTRAINT Nombre_Restricción]

· UNIQUE indica que no admita valores duplicados para esa columna. No es válida sobre columnas de tipo BYTE o TEXT.

· PRIMARY KEY se utiliza para indicar que una columna o conjunto de columnas sean la clave primaria, por lo que no admitirá valores nulos o duplicados. Sólo una definición de Primary Key es válida por tabla. No es válida sobre columnas de tipo BYTE o TEXT.

· FOREIGN KEY se utiliza para definir una columna o conjunto de columnas como una clave externa, para propósitos de establecer relaciones o joins con otras tablas, en las que deberá haber una Primary Key para coincidir. Para eso se indica con la cláusula REFERENCES la tabla con la cual se está estableciendo la relación, y sobre qué columnas de la misma. Por lo tanto, las columnas referenciadas deben ser Primary Key o -al menos- no contener valores duplicados o nulos (si no, reporta un error). No es válida sobre columnas de tipo BYTE o TEXT.

· CHECK permite establecer condiciones que deben cumplir los datos antes de insertar o actualizar una fila de la tabla. Se pueden establecer condiciones de comparación, pero no utilizar subqueries, usar funciones agregadas, identificador de fila, variables del host ni funciones como USER, TODAY, etc.

· CONSTRAINT se utiliza para generarle un nombre al constraint, por el que podrá ser luego invocado.

La forma general de una cláusula Default es:

DEFAULT { [ literal | NULL |

CURRENT [Opción del tipo DateTime] |

USER | TODAY

OL | SITENAME | DBSERVERNAME] }

· Con ella se puede indicar un valor literal o nulo para una dada columna

· CURRENT es válida para datos del tipo DateTime, y asigna el valor del instante de acuerdo a la precisión indicada.

· USER asigna un literal con el nombre del usuario

· TODAY asigna la fecha del día

· SITENAME o DBSERVERNAME (son sinónimos) asigna el nombre del server.

ALTER TABLE

Descripción

Esta sentencia se utiliza para insertar una columna en una tabla, borrar una columna de una tabla, modificar el tipo de dato de una columna, agregar, modificar o eliminar un constraint.

Sintaxis

SE ALTER TABLE { [Nombre-Tabla | Sinónimo] }

{ ADD (Nombre-Columna-Nueva Tipo-Dato [Cláusula DEFAULT]

[NOT NULL] [Cláusula CONSTRAINT]

[BEFORE Nombre-Columna-Existente][,...])

| DROP (Nombre-Columna-Vieja[,...] )

| MODIFY (Nombre-Col-Existente Tipo-Dato-Nuevo [Cláusula DEFAULT]

[NOT NULL] [Cláusula CONSTRAINT] [, ...])

| ADD CONSTRAINT [Cláusula CONSTRAINT]

| DROP CONSTRAINT [(] Nombre-Constraint [,...)] }

OL | MODIFY NEXT SIZE kbytes

OL | LOCK MODE { ( PAGE | ROW ) }

· La forma de la cláusula Default es como la descripta anteriormente

· En cuanto a la cláusula Constraint, pesan algunas restricciones: no se puede definir una Foreign Key, ni se puede agregar un constraint sobre columnas que ya tengan datos; si se utiliza Unique, la tabla sólo puede tener una fila.

· No se puede agregar una columna de tipo Serial a una tabla si ya tiene filas.

· La cláusula Not Null se puede utilizar sólo si la tabla está vacía.

· Al eliminarse una columna, se eliminan los índices o constraints definidos sobre ella.

· Al modificar una columna, se eliminan las constraints definidas sobre ella. Si se desea que permanezca alguna característica (como default, not null, etc.), se deben reespecificar en la cláusula de modificación. Se intenta la conversión de datos, aunque el éxito de la misma depende de qué tipos de datos estén involucrados.

RENAME TABLE

Descripción

Esta sentencia se utiliza para cambiar el nombre de una tabla

Sintaxis

RENAME TABLE [Propietario.]Nombre-Viejo TO Nombre-Nuevo

· Sólo puede renombrar la tabla su propietario o aquel que tenga privilegio de DBA o de Alter sobre la tabla.

· No sirve consignar un nombre de Propietario para cambiarlo: produce un error.

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

DROP TABLE

Descripción

Esta sentencia se utiliza para borrar una tabla, junto con sus datos

Sintaxis

DROP TABLE { Nombre-Tabla | Sinónimo }

· Sólo puede eliminar la tabla su propietario o aquel que tenga privilegio de DBA.

· Al eliminar una tabla, se borran todos sus datos, índices, constraints, sinónimos y vistas.

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

Vistas

Las vistas -views- son un nivel elevado de abstracción de las bases de datos, que proveen al usuario de una ventana de los datos existentes.

El usuario identifica una vista por un nombre, y la trata como a una tabla con algunas restricciones.

Una vista puede contener columnas de más de una tabla o valores resultantes de funciones sobre las columnas.

Las vistas son ventanas dinámicas de la base de datos, marcando una diferencia sustancial con las tablas temporarias, que sólo muestran el estado de la base de datos al momento que son creadas.

Habitualmente se utilizan las vistas para limitar el acceso a datos importantes, para que los usuarios puedan consultar, actualizar, insertar o borrar datos en las tablas con las restricciones que correspondan.

Las vistas pueden ser creadas o eliminadas en cualquier momento, y la información de control es mantenida en los catálogos de sistema sysviews y syscolumns.

Las vistas pueden ser definidas en función de otras vistas. Cuando se elimina la vista origen, se eliminan todas las que dependen de ella. Las consultas a través de una vista son tratadas como si fuesen sobre una tabla. A través de las vistas se pueden hacer inserciones, actualizaciones y borrados de las tablas con las siguientes consideraciones:

· No se puede modificar la base de datos si la definición de la vista incluye joins, cláusula GROUP BY, DISTINCT o UNIQUE o funciones agregadas.

· Se puede construir un form sobre una vista si las columnas de la misma se refieren sólo a una tabla.

· No se puede insertar filas a una tabla desde una vista con columnas virtuales, aunque sí se puede borrar una fila bajo esas condiciones.

· No se pueden ejecutar ALTER TABLE, CREATE INDEX o UPDATE STATISTICS sobre una vista. Se dispone de los índices existentes sobre las columnas en las tablas subyacentes.

· Si se inserta una fila desde una vista con solo algunas columnas de la tabla, las demás son completadas con NULL, salvo que esto no se permita. Si es así, no se puede insertar.

· Si se eliminan columnas de la tabla usadas en la definición de una vista, la misma deja de ser útil.

· Para evitar inserciones o actualizaciones de los datos que escapen a las restricciones de la definición de la vista, es obligatorio crearla con la clausula WITH CHECK OPTION.

· Hay que ser cuidadoso con las posibles filas duplicadas de una vista que se actualicen a una tabla con filas únicas.

Privilegios en las Vistas

Al crear una vista, se reciben los mismos privilegios que los detentados sobre la tabla subyacente. Si además esta tabla se creó con la cláusula WITH GRANT OPTION, se pueden pasar estos privilegios a otros usuarios.

Si hay más de una tabla en la construcción de la vista sólo se dispone del privilegio SELECT.

CREATE VIEW

Descripcion

Esta sentencia se utiliza para crear una nueva vista basada en tablas o vistas ya existentes

Sintaxis

CREATE VIEW Nombre-Vista [ (Lista-Columnas) ]

AS SELECT-Sentencia [WITH CHECK OPTION]

· Una vista, como se indica más arriba, puede ser usada como una tabla, salvo en las siguientes sentencias: Alter Index, Alter Table, Create Index, Create Table, Drop Index, Drop Table, Lock Table, Recover Table, Rename Table o Unlock Table.

· Se debe tener el privilegio de Select sobre todas las columnas implicadas en la definición de una vista.

· Los tipos de datos de las columnas de la vista se deducen de las columnas que le dan origen.

DROP VIEW

Descripción

Esta sentencia se utiliza para borrar una vista de una Base de Datos

Sintaxis

DROP VIEW { Nombre-Vista | Sinónimo }

· Se debe ser el propietario de la vista o tener privilegio DBA.

· También se eliminan las vistas definidas a partir de ella.

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

CREATE SYNONYM

Descripción

Esta sentencia se utiliza para propocionar un nombre alternativo a una tabla o a una vista.

Sintaxis

CREATE [PUBLIC | PRIVATE] SYNONYM Sinónimo

FOR [Nombre-Tabla | Nombre_Vista]

· El modo default de creación de un sinónimo es Public, por el cual se pone este sinónimo a disposición de todos los usuarios.

· No se puede crear un sinónimo para un sinónimo.

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

· En OL se puede crear un sinónimo para cualquier base de datos en el servidor de base de datos, de la forma Nombre_BD:Nombre_tabla.

DROP SYNONYM

Descripción

Esta sentencia se utiliza para remover un sinónimo previamente definido.

Sintaxis

DROP SYNONYM Sinónimo

· Se debe ser el propietario del sinómimo o tener privilegio DBA para eliminarlo.

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

Estrategias de Indexación

Hay dos razones básicas para crear un índice sobre columnas de una tabla en una base de datos:

Acelerar el ordenamiento de filas

Optimizar la performance de los queries

Contar con índices adecuados proporciona significativos ahorros de tiempo en los queries que efectúan los usuarios. Como contrapartida, las inserciones, borrados y modificaciones de datos se tornan mas lentas, pues los índices deben ser actualizados convenientemente.

Así resulta apropiado crear un índice sólo cuando es necesario y eliminarlo cuando deja de serlo. Aunque esto es muy sencillo en este ambiente, conviene tener presentes algunas consideraciones estratégicas.

Entonces al momento de indexar, valen las siguientes consideraciones:

No tienen sentido índices para tablas de menos de 200 filas.

No indexar sobre columnas con pocos valores posibles -sexo, estado civil, etc.-. En todo caso, puede tener sentido un índice clustered -lo veremos más adelante-.

Si la cláusula WHERE de una sentencia SELECT impone condiciones sobre una columna, crear un índice sobre la misma. Si la condición es sobre varias columnas, conviene entonces un índice compuesto sobre todas las columnas implicadas.

Si la clausula WHERE de una sentencia SELECT tiene un join entre una columna simple de una tabla y una columna simple de otra tabla, crear un índice sobre la columna de la tabla con mayor número de filas. Si varias columnas de una tabla tienen condiciones de join con varias columnas de otra tabla, crear un índice compuesto sobre las columnas implicadas de la tabla con mayor número de filas.

Para el uso de ciertos utilitarios -como DBLOAD o DBLINK- conviene eliminar previamente los índices existentes y luego regenerarlos.

Como comentario adicional, tener presente que, ante una sentencia SELECT que incluya un join entre 2 tablas sin un índice sobre las columnas implicadas, RDSQL crea un índice temporario por razones de optimización.

Estrategias de Indexacion-Indices Clustered

Como la información es extraída del disco en bloques, en ciertos casos puede convenir que la misma esté físicamente agrupada en el disco para una más óptima recuperación. Para ello se cuenta con la posibilidad de indicar que un índice sea clustered, a través de la sentencia CREATE INDEX o la ALTER INDEX. Por ejemplo, en ciertas consultas según el sexo o el estado civil, la existencia de un índice de este tipo puede resultar en un trabajo más eficiente del disco.

CREATE INDEX

Descripción

Esta sentencia se utiliza cuando se desea crear un índice para una o más columnas de una tabla.

Sintaxis

CREATE [UNIQUE|DISTINCT] [CLUSTER] INDEX Nombre-Indice

ON {[Nombre-Tabla|Sinónimo]} (Nombre-Columna [ASC|DESC] ,...)

· Para crear un índice, hace un lock exclusivo sobre la tabla.

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

· En SE no se puede utilizar la opción Cluster si la tabla tiene un audit trail file.

· Si se incluye más de una columna, es tratado como una composición de esos datos.

· En SE se pueden incluir hasta 8 columnas en la definición de un índice, siempre que no superen los 120 bytes.

· En OL se pueden incluir hasta 16 columnas en la definición de un índice, siempre que no superen los 255 bytes.

· La cláusula Unique o Distinct previene la admisión de valores duplicados en esa columna.

ALTER INDEX

Descripción

Esta sentencia se utiliza para organizar físicamente las filas de una tabla en el orden de un índice existente o para cancelar el atributo de clustering.

Sintaxis

ALTER INDEX Nombre-Indice TO [NOT] CLUSTER

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

· En SE no se puede modificar un índice a Cluster si la tabla tiene un audit trail file.

· Sólo puede haber 1 índice clustered por tabla.

· Para llevar un índice a clustered, hace un lock exclusivo sobre la tabla. Además, debe tener suficiente espacio en disco para mantener 2 copias de la tabla.

· Llevar un índice a Not Cluster no implica reordenamiento físico alguno.

DROP INDEX

Descripción

Esta sentencia se utiliza para borrar un índice.

Sintaxis

DROP INDEX Nombre-Indice

· Se debe ser el propietario del índice o tener privilegio DBA para eliminarlo.

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

· El uso de esta instrucción no tiene efecto sobre las constraints.

RENAME COLUMN

Descripción

Esta sentencia se utiliza para cambiar el nombre de una columna

Sintaxis

RENAME COLUMN Tabla.Columna-Vieja TO Columna-Nueva

· Se debe ser el propietario de la tabla, tener privilegio DBA o privilegio ALTER sobre la tabla para cambiar el nombre de una columna.

· En SE no sirve incluir esta sentencia en una transacción (no se vuelve atrás).

UPDATE STATISTICS

Descripción

Esta sentencia se utiliza para actualizar los datos en los system catalogs que se utilizan para optimizar las estrategias de búsqueda y/o los stored procedures.

Sintaxis

UPDATE STATISTICS

[FOR {Nombre-Tabla|Sinónimo}]

[FOR PROCEDURE [Nombre_Procedure]]

· Si no se especifica tabla, la información de todas las tablas es actualizada.

· Si no se especifica procedure, la información de todos los procedures es actualizada.

· Conviene utilizarla cuando se han hecho modificaciones intensivas sobre una tabla.

· La optimización de procedures es útil para mejorar la performance de la ejecución.

Lenguaje de Manejo de Datos - DML

En el DML se incluyen las sentencias que nos permiten consultar, insertar, modificar y/o eliminar información de las tablas de una base de datos.

SELECT

Descripción

Esta sentencia se utiliza para consultar la Base de Datos activa. Se compone de las 7 siguientes cláusulas, de las cuales únicamente son obligatorias SELECT y FROM

Sintaxis

SELECT <Cláusula-Select>

FROM <Cláusula-From>

[ WHERE Condición ]

[ GROUP BY Lista-Columnas ]

[ HAVING Condición ]

[ ORDER BY Nombre-Columnas [ASC|DESC] [,...]

[ INTO TEMP Nombre-Tabla [WITH NO LOG]]

· Brevemente, la Cláusula SELECT nombra un conjunto de columnas o expresiones a ser recuperadas, la Cláusula FROM nombra una lista de tablas, la Cláusula WHERE establece condiciones sobre las filas, la Cláusula GROUP BY agrupa filas, la Cláusula HAVING establece condiciones sobre los grupos, la Cláusula ORDER BY ordena las filas seleccionadas y la Cláusula INTO TEMP pone los resultados en una tabla temporaria.

· Si se ejecuta una sentencia Select sobre más de una tabla, el resultado es equivalente a la creación de una nueva tabla con el producto cartesiano de todas las tablas consignadas, adecuadamente filtradas por las condiciones estipuladas en la cláusula Where.

· Para nombrar una tabla, es obligatorio hacerlo con el nombre del propietario como prefijo si la base está en modo ANSI, y opcional si no lo está.

<Cláusula-Select>

SELECT [ALL | DISTINCT | UNIQUE] Lista-Select

· Indica qué retorna la sentencia, pudiendo ser columnas, expresiones o constantes.

· La cláusula ALL (valor por default) indica que se incluyan todas las filas en la respuesta, sin eliminar las duplicadas. Para evitar esto, se dispone de la cláusula DISTINCT o UNIQUE.

· Para elegir todas las columnas de una tabla, se puede usar el *

· Se puede dar un display label para cada columna, que aparecerá como el encabezamiento en la salida del Select, o como los nombres de las columnas de una tabla temporaria.

· Si se especifica una columna y una función agregada, la columna debe ser incluida en la cláusula GROUP BY.

<Cláusula-From>

FROM [OUTER] Nombre-Tabla [Alias-Tabla] [,...][,...]

· Indica de qué tablas se saca la información para esta sentencia.

· Se puede especificar un alias para cada tabla, por el cual puede posteriormente ser denominada. Más aún, en caso de los auto-join es necesario hacerlo.

· OUTER permite hacer un outer join, que se explica brevemente a continuación

Outer Joins

Un outer join entre 2 tablas las trata asimétricamente. Una de las tablas es la dominante y la otra es la sirviente. Si esta última no tiene filas que satisfagan la condición de join, en la respuesta habrá una fila con valores nulos acompañando a la fila de la tabla dominante que no tenga proyección. Por ejemplo, si tenemos las columnas zona de la tabla t1 y region de la tabla t2 con los siguientes valores:

t1.zona	t2.region
2	1
7	2
9	3
	7

y ejecutamos la siguiente sentencia Select:

Select zona, region

from t1, outer t2

where zona = region

obtenemos como respuesta:

zona	region
2	2
7	7
9	null

Para un outer join, siempre debe haber una cláusula Where.

Cláusula WHERE

Se utiliza para especificar criterios de búsqueda y condiciones de join sobre los datos que se quiere seleccionar.

WHERE Condición

Las formas posibles de Condición son:

Condiciones de Comparación

Condiciones de join

Condicion con Subqueries

Condiciones de Comparación

En todos los casos, expr se refiere a un nombre de columna, una constante o cualquier combinación de éstos conectados por los operadores aritméticos +, -, *, /, siempre que sea una operación que tenga sentido.

Se pueden utilizar también 3 funciones que actúan como una constante; ellas son USER (que devuelve el nombre del usuario actual), TODAY (devuelve la fecha del sistema) o CURRENT (devuelve la fecha y hora del sistema).

En la expr se pueden utilizar también las funciones agregadas -no combinadas con columnas- o de fecha. Se ven más adelante.

· expr op-rel expr

Un operador relacional es alguno de los siguientes: =, != o <>, >, >=, <, <=.

· expr [NOT] BETWEEN expr AND expr

Para determinar si una expresión está dentro de un rango de valores. Se aplica a expresiones numéricas o de fechas.

· expr [NOT] IN (listaval)

Para determinar si una expresión se encuentra en una lista dada de valores.

· nombrecol [NOT] LIKE "string"

Para comparar columnas caracteres con un dado pattern. Valen los wildcards: % indica 0 o más caracteres, y _ indica cualquier caracter en esa posición.

· nombre-col [NOT] MATCHES "string"

Para comparar columnas caracteres con un dado pattern. Valen los wildcards: * indica 0 o más caracteres, ? indica cualquier caracter en esa posición, [...] compara con cualesquiera de los caracteres encerrados, incluyendo rangos (a-z). Si el primer caracter es un ^, equivale a negarlos, y la / es el caracter de escape.

· nombre-col IS [NOT] NULL

Para determinar si una columna es nula.

Condiciones de join

Se hace un join de dos o más tablas cuando son consignadas en la cláusula FROM y se establece una relación en la cláusula WHERE entre al menos una columna de cada tabla.

El resultado de un join es el producto cartesiano entre las tablas, de modo tal que cada par de filas de la tabla compuesta resultante satisface la condicion del join.

Puede hacerse un auto-join de una tabla, siempre que se utilicen 2 alias diferentes en la sección FROM.

Puede haber un outer join cuando cada fila de la tabla 1 es incluída en el resultado, aunque no se cumpla la condición, en cuyo caso se completa con NULL.

Condición con Subqueries

Una condición de búsqueda en una sentencia SELECT puede:

Comparar una expresión con el resultado de otra sentencia SELECT:

WHERE expr op-rel { ALL | [ANY | SOME] } (sent. SELECT)

· ALL se usa para tratar la comparación con todas las filas que retorne la sentencia SELECT interior.

· ANY o SOME se usa para tratar la comparación con cualquier fila retornada por la sentencia SELECT interior.

Determinar si una expresión está incluida en el resultado de otra sentencia SELECT:

WHERE expr [NOT] IN (sent. SELECT)

Preguntar si existen filas seleccionadas por otra sentencia SELECT:

WHERE [NOT] EXISTS (sent. SELECT)

En todos los casos, en la sentencia SELECT interior sólo puede utilizarse una expresión en su lista de selección y no puede tener una cláusula ORDER BY.

Cláusula GROUP BY

GROUP BY Lista_columnas

· Se utiliza para obtener una respuesta de una fila por cada grupo de filas que satisface la condición del Where, con un valor para cada una de las columnas que conforman el grupo.

· Al usar esta cláusula, toda columna que figure en la lista de selección impone que también aparezca conformando el grupo.

· En lugar del nombre de las columnas, se puede incluir el número que indica su orden de aparición en la lista de selección.

· Se puede agrupar hasta 8 columnas.

Cláusula HAVING

HAVING Condición

· Se utiliza para aplicar condiciones de clasificación a los grupos. Complementa la cláusula Where.

· Si no se usó la cláusula GROUP BY, se asume todo como un solo grupo.

Cláusula ORDER BY

ORDER BY Nombre-Columna [ASC|DESC] [,...]

· Se utiliza para ordenar los resultados de la selección por los valores contenidos en una o más columnas, que deben figurar en la lista de selección.

· En lugar del nombre de las columnas, se puede incluir el número que indica su orden de aparición en la lista de selección.

Cláusula INTO TEMP

INTO TEMP Nombre-Tabla [WITH NO LOG]

· Se utiliza para crear una tabla temporaria con los resultados de la selección.

· La opción WITH NO LOG es soportada en el modo ANSI, y tiene efecto sólo si la base tiene transaction log file.

Operador UNION

Se pueden unir dos o más sentencias SELECT mediante el operador UNION:

Sentencia-SELECT UNION [ALL] Sentencia SELECT [UNION ... ]

· El número de ítem de la lista de selección de cada select debe ser igual, y los tipos de datos corresponderse uno a uno.

· Sólo se puede usar una cláusula ORDER BY en la última sentencia Select.

· Sólo se puede usar una cláusula INTO TEMP en la última sentencia Select.

· No se puede utilizar en un subquery o en la definición de una vista.

Funciones Agregadas

Se dispone de ciertas funciones que agregan cierta información sobre las filas seleccionadas por la cláusula WHERE. Ellas son:

COUNT(*)	Retorna el número de filas
COUNT(DISTINCT x)	Retorna el número de filas en las que la columna x tiene valores distintos
SUM([ALL| DISTINCT] x )	Retorna la suma de los valores de la columna x
AVG([ALL| DISTINCT] x )	Retorna el promedio de los valores de la columna x
MAX([ALL| DISTINCT] x)	Retorna el maximo de la columna x
MIN([ALL| DISTINCT] x)	Retorna el minimo de la columna x

Funciones adicionales para la lista de selección

DATE (expr)	Retorna un valor de tipo DATE de la expr consignada
DAY (expr_fecha)	Retorna el día de una expresión de fecha
LENGTH (string)	Retorna el número de bytes del string o columna.
MDY(exp1,exp2,exp3)	Retorna un tipo DATE donde exp1 es el mes, exp2 el día y exp3 el año.
MONTH(expr_fecha)	Retorna el mes de una expresión de fecha
WEEKDAY (exp1,exp2,exp3)	Retorna un entero que indica el día de la semana, donde 0 es domingo y 6 el sábado.
YEAR (expr_fecha)	Retorna el año de una expresión de fecha
CURRENT prim TO ult	Devuelve un dato de tipo DATETIME con el valor actual en el rango indicado.
EXTEND (valor[,prim TO ult])	Ajusta la precisión de un dato DATETIME

INSERT

Descripción

Esta sentencia se utiliza para insertar una o más filas nuevas en una tabla ya existente

Sintaxis

INSERT INTO Nombre-Tabla [( Lista-Columnas)]

{ VALUES (Lista-Valores)| SELECT -<Sentencia-Select> }

· Los datos se insertan en las columnas en el orden especificado.

· Si no se consignan los nombres de las columnas, se inserta un valor en cada columna, en el orden de su definición.

· Si se inserta con una sentencia Select, los datos deben extraerse de otras tablas, y no se puede usar la cláusula INTO TEMP en la Select.

· Al insertar, intenta convertir datos.

· Para una columna de tipo Serial, ponga un 0 para que se encargue el SQL de generar el número correspondiente. Si se insertan valores, no pueden ser repetidos.

· Todas las filas implicadas en una inserción son locked durante su ejecución. Si el número de filas superara el máximo de locks simultáneos, o bien se reduce la inserción o se hace un lock sobre toda la tabla.

UPDATE

Descripción

Esta sentencia se utiliza para modificar los valores de una o más columnas en una o más filas de la tabla.

Sintaxis

UPDATE Nombre-Tabla SET {Nombre-Columna = Expr [,...] |

{(Lista-Columnas) | *}= (Lista-Expr)}

[WHERE Condición]

· Si se modifica con una sentencia Select, los datos deben extraerse de otras tablas, y no se puede usar la cláusula UNIQUE en la Select.

· Al insertar, intenta convertir datos.

· Todas las filas implicadas en una actualización son locked durante su ejecución. Si el número de filas superara el máximo de locks simultáneos, o bien se reduce la actualización o se hace un lock sobre toda la tabla.

DELETE

Descripción

Esta sentencia se utiliza para borrar una o más filas de una tabla

Sintaxis

DELETE FROM Nombre-Tabla [WHERE Condición]

· Todas las filas implicadas en un borrado son locked durante su ejecución. Si el número de filas superara el máximo de locks simultáneos, o bien se reduce el borrado o se hace un lock sobre toda la tabla.

Acceso a los Datos

Para evitar situaciones de conflicto entre más de un usuario al acceder a los datos, se cuenta con diferentes niveles de locking, que proporciona un adecuado nivel de control de concurrencia.

En SQL hay dos niveles de locking:

A nivel de Tabla (o de archivo)

A nivel de Fila o de registro

Locks a nivel de Tabla

Si se utiliza de este modo, los demás usuarios pierden toda posibilidad de modificar o aún ver cualquier fila de esa tabla, dependiendo del modo en el que se hizo el lock. Si se efectuó en modo SHARE, los demás usuarios podrán seleccionar datos de esa tabla, pero si se hizo en modo EXCLUSIVE no podrán insertar, borrar o actualizar filas de la tabla.

El usuario deberá entonces ser muy cuidadoso con los locks en este nivel, y reservarlos para los casos en que resulten necesarios por las características de las operaciones a ejecutar (demasiadas filas involucradas, operaciones que afecten a to[1]da la tabla, etc.)

Locks a nivel Fila

Previsto para operaciones que involucren sólo algunas filas, hay dos posibilidades:

a) Locks de una fila individual

El SQL hace un lock automático de una fila cuando la misma es invocada en una sentencia UPDATE o se trabaja en modo Update desde el menú.

b) Locks de un grupo de filas

Se utiliza para tratar un conjunto de operaciones como una transacción, lo que además inhibe a los demás usuarios de acceder a esas filas mientras dura dicha transacción. Para ello se dispone de las sentencias BEGIN WORK y COMMIT WORK para abarcar el conjunto de operaciones. Todas las filas involucradas en las operaciones que allí se efectúan permanecen locked para los demás usuarios.

También las sentencias de actualización de datos que involucran múltiples filas implican locks sobre las mismas.

Cuando un usuario intenta una operación sobre filas en una tabla que fue locked en modo EXCLUSIVE, el SQL retorna un error. Para evitarlo, el usuario puede usar la sentencia SET LOCK MODE TO WAIT, con lo que el SQL lo retendrá en espera hasta que las filas sean liberadas.

LOCK TABLE

Descripción

Esta sentencia se utiliza para prohibir el acceso de otros usuarios a una determinada tabla.

Sintaxis

LOCK TABLE Nombre-Tabla IN {SHARE | EXCLUSIVE} MODE

· Sólo un lock por vez es admitido sobre una tabla.

· Si se trabaja con transacciones, la sentencia COMMIT WORK cancela todos los locks pendientes sobre la tabla.

UNLOCK TABLE

Descripción

Esta sentencia se utiliza para liberar el acceso por otros usuarios a una determinada tabla previamente denegado mediante una sentencia Lock Table.

Sintaxis

UNLOCK TABLE Nombre-Tabla

· No se puede utilizar esta sentencia dentro de una transacción.

SET LOCK MODE

Descripción

Esta sentencia se utiliza para determinar si las subsiguientes llamadas al RDSQL esperan a que se libere una fila que en ese momento pudiera estar bloqueada

Sintaxis

SET LOCK MODE TO [NOT] WAIT

· El modo default es NOT WAIT. En este caso, un intento de acceso a una fila bloqueada por otro proceso produce un código de error.

· El modo WAIT produce que, al invocar una fila que está locked, se espera por su liberación. Aquí puede suceder que, si la fila no es liberada por problemas en el otro proceso, no se salga de esta situación.

GRANT

Descripción

Esta sentencia se utiliza para especificar privilegios de acceso de los diversos usuarios a la Base de Datos o a las tablas o vistas de la misma.

Sintaxis

GRANT Privil-Base-De-Datos TO {PUBLIC | Lista-Usuarios}

GRANT Privil-Tabla ON {Nombre-Tabla | Nombre-Vista | Sinónimo}

TO {PUBLIC | Lista-Usuarios}

[WITH GRANT OPTION] [AS dador]

GRANT EXECUTE ON Nombre-Procedure TO {PUBLIC | Lista-Usuarios}

[WITH GRANT OPTION] [AS dador]

· Privil-Base-De-Datos: Uno de los siguientes tipos de acceso de nivel a la Base de Datos:

CONNECT	Permite que el Usuario acceda a la Base de Datos pero no permite crear tablas e índices permanentes.
RESOURCE	Permite que el Usuario acceda a las tablas de la Base de Datos y también crear tablas e índices permanentes.
DBA	Permite al Usuario la administración de privilegios sobre la Base de Datos completa.

· Privil-Tab: Uno o más de los siguientes tipos de acceso de nivel a la tabla (privilegios múltiples deben separarse con comas):

ALTER	Inserta o borra columnas o modifica tipos de datos de columnas.
DELETE	Borra filas
INDEX	Crea índices
INSERT	Inserta filas
SELECT[Cols]	Recupera datos de columnas especificadas
UPDATE[Cols]	Modifica valores en columnas especificadas
REFERENCES[Cols]	Refencia columnas en constraints, siempre que tenga el privilegio Resource.
ALL[PRIVILEGES]	Todos los mencionados anteriormente

· Cuando se utiliza la cláusula WITH GRANT OPTION le da al Usuario la posibilidad de asignar privilegios a otros usuarios. Así, no puede evitar que se diseminen los privilegios. De todos modos, esto se corta revocando el privilegio al usuario originalmente invocado.

· Con la cláusula AS dador, Ud. consignaría otro usuario como quien otorga los privilegios, con lo cual puede romper la cadena de pasaje de privilegios.

· La cláusula EXECUTE ON permite pasar a otro usuario la posibilidad de ejecutar un determinado stored procedure.

REVOKE

Descripción

Esta sentencia remueve los privilegios de acceso de usuario para una tabla, una base de datos o un procedure.

Sintaxis

REVOKE Privil-Base-De-Datos FROM {PUBLIC | Lista-Usuarios}

REVOKE Privil-Tabla ON {Nombre-Tabla | Nombre-Vista | Sinónimo}

FROM {PUBLIC | Lista-Usuarios}

REVOKE EXECUTE ON Procedure FROM {PUBLIC | Lista-Usuarios}

· Los Privilegios de Base de Datos y de Tablas son los mismos que en la sentencia anterior

Integridad de los Datos

La integridad de los datos es provista por dos medios:

los audit trails y

las transacciones

Transacciones

Las transacciones son un conjunto de operaciones que deben ser tratadas como una unidad de trabajo.

Para su implementación debe contarse con un archivo de auditoría de la actividad en la base de datos (el transaction-log file), que puede ser generado al momento de crear la base de datos (con la cláusula WITH LOG IN) o en cualquier momento con la sentencia START DATABASE.

Para indicar el inicio de una transacción, se utiliza la sentencia BEGIN WORK. De allí en más, todas las filas involucradas permanecen locked para los demás usuarios. El fin de una transacción se indica con la sentencia COMMIT WORK, que produce la liberación de todas las filas que estaban locked y actúa como una confirmación de todas las operaciones.

Si el usuario detecta un error que invalide las operaciones efectuadas dentro de una transacción, puede cancelarlas con la sentencia ROLLBACK WORK. Esto produce la vuelta de la base de datos al estado en que estaba al momento previo del inicio de la transacción y la liberación de las filas que estaban locked. De todos modos hay operaciones que no pueden ser canceladas como las de definición de datos o las de ceder o revocar permisos.

Hay un límite para el número de locks simultáneos por lo que una transacción muy grande puede ocasionar problemas con lo cual será necesario dividirla en dos transacciones o hacer LOCK TABLE.

Como el archivo de auditoría crece muy rápidamente es necesario inicializarlo periódicamente -mediante START DATABASE- después de haber hecho un back-up de la base de datos.

En caso de problemas externos se puede recuperar la base de datos con el uso de la sentencia ROLLFORWARD DATABASE: se carga la copia de back-up de la base de datos y, con el archivo de transacciones existente desde el momento en que se hizo dicho back-up, se recupera la base de datos.

Audit Trails

Un audit trail es un archivo que registra todos los cambios producidos en una tabla de una base de datos.

Para iniciar un audit trail se usa la sentencia CREATE AUDIT. Puede hacerse en cualquier momento, y produce que todas las operaciones que a continuación se hagan sobre la tabla sean registradas en él. Para eliminar un audit trail se usa la sentencia DROP AUDIT.

Si hay un problema con una tabla que deba ser recuperada, el usuario puede hacerlo mediante la sentencia RECOVER TABLE, una vez que levantó la copia de back-up y siempre que hubiera creado el audit trail justo antes de hacer el back-up.

Los audit trails no son muy utilizados por los incovenientes que traen aparejados:

Decrementan la performance del sistema.

Inciden en el número máximo de tablas abiertas simultáneamente.

No proveen protección contra operaciones que involucran más de una tabla.

CREATE AUDIT

Descripción

Esta sentencia se utiliza para crear un archivo de seguimiento de auditoría y comenzar las registraciones en el mismo.

Sintaxis

SE CREATE AUDIT FOR {Nombre-Tabla | Sinónimo} IN "pathname"

· Se requiere tener el privilegio DBA o ser el propietario para usar esta sentencia.

· En OL no está disponible, aunque está provisto por medio de los logs files.

DROP AUDIT

Descripción

Esta sentencia se utiliza para borrar un archivo de seguimiento de auditoría.

Sintaxis

SE DROP AUDIT FOR {Nombre-Tabla | Sinónimo}

· Se requiere tener el privilegio DBA o ser el propietario para usar esta sentencia.

RECOVER TABLE

Descripción

Esta sentencia se utiliza cuando ocurre una falla en el Sistema. Permite recuperar una tabla de Base de Datos desde una copia backup y un archivo auditoría

Sintaxis

SE RECOVER TABLE Nombre-Tabla

· Se requiere tener el privilegio DBA o ser el propietario para usar esta sentencia.

BEGIN WORK

Descripción

Esta sentencia se utiliza para comenzar una transacción.

Sintaxis

BEGIN WORK

· Cada fila afectada por una sentencia UPDATE, DELETE o INSERT durante una transacción es locked mientras no se cierra dicha transacción.

· En las bases de datos ANSI no es necesaria esta sentencia, pues las transacciones son implícitas.

COMMIT WORK

Descripción

Esta sentencia se utiliza para efectivizar todas las modificaciones hechas a la base de datos desde la última sentencia BEGIN WORK.

Sintaxis

COMMIT WORK

· Esta sentencia libera todas las filas y/o tablas involucradas en la transacción.

ROLLBACK WORK

Descripción

Esta sentencia se utiliza para cancelar todas las modificaciones hechas a la base de datos desde la última sentencia BEGIN WORK, dejando la base de datos en el estado previo al comienzo de la transacción.

Sintaxis

ROLLBACK WORK

· Esta sentencia libera todas las filas y/o tablas involucradas en la transacción.

· En SE no se deshacen aquellas sentencias que, aún comprendidas en una transacción, afecten la definición de las tablas o columnas, permisos, etc.

START DATABASE

Descripción

Esta sentencia se utiliza para trabajar con un nuevo archivo de transaction log

Sintaxis

SE START DATABASE Nombre-Base-De-Datos WITH LOG IN "pathname"

[MODE ANSI]

· En un server con SE se puede usar esta sentencia siempre que se tenga privilegio DBA, no haya base de datos activa y que los directorios especificados en pathname existan.

· Al ejecutar esta sentencia, la base de datos queda locked en modo exclusivo; hay que hacer un Close Database para liberarla.

ROLLFORWARD DATABASE

Descripción

Esta sentencia se utiliza para aplicar las transacciones registradas en el archivo de transaction-log a una copia de backup de la base de datos para recuperar todas las transacciones que están completas.

Sintaxis

SE ROLLFORWARD DATABASE Nombre-Base-De-Datos

· Para recuperar una base de datos bajo SE se necesitan tanto la copia de la base de datos como el transaction log file iniciado inmediatamente después que se hizo ese backup.

· Se debe tener el privilegio DBA para hacerlo, y la base de datos debe estar previamente cerrada.

· Al ejecutar esta sentencia, la base de datos queda locked en modo exclusivo; hay que hacer un Close Database para liberarla.

Sentencias Auxiliares

CHECK TABLE

Descripción

Esta sentencia se utiliza para comparar una tabla con sus índices para determinar si coinciden. Se utiliza cuando se supone que los datos o los índices se han corrompido por alguna razón, típicamente una terminación anormal.

Sintaxis

SE CHECK TABLE Nombre-Tabla

· Se debe contar con el privilegio DBA o ser el propietario para usar esta sentencia.

· Esta sentencia utiliza un utilitario del SE, el bcheck, que puede ser invocado desde el prompt.

REPAIR TABLE

Descripción

Esta sentencia se utiliza para borrar y regenerar los índices de una tabla previamente chequeada con la sentencia CHECK TABLE.

Sintaxis

REPAIR TABLE Nombre-Tabla

· Se debe contar con el privilegio DBA o ser el propietario para usar esta sentencia.

· Esta sentencia utiliza un utilitario del SE, el bcheck, que puede ser invocado desde el prompt.

LOAD

Descripción

Esta sentencia se utiliza para cargar datos desde un archivo ASCII a una tabla, sinónimo o vista de una Base de Datos existente.

Sintaxis

LOAD FROM “Path-Name” [DELIMITER “caracter” ]

INSERT INTO {Nombre-Tabla | Sinónimo | Nombre-Vista}

[(Nombre-Columna[,...])]

· Se debe tener el privilegio de INSERT sobre la tabla para usar esta sentencia.

· Si se está trabajando con transacciones, se debe ejecutar una sentencia BEGIN WORK antes de la Load.

· Esta sentencia agrega filas a la tabla, por lo que se puede producir error si se trata de insertar filas con claves duplicadas cuando no está permitido.

· El delimitador por default es la |, salvo que hubiera otro caracter especificado en la variable DBDELIMITER.

· El backslas (/) se usa como carácter de escape, por lo que no hay que usarlo como delimitador.

UNLOAD

Descripción

Esta sentencia se utiliza para grabar datos de una tabla de una base de datos a un archivo ASCII

Sintaxis

UNLOAD TO “Archivo” [DELIMITER “caracter” ] <Sentencia Select>

· Se debe contar con el privilegio de SELECT sobre todas las columnas implicadas en la sentencia SELECT.

· El delimitador por default es la |, salvo que hubiera otro caracter especificado en la variable DBDELIMITER.

INFO

Descripción

Esta sentencia se utiliza para mostrar algunas de las siguientes informaciones posibles:

Nombres de tablas en la base de datos actual

Columnas de una tabla

Indices para una tabla

Privilegios de acceso para una tabla

Privilegios de referencias para las columnas de una tabla

Estado de una tabla

Sintaxis

INFO {TABLES | COLUMNS FOR Nombre-Tabla

| INDEXES FOR Nombre-Tabla

| [ACCESS | PRIVILEGES] FOR Nombre-Tabla

| REFERENCES FOR Nombre-Tabla

| STATUS FOR Nombre-Tabla}

OUTPUT

Descripción

Esta sentencia se utiliza para escribir los resultados de un query a un archivo , mediante un pipe, enviarlo a otro programa.

Sintaxis

OUTPUT TO { Nombre-Archivo | PIPE Programa } [WITHOUT HEADINGS]

<Sentencia-Select>

FORMS

Un FORM se especifica mediante un archivo con el layout de la pantalla y las instrucciones que indican cómo mostrar y/o ingresar los datos. Puede tener varias páginas de longitud y contener columnas de varias tablas.

Estructura

Un FORM consta de cuatro secciones que deben estar presentes (DATABASE, SCREEN, TABLES y ATTRIBUTES) y una sección opcional (INSTRUCTIONS). A continuación analizaremos cada una de las secciones.

Sección DATABASE

DATABASE

nombre-base-de-datos [WITHOUT NULL INPUT]

Especifica la base de datos que se utilizará para trabajar con el FORM. Es la primera sección que debe estar presente.

without null input se utiliza cuando una base de datos no admite nulos. Muestra 0 para los campos numéricos y espacios para los caracteres.

Sección SCREEN

SCREEN

{

....

display field layout de la pantalla

...

}

Puede haber varias pantallas definidas en esta sección con un máximo de 20 líneas para cada una. Si son más, son automáticamente divididas en dos. La forma de los display field es la siguiente:

texto [fieldtag]

texto es lo que se muestra en la pantalla.

[] son los delimitadores del campo. El ancho del campo lo marcan la cantidad de espacios entre los delimitadores.

field-tag es la identificación del campo. Cada campo debe tener uno, y nada tienen que ver con las columnas de las tablas.

Las mayúsculas y minúsculas son indistintas. Para mostrar más de un campo se utiliza la barra |, de la forma

nombre [tag1 |tag2 ]

Si esto se utiliza debe incluirse la sentencia DELIMITERS en la sección INSTRUCTIONS.

Sección TABLES

TABLES

nombre-tabla[ nombre-tabla]

Especifica las tablas de las cuales se invocarán columnas en el FORM. No se puede incluir una tabla temporaria y pueden ser de una vista si en la definición de la misma solo participa una tabla y no tenga datos agregados.

Sección ATTRIBUTES

field-tag = field-descripcion;

Esta es la forma general de definir cada uno de los field-tag que aparecen en la sección SCREEN. En esta sección se indica cómo se muestra un campo, se especifica un valor default, se indica el rango de valores, etc. El orden en que aparecen los field-tag determina el movimiento del cursor. Los campos pueden estar asociados a columnas de las tablas o no, variando la forma de field-descripcion, como veremos ahora.

Modo de definir campos ligados a columnas de tablas

field-tag [tabla.]columna[,attr-list];

El field-tag se corresponde con uno de la sección SCREEN.

tabla indica la tabla a la que pertenece la columna. Es necesaria cuando ese nombre de columna se repite.

attr-list es la lista de atributos, separados por ,.

Los campos ligados a columnas de tablas pueden permitir ingreso de datos o no. Si no lo hacen, se denominan lookup fields.

Modo de definir campos display-only

field-tag = DISPLAYONLY [ALLOWING INPUT]

TYPE tipo-datos [NOT NULL][,attr-list];

DISPLAYONLY indica que es un campo no ligado a una columna.

ALLOWING INPUT se usan para indicar que permite ingreso de datos.

tipo-datos es uno de los tipos posibles excepto SERIAL.

Columnas de join

A través de un FORM se pueden establecer joins de dos o más columnas del mismo tipo, lo que redunda en mejor performance de los queries. La forma genérica de expresarlo es:

field-tag = tabla1.columna [,attr-list1] = tabla2.columna[attrlist2]

El orden en que aparecen los atributos determina el modo en el que actúan.

También se pueden hacer los verify join para verificar que el valor ingresado a un campo que corresponde a una columna de una tabla ya existe en la columna de la otra tabla (la dominante). Este join se denota con un * antes de la columna dominante. Esto es:

field-tag = col1 = *col2

Veamos ahora los posibles atributos de attr-list

AUTONEXT	Avanza el cursor al próximo campo cuando se completa el actual
COLOR=id-color	Permite mostrar el campo en alguno de los colores posibles
COMMENTS ="mensaje"	Para mostrar mensaje en la línea de comentarios al pie de la pantalla cuando el cursor pasa por el campo
DEFAULT=valor	Asigna valor como default para ese campo
DOWNSHIFT	Para campos CHAR. Convierte las mayúsculas a minúsculas
FORMAT= "format-string"	Permite controlar el modo de presentación de datos de tipo numérico o fecha
INCLUDE=(lista-valores)	lista-valores es un conjunto de valores -separados por ,- o un rango -val1 TO val2- que indica los posibles valores válidos para el campo
field-tag=tabla1.col, LOOKUP [field-tag1=tabla2.col1 [field-tag2= tabla2.col2,...]] JOINING [*]tabla2.col	Permite mostrar datos de otra tabla mientras ingresa datos de la tablaactiva pudiendo establecer un verify join
NOENTRY	Impide la entrada de datos durante una operación de ADD de nuevas filas
NOUPDATE	Impide la entrada de datos durante una operación de UPDATE de filas
PICTURE="pic-string"	Especifica un patrón para el ingreso de datos CHAR
QUERYCLEAR	Limpia un campo de join en la pantalla cuando se ingresa a un query
REQUIRED	Fuerza el ingreso de datos en ese campo durante una operación ADD
REVERSE	Muestra el campo en video reversa
RIGHT	Justifica a derecha el ingreso de los campos
UPSHIFT	Convierte en el ingreso de datos CHAR las minúsculas a mayúsculas
VERIFY	Provoca que el usuario ingrese 2 veces el dato para ese campo
ZEROFILL	Justifica a derecha y rellena con 0 a izquierda los campos numéricos

Seccion INSTRUCTIONS

INSTRUCTIONS

[composites sentencia]

[delimiters sentencia]

[master sentencia]

[bloques de control]

END

Esta sección es opcional, y permite establecer joins compuestos, delimitadores de campo alternativos, crear relaciones maestro/detalle y/odefinir bloques de control. Veamos las distintas posibilidades:

COMPOSITES <tabla1.col1,tabla1.col2[,tabla1.col3[, ... ]]>

[*]<tabla2.col1,tabla2.col2[,tabla2.col3[, ...]]>

Establece un join compuesto entre dos tablas y se usa cuando se deben especificar los valores de más de una columna en una tabla para identificar unívocamente a una fila.

DELIMITERS "ab"

Cambia los delimitadores default ([ ]) a los caracteres a y b respectivamente.

tabla1 MASTER OF tabla2

Establece que tabla1 es una tabla maestra mientras tabla2 es una tabla de detalle, simplificando los queries cruzados.

Bloques de Control

Mediante el conjunto de instrucciones disponibles en este punto, se puede controlar el movimiento del cursor ante operaciones ADD o UPDATE, chequear datos ingresados, modificar datos después de operaciones, efectuar cálculos y guardar los resultados en otro campo, mostrar información agregada, llamar a funciones en C. Cada bloque de control es un bloque BEFORE o AFTER como veremos.

{BEFORE | AFTER} lista-opciones OF tabla/lista-columnas

acción

...

acción

Indica que se ejecute el conjunto de acciones antes o después de que se ejecute una operación.

lista-opciones puede ser alguna de las siguientes: EDITADD, EDITUPDATE, ADD, UPDATE, QUERY REMOVE o DISPLAY.

acción puede ser alguna de las siguientes: ABORT, LET, NEXTFIELD, COMMENTS o IF-THEN-ELSE.

Reports

Un REPORT se especifica mediante un archivo con las instrucciones que especifiquen los datos que contendrá y el modo en que aparecerán impresos.

Estructura

Un REPORT consta de tres secciones que deben estar presentes (DATABASE, SELECT y FORMAT) y tres secciones opcionales (DEFINE, INPUT y OUTPUT). A continuación analizaremos cada una de las secciones.

Sección DATABASE

DATABASE

Nombre-Base-de-Datos

END

Especifica la base de datos que se utilizará como la base para el reporte. Es la primera sección que debe estar presente.

Sección DEFINE

DEFINE

PARAM sentencias

VARIABLE sentencias

END

Si está presente, en esta sección se declaran las variables usadas en el REPORT y los parámetros que puede recibir desde la línea de comandos. La sintaxis de ambas es:

PARAM [num-arg] nombre-variable tipo-de-dato

num-arg: es la posición del argumento en la línea de comandos.

VARIABLE nombre-variable tipo-de-dato

Se pueden definir hasta 100 variables entre ambos.

Si se va a utilizar una de las variables definidas en esta sección en la sección SELECT, debe estar precedida con un signo $.

Sección INPUT

INPUT

PROMPT FOR sentencias

END

Si está presente, esta sección permite al usuario interactuar durante la emisión del REPORT permitiendo el ingreso de datos.

PROMPT FOR nombre-variable USING "string"

"string": es el mensaje que le aparece al usuario. Debe ir entre comillas. Puede haber más de una sentencia PROMPT FOR en esta sección.

Sección OUTPUT

OUTPUT

REPORT TO {PRINTER | "nombre-archivo"}

LEFT MARGIN integer

RIGHT MARGIN integer

TOP MARGIN integer

BOTTOM MARGIN integer

PAGE LENGTH integer

END

Si está presente, esta sección permite controlar los márgenes derecho, izquierdo, superior e inferior, el largo de página y el dispositivo de salida.

Los valores default son:

Margen Izquierdo= 5

Margen Derecho= 132

Margen Superior= 3

Margen Inferior= 3

Longitud de Página= 66

Sección SELECT

select sentencia;

....

select sentencia;

end

Esta sección debe estar presente. Especifica las columnas y/o las tablas en las que se basa el REPORT. Además, puede indicar criterios de ordenamiento de las filas. Puede haber

múltiples sentencias SELECT -en sus formas habituales-, separadas por ;. En caso de múltiples sentencias SELECT, todas excepto la última deben contener la cláusula INTO TEMP, y sólo la última puede tener la cláusula ORDER BY.

Sección FORMAT

Debe estar presente, ya que determina el modo en que aparece el reporte. Trabaja con los datos producidos por la sección SELECT.

Hay dos formas típicas para esta sección, mutuamente excluyentes:

FORMAT

EVERY ROW sentencia

END

Es la más sencilla. Produce un REPORT default, ya que lista los datos encabezados por el nombre de las columnas.

FORMAT

PAGE HEADER bloque-de-control

PAGE TRAILER bloque-de-control

FIRST PAGE HEADER bloque-de-control

ON EVERY ROW bloque-de-control

ON LAST ROW bloque-de-control

BEFORE GROUP OF nombre-variable bloque-de-control

AFTER GROUP OF nombre-variable bloque-de-control

END REPORT

Mediante los bloques-de-control se pueden indicar encabezamientos, pies de página, qué hacer en cada fila, en la última, antes o después de cada grupo de filas, etc. Cada bloque es opcional, pero al menos uno debe estar presente. Además, cada bloque debe incluir al menos una sentencia.

Las posibles sentencias que componen un bloque son:

for ind-loop = exp1 to exp2 [step exp3] do sentencia	ind-loop debe estar definido en la seccion DEFINE exp-3 no puede ser negativo
if exp then sentencia1 [else sentencia2]
let variable[num1[,num2]] = lista-exp	num1 y num2 se usan para indicar rangos de variables CHAR
need exp-num lines	exp-num indica el número de líneas que se necesitan para imprimir lo que sigue. Si no está disponible, cambia de hoja
pause [string]	En los reportes a pantalla, produce su detención hasta que el usuario presione RETURN. Si está presente, string contiene un mensaje a mostrar.
print [lista-exp][;]	lista-exp contiene una o más expresiones a ser impresas, separadas por , ; si está presente, suprime el caracter NEWLINE.
print file "nombre-archivo"	Incluye en el REPORT el contenido de un archivo texto
skip num lines	Indica que produzca un salto de num líneas
skip to top of page	Indica que produzca un salto de página. No puede ser usada en un bloque de control FIRST PAGE HEADER, PAGE HEADER o PAGE TRAILER.
while exp do sentencia

Funciones Agregadas

Hay un conjunto de funciones agregadas que permiten sumarizar información en un Report.

[GROUP]

{ COUNT | PERCENT |

{ { TOTAL | { AVERAGE | AVG } | MIN | MAX } OF expr1 } }

[WHERE expr2]

GROUP	Si está presente, indica que los totales se calculen sobre un grupo formado con la cláusula ORDER BY. Sólo se puede utilizar en un bloque de control AFTER GROUP OF.
COUNT	Cuenta el total de filas seleccionadas por la sentencia SELECT y si está presente por la clausula WHERE expr2 de la función agregada.
PERCENT	Evalúa el total de filas involucradas en la SELECT y la cláusula WHERE expr2 como un porcentaje de las filas totales.
TOTAL	Evalúa el total de expr1 de las filas calificadas por la sentencia SELECT y la cláusula WHERE expr2.
AVERAGE/ AVG	Evalúa el porcentaje de expr1 de las filas calificadas por la sentencia SELECT y la cláusula WHERE expr2
MIN	Evalúa el mínimo de expr1 de las filas calificadas por la sentencia SELECT y la cláusula WHERE expr2
MAX	Evalúa el máximo de expr1 de las filas calificadas por la sentencia SELECT y la cláusula WHERE expr2
expr1	Es la expresión que evalúan las cláusulas recién descriptas. Es típicamente una columna numérica o una expresión que involucra una columna numérica

Por último, hay una serie de funciones que se utilizan al momento de impresión y que proveen determinadas funciones auxiliares. Ellas son:

ASCII num-expr	Evalúa la expresión numérica como un carácter. Se utiliza para mostrar caracteres de control.
char-expr CLIPPED	Muestra el campo caracter que lo precede sin los blancos de relleno.
COLUMN num-expr	Posiciona el siguiente item en la columna indicada
DATE	Muestra un string con la fecha del día
DATE(date-expr)	Convierte la expresión con que es llamada a una de tipo Date.
DAY(date-expr)	Retorna el día de la fecha con que es invocada.
LINENO	Tiene el valor del número de línea actual del reporte.
MDY(num-exp1, num-exp2, numexp3)	Devuelve una fecha a partir de los parámetros: día, mes y año
MONTHdateexpr	Retorna el mes de la fecha con que es invocada
PAGENO	Tiene el valor del número de página actual del reporte.
num-expr SPACE[S]	Produce el número deseado de espacios
TIME	Retorna la hora
TODAY	Retorna el día de la fecha en un tipo Date
expr1 USING expr2	Para dar formato a expresiones numéricas o fechas
WEEKDAY(date-expr)	Retorna el día de la semana de la fecha
YEAR(date-expr)	Retorna el año de la fecha con que es invocada

Trabajos Practicos

PRACTICA I

1. Crear a través de los menúes la base de datos PRACTICA y las siguientes tablas, con los índices que se indiquen según la estructura consignada:

Tabla CLIENTE

Nombre Campo	Tipo
Nro_Cliente	Serial
Nombre	Char(15)
Apellido	Char(15)
Compania	Char(20)
Direccion	Char(20)
Cod_Pos	SmallInt
Cod_Provi	Integer
Telefono	Char(15)

Indices: Nro_Cliente, sin duplicaciones

Tabla NOTAS_CLIENTE

Nombre Campo	Tipo
Nro_Cliente	Integer
Referencia	Char(30)

Indices: Nro_Cliente, con duplicaciones

Tabla PROVI

Nombre Campo	Tipo
Cod_Provi	Serial
Nom_Provi	Char(20)

2. Modifique la Tabla CLIENTES incorporando la columna Localidad Char(10) de modo que aparezca antes de Cod_Pos.

3. Crear mediante comandos las siguientes tablas, con los índices que se indiquen según la estructura consignada:

Tabla PROVEEDOR

Nombre Campo	Tipo
Cod_Prov	Smallint
Nom_Provi	Char(20)

Indices: Cod_Prov, sin duplicaciones

Tabla ORDENES

Nombre Campo	Tipo
Nro_Orden	Serial
Fecha_Orden	Date
Nro_Cliente	Integer
Instr_Envio	Char(20)
Fecha_Envio	Date
Precio_Envio	Money(10,2)
Fecha_Pago	Date

Indices: Nro_Cliente, con duplicaciones

Nro_Orden, sin duplicaciones

Tabla ITEMS

Nombre Campo	Tipo
Nro_Item	SmallInt
Nro_Orden	Integer
Nro_Stock	SmallInt
Cod_Prov	SmallInt
Cantidad	Integer

Indices: Cod_Prov, con duplicaciones

Nro_Stock, Cod_Prov, con duplicaciones

Nro_Item, Nro_Orden, sin duplicaciones

Tabla STOCK

Nombre Campo	Tipo
Nro_Stock	SmallInt
Cod_Prov	SmallInt
Descripcion	Char(20)
Precio_Unit	Money(10,2)
Cod_Uni	Char(3)

Indices: Nro_Stock, Cod_Prov, sin duplicaciones

Tabla UNIDAD

Nombre Campo	Tipo
Cod_Uni	Char(3)
Desc_Uni	Char(20)

PRACTICA II -FORMS-

1. Generar un archivo de especificación de pantalla default para ORDENES.

2. Modificar la sección SCREEN del anterior, de modo tal de centrar la parte que aparece en pantalla con títulos adecuados para cada columna.

3. Incorporar los atributos REVERSE y UPSHIFT para las columnas Nro_Orden e Instr_Envio respectivamente. Observar el efecto.

4. Modificar las columnas de tipo DATE para que las fechas aparezca de la forma dd-mm-yyyy, con un comentario al pie sobre la forma de ingresarlos.

5. Mostrar el nombre del cliente que está haciendo la orden a la derecha del número, validando que no se esté ingresando una orden de un cliente inexistente.

6. Que proponga la fecha del día por default para la fecha de la orden.

7. Generar una pantalla para CLIENTES y ORDENES.

8. Restringir el ingreso de Cod_Provi a números entre 1 y 24, mostrando el nombre de la provincia a la que corresponda.

9. Modificar el FORM para que Telefono permita el ingreso de 2 números.

10. Generar una pantalla para ORDENES e ITEMS.

11. Cambiar los delimitadores por espacios.

12. Una vez ingresados todos los item de una orden, calcular la cantidad de item que la componen y mostrar el resultado por pantalla.

13. Alterar el movimiento del cursor de modo tal que se ingrese la fecha de envío de la orden luego del ingreso de la fecha de la orden.

14. Calcular el monto a pagar por item y mostrarlo para cada uno.

15. Mostrar el monto total de la orden en el último renglón de la pantalla.

PRACTICA III

1. Seleccionar todos los datos de CLIENTES.

2. Listar el código de la provincia BUENOS AIRES.

3. Seleccionar los item cuyo número de item sea mayor que 2.

4. Listar los clientes que vivan en CAPITAL.

5. Seleccionar los productos cuyo código de proveedor sea 3 y su precio unitario menor que 30.

6. Listar los clientes que no tengan telefono.

7. Listar la descripción código de proveedor y números de stock de los productos que empiecen con "P".

8. Listar las provincias que comiencen con "SA".

9. Listar número de stock código de proveedor y descripción de los artículos cuya descripción termine con "TENIS" o comience con "B".

10. Listar los clientes cuyo nombre empiece con "M" o "A" o que termine con "S".

11. Listar número de orden y número de cliente de aquellas ordenes cuya fecha de pago supere a la fecha de envío en más de 2 días.

12. Listar el stock ordenado por código de artículo.

13. Listar las ordenes de abril ordenadas por número de orden.

PRACTICA IV

1. Seleccionar todos los datos de STOCK y PROVEEDOR.

2. Listar número de stock, código de proveedor, descripción y precio unitario de los artículos de STOCK.

3. Listar número de cliente, nombre, apellido, dirección, provincia y código postal de CLIENTES.

4. Listar número de item, número de orden, número de stock, descripción, código de proveedor y nombre del proveedor ordenado por número de item.

5. Idem 4 donde la cantidad supere las 300 unidades.

6. Imprimir para cada cliente las ordenes que tenga asociadas

7. Imprimir para cada orden los item que la forman ordenados por fecha de orden.

8. Imprimir número de orden, fecha, nombre del cliente y total de la orden para aquellas ordenes cuyo total exceda las 250 unidades.

9. Imprimir la descripción y precio unitario de los artículos de STOCK cuyo código de proveedor sea 1, ordenados por número de stock.

10. Listar las ordenes de cada cliente con número de cliente, nombre, número de orden, monto total de la orden, ordenado por número de cliente para aquellas órdenes cuyo monto total sea superior a 200.

11. Determinar el producto más vendido indicando su número de stock, descripción y proveedor.

12. Determinar cuál es el artículo de mayor precio unitario y cuál es el promedio de pedidos para ese artículo.

PRACTICA V -REPORTS-

1. Generar un REPORT default sobre STOCK y modificarlo para que salga ordenado por número de stock y, en caso de igualdad por número de proveedor.

2. Modificar el reporte para que el largo de hoja sea de 72, el margen superior de 3 líneas, el inferior de 2 y los márgenes derecho e izquierdo de 4 espacios.

3. Agregar títulos y encolumnar el REPORT.

4. Mostrar el número de página en el centro de la hoja en la última línea antes de cambiar de página.

5. Imprimir la cantidad total de artículos listados al final del REPORT.

6. Producir el mismo REPORT pero con cortes de control por código de proveedor imprimiendo la cantidad de artículos que provee cada uno.

7. Imprimir los clientes incluyendo la provincia donde viven y sus referencias con corte de control por provincia.

8. Emitir un REPORT a pantalla que muestre por páginas todas las ordenes de compra efectuadas por los clientes ordenadas por fecha de emisión con corte por cliente. Antes de cada grupo de órdenes imprimir el número y el nombre del cliente y después la cantidad de órdenes efectuadas por cada uno y el monto total.

9. Imprimir un REPORT al archivo SALIDA con las órdenes de compra con los item que la conforman, el número de stock, la descripción el código y nombre del proveedor, la cantidad pedida por item y la cantidad total de la orden. Imprimirlo ordenado por número de orden con los títulos adecuados y con el número de la hoja en el margen superior derecho.

---

[1]