PL/SQL – Armazenando um collection no banco de dados com ANYDATA

Armazenando um collection no banco de dados com ANYDATA

Olá pessoal !

Aqui estou novamente falando sobre ANYDATA, como prometido no artigo anterior. Quando comecei a escrever o primeiro texto, a idéia era que o assunto não se prolongasse. Isso mudou quando percebi que poderia mostrar alguns truques que também poderiam ser feitos usando ANYDATA, portanto, a série ainda terá um terceiro artigo ! 🙂

Hoje irei demonstrar em como persistir o resultado de uma collection no banco de dados. Resumindo, vou guardar o resultado de um SELECT em um registro de uma tabela.

Primeiro, vamos criar os types que nos auxiliarão nessa empreitada. Eles serão a estrutura dos dados que serão persistidos. Também criaremos a tabela no banco de dados.

-- Type que será o “registro”

CREATE OR REPLACE TYPE tData AS OBJECT
   (
    code    NUMBER
   ,description VARCHAR2(100)
   )
/       

-- Type que será a “tabela” contendo os “registros”

CREATE OR REPLACE TYPE tTableData AS TABLE OF tData
/

-- Tabela que conterá os dados 

CREATE TABLE anydata_result
(
 code    NUMBER,
 result  ANYDATA
)
/

Com a estrutura para armazenamento dos dados pronta, vamos agora criar a nossa procedure de teste. Ela fará a leitura dos dados, armazenará e depois fará a leitura da collection armazenada.

CREATE OR REPLACE PROCEDURE proc_anydata_test IS
   tableData   tTableData;       
   vAnyData    ANYDATA;
   vDummy      NUMBER;

BEGIN  
   FOR z IN 1..3 LOOP
      DBMS_OUTPUT.PUT_LINE('Armazena e lê registro de número: ' || z);

      -- Retorna Collection com o Resultado

      BEGIN
          SELECT tData(x.code,x.description)
          BULK COLLECT INTO tableData
          FROM
          (
            SELECT 1 code,'RESULTADO 1' description FROM DUAL
            UNION
            SELECT 2 code,'RESULTADO 2' description FROM DUAL
            UNION
            SELECT 3 code,'RESULTADO 3' description FROM DUAL
          ) x
          WHERE x.code <= z;

      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('READ DATA [ FAILED ] - ' || DBMS_UTILITY.FORMAT_ERROR_STACK); 

      END;  

      -- Converte Table para ANYDATA

      vAnyData := ANYDATA.convertCollection(tableData);

      -- Insere os dados na tabela

     BEGIN
        INSERT INTO anydata_result
        VALUES (z,vAnyData);
        COMMIT; 

     EXCEPTION
        WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('INSERT DATA [ FAILED ] - ' || DBMS_UTILITY.FORMAT_ERROR_STACK);       

     END;  

     -- Lê o registro com a tabela

     BEGIN
        SELECT y.result
        INTO   vAnyData
        FROM   anydata_result y
        WHERE  y.code = z;
 
     EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('READ DATA [ FAILED ] - ' || DBMS_UTILITY.FORMAT_ERROR_STACK);       

     END;  

     -- Converte AnyData para Table

      vDummy := vAnyData.getCollection(tableData);

     -- Lê a Tabela

     FOR x IN tableData.FIRST..tableData.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Codigo: ' || tableData(x).code || ' - Descricao : ' || tableData(x).description);
  
     END LOOP;

  END LOOP;

EXCEPTION
   WHEN OTHERS THEN         
      DBMS_OUTPUT.PUT_LINE('proc_anydata_test [ FAILED ] - ' || DBMS_UTILITY.FORMAT_ERROR_STACK);

END proc_anydata_test;

Basicamente a PROCEDURE pode ser dividida em:

• Leitura e armazenamento da collection em uma variável (tableData);
• Conversão do TABLE para ANYDATA;
• Inserção dos dados na tabela (anydata_result);
• Leitura dos dados na tabela (anydata_result);
• Conversão de ANYDATA para TABLE;
• Leitura da Collection;

Vamos agora executar nossa procedure…

BEGIN
   proc_anydata_test;

END;

E o resultado é:

SQL>

1 Armazena e lê registro de número: 1
2 Codigo: 1 - Descricao : RESULTADO 1
3 Armazena e lê registro de número: 2
4 Codigo: 1 - Descricao : RESULTADO 1
5 Codigo: 2 - Descricao : RESULTADO 2
6 Armazena e lê registro de número: 3
7 Codigo: 1 - Descricao : RESULTADO 1
8 Codigo: 2 - Descricao : RESULTADO 2
9 Codigo: 3 - Descricao : RESULTADO 3

Na primeira vez que o loop é executado, a collection tem apenas 1 registro, o segundo 2 e o terceiro respectivamente 3 ! 🙂

Vamos dar uma olhada no que foi armazenado no banco através de um SELECT simples…

SELECT * FROM  anydata_result 
/

Como podem observar, as aplicações de ANYDATA podem ser das mais variadas. No próximo artigo, vamos fazer algo ainda mais interessante !

Um grande abraço