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 caracter 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
      D.MOV_IDECAB,
      C.MVC_FECHAX AS MOV_FECHAX,
      C.MVC_TIPDOC AS MOV_TIPDOC,
      C.MVC_NRODOC AS MOV_NRODOC,
      D.MOV_IDEPRD,
      P.PRD_NOMBRE AS MOV_NOMPRD,
      D.MOV_CANTID
   FROM
      MOVIMDET D
   JOIN
      MOVIMCAB C
         ON D.MOV_IDECAB = C.MVC_IDENTI
   JOIN
      PRODUCTOS P
         ON D.MOV_IDEPRD = P.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