Casi siempre lo recomendable es que todas las tablas las crees afuera de tus stored procedures porque de esa manera el Firebird puede detectar cualquier error que cometas antes de que la tabla sea creada, y eso te evitará problemas futuros.

Sin embargo, a veces puedes necesitar crear una tabla adentro de un stored procedure y también insertarle datos a esa tabla.

La técnica para conseguirlo es usar el comando EXECUTE STATEMENT con la opción WITH AUTONOMOUS TRANSACTION, como puedes ver en el siguiente ejemplo:

CREATE PROCEDURE CREAR_E_INSERTAR
AS
   DECLARE VARIABLE lcNombreTabla VARCHAR(31);
   DECLARE VARIABLE lcComando     VARCHAR(32000);
BEGIN

   lcNombreTabla = 'PRUEBA1';

   lcComando = 'CREATE TABLE ' || lcNombreTabla || ' (PRU_IDENTI INTEGER, PRU_NOMBRE VARCHAR(80))' ;

   EXECUTE STATEMENT :lcComando WITH AUTONOMOUS TRANSACTION;

   lcComando = 'INSERT INTO ' || lcNombreTabla || '(PRU_IDENTI, PRU_NOMBRE) VALUES (1, ''AMÉRICA'')';

   EXECUTE STATEMENT :lcComando WITH AUTONOMOUS TRANSACTION;

   lcComando = 'INSERT INTO ' || lcNombreTabla || '(PRU_IDENTI, PRU_NOMBRE) VALUES (2, ''ÁFRICA'')';

   EXECUTE STATEMENT :lcComando WITH AUTONOMOUS TRANSACTION;

   lcComando = 'INSERT INTO ' || lcNombreTabla || '(PRU_IDENTI, PRU_NOMBRE) VALUES (3, ''ASIA'')';

   EXECUTE STATEMENT :lcComando WITH AUTONOMOUS TRANSACTION;

   lcComando = 'INSERT INTO ' || lcNombreTabla || '(PRU_IDENTI, PRU_NOMBRE) VALUES (4, ''EUROPA'')';

   EXECUTE STATEMENT :lcComando WITH AUTONOMOUS TRANSACTION;

   lcComando = 'INSERT INTO ' || lcNombreTabla || '(PRU_IDENTI, PRU_NOMBRE) VALUES (5, ''OCEANÍA'')';

   EXECUTE STATEMENT :lcComando WITH AUTONOMOUS TRANSACTION;

END;

Aquí, se crea una tabla llamada PRUEBA1, la cual está compuesta por dos columnas: PRU_IDENTI y PRU_NOMBRE. Luego de crear la tabla se le insertan 5 filas. El resultado de ejecutar este stored procedure es el siguiente:

CREAR1

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

En la Captura 1 podemos ver que se ha creado la tabla llamada PRUEBA1, con las columnas que habíamos especificado.

CREAR2

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

Y en la Captura 2 vemos que también se le insertaron las filas que habíamos pedido que se inserten.

Comentarios:

Al utilizar WITH AUTONOMOUS TRANSACTION el efecto es ejecutar el comando COMMIT dentro de nuestro stored procedure. Por defecto el Firebird no nos permite escribir ni COMMIT ni ROLLBACK dentro de un stored procedure pero podemos engañarle y ejecutar un COMMIT cuando llamamos al comando EXECUTE STATEMENT con la opción WITH AUTONOMOUS TRANSACTION.

Hay que recordar, sin embargo, que utilizar EXECUTE STATEMENT … WITH AUTONOMOUS TRANSACTION tiene sus desventajas:

  1. El Firebird no verifica que el comando a ejecutar sea correcto antes de ejecutarlo. La verificación siempre se hace después y si hay algo mal escrito entonces solamente se descubrirá en tiempo de ejecución (quizás por un usuario malhumorado).
  2. Usar EXECUTE STATEMENT es lento porque el comando no está compilado, debe ser compilado en el momento de ser ejecutado

 Si luego de sopesar las ventajas y desventajas decides que necesitas un COMMIT dentro de un stored procedure entonces recuerda que lo conseguirás usando AUTONOMOUS TRANSACTION.

Artículos relacionados:

EXECUTE STATEMENT

Usando EXECUTE STATEMENT con argumentos

El índice del blog Firebird21