Traducción de procedimientos almacenados entre dialectos - Java, SQL y jOOQ.

En los últimos años, hemos invertido mucho esfuerzo en mejorar nuestras capacidades de lenguaje de procedimientos en jOOQ. Lo que comenzó con una API interna simple para admitir emulaciones de cláusulas DDL como estas:

-- Some dialect that supports this
create table if not exists t (i varchar(10));

-- Db2
begin
  declare continue handler for sqlstate '42710' begin end;
  execute immediate 'create table T (I varchar(10))';
end

-- Oracle
begin
  execute immediate 'create table T (I varchar2(10))';
exception
  when others then
    if sqlerrm like 'ORA-00955%' then null;
    else raise;
    end if;
end;

-- SQL Server
begin try
  create table T (I varchar(10))
end try
begin catch
  if error_number() != 2714 throw;
end catch

…evolucionó hasta convertirse en una API completa para todo tipo de lógica de procedimiento que se ejecuta en su servidor de base de datos.

Bloques anónimos

Los ejemplos anteriores muestran lo que la mayoría de los RDBMS llaman "bloques anónimos", similares a las clases anónimas de Java, es decir, piezas de lógica de procedimiento que no tienen nombre.

Dependiendo de la base de datos, estos bloques se interpretan sobre la marcha o se compilan y almacenan en caché como sentencias SQL ordinarias. Pueden ser muy útiles por varias razones:

  • Para crear unidades de código ad hoc atómicas, ejecutadas en un solo viaje de ida y vuelta al servidor, similar a los scripts DDL anteriores con manejo de excepciones incorporado
  • Para crear código de procedimiento dinámico. Esto puede sonar esotérico para muchos, pero es exactamente lo correcto para los demás. Todo jOOQ se trata de SQL dinámico, así que ¿por qué no también PL/SQL dinámico, T-SQL, pgplsql, etc.? ?
  • Para eludir las limitaciones impuestas por la Ley de Conway, cuando no haya forma de obtener el GRANT necesario u otra señal burocrática para implementar su procedimiento en producción a su discreción. Quiero decir, todavía es una cosa en muchas empresas.
  • Un caso menor de la Ley de Conway podría ser cuando usted es un proveedor de productos y no está seguro de poder crear procedimientos en el sistema de producción de sus clientes. No lo hagas. Ejecute su lógica de procedimiento como un bloque anónimo si no puede, o como un procedimiento si puede. Mismo código jOOQ.
  • Si su código de procedimiento cambia con mucha frecuencia (incluso de forma dinámica), almacenarlo puede generar problemas complicados. Si alguna vez trabajó con Oracle y se encontró con el temido evento sin pestillousted sabe lo que quiero decir.

De ninguna manera recomiendo que use bloques anónimos en lugar de procedimientos almacenados en general. Si puede, almacene su código en la base de datos para un mejor rendimiento y reutilización. Pero a veces no se puede, ya veces no se debe.

Por lo tanto, jOOQ admite, como siempre, una combinación de varios elementos de lógica procesal, que incluyen:

  • Bloques con declaraciones de variables
  • IF declaración
  • Hebillas incluidas LOOP, WHILE, REPEAT, FOR rizado
  • EXIT (Donde LEAVE) y CONTINUE (Donde ITERATE) para flujo de control de bucle
  • RETURN retorno de trámites o funciones
  • GOTO (respiración !)
  • SIGNAL Donde RAISE
  • Etiquetas
  • CALL declaración para llamar a otros procedimientos almacenados
  • EXECUTE declaración (para ejecutar SQL dinámico desde la lógica de procedimiento. ¿Qué nivel de creación es este?)

Y estamos agregando más soporte todo el tiempo. El código Java podría verse así:

Variable<Integer> i = var(name("i"), INTEGER);
ctx.begin(
  for_(i).in(1, 10).loop(
    insertInto(T).columns(T.COL).values(i)
  )
).execute();

Suponiendo que no puede ejecutar una declaración de inserción masiva por algún motivo, este podría ser el camino a seguir. Se traduce a varios dialectos de la siguiente manera.

Db2 y MySQL (que no admite bloques anónimos, sino lotes de instrucciones)

begin
  declare I bigint;
  set I = 1;
  while I <= 10 do
    insert into T (COL) values (I);
    set I = (I + 1);
  end while;
end;

PostgreSQLName

do $$
begin
  for I in 1 .. 10 loop
    insert into T (COL) values (I);
  end loop;
end;
$$

Oráculo

begin
  for I in 1 .. 10 loop
    insert into T (COL) values (I);
  end loop;
end;

servidor SQL

begin
  declare @I bigint = 1;
  while @I <= 10 begin
    insert into T (COL) values (I);
    set @I = (@I + 1);
  end;
end;

Como siempre con jOOQ, no tiene que comenzar a escribir código basado en la API de jOOQ. Aunque este es el enfoque recomendado cuando su lógica de procedimiento (o SQL) es dinámicajOOQ también puede analizar y traducir SQL estático como una cadena. SQL Babelfish. Juega con él aquí para obtener más información: https://www.jooq.org/translate/

Almacenar código como procedimiento

Si no tiene ninguno de los casos de uso anteriores, querrá almacenar este código como un procedimiento (o función):

  • Para una mayor reutilización
  • Para un mejor rendimiento

En este caso, desde jOOQ 3.15, puedes usar nuestro CREATE PROCEDURE, CREATE FUNCTIONo incluso CREATE TRIGGER Apoyo.

Notar: CREATE PACKAGE está en la parte superior de nuestra lista de deseos, pero es posible que ya no esté en 3.15. Si los paquetes se usan solo para el espaciado de nombres, se pueden emular usando esquemas en otros dialectos. Otras funciones a nivel de paquete, como el estado del paquete, pueden ser más difíciles de traducir.

El bloque anónimo anterior se puede envolver fácilmente en un DSLContext.createProcedure() llamada

Variable<Integer> i = var("i", INTEGER);
Parameter<Integer> i1 = in("i1", INTEGER);
Parameter<Integer> i2 = in("i2", INTEGER);

ctx.createProcedure("insert_into_t")
   .parameters(i1, i2)
   // You may or may not wrap your block in BEGIN .. END.
   // jOOQ will figure it out per dialect...
   .as(for_(i).in(i1, i2).loop(
     insertInto(T).columns(T.COL).values(i)
   ))
   .execute();

Esto produciría los siguientes procedimientos:

DB2 y MySQL

create procedure INSERT_INTO_T(
  I1 integer,
  I2 integer
)
begin
  declare I bigint;
  set I = I1;
  while I <= I2 do
    insert into T (COL) values (I);
    set I = (I + 1);
  end while;
end;

MariaDB

create procedure INSERT_INTO_T(
  I1 int,
  I2 int
)
begin
  for I in I1 .. I2 do
    insert into T (COL) values (I);
  end for;
end;

Oráculo

create procedure INSERT_INTO_T(
  I1 number,
  I2 number
)
as
begin
  for I in I1 .. I2 loop
    insert into T (COL) values (I);
  end loop;
end;

PostgreSQLName

create procedure INSERT_INTO_T(
  I1 int,
  I2 int
)
language plpgsql
as
$$
begin
  for I in I1 .. I2 loop
    insert into T (COL) values (I);
  end loop;
end;
$$

servidor SQL

create procedure INSERT_INTO_T
  @I1 int,
  @I2 int
as
begin
  declare @I bigint = @I1;
  while @I <= @I2 begin
    insert into T (COL) values (@I);
    set @I = (@I + 1);
  end;
end;

Juega aquí para obtener más información: https://www.jooq.org/translate/. También esperamos sus informes de errores y/o solicitudes de funciones aquí: https://github.com/jOOQ/jOOQ/issues/new/choose.

Transformaciones delicadas

Los lenguajes de procedimiento están estandarizados a través de ISO/IEC 9075-4, y algunos RBDMS sorprendentemente están de acuerdo en gran medida con el estándar, que incluyen:

Otros hacen menos, pero todos los lenguajes procedimentales están de acuerdo en que son lenguajes muy simples, sin esas cosas "elegantes" como subtipo o polimorfismo paramétrico (OK, PL/SQL tiene algún subtipo de polimorfismo, pero no muy sofisticado (no admitiremos eso por ahora), expresiones lambda, distribución dinámica, tipos de datos algebraicos, etc. etc.

Lo que tienen en común es una estrecha integración con el lenguaje SQL, donde brillan.

Pero hay diferencias sutiles, no obstante. Por ejemplo, difieren en dónde puede declarar variables. Algunos tienen rango de bloque, otros no. Y algunos se adhieren a la norma, donde LEAVE requiere una etiqueta, algunos no.

Imagina que escribes este código jOOQ "fantástico"

Name t = unquotedName("t");
Name a = unquotedName("a");
Variable<Integer> i = var(unquotedName("i"), INTEGER);

ctx.begin(
     insertInto
     declare(i).set(2),
     loop(
       insertInto
       i.set(i.plus(1)),
       if_(i.gt(10)).then(loop(exit()), exit())
     )
   )
   .execute();

Es solo una versión más complicada del bucle original, que inserta los valores 1-10 en una matriz. No hay otra razón que mostrar las capacidades de transformación para el anidamiento de loop(exit())así como el infinito LOOP con EXIT uso, en lugar de indexado FOR lazo.

¡Hay algunas cosas que no siempre funcionan exactamente de esa manera en algunos dialectos!

Veamos qué hace Db2 con él.

begin
  -- Variable declarations need to be "pulled up" to the beginning
  -- of the block, i.e. before the INSERT statement
  declare i integer;
  insert into t (a) values (1);

  -- While irrelevant to this example, the init value for the
  -- variable declaration must remain at the original location
  set i = 2;

  -- We need a label to be able to leave this loop
  alias_1:
  loop
    insert into t (a) values (i);
    set i = (i + 1);
    if i > 10 then

      -- Same here, a label is required
      alias_2:
      loop
        leave alias_2;
      end loop;
      leave alias_1;
    end if;
  end loop;
end

Si no usamos EXIT en un bucle, entonces no habrá ninguna etiqueta. O, por supuesto, puede etiquetar sus bucles explícitamente, lo que siempre se recomienda. Pero a veces no tienes eso en tu código fuente original.

¿Qué hace Oracle con él?

Oracle tiene una sintaxis ligeramente diferente aquí:

declare
  i number(10);
begin
  insert into t (a) values (1);
  i := 2;
  loop
    insert into t (a) values (i);
    i := (i + 1);
    if i > 10 then
      loop
        exit;
      end loop;
      exit;
    end if;
  end loop;
end;

La principal diferencia es que también se extrae la declaración, pero una DECLARE Se necesita bloque para declarar variables fuera de BEGIN .. END. sin etiqueta EXIT es compatible de forma nativa, por lo que no es necesario transformar nada aquí.

Si está interesado en cómo funcionan estas transformaciones, lea este artículo.

Conclusión

Ya sea que migre de un dialecto a otro, o admita múltiples dialectos a la vez, o escriba SQL dinámico y lógica de procedimiento dinámica, o simplemente le guste escribir cosas en Java en lugar de SQL nativo, o si sufre de la Ley de Conway y no puede almacenar fácilmente su procedimiento código, jOOQ puede ayudarlo con estos esfuerzos.

Desde hace un tiempo, jOOQ admite declaraciones de procedimiento como bloques anónimos para los dialectos más populares. A partir de jOOQ 3.15, también admitiremos el almacenamiento de esta lógica en la base de datos de manera independiente del dialecto, así como el análisis/traducción del código de procedimiento en nuestro sitio web, o como una biblioteca/CLI o proxy JDBC para reemplazar su código de procedimiento/SQL . ad-hoc en una aplicación JDBC antigua.

¡Estén atentos para obtener más información sobre esta emocionante área de desarrollo de jOOQ!

Si quieres conocer otros artículos parecidos a Traducción de procedimientos almacenados entre dialectos - Java, SQL y jOOQ. puedes visitar la categoría Código.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Esta página web utiliza cookies para analizar de forma anónima y estadística el uso que haces de la web, mejorar los contenidos y tu experiencia de navegación. Para más información accede a la Política de Cookies . Ver mas