Freqüentemente, ao resolver problemas de integração de sistemas, é necessário apresentar uma certa quantidade de dados em um formato ou outro. Ao mesmo tempo, qualquer um pode ser consumidor de dados, mas a fonte quase sempre é um banco de dados corporativo. Por exemplo, um fabricante pode exigir que o fornecedor relate periodicamente o movimento de suas mercadorias no formato XLSX ou XML, etc.
Existem muitas ferramentas para converter dados em vários formatos, e a possibilidade de seu uso depende da pilha tecnológica adotada na empresa e da arquitetura do software. Ao mesmo tempo, você sempre deseja que a cadeia que consiste em várias bibliotecas, estruturas, camadas do sistema usadas para converter os dados de origem o mais curto possível. Isso reduziria o tempo gasto no desenvolvimento da solução e aumentaria sua eficiência produtiva.
Se assumirmos que, de fato, a consulta SQL está na raiz do processo de seleção de dados, idealmente a cadeia de transformações seria desejável para ver isso:
d′=f(SQL(d))
onde
d - dados de origem,
SQL(d) - consulta SQL para recuperar dados,
f - uma função que converte a seleção no formato desejado,
d′ - dados no formato requerido.
Para o Oracle PL / SQL, há vários pacotes internos e de terceiros que implementam essa funcionalidade. Estes são DBMS_XMLGEN, DBMS_XMLQUERY, AS_XLSX, PL / JSON e outros.
No entanto, quando surgiu a pergunta sobre a conversão de dados para o formato CSV, por algum motivo, não havia soluções prontas. Eu tive que fazer isso sozinho, então será mostrado como.
Declaração do problema
Crie uma ferramenta (pacote PL / SQL) que receba uma consulta SELECT arbitrária como uma sequência de caracteres ou uma variável de cursor na entrada e retorne um objeto do tipo CLOB que encapsula dados no formato CSV na saída. Em caso de erro, NULL deve ser retornado. O formato CSV em si não precisa ser representado - são strings cujos elementos são separados por algum caractere, na maioria das vezes ";", mas, no caso geral, um caractere arbitrário pode atuar como um separador. Suponha que os caracteres 0x0D + 0x0A sejam usados para separar as seqüências de caracteres. A primeira linha no arquivo CSV é geralmente o cabeçalho e define os nomes das colunas.
Defina a interface do pacote
CREATE OR REPLACE PACKAGE pp_csv AS PROCEDURE query2sheet( stmt IN VARCHAR2, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); PROCEDURE query2sheet( ref_cursor IN OUT SYS_REFCURSOR, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); END;
Existem dois procedimentos sobrecarregados, a diferença entre eles é que um deles recebe a solicitação como uma sequência e o outro como um link para o cursor. O segundo parâmetro é a saída; este é o resultado desejado no objeto CLOB. Finalmente, o terceiro parâmetro é o separador CSV.
Na implementação desses procedimentos, o pacote DBMS_SQL interno nos ajudará, o que nos permitirá trabalhar com cursores dinâmicos quando não for conhecido antecipadamente (no estágio de compilação) exatamente quantas colunas estão envolvidas na seleção.
Os recursos DBMS_SQL permitem analisar e executar dinamicamente consultas arbitrárias. Para um procedimento que aceita uma solicitação como uma sequência, isso acontece da seguinte maneira:
AS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
Para um procedimento que utiliza uma variável de cursor, tudo é mais simples - a partir da 11ª versão do Oracle, a conversão “variável do cursor → número do cursor SQL” ficou disponível.
A função DBMS_SQL.TO_CURSOR_NUMBER converte a variável REFCURSOR (tipada forte ou fracamente) no número do cursor SQL, que pode ser passado para as rotinas DBMS_SQL. Nesse caso, a variável do cursor deve estar aberta antes de ser passada para a função DBMS_SQL.TO_CURSOR_NUMBER.
cur PLS_INTEGER := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);
Assim, as duas versões da chamada se resumiram à obtenção do
número do cursor dinâmico e do conjunto de dados associado. A próxima etapa é obter informações sobre as colunas deste conjunto e extrair os dados em si.
O pacote DMBS_SQL permite descrever as colunas de um cursor dinâmico, retornando informações sobre cada coluna em uma matriz associativa de registros.
Para fazer isso, você deve declarar a coleção PL / SQL com base no tipo de coleção DBMS_SQL.DESC_TAB (ou DESC_TAB2 se a consulta puder retornar nomes de colunas com mais de 30 caracteres). Depois disso, você pode usar métodos de coleção para iterar sobre a tabela e recuperar informações do cursor.
AS cols DBMS_SQL.DESC_TAB2; ncols NUMBER;
Em seguida, o pacote DBMS_SQL deve ser informado sobre o tipo de cada coluna selecionada usando a consulta dinâmica. Isso é feito chamando DEFINE_COLUMN.
FOR i IN 1 .. ncols LOOP DBMS_SQL.DEFINE_COLUMN(cur, i, col_val_chr, 32767); END LOOP;
No segundo argumento DEFINE_COLUMN, um número é passado - a posição seqüencial da coluna na lista. O terceiro argumento define o tipo de dados da coluna do cursor. Passa uma expressão do tipo apropriado. Em outras palavras, DBMS_SQL.DEFINE_COLUMN não passa uma sequência com um nome de tipo (por exemplo, "VARCHAR2"), mas uma variável definida com o tipo VARCHAR2.
Ao definir uma coluna do tipo caractere no quarto argumento, você deve especificar o comprimento máximo dos valores que podem ser carregados no cursor.
As operações preparatórias estão concluídas, agora você pode criar uma linha de cabeçalho e começar a extrair dados.
AS cap CLOB;
Os dados são recuperados linha por linha usando DBMS_SQL.FETCH_ROWS e chamadas subseqüentes para DBMS_SQL.COLUMN_VALUE para obter os valores das colunas individuais.
AS bod CLOB;
Resta apenas coletar o CSV resultante
DBMS_LOB.APPEND(sheet, cap); DBMS_LOB.APPEND(sheet, c_line_break); DBMS_LOB.APPEND(sheet, bod);
Lidar com erros
EXCEPTION WHEN OTHERS THEN sheet := NULL;
E feche o cursor
DBMS_SQL.CLOSE_CURSOR(cur);
Opções de uso do pacote
DECLARE csv CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet('SELECT empcode, fio FROM employee WHERE ROWNUM < 10', csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;
DECLARE csv CLOB; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT empcode, fio FROM employee WHERE ROWNUM < 10; DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet(cur, csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;
Isso, de fato, é tudo, o
código fonte está anexado .
O livro ajudou no desenvolvimento
Feuerstein S., Chegou B. - Oracle PL / SQL. Para profissionais.