Curso de C++ Builder

Acceso a bases de datos

SQL

SQL es el lenguaje que se utiliza usualmente para consultar y gestionar bases de datos relacionales. Los orígenes de SQL se remontan a 1974, cuando Chamberlin y Boyce publicaron el primer artículo de SEQUEL, que después se convertiría en SQL ("SEQUEL: A structured English query language", Proceedings of the ACM SIGFIDET Workshop, May 1974, pp. 249–264), que estaba basado en un trabajo de E.F. Codd ("A relational model of data for large shared data banks". Commun. ACM, 13:6, 1974, pp. 377–387). En 1978, el ANSI comenzó a estandarizar un lenguaje de definición de datos

El comité técnico X3H2 del ANSI reconoció la importancia del modelo relacional y, en cooperación con el grupo correspondiente de ISO, desarrolló la primera especificación de SQL en 1986. El SQL-86 no incluía la integridad referencial, que fue añadida en el SQL-89. No obstante, los estándares SQL-86 y SQL-89 no eran adecuados para aplicaciones reales y en 1992 apareció el SQL-92 (ANSI X3.135-1992, ISO/IEC 9075:1992), el estándar actualmente utilizado por la mayor parte de las bases de datos. Una cuarta generación de SQL, el SQL3, aún está por conseguir su estandarización de hecho.

Visión general

El concepto fundamental de SQL es el de tabla, con una o más columnas que tienen un nombre y un tipo (numérico, cadena, fecha...). Los datos se almacenan como filas en las tablas (tuplas). Algunos de los datos pueden ser desconocidos o inaplicables, y SQL utiliza 'null' para representar este hecho.

Las bases de datos SQL son autodescriptivas: las tablas del catálogo (cuya implementación depende del DBMS) almacenan información acerca de las tablas de la base de datos (metadatos). Las restricciones de integridad se utilizan para forzar el cumplimiento de determinadas condiciones ('business rules', integridad referencial...) y su violación produce excepciones que bloquean la ejecución de las sentencias que las ocasionan. Los disparadores [triggers] especifican acciones que el DBMS ha de realizar cada vez que se produce algún evento determinado y los procedimientos almacenados sirven para encapsular conjuntos de sentencias SQL.

DML: Lenguaje de manipulación de datos

SELECT

SELECT [DISTINCT] lista-select | *
 FROM tabla, tabla . . .
 WHERE condición
 GROUP BY columnas
 HAVING condición
 ORDER BY columnas

Las consultas a la base de datos se realizan en SQL mediante la sentencia SELECT:

Recuperar todas las filas de la(s) tabla(s) especificada en FROM. Cuando se indica más de una, se devuelve su producto cartesiano.
La claúsula WHERE, cuando está presente, elimina las tuplas que no satisfacen la condición.
La claúsula GROUP BY, si existe, agrupa las tuplas que tienen los mismos valores en todas las columnas especificadas.
Si existe una claúsula HAVING, los grupos resultantes de GROUP BY que no satisfagan la condición se eliminan.
La lista de columnas separadas por comas que aparece en el SELECT determina las columnas que se devuelven (* devuelve todas).
DISTINCT, cuando aparece, elimina duplicados.
La claúsula ORDER BY sirve para ordenar los valores devueltos por una consulta.

Ejemplo: Visualizar todos los vuelos que tengan como origen o destino Granada

SELECT *
  FROM VUELOS
  WHERE ORIGEN='GRANADA'
     OR DESTINO='GRANADA'

Claúsula IN

Expresa la pertenencia del valor de una columna a un determinado conjunto de valores.

Ejemplo: Seleccionar aquellos vuelos que tengan como origen Madrid, Barcelona o Sevilla.

SELECT *
  FROM VUELOS
  WHERE ORIGEN IN ('MADRID','BARCELONA','SEVILLA')

o también

SELECT *
  FROM VUELOS
  WHERE ORIGEN='MADRID' OR ORIGEN='BARCELONA' OR ORIGEN='SEVILLA'

Ejemplo: Visualizar todos los vuelos existentes excepto aquellos que llegan a Londres o a Copenhague.

SELECT *
  FROM VUELOS
  WHERE DESTINO NOT IN ('LONDRES','COPENHAGUE')

Claúsula BETWEEN

Sirve para establecer o expresar un rango de valores (valores extremos incluidos).

Ejemplo: Recuperar todos los vuelos que salgan entre las 6 y las 12 de la mañana

SELECT *
  FROM VUELOS
  WHERE HORA_SALIDA BETWEEN '06.00.00' 
                        AND '12.00.00'

equivale a

SELECT *
  FROM VUELOS
   WHERE HORA_SALIDA >= '06.00.00'    
     AND HORA_SALIDA <= '12.00.00'

Ejemplo: La columna NUM_VUELO representa los vuelos con 6 caracteres. Los dos primeros caracteres indican la compañía a la que pertenece cada vuelo (vgt: IB Iberia, BA British Airways) y los cuatro siguientes corresponden al número de vuelo. Bajo estas condiciones, recupérense todos los vuelos que no pertenecen a IBERIA.

SELECT *
  FROM VUELOS
  WHERE NUM_VUELO NOT BETWEEN 'IB0000'
                          AND 'IB9999'

Claúsula LIKE

Sirve para especificar, con la ayuda de metasímbolos (comodines), cadenas de caracteres que comparten ciertos caracteres: '%' equivale a una cadena de caracteres de longitud comprendida entre 0 y n (el '*' en MS-DOS) y '_' equivale a un único carácter (igual que '?' en MS-DOS).

Ejemplo: Recuperar todos los vuelos de Iberia.

SELECT *
  FROM VUELOS
  WHERE NUM_VUELO LIKE 'IB%'

SELECT *
  FROM VUELOS
  WHERE NUM_VUELOS LIKE 'IB____'

Expresiones aritméticas

Pueden ser utilizadas tanto en la lista de columnas que sigue a SELECT como en la claúsula WHERE.

Ejemplo: Visualizar la longitud y la envergadura de todos los aviones expresando las magnitudes en pies en vez de en metros y la velocidad de crucero en mph (en lugar de Km/h).

SELECT LONGITUD*3.28, ENVERGADURA*3.28, VELOCIDAD_CRUCERO/1.6
  FROM AVIONES

Funciones de agregación

Son funciones que operan con todas las filas que cumplen la condicion expuesta en la claúsula WHERE. Su resultado es un único valor. Pueden especificarse detrás de SELECT o en la claúsula HAVING (nunca dentro de WHERE).

f(expresión) aplica una función (MIN,MAX,AVG,SUM,COUNT) a una expresión aritmética en la cual debe participar, al menos, una columna.
COUNT(*) cuenta el número de filas de una tabla.

Ejemplo: Número de vuelos incluidos en nuestra base de datos.

SELECT COUNT(*)
  FROM VUELOS

Claúsula GROUP BY-HAVING

Sirve para dividir una tabla en grupos de filas que comparten características comunes.

Ejemplo: Primera salida y número de vuelos diarios para cada destino

SELECT DESTINO, MIN(HORA_SALIDA), COUNT(*)
  FROM VUELOS
  GROUP BY DESTINO

Notas:

GROUP BY crea una serie de subtablas compuestas por las filas que comparten el mismo valor en la(s) columna(s) de agrupamiento (la columna DESTINO en el ejemplo) y las funciones de agregación se aplican a cada subtabla de forma independiente.
No se puede poner en GROUP BY una columnaque no se haya incluido en la sentencia SELECT.
La claúsula HAVING permite elegir aquellos grupos que se quieren visualizar y no interfiere en la agrupación realizada por GROUP BY.

Ejemplo: Visualizar los destinos que tengan más de dos vuelos.

SELECT DESTINO
  FROM VUELOS
  GROUP BY DESTINO
  HAVING COUNT(*) > 2

Ejemplo: Visualizar los vuelos de Iberia que tengan plazas libres.

SELECT NUM_VUELO, CAPACIDAD-COUNT(*)
  FROM RESERVAS
  WHERE NUM_VUELO LIKE 'IB%'
  GROUP BY NUM_VUELO
  HAVING COUNT(*)<CAPACIDAD

Valores nulos

Cualquier operación aritmética sobre un campo nulo nos devolverá como resultado un valor nulo.
Las funciones de agregación ignoran los campos nulos, exceptuando la función COUNT. Es decir, AVG(x) no siempre será igual a SUM(x)/COUNT(*).
Dos valores nulos no son iguales ni son distintos, sino indeterminados. La expresión booleana colA=colB es falsa en cuanto colA o colB sean nulos, mientras que colA<>colB será cierta si sólo una de las dos columnas es nula (será falsa cuando ambas sean nulas).
Las columnas sobre las que haya definido un índice único (UNIQUE) sólo pueden incluir un valor nulo (en este caso sí se consideran iguales dos valores nulos).
Dependiendo del sistema gestor de bases de datos, los valores nulos son mayores o menores que cualquier otro valor cuando se devuelve un conjunto de datos ordenado.
DISTINCT no elimina los valores nulos repetidos.
GROUP BY agrupa todos los valores nulos en un único grupo.
La expresión IS NULL (e IS NOT NULL) sirve en una consulta para devolver las tuplas cuyo valor para la columna indicada sea nulo (o no).
Ejemplo: Visualizar las tuplas de T cuyo valor para la columna X es nulo:
```
SELECT *
  FROM T
  WHERE X IS NULL
```

Subconsultas

Responden a la siguiente sintaxis:

SELECT ...
  FROM ...
  WHERE columna concatenador (SELECT ...)

El concatenador puede ser un operador de comparación (<,>,=,<>,<=,>=) o la claúsula IN. En el primer caso, la subconsulta ha de devolver un único valor.

Ejemplo: Plazas libres que hay en cada vuelo MADRID-LONDRES del día 20/02/2001.

SELECT *
  FROM RESERVAS
  WHERE FECHA_SALIDA='20.02.2001'
  AND NUM_VUELO IN ( SELECT NUM_VUELO
                       FROM VUELOS
                       WHERE ORIGEN='MADRID'
                         AND DESTINO='LONDRES')

ANY y ALL se usan para poder utilizar operadores de comparacion con subconsultas que nos devuelvan más de un valor como resultado:

SELECT ...
  FROM ...
  WHERE columna operador {ANY/ALL} (subconsulta)

Nota: =ANY equivale a IN.

La subconsulta puede estar correlacionada con la consulta exterior:

Ejemplo: Reservas cuyo número de plazas libres sea mayor que la media para ese mismo vuelo.

SELECT *
  FROM RESERVAS A
  WHERE PLAZAS_LIBRES > (SELECT AVG(PLAZAS_LIBRES)
                         FROM RESERVAS
                         WHERE NUM_VUELO=A.NUM_VUELO)

A es un alias, un sobrenombre que se le da a una tabla y que debe ser £nico para toda la consulta.

EXISTS y NOT EXISTS se emplean para comprobar la existencia o ausencia del valor devuelto por una subconsulta:

Ejemplo: Obtener los vuelos con reservas efectuadas en los cuales aún quedan plzas libres, ordenando el resultado de mayor a menor número de plazas libres de mayor a menor. Ordenar por destino para igual número de plazas libres:

SELECT NUM_VUELO, DESTINO, SUM(PLAZAS-LIBRES)
  FROM VUELOS
  WHERE EXISTS (SELECT *
                FROM RESERVAS
		WHERE RESERVAS.NUM_VUELO=VUELOS.NUM_VUELO)
  GROUP BY NUM_VUELO
  ORDER BY 3 DESC, DESTINO, NUM_VUELO

Peculiaridades

Algunos gestores de bases de datos incluyen extensiones no estándar como, por ejemplo, mecanismos para limitar el tamaño de una consulta (que siempre se puede hacer mediante procedimientos almacenados):

IBM DB2: fetch first ... rows only
Oracle: ... and rownum <= valor
SQL Server: ... top valor [percent]

INSERT

La sentencia INSERT se utiliza para insertar tuplas en una tabla.

INSERT INTO tabla
 VALUES (valor, ...)

INSERT INTO tabla
 SELECT ...

UPDATE

UPDATE tabla
 SET columna=valor, ...
 [WHERE condición]

La sentencia UPDATE sirve para actualizar datos. Una claúsula opcional WHERE selecciona los datos que han de modificarse. Cuando esta claúsula se omite, se modifican todas las tuplas de la tabla.

DELETE

DELETE FROM tabla
 [WHERE condición]

DELETE permite eliminar tuplas. Igual que UPDATE, incluye una claúsula WHERE opcional.

DDL: Lenguaje de definición de datos

El sublenguaje de definición de datos de SQL se utiliza para crear, modificar y eliminar tablas, vistas, triggers, procedimientos almacenados, etcétera. Por desgracia, no está estandarizado suficientemente por el momento y muchos detalles dependen de la base de datos que utilicemos.

CREATE TABLE sirve para crear tablas, ALTER TABLE para modificarlas y DROP TABLE para eliminarlas. Se pueden definir restricciones sobre las columnas de una tabla: NOT NULL prohíbe que se inserten valores nulos en una columna, UNIQUE prohíbe que existan valores duplicados en una columna y CHECK restringe los valores que puede tomar una columna. También se pueden definir restricciones a nivel de tabla: CHECK puede involucrar a varias columnas; las claves primarias (PRIMARY KEY) aseguran que los valores de las columnas especificadas son únicos y no nulos; finalmente, las claves externas (REFERENCES) sirven para especificar restricciones de integridad referencial (esto es, cada tupla de la tabla ha de referenciar a una tupla existente en otra tabla con los mismos valores para las columnas incluidas en la clave externa).

Los tipos de datos permitidos para las columnas de las tablas varían según el servidor que utilicemos. La siguiente tabla recoge algunos de los más usuales:

Servidor	Cadenas	Enteros	Reales	Números	Fechas	Otros
SQL estándar					date time timestamp
IBM DB2	char(n) varchar(n)	integer smallint	real=float double	decimal(p,s)	date time timestamp	[var]graphic(n) blob,clob,dbclob
InterBase	char(n) varchar(n)	integer=int smallint	float double precision	numeric(p,d) decimal(p,d)	- - date	blob
Oracle	char(n) varchar[2](n)			number(p,s)	- - date	rowid, long [raw] blob, clob...
SQL Server	char[(n)] varchar[(n)]	int smallint tinyint	float[(n)] real	numeric[(p[,d])] decimal[(p[,d])]	- - datetime	[var]binary[(n)] money, text, image...

Las mayores diferencias suelen aparecer al manejar fechas. Por ejemplo, si queremos obtener la hora actual utilizaremos la palabra reservada "Now" en InterBase, "sysdate" en Oracle o la función "getdate()" en SQL Server.

Aparte de los tipos de datos estándar el usuario también puede definir sus propios tipos:

Servidor Mecanismo
IBM DB2 create distinct type ...
InterBase Definición de dominios con create domain (*)
Oracle create type ...
SQL Server Procedimiento almacenado sp_addtype (*)
Creación de reglas: create rule
Asociación de reglas con tipos de datos: sp_bindrule
Valor por defecto: create default & sp_binddefault
(*) reconocidos automáticamente por C++Builder

Generadores

Las distintas bases de datos ofrecen distintos mecanismos para asignar enteros diferentes a cada tupla (sin garantizar la secuencialidad de los valores):

Servidor Nombre Mecanismo
InterBase Generadores
Definición de generadores con create generator
Valor establecido con set generator
Eliminación con delete from rdb$generators where rdb$generator_name='...'
Uso con gen_id (id, incremento)
Oracle Secuencias
Definición: create_sequence id increment by valor start with valor
Uso: id.CurrVal & id.NextVal
SQL Server Identidades
Definición de columnas con el atributo identity (valor_inicial,incremento)

Uso de generadores con C++Builder

El uso correcto de generadores en C++Builder consiste en crear un procedimiento almacenado en el servidor que devuelva un valor entero e invocar a dicho procedimiento interceptanto el evento OnNewRecord o BeforePost de la tabla en la aplicación cliente.

En el servidor (InterBase):

  create procedure nextID returns (cod integer) as
  begin
    cod = gen_id(ID);
  end ^

En el cliente (aplicación C++Builder):

  TStoredProc *spNextID
		
  void __fastcall T...::Table...BeforePost(TDataSet *DataSet)
  {
    spNextID->ExecProc();
    Table...ID->Value = spNextID->ParamByName("COD")->AsInteger;
  }

También podemos ahorrarnos trabajo y asignar la clave primaria en el trigger si la tabla no tiene columnas con valores default, existe un índice único, o casi único, sobre alguna columna alternativa a la clave primaria que utilizaremos como criterio de ordenación para la navegación, o el valor de la clave primaria no nos importa realmente en nuestra aplicación, como sucede con las claves artificiales.

En un trigger (InterBase):

  set term ^;
  create trigger BItabla for tabla
  active before insert as
  begin
    Codigo = gen_id(generador, 1);
  end ^

Detalles de InterBase

Restricciones

 Valores nulos no permitidos: not null
 Valores por defecto: default

 Restricciones: check
 Identificación de las restricciones: constraint

 Clave primaria: primary key
 Clave candidata: unique
 Clave externa: references | foreign key

 Acciones referenciales: (on delete|on update) (no action|cascade|set default)
 Columnas calculadas: computed by

Índices

 create [unique] [asc|desc] index ... on ...
 alter index ... (active|inactive)
 set statistics index ...

Consultas

 Valores nulos: is [not] null
 Sinónimos para las columnas: ... as ...
 Funciones de agregación: count, sum, min, max, avg
 Subconsultas: in, exists

Cadenas

 Operador like (comodines % y _)
 Concatenación: ||
 Funciones: upper, lower, trim, substring

Procedimientos almacenados y triggers en InterBase


  create procedure nombre [ ( ParámetrosDeEntrada ) ]
    [ returns ( ParámetrosDeSalida ) ]
    as CuerpoDeProcedimiento

  create trigger nombre for tabla
    [active | inactive] {before | after} {delete | insert | update}
    [position prioridad]
    as CuerpoDeProcedimiento

  CuerpoDeProcedimiento ::= declaración* begin ... end


Declaración de variables

  declare variable id tipo

  NB: Al usarse, los identificadores de variables van precedidos de ':'

Asignación

  variable = expresión

Ejecución de un procedimiento almacenado
NB: No se pueden pasar expresiones como parámetros

  execute procedure nombre [(ParsEntrada)] [returning_values ParsSalida]

  select * from nombre [(ParsEntrada)], si se usa suspend dentro de un bucle

Estructuras condicionales

  if (condición) then instrucción [else instrucción]

Bucles

  while (condición) do instrucción

Instrucciones SQL: insert, update, delete & singleton select

    vg: select columna from tabla where condición into :variable

Iteradores sobre consultas (con cursores en Oracle o SQL Server)

  for select into variables do instrucción

Instrucción compuesta

  begin instrucción* end

Lanzamiento de excepciones

  exception id

Captura de excepciones (al final de un bloque begin...end)

  when exception id do instrucción
  when sqlcode n do instrucción
  when gdscode n do instrucción

Instrucciones de control

  exit
  suspend  (similar a los iteradores del lenguaje CLU)

Variables especiales

  new & old (igual que en Oracle)


Excepciones

create exception id "mensaje"


Alertas

 InterBase:   post_event mensaje

 C++Builder:  Componente TIBEventAlerter (página Samples de la paleta de componentes)


UDFs

 User Defined Functions: DLLs + declare external function

Índice de la sección

Servidor	Mecanismo
IBM DB2	create distinct type ...
InterBase	Definición de dominios con create domain (*)
Oracle	create type ...
SQL Server	Procedimiento almacenado sp_addtype () Creación de reglas: create rule* Asociación de reglas con tipos de datos: sp_bindrule Valor por defecto: create default & sp_binddefault
(*) reconocidos automáticamente por C++Builder

Servidor	Nombre	Mecanismo
InterBase	Generadores	Definición de generadores con create generator Valor establecido con set generator Eliminación con delete from rdb$generators where rdb$generator_name='...' Uso con gen_id (id, incremento)
Oracle	Secuencias	Definición: create_sequence id increment by valor start with valor Uso: id.CurrVal & id.NextVal
SQL Server	Identidades	Definición de columnas con el atributo identity (valor_inicial,incremento)