Creando y actualizando tablas agregadas de uso general

1 comentario

En este artículo ya habíamos visto una forma de crear tablas agregadas y de insertarles datos:

https://firebird21.wordpress.com/2013/12/28/creando-tablas-agregadas-e-insertandoles-datos/

sin embargo, tenía un problema: para cada tabla agregada que decidieras crear tendrías que escribir dos stored procedures:

  • Uno para crear la tabla y su Primary Key
  • Otro para insertarle datos a la tabla

Si solamente vas a necesitar una o dos tablas agregadas eso no sería un problema pero si necesitarás 30 ó 40 tablas agregadas entonces ya es otro tema porque tendrías que escribir 60 u 80 stored procedures. Muy aburrido.

Por ese motivo escribí dos stored procedures de uso general. Siempre se necesitan al menos dos porque dentro de un stored procedure no se puede escribir un COMMIT y si no se le hizo el COMMIT a una tabla entonces no se le pueden insertar datos.

También escribí otro stored procedure llamado PARSER cuya misión es extraer un subtexto dentro de un texto.

El stored procedure PARSER

CREATE PROCEDURE PARSER(
      tcTexto     VARCHAR(18192),
      tcSeparador VARCHAR(12))
   RETURNS(
      ftcNombre    VARCHAR(1024))
AS
   DECLARE VARIABLE lnPosicion SMALLINT;
BEGIN

   lnPosicion = Position(tcSeparador IN tcTexto);

   ftcNombre = Left(tcTexto, lnPosicion - 1) ;

END;

Este stored procedure extrae de un texto un subtexto, para saber cual es el subtexto que debe extraer se usa un separador o delimitador. Algunos ejemplos:

PARSER de ‘hoy,es,un,día,soleado’ usando como delimitador la coma nos dará como resultado ‘hoy’

PARSER de ‘mañana*será*otro*día’ usando como delimitador el asterisco nos dará como resultado ‘mañana’

El delimitador puede tener más de un carácter si lo deseas. Está definido VARCHAR(12) así que podrías tener delimitadores de hasta 12 caracteres. Nunca necesitarás tanto, pero bueno, ahí está.

Las vista V_MOVIMIENTOS

Los datos que se insertarán en la tabla agregada deben tener un origen, o sea que deben salir de algún lado. Ese origen puede ser una tabla o una vista, en estos ejemplos usé una vista llamada V_MOVIMIENTOS.

CREATE VIEW V_MOVIMIENTOS(
   MOV_IDECAB,
   MOV_FECHAX,
   MOV_TIPDOC,
   MOV_NRODOC,
   MOV_IDEPRD,
   MOV_NOMPRD,
   MOV_CANTID)
AS
   SELECT
      MOV_IDECAB,
      MVC_FECHAX AS MOV_FECHAX,
      MVC_TIPDOC AS MOV_TIPDOC,
      MVC_NRODOC AS MOV_NRODOC,
      MOV_IDEPRD,
      PRD_NOMBRE AS MOV_NOMPRD,
      MOV_CANTID
   FROM
      MOVIMDET
   JOIN
      MOVIMCAB
         ON MOV_IDECAB = MVC_IDENTI
   JOIN
      PRODUCTOS
         ON MOV_IDEPRD = PRD_IDENTI;

En la tabla PRODUCTOS tenemos los datos de los productos, en la tabla MOVIMDET (detalles de movimientos) los detalles de las ventas y en la tabla MOVIMCAB (cabecera de movimientos) los datos de cabecera.

PIVOT1

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

El stored procedure CREAR_TABLA_PIVOT

CREATE PROCEDURE CREAR_TABLA_PIVOT(
   tcNombreTabla            VARCHAR(28),
   tcVista                  VARCHAR(28),
   tcPrimeraColumnaCabecera VARCHAR(64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(28),
   tcTipoDatos              VARCHAR(64))
AS
   DECLARE VARIABLE lcCreate VARCHAR(4096);
   DECLARE VARIABLE lcColumna VARCHAR(28);
   DECLARE VARIABLE lcMensajeError VARCHAR(1024);
BEGIN

   -- Primero, creamos la tabla

   lcCreate = 'CREATE TABLE ' || tcNombreTabla || ' (';

   lcCreate = lcCreate || tcPrimeraColumnaCabecera || ' NOT NULL, ';

   IF (Char_Length(tcOtrasColumnasCabecera) > 0) THEN BEGIN
      tcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',';
      WHILE (Char_Length(tcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(tcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcCreate = lcCreate || Trim(lcColumna) || ' , ' ;
         tcOtrasColumnasCabecera = Replace(tcOtrasColumnasCabecera, lcColumna || ',', '');
      END
   END

   FOR EXECUTE STATEMENT
      'SELECT DISTINCT ' || tcColumnaDatos || ' FROM ' || tcVista INTO :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcColumna = Replace(lcColumna, '%', '_');
      lcCreate = lcCreate || lcColumna || ' ' || tcTipoDatos || ', ' ;
   END

   lcCreate = Left(lcCreate, Char_Length(lcCreate) - 2);

   lcCreate = lcCreate || ');';

   EXECUTE STATEMENT lcCreate;

   -- Segundo, le agregamos una Primary Key

   EXECUTE STATEMENT
      'ALTER TABLE ' || tcNombreTabla ||
      ' ADD CONSTRAINT PK_' || tcNombreTabla ||
      ' PRIMARY KEY (' || Left(:tcPrimeraColumnaCabecera, Position(' ', :tcPrimeraColumnaCabecera)) || ')';

END;

Este stored procedure te permite crear una tabla y es totalmente general, esa tabla puede tener la cantidad de columnas fijas que desees. Puedes crear una tabla con 1 columna fija, otra tabla con 2 columnas fijas, otra tabla con 3 columnas fijas, etc. Las columnas variables son ilimitadas pero debes mantener un número que sea manejable. En estos ejemplos si tienes 4 productos entonces tendrás 4 columnas, si tienes 20 productos entonces tendrás 20 columnas. Pero si tienes 300 productos nadie se molestará en mirar todas las columnas de la tabla, así que sería una pérdida de tiempo crearla.

Mi regla cuando uso tablas agregadas es nunca crearlas con más de 12 columnas variables porque simplemente nadie las utiliza cuando tienen más columnas, así que ni vale la pena crearlas.

El stored procedure ACTUALIZAR_TABLA_PIVOT

CREATE PROCEDURE ACTUALIZAR_TABLA_PIVOT(
   tcTablaPivot             VARCHAR(  28),
   tcVista                  VARCHAR(  28),
   tcPrimeraColumnaCabecera VARCHAR(  64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(  28),
   tcValoresDatos           VARCHAR(1024))
AS
   DECLARE VARIABLE lcOtrasColumnasCabecera VARCHAR(4096);
   DECLARE VARIABLE lcSelect                VARCHAR(4096);
   DECLARE VARIABLE lcColumna               VARCHAR(  28);
   DECLARE VARIABLE lcGrupo                 VARCHAR(4096);
   DECLARE VARIABLE lcInto1                 VARCHAR(1024);
   DECLARE VARIABLE lcInto2                 VARCHAR(1024);
   DECLARE VARIABLE lcInto3                 VARCHAR(1024);
   DECLARE VARIABLE lcActualizar            VARCHAR(1024);
BEGIN

   lcOtrasColumnasCabecera = tcOtrasColumnasCabecera;

   lcSelect = 'SELECT ' ||
              Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ', ' ;

   lcGrupo = Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ', ' ;

   IF (Char_Length(lcOtrasColumnasCabecera) > 0) THEN BEGIN
      lcOtrasColumnasCabecera = lcOtrasColumnasCabecera || ',' ;
      WHILE (Char_Length(lcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(lcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcGrupo = lcGrupo || Left(lcColumna, Position(' ', lcColumna) - 1) || ',' ;
         lcOtrasColumnasCabecera = Replace(lcOtrasColumnasCabecera, lcColumna || ',', '') ;
      END
   END

   lcSelect = lcSelect ||
              tcColumnaDatos || ', ' ||
              tcValoresDatos ||
              ' FROM ' || tcVista ||
              ' GROUP BY ' || lcGrupo ||
              tcColumnaDatos ;

   FOR EXECUTE STATEMENT
      lcSelect
   INTO
      :lcInto1,
      :lcInto2,
      :lcInto3
   DO BEGIN
      -- Aquí se insertan o actualizan la primera columna y los datos agrupados
      lcInto2 = Left(lcInto2, 28);
      lcInto2 = Replace(lcInto2, ' ', '_');
      lcInto2 = Replace(lcInto2, '.', '_');
      lcInto2 = Replace(lcInto2, '/', '_');
      lcInto2 = Replace(lcInto2, '%', '_');
      lcActualizar = 'UPDATE OR INSERT INTO ' ||
                     tcTablaPivot || ' (' ||
                     Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ',' ||
                     lcInto2 ||
                     ') VALUES(' ||
                     lcInto1 || ',' ||
                     lcInto3 || ')' ;
      EXECUTE STATEMENT lcActualizar;
      IF (Char_Length(Trim(tcOtrasColumnasCabecera)) > 0) THEN BEGIN
         lcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',' ;
         lcActualizar = 'UPDATE ' || tcTablaPivot || ' SET ' ;
         WHILE (Char_Length(lcOtrasColumnasCabecera) > 0) DO BEGIN
            -- Aquí se actualizan todas las demás columnas de la cabecera
            EXECUTE PROCEDURE Parser(lcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
            lcActualizar = lcActualizar ||
                           Left(lcColumna, Position(' ', lcColumna) - 1) ||
                           ' = (SELECT ' || Left(lcColumna, Position(' ', lcColumna) - 1) ||
                           ' FROM ' || tcVista ||
                           ' WHERE ' ||
                           Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) ||
                           '=' || lcInto1 ||
                           ' ROWS 1),' ;
            lcOtrasColumnasCabecera = Replace(lcOtrasColumnasCabecera, lcColumna || ',', '') ;
         END
         lcActualizar = Left(lcActualizar, Char_Length(lcActualizar) - 1) ||
                       ' WHERE ' ||
                       Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || '=' ||
         lcInto1;
         EXECUTE STATEMENT lcActualizar;
      END
   END

END;

Este stored procedure es el encargado de insertarle datos a la tabla agregada. No importa cuantas columnas tenga la tabla, él se encargará de la inserción y actualización de los datos.

Ejemplo:

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT(
      'PRUEBA1',
      'V_MOVIMIENTOS',
      'MOV_IDECAB INTEGER',
      'MOV_FECHAX DATE,MOV_TIPDOC SMALLINT,MOV_NRODOC VARCHAR(15)',
      'MOV_NOMPRD',
      'NUMERIC(17, 4)')

PIVOT3

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

Como puedes ver, la tabla PRUEBA1 se creó exactamente como se le indicó.

EXECUTE PROCEDURE ACTUALIZAR_TABLA_PIVOT(
   'PRUEBA1',
   'V_MOVIMIENTOS',
   'MOV_IDECAB INTEGER',
   'MOV_FECHAX DATE,MOV_TIPDOC SMALLINT,MOV_NRODOC VARCHAR(15)',
   'MOV_NOMPRD',
   'SUM(MOV_CANTID)')

Y este es el stored procedure encargado de insertarle y de actualizarle las filas a la tabla PRUEBA1. Al ejecutarlo esto es lo que obtenemos:

PIVOT2

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

Donde tenemos 4 columnas fijas (MOV_IDECAB, MOV_FECHAX, MOV_TIPDOC, MOV_NRODOC) y 4 columnas variables (todas las demás).

Los parámetros de entrada de los stored procedures

tcNombreTabla. Es el nombre que queremos darle a la tabla que vamos a crear, o el nombre que tiene la tabla a la cual le insertaremos datos. Para saber si la tabla ya existe y no intentar crearla en ese caso podrías escribir:

lcTablaExiste = IIF((SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'PRUEBA1') = 'PRUEBA1', 'S', 'N')

Si el valor de la variable lcTablaExiste es igual a ‘S’ entonces ya existe una tabla con el nombre ‘PRUEBA1’.

tcVista. Es el nombre de la vista o de la tabla que contiene los datos que serán agrupados.

tcPrimeraColumnaCabecera. Es el nombre que veremos en la primera columna. Este dato siempre debe especificarse porque no se puede crear ni usar la tabla si no se lo especifica. Las filas de la vista tcvista son agrupadas según esta columna. Fíjate que debes escribir el nombre de una columna de tcvista y también su tipo de datos (SMALLINT, INTEGER, etc.).

tcOtrasColumnasCabecera. Esto es opcional, no es obligatorio tener más de una columna fija. Si las usarás entonces deberás escribir el nombre de la columna, su tipo de datos (INTEGER, DATE, NUMERIC, etc.) y luego una coma para separar a una columna de la siguiente.

tcColumnaDatos. Es la columna que tiene los datos variables. En nuestro ejemplo, es la columna donde se tienen registrados los nombres de los productos. En otros casos podría tener nombres de alumnos, de profesores, de países, de libros, etc.

tcTipoDatos. Es el tipo de los datos variables. Debe ser uno numérico (SMALLINT, INTEGER, DECIMAL, NUMERIC, etc.) y se lo necesita cuando se crea la tabla.

tcValoresDatos. En general se trata de una función agregada. El resultado de ejecutar la función agregada es el que se guardará en las columnas variables. Ejemplos: SUM(MOV_CANTID), SUM(MOV_CANTID * MOV_PRECIO), MAX(MOV_PRECIO), etc.

Manejo de errores

Para que los stored procedures no sean muy largos no les escribí código que se encargue de atrapar los errores, pero es algo que tú deberías hacer para evitar errores como los siguientes:

  • No existe la tabla
  • No existe el nombre de la columna
  • No existe el tipo de datos
  • El tipo de datos es incorrecto
  • etc.

Conclusión:

Si usamos tablas agregadas nuestras aplicaciones se potenciarán muchísimo porque ellas les permiten a los gerentes analizar lo que está ocurriendo y por lo tanto son de una gran ayuda para la toma de decisiones.

Los stored procedures que has visto en este artículo son de uso general, o sea que te servirán con cualquier tabla y con cualquier vista. Además, puedes tener todas las columnas fijas y todas las columnas variables que desees. Sin embargo, debes recordar que la mayoría de las personas no mirarán tablas que tengan más de 12 columnas variables y eso implica que en tu vista debes poner condiciones que limiten la cantidad de filas con nombres de productos (o de lo que sea) distintos.

Artículos relacionados:

Creando tablas agregadas e insertándoles datos

El índice del blog Firebird21

Creando tablas dentro de un stored procedure o de un trigger

2 comentarios

Como seguramente sabes, lo normal y generalmente lo mejor es que crees tablas afuera de los stored procedures y de los triggers. ¿Por qué? porque de esa manera es el propio Firebird quien se encargará de verificar que todo esté correcto en tiempo de compilación, o sea cuando los usuarios aún no están usando la Base de Datos. Si él descubre algún error (por ejemplo quisiste escribir INTEGER pero escribiste INTEGGGGER) te mostrará un mensaje adecuado y no te permitirá grabar la estructura de esa tabla. O sea que en tiempo de compilación ya descubrió el error y te lo señaló. Y eso está muy bien.

Sin embargo, a veces puedes necesitar crear tablas cuyas características desconoces en tiempo de compilación y solamente las puedes conocer en tiempo de ejecución (o sea cuando los usuarios ya están usando la Base de Datos). El problema es que si escribiste algo mal recién te enterarás en tiempo de ejecución.

Para crear una tabla en tiempo de ejecución utilizaremos el comando EXECUTE STATEMENT, como vemos a continuación:

CREATE PROCEDURE CREAR_TABLA
AS
   DECLARE VARIABLE lcComando VARCHAR(4096);
BEGIN

   lcComando = 'CREATE TABLE PRUEBA1 (
                   PRU_NUMERO INTEGER,
                   PRU_NOMBRE VARCHAR(40),
                   PRU_CANTID SMALLINT);' ;

   EXECUTE STATEMENT lcComando;

END;

Como puedes ver, el truco está en escribir un comando que se encargue de crear la tabla. Desde luego que el comando debe ser sintáticamente correcto, ya que si hay un error (escribiste INTEGGGER en lugar de INTEGER) la tabla no será creada y verás un mensaje similar al siguiente:

Invalid command.
Specified domain or source column INTEGGGER does not exist.
At procedure ‘CREAR_TABLA’ line: 11, col: 4.

Pero si escribiste todo bien podrás crear todas las tablas que quieras en tiempo de ejecución. Y eso puede ser muy útil en ciertos casos.

Además, no solamente puedes crear tablas, también si lo deseas puedes alterar tablas, borrar tablas, crearles índices, Primary Keys, Foreign Keys, etc.

CREATE PROCEDURE BORRAR_TABLA
AS
   DECLARE VARIABLE lcComando VARCHAR(1024);
BEGIN

   lcComando = 'DROP TABLE PRUEBA1;' ;

   EXECUTE STATEMENT lcComando;

END;

El stored procedure BORRAR_TABLA se encarga de borrar la tabla PRUEBA1, también si quisiéramos podríamos hacerlo más general, para que pueda borrar a cualquier tabla, no solamente a PRUEBA1. En ese caso escribiríamos algo como:

CREATE PROCEDURE BORRAR_TABLA(
   tcNombreTabla VARCHAR(32))
AS
   DECLARE VARIABLE lcComando VARCHAR(1024);
BEGIN

   lcComando = 'DROP TABLE ' || tcNombreTabla || ';' ;

   EXECUTE STATEMENT lcComando;

END;

Y lo ejecutaríamos de esta manera:

EXECUTE PROCEDURE BORRAR_TABLA('PRUEBA1');

Donde el stored procedure recibe como parámetro de entrada el nombre de la tabla que se desea borrar.

Manejo de errores

Si no eres cuidadoso varios errores pueden ocurrir, por ejemplo: quieres crear la tabla PRUEBA1 pero ya existe la tabla PRUEBA1. O quieres borrar la tabla PRUEBA1 pero no existe una tabla con ese nombre. O cuando creas una tabla su estructura está mal. O muchos otros.

Debes estar prevenido ante esas posibilidades y en tu stored procedure o trigger prever todas las posibilidades y usar excepciones para manejar los errores.

Artículos relacionados:

Entendiendo las excepciones

El índice del blog Firebird21

Insertando datos en tablas externas

7 comentarios

En este artículo habíamos visto como insertar datos que se encuentran en un archivo externo dentro de una tabla de Firebird:

https://firebird21.wordpress.com/2013/03/03/insertando-una-gran-cantidad-de-filas/

Ahora veremos el proceso inverso: crear un archivo externo e insertarle filas.

En realidad es muy sencillo.

CREATE TABLE MITABLAEXTERNA EXTERNAL 'E:\DATABASES\EXTERNA.TXT' (
   EXT_IDENTI SMALLINT,
   EXT_NOMBRE VARCHAR(40));

Como ves, al comando CREATE TABLA solamente se le agrega la palabra clave EXTERNAL y el nombre que queremos darle al archivo externo, rodeado con apóstrofos.

Para insertarle datos, se hace exactamente igual que con una tabla normal:

INSERT INTO MITABLAEXTERNA (EXT_IDENTI, EXT_NOMBRE) VALUES (1, 'MARCELA')
INSERT INTO MITABLAEXTERNA (EXT_IDENTI, EXT_NOMBRE) VALUES (2, 'SILVIA')

EXTERNAL1

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

Pero a una tabla externa no se le pueden actualizar filas ni tampoco borrar filas. Solamente se le pueden insertar filas.

Si ahora abrimos el archivo ‘EXTERNA.TXT’ con el bloc de notas, esto es lo que veremos:

EXTERNAL2

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

Los nombres se ven bien, pero los números no. ¿Por qué? ¿qué pasó?

Es que los números se guardan con su representación binaria, no como su representación de texto. En otras palabras, todos los números serán ilegibles para la gran mayoría de los seres humanos. ¿Y cuál es la solución? Muy fácil, que todas las columnas sean de tipo CHAR, así siempre podremos visualizarlas correctamente:

CREATE TABLE MITABLAEXTERNA2 EXTERNAL 'E:\DATABASES\EXTERNA2.TXT' (
   EXT_IDENTI CHAR(5),
   EXT_NOMBRE CHAR(40));

Y como ahora todas las columnas son de tipo CHAR entonces podemos rodear a las constantes con apóstrofos si queremos (pero no es obligatorio con las constantes numéricas):

INSERT INTO MITABLAEXTERNA (EXT_IDENTI, EXT_NOMBRE) VALUES ('1', 'MARCELA')
INSERT INTO MITABLAEXTERNA (EXT_IDENTI, EXT_NOMBRE) VALUES ('2', 'SILVIA')

Y si ahora abrimos el archivo ‘EXTERNA2.TXT’ con el bloc de notas, esto es lo que veremos:

EXTERNAL3

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

¡¡¡PERFECTO!!! ya vemos los identificadores y los nombres de las personas. Que es exactamente lo que deseábamos.

Si los datos que queremos insertar se encuentran en alguna tabla de Firebird (que es el caso más común) entonces para insertarlos podemos utilizar una subconsulta:

INSERT INTO MITABLAEXTERNA2
   (EXT_IDENTI, EXT_NOMBRE)
SELECT
   CLI_IDENTI,
   CLI_NOMBRE
FROM
   CLIENTES

EXTERNAL4

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

A las dos filas que ya teníamos anteriormente en nuestra tabla externa ahora se le agregaron los datos de los clientes. Usar SELECT para insertar datos a las tablas es lo más rápido que existe.

IMPORTANTE:

Un archivo externo no puede tener Primary Key, ni Foreign Key, ni cualquier clase de índices. ¿Por qué no? porque no tiene sentido, ya que puede ser abierto por cualquier programa (como el Bloc de notas del Windows, por ejemplo) y por lo tanto está afuera del control del Firebird. Las restricciones y los índices solamente tienen sentido si están bajo el control del Firebird.

Tampoco se justifica especificar que una columna será NOT NULL porque en un archivo externo todas las columnas siempre son NOT NULL, así que declarar a una columna como NOT NULL es innecesario.

Conclusión:

Tener la posibilidad de exportar datos en archivos externos puede ser muy importante en algunas ocasiones, con Firebird es muy fácil hacerlo, solamente debemos recordar que a esos archivos solamente se le pueden insertar datos, no se puede modificar esos datos ni borrarlos. Y también hay que recordar que los archivos externos no pueden tener restricciones ni índices porque como son externos entonces pueden ser abiertos por otros programas y en ese caso se encontrarían afuera del control del Firebird.

Artículos relacionados:

Insertando una gran cantidad de filas

El índice del blog Firebird21