Usando parámetros para devolver la cantidad de filas

1 comentario

Como recordarás, cuando queremos limitar la cantidad de filas que devuelve un SELECT podemos usar la cláusula FIRST … SKIP o la cláusula ROWS. Cuando escribimos un stored procedure a veces no conocemos de antemano cuantas filas necesitaremos, o en otras palabras la cantidad de filas que devolverá el stored procedure es variable.

Entonces, ¿cómo lo solucionamos?

Rodeando a nuestra variable con paréntesis, como en los siguientes ejemplos:

Ejemplo 1:

CREATE PROCEDURE CANTIDAD_VARIABLE_1(
   tnCantidadFilas INTEGER)
RETURNS(
   ftcNombreCliente TYPE OF COLUMN CLIENTES.CLI_NOMBRE)
AS
BEGIN

   FOR SELECT
      FIRST (:tnCantidadFilas)
      CLI_NOMBRE
   FROM
      CLIENTES
   INTO
      :ftcNombreCliente
   DO BEGIN
      SUSPEND;
   END

END;

Ejecutamos a nuestro stored procedure así:

SELECT
   *
FROM
   CANTIDAD_VARIABLE_1(3)

Ejemplo 2:

CREATE PROCEDURE CANTIDAD_VARIABLE_2(
   tnCantidadFilas INTEGER)
RETURNS(
   ftcNombreCliente TYPE OF COLUMN CLIENTES.CLI_NOMBRE)
AS
BEGIN

   FOR SELECT
      CLI_NOMBRE
   FROM
      CLIENTES
   ROWS
      (:tnCantidadFilas)
   INTO
      :ftcNombreCliente
   DO BEGIN
      SUSPEND;
   END

END;

Ahora, ejecutamos a este stored procedure así:

SELECT
   *
FROM
   CANTIDAD_VARIABLE_2(3)

Resultados:

En ambos casos obtendremos algo similar a esto: CANTIDADVARIABLE1

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

Conclusión:

Si en un stored procedure queremos usar las cláusulas FIRST o ROWS y el valor que vendrá a continuación es una variable entonces deberemos rodearla con paréntesis, como se vio en los ejemplos anteriores, o sea que la sintaxis es:

FIRST (:MiVariable)

ROWS (:MiVariable)

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Paginando un SELECT

2 comentarios

En ocasiones el conjunto resultado de un SELECT se utiliza para imprimir informes. Por ejemplo, un informe de ventas, o un informe de cobranzas, los cuales el usuario quiere tener impresos en papel.

También puede ocurrir que al usuario le interese imprimir solamente alguna página en especial. Por ejemplo, cayó café sobre la página número cinco, se hizo un desastre, y por eso el usuario quiere volver a imprimir solamente la página cinco; las demás no, porque las demás no se mancharon con café.

Si sabemos cuantas filas del conjunto resultado se imprimen en cada página entonces es muy fácil obtener las filas que nos interesan.

Ejemplo 1:

  • En cada página se imprimen 40 filas
  • Queremos re-imprimir la página número 5
SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla
ROWS
   (5 - 1) * 40 + 1 TO 5 * 40

En este ejemplo se obtendrán las filas 161 a 200, o sea todas las filas que corresponden a la página número 5, ya que cada página tiene 40 filas.

Ejemplo 2:

  • En cada página se imprimen 40 filas
  • Queremos re-imprimir las páginas números 5 y 6
SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla
ROWS
   (5 - 1) * 40 + 1 TO (5 + 1) * 40

En este ejemplo se obtendrán las filas 161 a 240, o sea todas las filas que corresponden a la página número 5 ó a la página número 6.

Forma general

La forma general, para usarla con cualquier números de página, cualquier cantidad de páginas y con cualquier cantidad de filas, es:

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla
ROWS
   (nPaginaNro - 1) * nCantidadFilas + 1 TO (nPaginaNro + (nCantidadPaginas - 1)) * nCantidadFilas

En esta fórmula hay que reemplazar a nPaginaNro por el número de la primera página que queremos imprimir (en nuestro ejemplo sería 5). Hay que reemplazar a nCantidadFilas por la cantidad de filas que tiene cada página (en nuestro ejemplo sería 40). Hay que reemplazar a nCantidadPaginas por la cantidad de páginas que queremos imprimir (en nuestro último ejemplo sería 2).

Artículo relacionado:

El índice del blog Firebird21

 

Multiple rows in singleton select

Deja un comentario

Este mensaje de error traducido significa: “hay muchas filas en el SELECT y debería haber solamente una”

Y ocurre cuando el Firebird está esperando que un SELECT devuelva una y solamente una fila pero el SELECT está devolviendo más de una fila.

Ejemplo:

UPDATE
   MiTabla
SET
   MiColumna = (SELECT MiOtraColumna FROM MiOtraTabla WHERE MiCondición)

¿Cuál es la solución?

El problema está en el SELECT entonces es allí donde se debe solucionar. Lo más sencillo es ejecutar solamente ese SELECT, ver cual es el conjunto resultado que devuelve y modificar el SELECT para que solamente devuelva una fila.

Para que un SELECT devuelva una fila y solamente una fila tenemos varias alternativas:

  1. Cambiar la condición MiCondición
  2. Usar FIRST 1
  3. Usar ROWS 1
  4. Usar DISTINCT

Conclusión:

El error ocurre cuando un SELECT devuelve más de una fila y debería devolver solamente una fila. La solución es revisar el SELECT para comprobar que es lo que devuelve y luego modificarlo para que devuelva solamente una fila.

Artículo relacionado:

El índice del blog Firebird21

 

Insertando datos aleatorios

15 comentarios

Muchas veces, generalmente cuando estamos haciendo pruebas para verificar el correcto funcionamiento de nuestros programas, necesitamos que nuestras tablas tengan muchos datos, así podemos comprobar que todo está funcionando bien … o no.

Desde luego que una posibilidad es ingresar esos datos manualmente, con un montón de INSERTs y luego un COMMIT. La situación se complica cuando los datos que necesitamos insertar no son unos pocos, sino cientos o miles. Para esos casos lo mejor es automatizar el proceso y que sea un stored procedure el encargado de realizar la tarea.

Para mostrar el concepto creé una tabla llamada NOMBRES, que tiene esta estructura:

ALEATORIO2

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

y le agregué (manualmente) estas filas:

ALEATORIO3

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

Después creé una tabla llamada APELLIDOS con esta estructura:

ALEATORIO4

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

Y le agregué, también manualmente, estas filas:

ALEATORIO5

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

A continuación creé otra tabla, llamada ALEATORIOS, con esta estructura:

ALEATORIO1

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

Y para insertar automáticamente filas en la tabla ALEATORIOS escribí este stored procedure:

SET TERM ^ ;

CREATE PROCEDURE INSERTAR_DATOS_ALEATORIOS(
   TNCANTIDADFILAS INTEGER)
AS
   DECLARE VARIABLE lnI INTEGER;
   DECLARE VARIABLE lnJ SMALLINT;
   DECLARE VARIABLE lnDia SMALLINT;
   DECLARE VARIABLE lnMes SMALLINT;
   DECLARE VARIABLE lnDiasMes SMALLINT;
   DECLARE VARIABLE ldFechax DATE;
   DECLARE VARIABLE lnHoras SMALLINT;
   DECLARE VARIABLE lnMinutos SMALLINT;
   DECLARE VARIABLE ltHoraxx TIME;
   DECLARE VARIABLE lnNumero SMALLINT;
   DECLARE VARIABLE lnFilasTabla INTEGER;
   DECLARE VARIABLE lnFilaElegida INTEGER;
   DECLARE VARIABLE lnCantidadNombres SMALLINT;
   DECLARE VARIABLE lcNombre VARCHAR(40);
   DECLARE VARIABLE lnCantidadApellidos SMALLINT;
   DECLARE VARIABLE lcApelld VARCHAR(40);
BEGIN

   lnI = 1;

   WHILE (lnI <= tnCantidadFilas) DO BEGIN
      -- Fechas aleatorias
      lnMes     = 1 + FLOOR(RAND() * 12);
      lnDiasMes = DECODE(lnMes, 1, 31, 2, 28, 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31);
      lnDia     = 1 + FLOOR(RAND() * lnDiasMes) ;
      ldFechax  = CAST(lnDia || '.' || lnMes || '.' || 2013 AS DATE);
      -- Horas aleatorias
      lnHoras   = FLOOR(RAND() * 24);
      lnMinutos = FLOOR(RAND() * 60);
      ltHoraxx  = CAST(lnHoras || ':' || lnMinutos AS TIME);
      -- Números aleatorios
      lnNumero = 1 + FLOOR(RAND() * 32767);
      -- Nombres aleatorios. Pueden haber 1, 2, ó 3 nombres
      lnCantidadNombres = 1 + FLOOR(RAND() * 3);
      lnFilasTabla      = (SELECT COUNT(*) FROM NOMBRES);
      lcNombre          = '';
      lnJ               = 1;
      WHILE (lnJ <= lnCantidadNombres) DO BEGIN
         lnFilaElegida = 1 + FLOOR(RAND() * lnFilasTabla);
         lcNombre      = lcNombre || (SELECT TRIM(NOM_NOMBRE) FROM NOMBRES WHERE NOM_IDENTI = :lnFilaElegida) ||' ';
         lnJ           = lnJ + 1;
      END
      -- Apellidos aleatorios. Pueden haber 1 ó 2 apellidos
      lnCantidadApellidos = 1 + FLOOR(RAND() * 2);
      lnFilasTabla        = (SELECT COUNT(*) FROM APELLIDOS);
      lcApelld            = '';
      lnJ                 = 1;
      WHILE (lnJ <= lnCantidadApellidos) DO BEGIN
         lnFilaElegida = 1 + FLOOR(RAND() * lnFilasTabla);
         lcApelld      = lcApelld || (SELECT TRIM(APE_APELLD) FROM APELLIDOS WHERE APE_IDENTI = :lnFilaElegida) || ' ';
         lnJ           = lnJ + 1;
      END
      -- Se insertan en la tabla los datos obtenidos
      INSERT INTO ALEATORIOS (ALE_IDENTI, ALE_FECHAX, ALE_HORAXX, ALE_NUMERO, ALE_NOMBRE, ALE_APELLD)
                      VALUES ( 0, :ldFechax , :ltHoraxx , :lnNumero , :lcNombre , :lcApelld ) ;
      lnI = lnI + 1;
   END

END^

SET TERM ; ^

Este stored procedure recibe como parámetro la cantidad de filas que se desean insertar, genera aleatoriamente los datos de las columnas y luego los inserta en la tabla ALEATORIOS. Entonces, nos será muy fácil insertar 1.000 filas, 25.000 filas, 3.000.000 de filas, o las que necesitemos. Desde luego que en tu caso la tabla no se llamará ALEATORIOS, tendrá otro nombre; así mismo en tu caso los nombres de las columnas seguramente serán distintos. Este es un ejemplo, para que captes la idea y te resulte muy fácil hacer algo similar cuando lo necesites.

Para verificar que funciona bien, escribí:

EXECUTE PROCEDURE INSERTAR_DATOS_ALEATORIOS(25);

y luego verifiqué el contenido de la tabla ALEATORIOS y esto fue lo que obtuve:

ALEATORIO6

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

O sea, 25 filas con contenido totalmente al azar. Por supuesto que si tú ejecutas el stored procedure obtendrás datos distintos, pues justamente de eso se trata: de tener datos distintos, al azar, como sucede en la vida real, donde generalmente no podemos saber lo que ocurrirá.

Supongo que te habrás fijado que algunas personas tienen un solo nombre, algunas tienen dos nombres y algunas tienen tres nombres. Así mismo algunas personas tienen un solo apellido y otras personas tienen dos apellidos.

Conclusión:

Poder insertar rápidamente y fácilmente miles o millones de filas a nuestras tablas nos será de gran ayuda, sobre todo cuando estamos haciendo pruebas para verificar el correcto funcionamiento de nuestros programas.

La cantidad de nombres que tendrá una persona se encuentra en la variable lnCantidadNombres y la cantidad de apellidos en la variable lnCantidadApellidos. Hice de esta manera para que se vea más real, pero no siempre necesitarás tener más de un nombre o más de un apellido.

Inclusive se podría haberle puesto pesos a la cantidad de nombres y de apellidos. Por ejemplo que el 80% de las personas tengan 2 nombres, el 15% que tenga 1 nombre y el restante 5% que tenga 3 nombres, cosas así.

Si quieres números que vayan por ejemplo desde el 32 hasta el 212, y solamente números en ese rango, tendrías que escribir:

lnNumero = 32 + FLOOR(RAND() * 180)

donde 32 es el número más bajo que se podría obtener y 180 es la cantidad de números distintos que se podrían obtener.

El número más bajo que se puede obtener con FLOOR(RAND() * n) es el 0, por eso si se quiere que los números aleatorios empiecen desde otro número hay que hacer una suma.

Artículo relacionado:

El índice del blog Firebird21