Agregando filas adicionales

7 comentarios

Un lector del foro de este blog hizo un pedido interesante: mostrar una consulta de una forma especial.

CIUDADES

Captura 1. Si haces clic en la imagen la verás más grande

O sea que se tienen 3 tablas: PAISES, ESTADOS, CIUDADES, relacionadas mediante sus identificadores, tal y como debe ser.

Pero ¿cómo hacemos para mostrar las filas de la forma pedida?

Hay varias alternativas, en este artículo mostraremos una de ellas.

La idea es agregar a la tabla de CIUDADES, dos columnas:

  • Si mostraremos el nombre del País
  • Si mostraremos el nombre del Estado

Nuestras tablas por lo tanto tendrán las siguientes estructuras:

CIUDADES2

Captura 2. Si haces clic en la imagen la verás más grande

CIUDADES3

Captura 3. Si haces clic en la imagen la verás más grande

CIUDADES4.png

Captura 4. Si haces clic en la imagen la verás más grande

Los datos contenidos en esas tablas podemos ver a continuación:

CIUDADES6

Captura 5. Si haces clic en la imagen la verás más grande

CIUDADES7

Captura 6. Si haces clic en la imagen la verás más grande

CIUDADES8

Captura 7. Si haces clic en la imagen la verás más grande

Una consulta simple sería la siguiente:

Listado 1.

SELECT
   PAI_NOMBRE,
   EST_NOMBRE,
   CIU_NOMBRE
FROM
   CIUDADES
JOIN
   PAISES
      ON CIU_IDEPAI = PAI_IDENTI
JOIN
   ESTADOS
      ON CIU_IDEPAI = EST_IDEPAI AND
         CIU_IDEEST = EST_IDENTI
ORDER BY
   CIU_IDEPAI,
   CIU_IDEEST,
   CIU_IDENTI

CIUDADES5

Captura 8. Si haces clic en la imagen la verás más grande

Pero eso no es lo que queremos obtener, porque los nombres de los Países y de los Estados están repetidos, y lo que queremos es verlos una sola vez.

¿Y entonces, qué hacemos?

Si nos fijamos en la Captura 1. veremos que se muestran en total 18 filas aunque en la tabla de CIUDADES solamente hay 12 filas. Eso significa que se están mostrando 6 filas más. Por lo tanto, lo que debemos hacer es agregar esas 6 filas adicionales.

¿Y cómo lo conseguimos?

Mediante una construcción del Firebird que se llama stored procedure seleccionable.

Listado 2.

CREATE PROCEDURE SP_MOSTRAR_CIUDADES
   RETURNS(
      ftcNombrePais   TYPE OF COLUMN PAISES.PAI_NOMBRE,
      ftcNombreEstado TYPE OF COLUMN ESTADOS.EST_NOMBRE,
      ftcNombreCiudad TYPE OF COLUMN CIUDADES.CIU_NOMBRE)
AS
   DECLARE VARIABLE lcNombrePais    TYPE OF COLUMN PAISES.PAI_NOMBRE;
   DECLARE VARIABLE lcNombreEstado  TYPE OF COLUMN ESTADOS.EST_NOMBRE;
   DECLARE VARIABLE lcNombreCiudad  TYPE OF COLUMN CIUDADES.CIU_NOMBRE;
   DECLARE VARIABLE lcMostrarPais   TYPE OF COLUMN CIUDADES.CIU_MOSPAI;
   DECLARE VARIABLE lcMostrarEstado TYPE OF COLUMN CIUDADES.CIU_MOSEST;
BEGIN

   FOR SELECT
      PAI_NOMBRE,
      EST_NOMBRE,
      CIU_NOMBRE,
      CIU_MOSPAI,
      CIU_MOSEST
   FROM
      CIUDADES
   JOIN
      PAISES
         ON CIU_IDEPAI = PAI_IDENTI
   JOIN
      ESTADOS
         ON CIU_IDEPAI = EST_IDEPAI AND
            CIU_IDEEST = EST_IDENTI
   ORDER BY
      CIU_IDEPAI,
      CIU_IDEEST,
      CIU_IDENTI
   INTO
      :lcNombrePais,
      :lcNombreEstado,
      :lcNombreCiudad,
      :lcMostrarPais,
      :lcMostrarEstado
   DO BEGIN
      IF (lcMostrarPais = 'T') THEN BEGIN
         ftcNombrePais   = lcNombrePais;
         ftcNombreEstado = '';
         ftcNombreCiudad = '';
         SUSPEND;
      END
      IF (lcMostrarEstado = 'T') THEN BEGIN
         ftcNombrePais   = '';
         ftcNombreEstado = lcNombreEstado;
         ftcNombreCiudad = '';
         SUSPEND;
      END
      ftcNombrePais   = '';
      ftcNombreEstado = '';
      ftcNombreCiudad = lcNombreCiudad;
      SUSPEND;
   END

END;

Explicación:

Como necesitamos agregar 6 filas lo podemos conseguir fácilmente usando un stored procedure seleccionable.

Obtenemos y colocamos en variables locales los nombres de los Países, de los Estados, de las Ciudades, y si debemos mostrar el País, y si debemos mostrar el Estado.

Luego, colocamos en los parámetros de salida los valores que deseamos sean devueltos.

Como el stored procedure es seleccionable entonces debemos usar el comando SUSPEND cada vez que deseamos devolver los parámetros de salida.

Listado 3.

SELECT
   *
FROM
   SP_MOSTRAR_CIUDADES

Un stored procedure seleccionable puede ser tratado como si fuera una tabla. Al ejecutar el Listado 3. obtendremos el siguiente resultado.

CIUDADES9

Captura 9. Si haces clic en la imagen la verás más grande

Que es exactamente lo que deseábamos obtener.

Artículos relacionados:

Entendiendo a los stored procedures

Agregando filas adicionales (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Validando números de teléfono

2 comentarios

La operación más importante de todas en una Base de Datos es la introducción de datos válidos. Si los datos no son válidos entonces todo lo demás que hagamos (consultas, procesamientos) será incorrecto y no servirá, será inútil.

Un dato que a veces es importante validar y a veces no, es el número de teléfono.

Muchas veces el número de teléfono es simplemente informativo, está ahí pero prácticamente no se lo usa. Sin embargo en ocasiones es extremadamente importante que sea válido. Es para estos últimos casos que debemos asegurarnos de que pueda ser utilizado cuando se lo necesita.

¿Cómo validamos un número de teléfono?

Los números de teléfono completos siempre están compuestos de la siguiente forma:

  • Código del país o región
  • Código del área
  • Número local

Cada país o región grande tiene un número que lo identifica. Por ejemplo el código de Argentina es 54, el de Bolivia 591, el de Brasil 55, el de Estados Unidos 1, el de Paraguay 595, el de Puerto Rico 1787, etc. Para saber que se trata del código del país o región y no de un número local, se le antecede con el signo + o con doble cero. Es decir que escribir 00 ó escribir + es lo mismo, la misma cosa.

El código del área (el área puede ser una ciudad grande, un estado o provincia o departamento) siempre viene a continuación del código del país.

Y finalmente viene el número local.

Ejemplos:

+49 30 1234567 corresponde a Alemania (porque empieza con 49), Berlín (porque continúa con 30), y el 1234567 es el número de esa ciudad y país. También se lo podría haber escrito como. 00 49 30 1234567.

+54 351 1234567 corresponde a Argentina (porque empieza con 54), provincia de Córdoba (porque sigue con 351) y el 1234567 es el número de esa ciudad y de ese país al cual se desea llamar. También se lo podría escribir como: 00 54 351 1234567.

+595 21 123456 corresponde a Paraguay (porque empieza con 595), ciudad de Asunción (porque sigue con 21),  el 123456 es el número de esa ciudad y ese país al cual se desea llamar. También se lo podría escribir como: 00 595 21 123456.

¿Cómo diseñamos la tabla?

Es muy común que para guardar números de teléfono se defina una columna como CHAR o VARCHAR y luego una longitud suficientemente grande, como por ejemplo:

CREATE TABLE CLIENTES(
   CLI_IDENTI INTEGER,
   CLI_NOMBRE VARCHAR(80),
   CLI_TELEFO CHAR(20)
);

En este caso la columna CLI_TELEFO está definida como CHAR(20) y la mayoría de los diseñadores pueden creer que está bien. Sin embargo, no es lo correcto. ¿Por qué no es correcto? Porque los usuarios en la columna CLI_TELEFO podrían insertar datos así:

1234567

+595-21-1234567

021-1234567

21.1234567

00-595-21-1234567

Y si nuestra aplicación debe usar esos números para hacerles llamadas a los clientes será muy problemático conseguirlo. Lo correcto por lo tanto es:

CREATE TABLE CLIENTES(
   CLI_IDENTI INTEGER,         -- Identificador del Cliente
   CLI_NOMBRE VARCHAR(80),     -- Nombre del Cliente
   CLI_CODPAI CHAR(4),         -- Código del país al cual corresponde el teléfono
   CLI_CODARE CHAR(4),         -- Código del área dentro del país
   CLI_TELEFO CHAR(8)          -- Número de teléfono local
);

De esa manera nunca habrá confusión posible. Inclusive podríamos tener un trigger BEFORE INSERT OR UPDATE que verifique el código del país, algo como:

CREATE EXCEPTION 
   E_CODIGO_PAIS_INCORRECTO 'El código del país es incorrecto';
CREATE TRIGGER CLIENTES_BIU FOR CLIENTES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
   AS
   BEGIN
 
      IF (NEW.CLI_CODPAI <> '54' AND NEW.CLI_CODPAI <> '595') THEN
         EXCEPTION E_CODIGO_PAIS_INCORRECTO;
 
   END;

Este trigger verifica que el país sea Argentina (código 54) o que sea Paraguay (código 595). Si los países fueran más de 2 entonces lo más conveniente sería tener una tabla, llamada por ejemplo PAISES en la cual se encontraran los códigos y los nombres de los países.

De todas maneras lo importante del trigger es que valida que el país ingresado sea solamente uno de los países válidos, evitando así que el usuario introduzca datos que no tienen sentido.

Análogamente a como se validó el código del país se podría validar el código del área. Algo como:

CREATE TRIGGER CLIENTES_BIU FOR CLIENTES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
   AS
   BEGIN
 
      IF (NEW.CLI_CODPAI <> '54' AND NEW.CLI_CODPAI <> '595') THEN
         EXCEPTION E_CODIGO_PAIS_INCORRECTO;
 
      IF (NEW.CLI_CODPAI = '595') THEN BEGIN
         IF (NEW.CLI_CODARE <> '21' AND NEW.CLI_CODARE <> '61') THEN
            EXCEPTION E_CODIGO_AREA_INCORRECTO;
      END
 
   END;

Aquí, si el código del país es 595 el código del área debe ser 21 ó 61, ningún otro código de área será aceptado.

Conclusión:

Validar los números de teléfono a veces no es importante porque es un dato meramente informativo, pero hay ocasiones en que debemos asegurarnos de que sea un número válido. Para esos casos lo más conveniente es agruparlo según el código del país, del área, y número local. Esas tres columnas deben ser validadas para asegurarnos de que contienen números correctos.

Artículos relacionados:

Diseño de bases de datos. 1FN

El índice del blog Firebird21

El foro del blog Firebird21