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:

  1. Recuperar todas las filas de la(s) tabla(s) especificada en FROM. Cuando se indica más de una, se devuelve su producto cartesiano.
  2. La claúsula WHERE, cuando está presente, elimina las tuplas que no satisfacen la condición.
  3. La claúsula GROUP BY, si existe, agrupa las tuplas que tienen los mismos valores en todas las columnas especificadas.
  4. Si existe una claúsula HAVING, los grupos resultantes de GROUP BY que no satisfagan la condición se eliminan.
  5. La lista de columnas separadas por comas que aparece en el SELECT determina las columnas que se devuelven (* devuelve todas).
  6. DISTINCT, cuando aparece, elimina duplicados.
  7. 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%'
o
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).

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:

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

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):

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