8 SQL dinâmico (Português)

SQL dinâmico é uma técnica de programação que permite construir instruções SQL dinamicamente em tempo de execução. Você pode criar aplicativos mais flexíveis e de uso geral usando SQL dinâmico porque o texto completo de uma instrução SQL pode ser desconhecido na compilação. Por exemplo, SQL dinâmico permite criar um procedimento que opera em uma tabela cujo nome não é conhecido até o tempo de execução.

Em versões anteriores do Oracle, a única maneira de implementar SQL dinâmico em um aplicativo PL / SQL era usando o pacote DBMS_SQL. O Oracle8i apresenta SQL dinâmico nativo, uma alternativa ao pacote DBMS_SQL. Usando SQL dinâmico nativo, você pode colocar instruções SQL dinâmicas diretamente em blocos PL / SQL.

Este capítulo cobre os seguintes tópicos:

• O que é SQL dinâmico?
• Quando usar SQL dinâmico
• Um cenário de SQL dinâmico usando SQL dinâmico nativo
• SQL dinâmico nativo vs. pacote DBMS_SQL
• Aplicativo Linguagens de desenvolvimento diferentes de PL / SQL

O que é SQL dinâmico?

O SQL dinâmico permite que você escreva programas que fazem referência a instruções SQL cujo texto completo não é conhecido até o tempo de execução. Antes de discutir a SQL dinâmica em detalhes, uma definição clara de SQL estática pode fornecer um bom ponto de partida para entender a SQL dinâmica. As instruções SQL estáticas não mudam de execução para execução. O texto completo das instruções SQL estáticas são conhecidas na compilação, o que fornece os seguintes benefícios:

• A compilação bem-sucedida verifica se as instruções SQL fazem referência a objetos de banco de dados válidos.
• A compilação bem-sucedida verifica se os privilégios necessários estão disponíveis para acessar os objetos de banco de dados.
• O desempenho do SQL estático geralmente é melhor do que o SQL dinâmico.

Por causa dessas vantagens, você deve usar SQL dinâmico apenas se não puder usar SQL estático para atingir seus objetivos, ou se usar SQL estático for complicado em comparação com SQL dinâmico. No entanto, o SQL estático tem limitações que podem ser superadas com o SQL dinâmico. Nem sempre você sabe o texto completo das instruções SQL que devem ser executadas em um procedimento PL / SQL. Seu programa pode aceitar a entrada do usuário que define as instruções SQL a serem executadas, ou seu programa pode precisar concluir algum trabalho de processamento para determinar o curso de ação correto. Nesses casos, você deve usar SQL dinâmico.

Por exemplo, considere um aplicativo de relatório que executa consultas padrão em tabelas em um ambiente de data warehouse onde o nome exato da tabela é desconhecido até o tempo de execução. Para acomodar a grande quantidade de dados no data warehouse com eficiência, você cria uma nova tabela a cada trimestre para armazenar as informações da fatura do trimestre. Todas essas tabelas têm exatamente a mesma definição e são nomeadas de acordo com o mês e ano de início do trimestre, por exemplo INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, etc. Nesse caso, você pode usar a dinâmica SQL em seu aplicativo de relatório para especificar o nome da tabela em tempo de execução.

Com SQL estático, todas as informações de definição de dados, como definições de tabela, referenciadas pelas instruções SQL em seu programa devem ser conhecidas na compilação. Se a definição dos dados mudar, você deve alterar e recompilar o programa. Os programas SQL dinâmicos podem lidar com mudanças nas informações de definição de dados, porque as instruções SQL podem mudar “em tempo real” no tempo de execução. Portanto, o SQL dinâmico é muito mais flexível do que o SQL estático. O SQL dinâmico permite que você escreva o código do aplicativo reutilizável porque o código define um processo que é independente das instruções SQL específicas usadas.

Além disso, o SQL dinâmico permite que você execute instruções SQL que não são suportadas no estático Programas SQL, como instruções de linguagem de definição de dados (DDL). O suporte a essas instruções permite realizar mais com seus programas PL / SQL.

Quando usar SQL dinâmico

Você deve usar SQL dinâmico nos casos em que o SQL estático não suporta o operação que você deseja executar ou nos casos em que não conhece as instruções SQL exatas que devem ser executadas por um procedimento PL / SQL. Essas instruções SQL podem depender da entrada do usuário ou do trabalho de processamento realizado pelo programa. As seções a seguir descrevem situações típicas em que você deve usar SQL dinâmico e problemas típicos que podem ser resolvidos usando SQL dinâmico.

Para executar instruções DML dinâmicas

Você pode usar SQL dinâmico para executar Instruções DML nas quais a instrução SQL exata não é conhecida até o tempo de execução.Para obter exemplos, consulte os exemplos de DML em “Exemplos de código de pacote DBMS_SQL e código SQL dinâmico nativo” e “Amostra de operação DML”.

Para executar instruções não suportadas por SQL estático em PL / SQL

Em PL / SQL, você não pode executar os seguintes tipos de instruções usando SQL estático:

• Instruções de linguagem de definição de dados (DDL), como CREATE, DROP, GRANT e REVOKE
• Instruções de linguagem de controle de sessão (SCL), como ALTER SESSION e SET ROLE
  Consulte também:

  Referência do Oracle8i SQL para obter informações sobre instruções DDL e SCL.

Use SQL dinâmico se precisar executar qualquer um desses tipos de instruções em um PL / Bloco SQL.

Além disso, o SQL estático em PL / SQL não permite o uso da cláusula TABLE na SELECT instruções. Não existe essa limitação no SQL dinâmico. Por exemplo, o seguinte bloco PL / SQL contém uma instrução SELECT que usa a cláusula TABLE e SQL dinâmico nativo:

Para executar consultas dinâmicas

Você pode usar SQL dinâmico para criar aplicativos que executam consultas dinâmicas, que são consultas cujo texto completo não é conhecido até o tempo de execução. Muitos tipos de aplicativos precisam usar consultas dinâmicas, incluindo:

• Aplicativos que permitem aos usuários inserir ou escolher pesquisa de consulta ou critérios de classificação em tempo de execução
• Aplicativos que permitem aos usuários inserir ou escolha dicas do otimizador em tempo de execução
• Aplicativos que consultam um banco de dados onde as definições de dados das tabelas mudam constantemente
• Aplicativos que consultam um banco de dados onde novas tabelas são criadas frequentemente

Para obter exemplos, consulte “Exemplo de consulta” e os exemplos de consulta em “Um cenário de SQL dinâmico usando SQL dinâmico nativo”.

Para fazer referência a objetos de banco de dados que não existem na compilação

Muitos tipos de aplicativos devem interagir com os dados gerados periodicamente. Por exemplo, pode ser possível determinar a definição das tabelas do banco de dados na compilação, mas não os nomes das tabelas, porque novas tabelas estão sendo geradas periodicamente. Seu aplicativo precisa acessar os dados, mas não há como saber os nomes exatos das tabelas até o tempo de execução.

O SQL dinâmico pode resolver esse problema, porque o SQL dinâmico permite que você espere até o tempo de execução para especificar o nomes de tabelas que você precisa acessar. Por exemplo, no aplicativo de warehouse de dados de amostra discutido em “O que é SQL dinâmico?”, Novas tabelas são geradas a cada trimestre e essas tabelas sempre têm a mesma definição. Nesse caso, você pode permitir que um usuário especifique o nome da tabela em tempo de execução com uma consulta SQL dinâmica semelhante à seguinte:

Para otimizar a execução dinamicamente

Se você usar estática SQL, você deve decidir na compilação como deseja construir suas instruções SQL, se deseja ter dicas em suas instruções e, se incluir dicas, exatamente quais dicas ter. No entanto, você pode usar o SQL dinâmico para construir uma instrução SQL de uma forma que otimize a execução e / ou concatene as dicas em uma instrução SQL dinamicamente. Isso permite que você altere as dicas com base nas estatísticas atuais do banco de dados, sem exigir recompilação.

Por exemplo, o procedimento a seguir usa uma variável chamada a_hint para permitir aos usuários para passar uma opção de dica para a instrução SELECT:

Neste exemplo, o usuário pode passar qualquer um dos seguintes valores para a_hint:

Para invocar blocos PL / SQL dinâmicos

Você pode usar o EXECUTE IMMEDIATE instrução para invocar blocos PL / SQL anônimos. A capacidade de invocar blocos PL / SQL dinâmicos pode ser útil para extensão e personalização de aplicativos, onde o módulo a ser executado é determinado dinamicamente em tempo de execução.

Por exemplo, suponha que você queira escrever um aplicativo que leva um evento número e despachos para um manipulador do evento. O nome do manipulador está no formato EVENT_HANDLER_ event_num, onde event_num é o número do evento. Uma abordagem seria implementar o despachante como uma instrução switch, conforme mostrado abaixo, onde o código lida com cada evento fazendo uma chamada estática para seu manipulador apropriado.

Este código não é muito extensível porque o código do despachante deve ser atualizado sempre que um manipulador para um novo evento é adicionado. No entanto, usando SQL dinâmico nativo, você pode escrever um distribuidor de evento extensível semelhante ao seguinte:

Para executar operações dinâmicas usando direitos de invocador

Usando o recurso de direitos de invocador com o dinâmico SQL, você pode construir aplicativos que emitem instruções SQL dinâmicas sob os privilégios e o esquema do invocador.Esses dois recursos, direitos de invocador e SQL dinâmico, permitem que você crie subcomponentes de aplicativo reutilizáveis que podem operar e acessar os dados e módulos do invocador.

Um cenário de SQL dinâmico usando SQL dinâmico nativo

O cenário descrito nesta seção ilustra o poder e a flexibilidade de SQL dinâmico nativo. Este cenário inclui exemplos que mostram como realizar as seguintes operações usando SQL dinâmico nativo:

• Executar operações DDL e DML
• Executar consultas de linha única e várias linhas

Modelo de dados

O banco de dados neste cenário é um banco de dados de recursos humanos da empresa (denominado hr) com o seguinte modelo de dados :

Uma tabela mestre chamada offices contém a lista de todos os locais da empresa. A tabela offices tem a seguinte definição :

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Várias emp_ tabelas de localização contêm as informações do funcionário, em que localização é o nome da cidade onde fica o escritório está localizado. Por exemplo, uma tabela chamada emp_houston contém informações de funcionários para o escritório da empresa em Houston, enquanto uma tabela chamada emp_boston contém funcionários informações para o escritório da empresa em Boston.

Cada emp_ a tabela de localização tem a seguinte definição:

As seções a seguir descrevem várias operações SQL dinâmicas nativas que podem ser realizadas nos dados no hr banco de dados.

Exemplo de operação DML

O seguinte procedimento SQL dinâmico nativo dá um aumento a todos os funcionários com um cargo específico:

Exemplo de operação DDL

A instrução EXECUTE IMMEDIATE pode executar operações DDL. Por exemplo, o procedimento a seguir adiciona um local de escritório:

O procedimento a seguir exclui um local de escritório:

Exemplo de consulta dinâmica de linha única

O EXECUTE IMMEDIATE a instrução pode executar consultas dinâmicas de uma única linha. Você pode especificar variáveis de ligação na cláusula USING e buscar a linha resultante no destino especificado na INTO cláusula da instrução.

A função a seguir recupera o número de funcionários em um determinado local executando um trabalho especificado:

Exemplo de consulta dinâmica de várias linhas

O OPEN-FOR, FETCH e CLOSE as instruções podem executar consultas dinâmicas de várias linhas. Por exemplo, o procedimento a seguir lista todos os funcionários com um determinado trabalho em um local especificado:

SQL dinâmico nativo versus o pacote DBMS_SQL

A Oracle fornece dois métodos para usar SQL dinâmico no PL / SQL: SQL dinâmico nativo e o pacote DBMS_SQL. O SQL dinâmico nativo permite que você coloque instruções SQL dinâmicas diretamente no código PL / SQL. Essas instruções dinâmicas incluem instruções DML (incluindo consultas), blocos anônimos PL / SQL, instruções DDL, instruções de controle de transação e instruções de controle de sessão.

Para processar a maioria das instruções SQL dinâmicas nativas, você usa o EXECUTE IMMEDIATE. No entanto, para processar uma consulta de várias linhas (SELECT instrução), você usa OPEN-FOR, FETCH e CLOSE instruções.

O pacote DBMS_SQL é uma biblioteca PL / SQL que oferece uma API programática para execute instruções SQL dinamicamente. O pacote DBMS_SQL tem interfaces programáticas para abrir um cursor, analisar um cursor, fornecer ligações, etc. Programas que usam o pacote DBMS_SQL fazem chamadas a este pacote para executar operações SQL dinâmicas.

As seções a seguir fornecem informações detalhadas sobre as vantagens de ambos os métodos.

Vantagens do SQL dinâmico nativo

O SQL dinâmico nativo oferece as seguintes vantagens sobre o :

Facilidade de uso

O SQL dinâmico nativo é muito mais simples de usar do que o pacote DBMS_SQL.Como o SQL dinâmico nativo está integrado ao SQL, você pode usá-lo da mesma forma que usa atualmente o SQL estático no código PL / SQL. Além disso, o código SQL dinâmico nativo é normalmente mais compacto e legível do que o código equivalente que usa o pacote DBMS_SQL.

O DBMS_SQL não é tão fácil de usar quanto o SQL dinâmico nativo. Existem muitos procedimentos e funções que devem ser usados em uma seqüência estrita. Normalmente, a execução de operações simples requer uma grande quantidade de código quando você usa o pacote DBMS_SQL. Você pode evitar essa complexidade usando o SQL dinâmico nativo.

A Tabela 8-1 ilustra a diferença na quantidade de código necessária para realizar a mesma operação usando o DBMS_SQL pacote e SQL dinâmico nativo.

Tabela 8-1 Comparação de código do pacote DBMS_SQL e SQL dinâmico nativo

Melhorias de desempenho

O desempenho do SQL dinâmico nativo em PL / SQL é comparável ao desempenho de SQL estático porque o interpretador PL / SQL tem suporte integrado para SQL dinâmico nativo. Portanto, o desempenho dos programas que usam SQL dinâmico nativo é muito melhor do que os programas que usam o pacote DBMS_SQL. Normalmente, as instruções SQL dinâmicas nativas têm um desempenho 1,5 a 3 vezes melhor do que as instruções equivalentes que usam o pacote DBMS_SQL. Claro, seus ganhos de desempenho podem variar dependendo de sua aplicação.

O pacote DBMS_SQL é baseado em uma API procedural e, como resultado, incorre em alta chamada de procedimento e sobrecarga de cópia de dados. Por exemplo, toda vez que você vincula uma variável, o pacote DBMS_SQL copia a variável de vinculação PL / SQL em seu espaço para uso posterior durante a execução. Da mesma forma, toda vez que você executa uma busca, primeiro os dados são copiados para o espaço gerenciado pelo pacote DBMS_SQL e, em seguida, os dados buscados são copiados, uma coluna por vez, para o Variáveis PL / SQL, resultando em sobrecarga substancial resultante da cópia de dados. Em contraste, o SQL dinâmico nativo agrupa as etapas de preparação, ligação e execução da instrução em uma única operação, o que minimiza a cópia de dados e a sobrecarga da chamada de procedimento e melhora o desempenho. Ao usar o SQL dinâmico nativo ou o pacote DBMS_SQL, você pode melhorar o desempenho usando variáveis de ligação, porque o uso de variáveis de ligação permite que o Oracle compartilhe um único cursor para várias instruções SQL.

Por exemplo, o seguinte código SQL dinâmico nativo não usa variáveis de ligação:

Para cada variável my_deptno distinta, um novo cursor é criado, que pode causar contenção de recursos e baixo desempenho. Em vez disso, vincule my_deptno como uma variável de vinculação, como no exemplo a seguir:

Aqui, o mesmo cursor é reutilizado para diferentes valores de vinculação my_deptno, melhorando assim o desempenho e a escalabilidade.

Suporte para tipos definidos pelo usuário

SQL dinâmico nativo oferece suporte a todos os tipos suportados por SQL estático em PL / SQL. Portanto, o SQL dinâmico nativo fornece suporte para tipos definidos pelo usuário, como objetos definidos pelo usuário, coleções e REFs. O pacote DBMS_SQL não oferece suporte a esses tipos definidos pelo usuário.

Suporte para busca em registros

SQL dinâmico nativo e SQL estático suportam busca em registros, mas o DBMS_SQL pacote não. Com o SQL dinâmico nativo, as linhas resultantes de uma consulta podem ser buscadas diretamente em registros PL / SQL.

No exemplo a seguir, as linhas de uma consulta são buscadas no emp_rec registro:

Vantagens do pacote DBMS_SQL

O pacote DBMS_SQL oferece as seguintes vantagens sobre o SQL dinâmico nativo:

Suporte para programas do lado do cliente

Atualmente, o pacote DBMS_SQL é compatível com programas do lado do cliente, mas o SQL dinâmico nativo não. Cada chamada para o pacote DBMS_SQL do programa do lado do cliente se traduz em uma chamada de procedimento remoto PL / SQL (RPC); essas chamadas ocorrem quando você precisa vincular uma variável, definir uma variável ou executar uma instrução.

Suporte para DESCRIBE

O procedimento DESCRIBE_COLUMNS no pacote DBMS_SQL pode ser usado para descreva as colunas de um cursor aberto e analisado por meio de DBMS_SQL. A funcionalidade é semelhante ao comando DESCRIBE no SQL * Plus. O SQL dinâmico nativo não tem um recurso DESCRIBE.

Suporte para Bulk Dynamic SQL

Bulk SQL é a capacidade de processar várias linhas de dados em uma única instrução DML. Bulk SQL melhora o desempenho reduzindo a quantidade de alternância de contexto entre SQL e a linguagem do host. Atualmente, o pacote DBMS_SQL oferece suporte a SQL dinâmico em massa.

Embora não haja suporte direto para operações em massa em SQL dinâmico nativo, você pode simular um SQL dinâmico em massa nativo colocando a instrução SQL em massa em um bloco “BEGIN … END” e executando o bloco dinamicamente. Essa solução alternativa permite que você perceba os benefícios do SQL em massa em um programa SQL dinâmico nativo. Por exemplo, o seguinte código SQL dinâmico nativo copia a coluna ename de uma tabela para outra:

Atualizações e exclusões de várias linhas com uma cláusula RETURNING

O pacote DBMS_SQL oferece suporte a instruções com uma cláusula RETURNING que atualiza ou exclui várias linhas. SQL dinâmico nativo suporta apenas uma cláusula RETURNING se uma única linha for retornada.

Suporte para instruções SQL maiores que 32 KB

O DBMS_SQL o pacote suporta instruções SQL maiores que 32 KB; SQL dinâmico nativo não.

Reutilização de instruções SQL

O procedimento PARSE no DBMS_SQL package analisa uma instrução SQL uma vez. Após a análise inicial, a instrução pode ser usada várias vezes com diferentes conjuntos de argumentos de ligação.

Em contraste, o SQL dinâmico nativo prepara uma instrução SQL para execução sempre que a instrução é usada. A preparação da instrução geralmente envolve análise, otimização e geração de plano. A preparação de uma declaração cada vez que ela é usada incorre em uma pequena penalidade de desempenho. No entanto, o mecanismo de cursor compartilhado da Oracle minimiza o custo, e a penalidade de desempenho normalmente é trivial quando comparada aos benefícios de desempenho do SQL dinâmico nativo.

Exemplos de código de pacote DBMS_SQL e código de SQL dinâmico nativo

Os exemplos a seguir ilustram as diferenças no código necessário para concluir as operações com o pacote DBMS_SQL e SQL dinâmico nativo. Especificamente, são apresentados os seguintes tipos de exemplos:

• Uma consulta
• Uma operação DML
• Uma operação de retorno DML

Em geral, o código SQL dinâmico nativo é mais legível e compacto, o que pode melhorar a produtividade do desenvolvedor.

Exemplo de consulta

O exemplo a seguir inclui uma instrução de consulta dinâmica com uma variável de ligação (:jobname) e duas colunas de seleção (ename e sal):

stmt_str := "SELECT ename, sal FROM emp WHERE job = :jobname";

Este exemplo consulta funcionários com j descrição ob SALESMAN na coluna job da tabela emp. A Tabela 8-2 mostra o código de amostra que realiza essa consulta usando o pacote DBMS_SQL e SQL dinâmico nativo.

Tabela 8-2 Consulta usando o pacote DBMS_SQL e SQL dinâmico nativo

Exemplo de DML

O o exemplo a seguir inclui uma instrução INSERT dinâmica para uma tabela com três colunas:

stmt_str := "INSERT INTO dept_new VALUES (:deptno, :dname, :loc)";

Este exemplo insere uma nova linha para os quais os valores da coluna estão nas variáveis PL / SQL deptnumber, deptname e location. A Tabela 8-3 mostra o código de amostra que realiza esta operação DML usando o pacote DBMS_SQL e SQL dinâmico nativo.

Tabela 8-3 Operação DML usando o pacote DBMS_SQL e SQL dinâmico nativo

Exemplo de retorno de DML

O exemplo a seguir inclui um UPDATE declaração que atualiza a localização de um departamento quando recebe o número do departamento (deptnumber) e um novo local (location), e então retorna o nome do departamento:

stmt_str := "UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname";

Este exemplo insere uma nova linha para a qual os valores da coluna estão nas variáveis PL / SQL deptnumber, deptname e location. A Tabela 8-4 mostra o código de amostra que realiza essa operação de retorno DML usando o pacote DBMS_SQL e SQL dinâmico nativo.

Tabela 8-4 Operação de retorno DML usando o DBMS_SQL Pacote e SQL dinâmico nativo

Linguagens de desenvolvimento de aplicativos além de PL / SQL

Até agora, a discussão neste capítulo foi sobre o suporte PL / SQL para SQL dinâmico.No entanto, você pode usar outras linguagens de desenvolvimento de aplicativos para implementar programas que usam SQL dinâmico. Essas linguagens de desenvolvimento de aplicativos incluem C / C ++, COBOL e Java.

Se você usa C / C ++, pode desenvolver aplicativos que usam SQL dinâmico com a Oracle Call Interface (OCI) ou pode usar o pré-compilador Pro * C / C ++ para adicionar extensões SQL dinâmicas ao seu C código. Da mesma forma, se você usar COBOL, poderá usar o pré-compilador Pro * COBOL para adicionar extensões SQL dinâmicas ao seu código COBOL. Se você usa Java, pode desenvolver aplicativos que usam SQL dinâmico com JDBC.

No passado, a única maneira de usar SQL dinâmico em aplicativos PL / SQL era usando DBMS_SQL. Existem várias limitações no uso deste pacote, incluindo questões de desempenho. Conseqüentemente, os desenvolvedores de aplicativos podem ter usado uma das alternativas ao PL / SQL discutidas acima para implementar o SQL dinâmico. No entanto, com a introdução do SQL dinâmico nativo na PL / SQL, muitas das desvantagens de usar PL / SQL para SQL dinâmico foram eliminadas.

Se você tiver um aplicativo que usa OCI, Pro * C / C ++ ou Pro * COBOL para execução de SQL dinâmica, as viagens de ida e volta da rede necessárias para executar operações de SQL dinâmico podem prejudicar o desempenho. Como esses aplicativos geralmente residem em clientes, mais chamadas de rede são necessárias para concluir as operações SQL dinâmicas. Se você tiver esse tipo de aplicativo, considere mover a funcionalidade SQL dinâmica para procedimentos armazenados e funções armazenadas em PL / SQL que usam SQL dinâmica nativa. Isso pode melhorar o desempenho de seu aplicativo porque os procedimentos armazenados podem residir no servidor, eliminando assim a sobrecarga da rede. Você pode então chamar os procedimentos armazenados PL / SQL e funções armazenadas do aplicativo.