Creando tablas agregadas e insertándoles datos

3 comentarios

En el artículo:

https://firebird21.wordpress.com/2013/04/28/tablas-agregadas/

habíamos visto lo que son las tablas agregadas y la gran utilidad que podemos obtener de ellas. En éste veremos una forma de crearlas (no la única forma, sino una forma) y también como insertarles datos.

Necesitaremos escribir dos stored procedures para conseguir nuestro objetivo. ¿Por qué? porque el Firebird no nos permite tener un COMMIT dentro de un stored procedure y no podemos insertarle filas a una tabla antes de hacerle un COMMIT exitoso, por lo tanto deberemos crear dos stored procedures:

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

Ejemplo:

Tenemos una tabla llamada PRODUCTOS, en la cual se guardan el Identificador de cada producto, su Nombre y otros datos. Una tabla llamada MOVIMCAB (cabecera de movimientos) en la cual se guardan el Identificador del movimiento, la Fecha en que ocurrió, el Tipo de Documento que lo avala, el Número del Documento y otros datos. Y una tabla llamada MOVIMDET (detalles de movimientos) en la cual se guarda el Identificador del detalle, el Identificador de la cabecera, el Identificador del producto, la Cantidad vendida y otros datos.

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

En la Captura 1. vemos el Identificador y el Nombre de algunos productos.

AGRUPADAS2

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

Y en la Captura 2. vemos el Identificador del detalle, el Identificador de la cabecera, el Identificador del producto y la Cantidad vendida.

Y lo que queremos obtener es lo siguiente:

AGRUPADAS3

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

En la columna TAB_IDENTI se guarda el Identificador de la cabecera, y en las demás columnas la Cantidad vendida de cada uno de esos productos. Para hacer el ejemplo simple no se agregaron más columnas pero si tú quieres además del Identificador de la cabecera podrías agregarle la Fecha de la Venta, el Tipo de Documento, el Número del Documento, el Nombre del Cliente, etc., o sea que cualquier columna de la tabla MOVIMCAB o sus referenciadas podrías agregar.

Un stored procedure para crear la tabla agregada

CREATE PROCEDURE CREAR_TABLA_AGREGADA(
   tcNombreTabla VARCHAR(32))
AS
   DECLARE VARIABLE lcCreate   VARCHAR(4096);
   DECLARE VARIABLE lcColumna  VARCHAR(28);
BEGIN

 -- Primero, creamos la tabla

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

   lcCreate = lcCreate || 'TAB_IDENTI INTEGER NOT NULL, ';

   FOR SELECT
      DISTINCT
      PRD_NOMBRE AS MOV_NOMPRD
   FROM
      MOVIMDET
   JOIN
      PRODUCTOS
         ON MOV_IDEPRD = PRD_IDENTI
   ORDER BY
      MOV_IDECAB
   INTO
      :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcCreate = lcCreate || lcColumna || ' VARCHAR(28), ' ;
   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 (TAB_IDENTI)';

END;

Este stored procedure crea una tabla con el nombre que quieras asignarle y también una Primary Key para esa tabla. Fíjate que se usa DISTINCT porque los nombres de las columnas no pueden estar repetidos. Y el nombre de la columna se obtiene editando el Nombre del Producto y reemplazando algunos caracteres por guiones bajos. Por supuesto que si no te gustan los guiones bajos podrías usar otros caracteres para el reemplazo.

Un ejemplo de llamada sería el siguiente:

EXECUTE PROCEDURE CREAR_TABLA_AGREGADA('PRUEBA2');


AGRUPADAS5

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

Y así hemos creado una tabla que tiene columnas con ¡¡¡los nombres de los productos!!!. Muy interesante, ¿verdad?. Desde luego que en tu caso podrías poner nombres de alumnos, de profesores, de clientes, de países, de lo que se te ocurra.

Un stored procedure para insertarle filas a la tabla agregada

CREATE PROCEDURE ACTUALIZAR_TABLA_AGREGADA(
   tcNombreTabla VARCHAR(32))
AS
   DECLARE VARIABLE lcUpdate VARCHAR(4096);
   DECLARE VARIABLE lcColumna VARCHAR(28);
   DECLARE VARIABLE lnIdenti INTEGER;
   DECLARE VARIABLE lnCantidad SMALLINT;
BEGIN

   FOR SELECT
      MOV_IDECAB,
      PRD_NOMBRE,
      SUM(MOV_CANTID)
   FROM
      MOVIMDET
   JOIN
      PRODUCTOS
         ON MOV_IDEPRD = PRD_IDENTI
   GROUP BY
      MOV_IDECAB,
      PRD_NOMBRE
   INTO
      :lnIdenti,
      :lcColumna,
      :lnCantidad
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcUpdate = 'UPDATE OR INSERT INTO ' ||
                 tcNombreTabla || ' (' ||
                 'TAB_IDENTI, ' || lcColumna || ') VALUES (' ||
                 lnIdenti || ', '||
                 lnCantidad || ')';
      EXECUTE STATEMENT lcUpdate;
   END

END;

Aquí se editó el Nombre del Producto de la misma forma que habíamos hecho en el stored procedure CREAR_TABLA_AGREGADA porque los nombres de las columnas deben coincidir. Para poder usar el comando UPDATE OR INSERT se requiere de una Primary Key y por eso la tabla tiene una Primary Key. Este stored procedure debe recibir como parámetro de entrada el mismo nombre que se usó para crear la tabla agregada. Por ejemplo:

EXECUTE PROCEDURE ACTUALIZAR_TABLA_AGREGADA('PRUEBA2')

AGRUPADAS3

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

Y listo, eso es todo. Escribiendo dos stored procedures similares a los mostrados arriba podrás tener todas las tablas agregadas que quieras. Como recordarás, lo que generalmente se coloca en las tablas agregadas son agrupamiento de datos (sumas, cantidades totales, promedios, máximos, mínimos, etc.) porque sirven para analizar datos, no para verificar datos.

Artículos relacionados:

Tablas agregadas

Creando tablas dentro de un stored procedure o de un trigger

El índice del blog Firebird21

Anuncios

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