¿Cómo funciona una transacción SNAPSHOT?

Deja un comentario

Como recordarás, una transacción en Firebird puede tener uno de estos tres aislamientos:

  • READ COMMITED
  • SNAPSHOT
  • SNAPSHOT TABLE STABILITY

Los aislamientos le dicen al Firebird lo que debe hacer cuando una fila quiere ser actualizada (UPDATE) o borrada (DELETE) por más de una transacción al mismo tiempo.

Veamos un ejemplo:

  1. La transacción T1 empieza (su aislamiento es SNAPSHOT, y su modo de bloqueo es WAIT)
  2. La transacción T2 empieza (su aislamiento es SNAPSHOT)
  3. La transacción T2 actualiza (UPDATE) a una fila X de la tabla PRODUCTOS.
  4. La transacción T2 finaliza con un COMMIT
  5. La transacción T3 empieza (su aislamiento es SNAPSHOT)
  6. La transacción T3 también actualiza (UPDATE) a la misma fila X de la tabla PRODUCTOS
  7. La transacción T1 trata de actualizar (UPDATE) a la misma fila X de la tabla PRODUCTOS, pero como esa fila está bloqueada por la transacción T3 entonces deberá esperar hasta que la transacción T3 finalice.

Sin embargo, debemos notar que en este ejemplo la transacción T1 fallará siempre. ¿Por qué? Porque tendrá un conflicto con la transacción T3 si la transacción T3 finalizó con un COMMIT, o tendrá un conflicto con la transacción T2 si la transacción T3 finalizó con un ROLLBACK. O sea que, sin importar como termine la transacción T3 (con un COMMIT o con un ROLLBACK) la transacción T1 fallará.

Te puedes preguntar: ¿y por qué la transacción T1 debe esperar hasta que finalice la transacción T3? Después de todo, en ambos casos fallará, entonces ¿por qué la espera?

La respuesta está en que el Firebird solamente verifica la última versión de una fila para saber si ocurrió un conflicto o no. Si verificara la anteúltima versión entonces podría hacer fallar a la transacción T1 en el mismo momento en que hiciera un UPDATE, pero eso implicaría más trabajo y por lo tanto solamente verifica a la última versión.

Una transacción SNAPSHOT en el momento en que se inicia copia en su porción de la memoria RAM de la computadora la TIP (Transaction Inventory Page) conteniendo a todas las transacciones que están activas en ese momento. O sea, la transacción T2 conoce cuales son todas las transacciones que estaban activas cuando se inició la transacción T2, pero desconoce totalmente a las transacciones que se iniciaron después que ella y por lo tanto supone que están activas ya que evidentemente no habían finalizado cuando empezó la transacción T2. Cada transacción tiene un número único, que se guarda en la TIP. Cuando una transacción inicia bloquea a su propio número y lo desbloquea cuando finaliza (sea con un COMMIT o con un ROLLBACK). De esta manera es muy fácil saber si una transacción está activa o no. Si no se puede desbloquear su número, está activa. Si se puede desbloquear su número, no está activa.

Cuando una transacción SNAPSHOT trata de bloquear a una fila para hacerle un UPDATE o un DELETE, lo que puede ocurrir es lo siguiente:

  • Si la última versión de esa fila fue creada por una transacción que tiene un número menor y que no está en su copia de la TIP, el bloqueo tendrá éxito. ¿Por qué? porque como no está en su copia de la TIP y el número es menor, significa que una transacción anterior que insertó o actualizó a la fila ya finalizó con un COMMIT.
  • Si la última versión de una fila fue creada por una transacción cuyo número está en la TIP entonces debe verificar si esa transacción ya finalizó. ¿Y cómo lo verifica? Tratando de bloquear el número que esa otra transacción tiene en la TIP. Si lo consigue, la otra transacción ya finalizó y se puede bloquear a la fila con éxito.
  • Si el último COMMIT a la fila fue realizado por una transacción que tiene un número de transacción mayor, eso significa que esa otra transacción empezó después. Y por lo tanto, no se podrá bloquear a la fila.

Ejemplo:

Empieza una transacción, su número es 529, y en su copia de la TIP tiene a los números 521, 525, 526, 528. Eso significa que esas 4 transacciones están activas, aún no han finalizado ni con un COMMIT ni con un ROLLBACK.

La transacción cuyo número es 529 quiere hacer un UPDATE a una fila X de la tabla PRODUCTOS, el número de transacción que tiene la última versión de esa fila X es el 291. Como el número de transacción 291 no está en la copia de la TIP, eso significa que la transacción 291 (o una transacción anterior a ella) ya ha finalizado con un COMMIT y por lo tanto se podrá realizar el UPDATE con éxito a la fila X.

La transacción cuyo número es 529 quiere hacer un UPDATE a una fila X de la tabla PRODUCTOS, el número de transacción que tiene la última versión de esa fila es el 526. Como el número de transacción 526 está en la copia de la TIP, eso significa que la transacción 526 estaba activa cuando se inició la transacción 529. Pero ¿está activa ahora? quizás sí, quizás no, para verificarlo la transacción 529 trata de bloquear al número 526 en la TIP global, no en su propia copia de la TIP. Si consigue realizar el bloqueo, la transacción 526 ya no está activa y entonces podrá realizar el UPDATE con éxito. ¿Y si no consigue bloquear, qué hace? Eso dependerá del modo de bloqueo. Si es WAIT, seguirá intentando bloquear hasta tener éxito. Si es NO WAIT lanzará una excepción con un mensaje de error.

La transacción cuyo número es 529 quiere hacer un UPDATE a una fila X de la tabla PRODUCTOS, el número de transacción que tiene la última versión de esa fila es el 540. ¿Podrá la transacción 529 realizar el UPDATE? Depende. Si la transacción 540 finaliza con un COMMIT, no podrá. ¿Por qué no? Porque 540 es mayor que 529. Si la transacción 540 finaliza con un ROLLBACK entonces hay que buscar el número que tiene la última versión de esa fila X cuya transacción finalizó con un COMMIT. Si el último COMMIT a la fila X fue realizado por la transacción 520, la transacción 529 podrá realizar el UPDATE (porque 520 es menor que 529). Si el último COMMIT a la fila X fue realizado por la transacción 535, la transacción 529 no podrá realizar el UPDATE (porque 535 es mayor que 529).

Una transacción SNAPSHOT solamente puede actualizar (UPDATE) o borrar (DELETE) a las filas creadas por las transacciones que empezaron antes que ella.

Recuerda que el Firebird crea una nueva versión de una fila cada vez que se ejecuta el comando UPDATE o el comando DELETE en esa fila.

Sin importar como finalice la transacción (con un COMMIT o con un ROLLBACK) hay una nueva fila. Esto va creando filas inservibles (se les llama “basura”) y por ese motivo hay que limpiar a la Base de Datos de basura cada cierto tiempo.

Una fila tiene la siguiente forma:

| Nº de Transacción | Columna1 | Columna2 | Columna 3| etc.

Importante: Una transacción T1 (cuyo aislamiento es SNAPSHOT) puede actualizar (UPDATE) o borrar (DELETE) a una fila solamente cuando el Nº de Transacción que realizó el último COMMIT a esa fila es menor que el número de la transacción T1.

Artículos relacionados:

Entendiendo a las transacciones

Entendiendo a los identificadores de las transacciones

Modos de bloqueo de las transacciones

Bloqueos mortales

Lock conflict on no wait transaction. Deadlock

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Respuestas a más preguntas sobre transacciones (4)

1 comentario

¿Estás respondiendo las preguntas sobre transacciones?

Si lo estás haciendo, felicitaciones, si no lo estás haciendo, …. deberías, para aprender más.

Veamos cuales son las respuestas correctas del artículo: Más preguntas sobre transacciones (4)

Pregunta 1. Si una transacción solamente hará SELECTs ¿cuál debe ser su acceso?

[READ ONLY]     [READ WRITE]     [Es indiferente]

La respuesta correcta: Es indiferente. ¿Por qué? porque en ambos casos podrá hacer los SELECTs, sin embargo es preferible que sea READ ONLY porque las transacciones READ ONLY terminan más rápido que las transacciones READ WRITE debido a que el Firebird realiza menos tareas, pero no es obligatorio que sea READ ONLY.

Pregunta 2. Si una transacción solamente hará INSERTs ¿cuál debe ser su acceso?

[READ ONLY]     [READ WRITE]     [Es indiferente]

En este caso la transacción debe ser READ WRITE porque no se puede realizar un INSERT (ni un UPDATE, ni un DELETE) en las transacciones READ ONLY.

Pregunta 3. Si una transacción hará SELECTs e INSERTs, ¿cuál debe ser su acceso?

[READ ONLY]     [READ WRITE]     [Es indiferente]

Debe ser READ WRITE sí o sí, porque no se puede realizar un INSERT (ni un UPDATE, ni un DELETE) en una transacción READ ONLY.

Pregunta 4. Si una transacción quiere conocer lo que otras transacciones que finalizaron con COMMIT hicieron. ¿Cuál debe ser su aislamiento?

[READ COMMITTED]     [SNAPSHOT]     [Es imposible, nunca puede conocer lo que otras transacciones hicieron]

El aislamiento READ COMMITTED permite conocer lo que otras transacciones que ya finalizaron con un COMMIT hicieron. El aislamiento SNAPSHOT no permite tal cosa.

Pregunta 5. Si una transacción T1 ha bloqueado a una fila y una transacción T2 quiere actualizar a esa misma fila. ¿Cuál debe ser el modo de bloqueo de la transacción T2 para que pueda hacerlo?

[WAIT]     [NO WAIT]     [Cualquiera, es indiferente]     [Nunca la transacción T2 podrá actualizar esa fila]

Debe ser WAIT y entonces la transacción T2 esperará hasta que la transacción T1 finalice. Si el modo de bloqueo de la transacción T2 es NO WAIT entonces inmediatamente obtendrá una excepción y nunca podrá actualizar a esa fila.

Pregunta 6. Si una transacción T1 ha bloqueado a una fila, y quieres que la transacción T2 pueda ver la última versión confirmada de esa fila. ¿Cuál versión de registro debe tener la transacción T2?

[RECORD_VERSION]     [NO RECORD_VERSION]     [Es indiferente]

Debes usar RECORD_VERSION, porque en este caso la transacción T2 podrá ver la última versión confirmada de la fila. Si usas NO RECORD_VERSION entonces la fila que actualizó la transacción T1 será inaccesible para la transacción T2, inclusive para lectura.

Pregunta 7. Si una transacción T1 ha bloqueado una fila y no quieres que la transacción T2 pueda ni siquiera ver la última versión confirmada de esa fila ¿Cuál versión de registro debe tener la transacción T2?

[RECORD_VERSION]     [NO RECORD_VERSION]     [Es indiferente]

Debe ser NO RECORD_VERSION porque en este caso si una fila está bloqueada por una transacción las demás transacciones no pueden acceder a esa fila, ni siquiera para lectura.

Pregunta 8. Si una transacción quiere tener acceso exclusivo a todas las tablas que utiliza ¿puede hacerlo?

[Sí]     [No]

La respuesta es . En ese caso su aislamiento debe ser SNAPSHOT TABLE STABILITY. Si la transacción pudo iniciarse (no siempre podrá, otras transacciones podrían impedírselo) entonces tendrá acceso exclusivo.

Pregunta 9. Si una transacción quiere tener acceso exclusivo solamente a algunas de las tablas que utiliza ¿puede hacerlo?

[Sí]     [No]

La respuesta es . En ese caso debe escribir RESERVING y la lista de las tablas a las que quiere acceder de forma exclusiva.

Artículos relacionados:

Entendiendo a las transacciones

3 preguntas sobre transacciones

Respuestas a las 3 preguntas sobre transacciones

Más preguntas sobre transacciones (2)

Respuestas a más preguntas sobre transacciones (2)

Más preguntas sobre transacciones (3)

Respuestas a más preguntas sobre transacciones (3)

Más preguntas sobre transacciones (4)

El índice del blog Firebird21

El foro del blog Firebird21

Usando recursividad con CTE

4 comentarios

Una técnica muy poderosa aunque muy poco usada es la de la recursividad. ¿Qué significa recursividad? Que un código se ejecute y al finalizar se llame a sí mismo para volver a ejecutarse. Sin embargo, al volver a ejecutarse lo hace con algún parámetro cambiado y continúa llamándose a sí mismo hasta que se cumpla una condición de fin. Sin esa condición de fin se ejecutaría infinitas veces (o más propiamente, hasta que usara toda la memoria disponible de la computadora).

En Firebird podemos tener stored procedures recursivos y también SELECTs recursivos.

Los stored procedures recursivos ya los vimos en el artículo:

Stored procedures recursivos

Así que ahora veremos algo también muy interesante, los SELECTs recursivos.

Para crear un SELECT recursivo debemos usar CTE (Common Table Expression) porque esa construcción permite recursividad.

Ejemplo:

Tenemos una tabla llamada GENEALOGIA donde guardamos los nombres de las personas y nos gustaría conocer quienes son los ascendientes de cada persona (o sea, sus padres, abuelos, bisabuelos, tatarabuelos, etc.)

Algo así podríamos conseguir con tablas autoreferenciadas, como vimos en el artículo:

Entendiendo las tablas autoreferenciadas

y también con recursividad, que es el tema que ahora nos ocupa.

RECURSIVO02

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

RECURSIVO03

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

En la columna GEN_IDEPAD se guarda el Identificador de la fila padre. Una fila puede no tener fila padre (como es el caso de “ALICIA” o puede tener una sola fila padre (los demás casos).

Gráficamente, lo que tenemos es esto:

RECURSIVO01

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

Aquí podemos ver que BRIGIDA, BEATRIZ y BENITA son hijas de ALICIA; y que CORINA, CAROL, CARMEN y CLAUDIA son hijas de BRIGIDA y que DALILA y DIANA son hijas de CAROL.

Entonces, para ver el nombre de una persona y de todos sus ascendientes podríamos escribir algo como:

Listado 1.

WITH RECURSIVE PARIENTES AS (
   
   SELECT 
      * 
   FROM 
      GENEALOGIA 
   WHERE 
      GEN_IDENTI = 10
   
   UNION ALL
   
   SELECT 
      GENEALOGIA.* 
   FROM 
      GENEALOGIA 
   JOIN 
      PARIENTES 
         ON GENEALOGIA.GEN_IDENTI = PARIENTES.GEN_IDEPAD
   
)

SELECT
   * 
FROM 
   PARIENTES
ORDER BY
   GEN_IDENTI

Aquí hemos pedido la genealogía de la persona con Identificador igual a 10, entonces obtendremos lo siguiente:

RECURSIVO04

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

Explicación:

Con la instrucción WITH RECURSIVE PARIENTES AS ( … ) hemos creado una tabla virtual, o sea una tabla que solamente existe en la memoria de la computadora, cuyo nombre es PARIENTES y que es recursiva. ¿Qué significa que sea recursiva? que mientras pueda irá ejecutando los comandos que hayamos colocado dentro suyo.

¿Y cuál es la condición de fin?

Siempre que usamos recursividad debemos colocar una condición de fin, o de lo contrario se ejecutará infinitas veces (o mejor dicho, mientras tenga memoria disponible y luego se “colgará”). En este caso nuestra condición de fin lo da el JOIN, mientras una fila tenga algún valor en la columna GEN_IDEPAD continuará la ejecución, cuando ya no tenga valor (o sea, cuando GEN_IDEPAD sea NULL) finalizará porque ya no se cumplicará la condición que habíamos establecido de que GENEALOGIA.GEN_IDENTI = PARIENTES.GEN_IDEPAD.

¿Y por qué se usó UNION ALL?

Porque el primer SELECT es el que nos dice desde donde debemos empezar y en una CTE recursiva siempre hay que usar UNION ALL para agregar las demás filas que necesitamos. El primer SELECT se ejecuta una sola vez, el segundo SELECT se ejecuta muchas veces. Y si te fijas, en el segundo SELECT se usó el nombre de la tabla CTE que estamos creando, que en este caso es PARIENTES. O sea que dentro de la tabla virtual PARIENTES se usó a la tabla virtual PARIENTES y a eso se le llama … RECURSIVIDAD.

En síntesis:

Para crear una CTE recursiva, debemos:

  1. Escribir la palabra RECURSIVE a continuación de la palabra WITH
  2. Escribir un SELECT que determine cual será la primera fila, o sea donde empezará la recursividad
  3. Escribir UNION ALL
  4. Escribir un SELECT que tenga una condición de fin. Esa condición de fin puede colocarse en la cláusula WHERE o en la cláusula JOIN

Una vez que hemos creado a nuestra tabla virtual (o sea, a nuestra tabla CTE) ya la podemos usar como a cualquier otra tabla. Podemos mostrar su contenido (como en el ejemplo de este artículo) o usarla en una subconsulta, en un JOIN, etc.

Conclusión:

La recursividad es una técnica muy poderosa aunque muy poco usada. Debemos pensar en usar recursividad cuando hay una relación directa entre dos filas, lo cual generalmente se da en las tablas que pueden ser autoreferenciadas.

Cuidado especial debemos tomar en asegurarnos de que siempre exista una condición de fin y que a esa condición de fin se llegue alguna vez, de lo contrario a nuestra tabla virtual se le irán agregando filas y más filas, hasta que la computadora se quede sin memoria disponible y en ese momento se “colgará” por falta de memoria.

Hay personas que tienen facilidad para pensar de forma recursiva (como el autor de este blog) y personas a quienes les cuesta mucho pensar así. El no pensar de forma recursiva no es un defecto, todos los algoritmos recursivos también pueden expresarse de otra manera, sin usar recursividad. La ventaja de la recursividad es que en algunos algoritmos (no en todos, en algunos) es muy sencilla de implementar, con pocas líneas se llega al resultado buscado.

Por ejemplo, si quieres obtener los mismos resultados que te muestra el Listado 1. sin usar recursividad desde luego que podrás hacerlo, pero sin dudas que escribirás mucho más. Se notará muchísimo la diferencia cuando los niveles no sean 4 como en nuestro ejemplo sino que sean 12, 20, ó más.

Si aprendemos a usar recursividad entonces tendremos una herramienta más para poder realizar nuestras tareas.

Artículos relacionados:

Stored procedures recursivos

Entendiendo las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

El índice del blog Firebird21

El foro del blog Firebird21

Error con Global Temporary Table

Deja un comentario

Las GTT (Global Temporary Table) son muy útiles porque nos permiten tener tablas temporarias, tablas que solamente existirán mientras dure la transacción o la conexión.

Pero cuando las tablas GTT son grandes y necesitan ser ordenadas fuera del sort buffer pueden ocurrir problemas. Si usamos un disco RAM para los archivos temporales (los creados cuando en un SELECT tenemos la cláusula ORDER BY y ningún índice disponible) podríamos obtener este mensaje de error:

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
I/O error during “CreateFile (create)” operation for file “”.
Error while trying to create file.
The system can not find the path specified.

¿Cuál es el problema?

Que la carpeta donde se guardan los archivos temporales se encuentra en un disco RAM.

¿La solución?

Indicarle al Firebird que no use un disco RAM para los archivos temporales.

¿Qué versiones de Firebird tienen este problema?

Se lo ha detectado en Firebird 2.5.4 de 64 bits usando Windows 8.1

Podría ocurrir también en otras versiones.

Artículos relacionados:

Creando y usando tablas temporales
Usando un disco RAM para aumentar la velocidad
Los archivos temporales del Firebird
Acelerando los SORT
Configurando al Firebird
El índice del blog Firebird21
El foro del blog Firebird21

Ordenando las columnas variables de las tablas agregadas

Deja un comentario

En artículos anteriores habíamos visto como crear tablas agregadas, de una forma muy fácil y sencilla pero  había un pequeño problema que quizás habrás notado: los nombres de las columnas variables aparecían en orden alfabético.

Y eso a veces puede ser lo que quieres y a veces no. Por ejemplo, si las columnas variables se refieren a nombres de productos, de clientes, de países, de alumnos, etc., entonces está bien mostrarlas ordenadas alfabéticamente, pero si se refieren a nombres de meses eso no es lo usual. No se acostumbra ver a los meses así: ABR, AGO, DIC, ENE, FEB, etc. sino que se acostumbra verlos así: ENE, FEB, MAR, ABR, MAY, etc.

Por ese motivo le hice una pequeña modificación al stored procedure CREAR_TABLA_PIVOT el cual ahora permite especificar el orden en el cual se mostrarán las columnas variables. La nueva versión es la siguiente:

CREATE PROCEDURE CREAR_TABLA_PIVOT(
   tcNombreTabla            VARCHAR(  28),
   tcVista                  VARCHAR(  28),
   tcPrimeraColumnaCabecera VARCHAR(  64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(  28),
   tcTipoDatos              VARCHAR(  64),
   tcOrden                  VARCHAR(  28))
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 || IIF(Char_Length(:tcOrden) > 0, ' ORDER BY ' || :tcOrden, '')
   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;

donde como puedes ver se agregó el parámetro de entrada tcOrden.

La vista V_VENTAS

CREATE VIEW V_VENTAS(
   MVC_IDECLI,
   MVC_NOMCLI,
   MVC_NUMANO,
   MVC_NUMMES,
   NOMBRE_MES,
   MVC_NOMCLI1,
   MVC_TOTALX)
AS
   SELECT
      MVC_IDECLI,
      C.CLI_NOMBRE AS MVC_NOMCLI,
      EXTRACT(YEAR  FROM MVC_FECHAX) AS MVC_NUMANO,
      EXTRACT(MONTH FROM MVC_FECHAX) AS MVC_NUMMES,
      DECODE(
         EXTRACT(MONTH FROM MVC_FECHAX),
             1, 'ENE',
             2, 'FEB',
             3, 'MAR',
             4, 'ABR',
             5, 'MAY',
             6, 'JUN',
             7, 'JUL',
             8, 'AGO',
             9, 'SEP',
            10, 'OCT',
            11, 'NOV',
            12, 'DIC') AS NOMBRE_MES,
      C.CLI_NOMBRE AS MVC_NOMCLI,
      M.MVC_TOTALX
   FROM
      MOVIMCAB M
   JOIN
      CLIENTES C
         ON M.MVC_IDECLI = C.CLI_IDENTI;

En esta vista tenemos una columna llamada MVC_NUMMES que usaremos para ordenar los nombres de los meses según el número de los meses.

Creando la tabla agregada AGR_VENTAS_MENSUALES

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'AGR_VENTAS_MENSUALES',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'INTEGER',
      'MVC_NUMMES')

El último parámetro nos indica el orden en el cual queremos ver a las columnas variables. Si no se especifica ese orden entonces las veremos ordenadas alfabéticamente. Como la columna MVC_NUMMES es de tipo numérico y en ella guardamos los números de los meses (1, 2, 3, 4, 5, etc.) entonces los nombres de los meses estarán ordenados según ese criterio y serán ENE, FEB, MAR, ABR, MAY, etc.

Estructura de la tabla AGR_VENTAS_MENSUALES

AGR1

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

Insertándole datos a la tabla AGR_VENTAS_MENSUALES

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'AGR_VENTAS_MENSUALES',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

AGR2

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

Y listo, ya obtuvimos lo que queríamos obtener: los nombres de los clientes y a continuación los nombres de los meses. Si además del nombre del mes también quisiéramos tener el número del año sería muy fácil hacerlo: en la vista V_VENTAS reemplazamos la línea:

12, 'DIC') AS NOMBRE_MES,

por:

12, 'DIC') || '/' || EXTRACT(YEAR FROM MVC_FECHAX) AS NOMBRE_MES,

y después de ejecutar el stored procedure CREAR_TABLA_PIVOT esta será la estructura de nuestra tabla AGR_VENTAS_MENSUALES:

AGR3

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

Y después de insertarle datos con el stored procedure ACTUALIZAR_TABLA_PIVOT obtendremos esto:

AGR4

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

Así que ahora el nombre de cada columna variable está compuesto por el nombre del mes y el número del año, generalmente es mejor tenerlas de esta forma para que no se preste a confusión.

Artículos relacionados:

Aplicaciones OLTP y aplicaciones OLAP

Tablas agregadas

Creando tablas dentro de un stored procedure o de un trigger

Creando tablas agregadas e insertándoles datos

Creando y actualizando tablas agregadas de uso general

Otro ejemplo de uso de tablas agregadas

Ventas mensuales a cada cliente usando tablas agregadas

El índice del blog Firebird21

El comando INSERT

Deja un comentario

El comando INSERT (insertar) se utiliza para agregarle nuevas filas a una tabla. Tiene dos sintaxis:

Sintaxis 1:

INSERT INTO tabla | vista (<lista de columnas>)
VALUES (<lista de valores>)
[RETURNING <lista de columnas a retornar>
[INTO <lista con el nombre de las variables>]]

Se utiliza cuando se quiere agregar una fila a la tabla o vista.

Ejemplo:

INSERT INTO ALUMNOS
        (ALU_CODIGO, ALU_NOMBRE, ALU_APELLD)
VALUES  ('1253', 'MIRTHA', 'ROMERO');

Sintaxis 2:

INSERT INTO tabla | vista (<lista de columnas>)
SELECT <lista de valores> FROM ...
[RETURNING <lista de columnas a retornar>
[INTO <lista con el nombre de las variables>]]

 Ejemplo:

INSERT INTO GERENTES
         (GER_CODIGO, GER_NOMBRE)
   SELECT E.EMP_CODIGO, E.EMP_NOMBRE FROM EMPLEADOS E WHERE E.EMP_CARGOX = 'GERENTE'

Esta segunda sintaxis se utiliza cuando se quieren agregar muchas filas de una sola vez.

Si los valores que queremos agregarle a una tabla se encuentran en otra u otras tablas, esta segunda sintaxis es la adecuada porque es muy rápida.

Artículo relacionado:

El índice del blog Firebird21