8 Coding Dynamic SQL Statements (Português)

Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at run time. Você pode criar aplicações mais gerais e flexíveis usando SQL dinâmico porque o texto completo de uma declaração SQL pode ser desconhecido na compilação. Por exemplo, o SQL dinâmico permite criar um procedimento que opera em uma tabela cujo nome não é conhecido até o tempo de execução.,

Oracle inclui duas maneiras de implementar SQL dinâmico em uma aplicação PL/SQL:

• SQL dinâmico nativo, onde você coloca declarações SQL dinâmico diretamente em blocos PL/SQL.procedimentos de chamada no Pacote”1bd158c30b”>

Este capítulo abrange os seguintes tópicos:

• “What Is Dynamic SQL?”
• ” Why Use Dynamic SQL?,”
• “Um SQL Dinâmico Cenário Usando Nativo SQL Dinâmico”
• “Escolher Entre o Nativo SQL Dinâmica e o DBMS_SQL Pacote”
• “Usando o SQL Dinâmico em Outros Idiomas Que não o de PL/SQL”
• “Usando o PL/SQL Registros no SQL Instruções INSERT e UPDATE”

Você pode encontrar mais informações sobre o DBMS_SQL pacote no Oracle9i Fornecido PL/SQL Pacotes e Tipos de Referência.o que é SQL dinâmico?

SQL dinâmico permite-lhe escrever programas que referenciem declarações SQL cujo texto completo não é conhecido até ao tempo de execução., Antes de discutir o SQL dinâmico em detalhe, uma definição clara do SQL estático pode fornecer um bom ponto de partida para a compreensão do SQL dinâmico. As declarações SQL estáticas não mudam de execução para execução. O texto completo das declarações SQL estáticas é conhecido na compilação, que fornece os seguintes benefícios:

• compilação bem sucedida verifica que as declarações SQL referenciam objetos válidos de banco de dados.
• compilação bem sucedida verifica se existem os privilégios necessários para aceder aos objectos da base de dados.,
• o desempenho de SQL estático é geralmente melhor do que sql dinâmico.

devido a estas vantagens, deve usar SQL dinâmico apenas se não puder usar SQL estático para atingir os seus objectivos, ou se a utilização de SQL estático for complicada 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. Você pode nem sempre saber o texto completo das declarações SQL que devem ser executadas em um procedimento PL/SQL., Seu programa pode aceitar a entrada do usuário que define as declarações SQL a executar, ou seu programa pode precisar de completar algum trabalho de processamento para determinar o curso correto de ação. Em tais casos, você deve usar SQL dinâmico.

Por exemplo, uma aplicação de relatórios em um ambiente de armazenamento de dados pode não saber o nome exato da tabela até o tempo de execução., Estas mesas podem ser nomeados de acordo com o mês de início e ano de bairro, por exemplo, INV_01_1997 INV_04_1997 INV_07_1997 INV_10_1997 INV_01_1998, e assim por diante. Você pode usar SQL dinâmico em sua aplicação de relatórios para especificar o nome da tabela em tempo de execução.

Você também pode querer executar uma consulta complexa com uma ordem de ordenação selecionável pelo Usuário., Em vez de codificar a consulta duas vezes, com diferentes cláusulas ORDER BY, você pode construir a consulta dinamicamente para incluir uma cláusula ORDER BY.os programas SQL dinâmicos podem lidar com mudanças nas definições de dados, sem a necessidade de recompilar. Isto torna o SQL dinâmico muito mais flexível do que o SQL estático. O SQL dinâmico permite-lhe escrever um código reutilizável porque o SQL pode ser facilmente adaptado para diferentes ambientes..,

SQL dinâmico também lhe permite executar as declarações da linguagem de definição de dados (DDL) e outras declarações SQL que não são suportadas em programas SQL puramente estáticos.

por que utilizar SQL dinâmico?

você deve usar SQL dinâmico nos casos em que SQL estático não suporta a operação que você deseja realizar, ou nos casos em que você não sabe as declarações SQL exatas que devem ser executadas por um procedimento PL/SQL. Estas declarações SQL podem depender da entrada do usuário, ou eles podem depender do trabalho de Processamento feito pelo programa.,DL and SCL Statements in PL/SQL

In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:

• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
• Session control language (SCL) statements, such as ALTER SESSION and SET ROLE
  
  See Also:

  Oracle9i SQL Reference for information about DDL and SCL statements.,

também, você só pode usar a cláusulaTABLEna cláusulaSELECT declaração através da dinâmica SQL. Por exemplo, o seguinte bloco PL/SQL contém uma cláusula SELECT que usa a declaração TABLE e a cláusula SQL dinâmica nativa:

executando consultas dinâmicas

pode usar a SQL dinâmica para criar aplicações que executam consultas dinâmicas, cujo texto completo não é conhecido até à execução., Muitos tipos de aplicações precisam para usar consultas dinâmicas, incluindo:

• Aplicativos que permitem aos usuários inserir ou escolha de consulta de pesquisa ou critérios de ordenação em tempo de execução
• Aplicativos que permitem aos usuários inserir ou escolha dicas de otimização em tempo de execução
• Aplicações que consulta um banco de dados onde os dados de definições de tabelas que estão em constante mudança
• Aplicações que consulta um banco de dados onde as novas tabelas são criadas, muitas vezes,

Por exemplo, consulte “Consultando Usando SQL Dinâmico: Exemplo”, e veja os exemplos de consulta em “Um SQL Dinâmico Cenário Usando Nativo SQL Dinâmico”.,

Referenciando objetos de banco de dados que não existem na compilação

muitos tipos de aplicações devem interagir com os dados que são gerados periodicamente. Por exemplo, você pode conhecer as definições de tabelas no momento da compilação, mas não os nomes das tabelas.

SQL dinâmico pode resolver este problema, porque lhe permite esperar até o tempo de execução para especificar os nomes das tabelas. Por exemplo, na aplicação sample data warehouse discutida em “What Is Dynamic SQL?”, novas tabelas são geradas a cada trimestre, e essas tabelas sempre têm a mesma definição., Você pode permitir que um usuário especifique o nome da tabela em tempo de execução com uma dinâmica de consulta SQL semelhante à seguinte:

de Otimização de Execução Dinamicamente

Você pode usar o SQL dinâmico para criar uma instrução SQL de uma forma que otimiza a execução concatenando as dicas em uma instrução SQL dinamicamente. Isto permite-lhe alterar as dicas com base nas estatísticas actuais da sua base de dados, sem necessitar de recompilação.,

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

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

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'ou qualquer outro documento de opção de dica.

executando blocos dinâmicos PL/SQL

você pode usar oEXECUTEIMMEDIATE declaração para executar blocos anônimos PL / SQL. Você pode adicionar flexibilidade construindo o conteúdo do bloco em tempo de execução.

por exemplo, suponha que através de você quer escrever uma aplicação que leva um número de Evento e envia para um manipulador para o evento. O nome do manipulador é a forma EVENT_HANDLER_event_num, onde event_num é o número do evento., Uma abordagem é implementar o dispatcher como uma declaração de comutação, onde o código lida com cada evento, fazendo uma chamada estática para o seu handler apropriado. Este código não é muito extensível porque o código do expedidor deve ser atualizado sempre que um manipulador para um novo evento é adicionado.,

usando SQL dinâmico nativo, você poderá escrever um expedidor de eventos mais pequeno e mais flexível, semelhante ao seguinte:

executando operações dinâmicas usando Invoker-Rights

Usando a funcionalidade invoker-rights com dynamic SQL, você poderá criar aplicações que emitem demonstrações SQL dinâmicas sob os privilégios e esquemas do invoker. Estas duas funcionalidades, invoker-rights e dynamic SQL, permitem construir subcomponentes reutilizáveis de aplicação que podem operar e acessar os dados e módulos do invoker.,

Um SQL Dinâmico Cenário Usando Nativo SQL Dinâmico

Este cenário mostra como realizar as seguintes operações utilizando SQL dinâmico nativo:

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

O banco de dados neste cenário é uma empresa de recursos humanos do banco de dados (chamado de hr) com o seguinte modelo de dados:

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

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

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

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

As seguintes secções descrevem várias operações SQL dinâmicas nativas que podem ser realizadas nos dados na base de dadoshr.

operação DML de amostra usando SQL dinâmico nativo

o seguinte procedimento SQL dinâmico nativo dá um aumento para todos os funcionários com um título de trabalho específico:

operação DDL de amostra usando SQL dinâmico nativo

oEXECUTE IMMEDIATE declaração pode realizar operações DDL., Por exemplo, o procedimento a seguir adiciona um escritório local:

O seguinte procedimento exclui a localização do escritório:

Exemplo de Linha Única Consulta Usando Nativo SQL Dinâmico

EXECUTE IMMEDIATE instrução pode realizar a fileira de consultas. Poderá indicar as variáveis bind na cláusula USING e obter a linha resultante para o alvo especificado na cláusula INTO da declaração.,

a função A seguir recupera o número de empregados em um determinado local realizando um trabalho especificado:

Exemplo de Várias linhas de consultas Usando SQL Dinâmico Nativo

OPEN-FOR FETCH e CLOSE as expressões podem executar dinâmica de linhas múltiplas consultas., Por exemplo, o seguinte procedimento lista todos os funcionários com um trabalho em particular em um local especificado:

escolhendo entre sql dinâmico nativo e o pacote DBMS_SQL

Oracle fornece dois métodos para usar SQL dinâmico dentro PL/SQL: SQL dinâmico nativo e o id

pacote. O SQL dinâmico nativo permite-lhe colocar as declarações SQL dinâmicas directamente no código PL/SQL. Estas declarações dinâmicas incluem declarações DML( incluindo consultas), PL/SQL blocos anônimos, declarações DDL, declarações de controle de transação e declarações de controle de sessão.,

To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.

TheDBMS_SQL package is a PL / SQL library that offers an API to execute SQL statements dynamically. O pacote DBMS_SQL tem procedimentos para abrir um cursor, processar um cursor, supply binds, e assim por diante. Programas que usam o pacote DBMS_SQL fazem chamadas para este pacote para realizar operações SQL dinâmicas.as secções seguintes fornecem informações detalhadas sobre as vantagens de ambos os métodos.,

Vantagens do Nativo SQL Dinâmico

SQL dinâmico Nativo oferece as seguintes vantagens sobre o DBMS_SQL embalagem:

Nativo SQL Dinâmico é Fácil de Usar

Devido nativo SQL dinâmico é integrado com o SQL, você pode usá-lo da mesma forma que você usa o SQL estático dentro do código de PL/SQL. Native dynamic SQL code is typically more compact and readable than equivalent code that uses the DBMS_SQL package.,

com o pacote DBMS_SQL você deve chamar muitos procedimentos e funções em uma sequência estrita, fazendo operações simples requererem um monte de código. Você pode evitar esta complexidade usando SQL dinâmico nativo em vez disso.

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.,

Table 8-1 Code Comparison of DBMS_SQL Package and Native Dynamic SQL

Native Dynamic SQL is Faster than DBMS_SQL

Native dynamic SQL in PL/SQL performs comparably to the performance of static SQL, because the PL / SQL interpreter has built-in support for it. Os programas que usam SQL dinâmico nativo são muito mais rápidos do que os programas que usam o pacote DBMS_SQL. Tipicamente, as declarações SQL dinâmicas nativas executam 1.5 a 3 vezes melhor do que equivalente DBMS_SQL chamadas. (Seus ganhos de desempenho podem variar dependendo de sua aplicação.,)

Native dynamic SQL agrupa os passos de preparação da declaração, ligação e execução em uma única operação, o que minimiza a cópia de dados e procedimento chamada overhead e melhora o desempenho.

The DBMS_SQL package is based on a procedural API and incurss high procedure call and data copy overhead. Cada vez que você liga uma variável, o pacote DBMS_SQL copia a variável PL/SQL bind no seu espaço para ser usado durante a execução., Cada vez que você executa uma busca, os dados são copiados para o espaço gerenciado pelo ID

pacote e então os dados obtidos são copiados, uma coluna de cada vez, para as variáveis PL/SQL apropriadas, resultando em sobrecarga substancial.

dica de desempenho: usando variáveis Bind

ao usar SQL dinâmico nativo ou o id

pacote, você pode melhorar o desempenho usando variáveis bind, porque as variáveis bind permitem que a Oracle partilhe um único cursor para múltiplas declarações SQL.,

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

para cada distinto my_deptno variável, um novo cursor é criado, causando contenção de recursos e fraco desempenho. Em vez disso, bind my_deptno como uma variável bind:

aqui, o mesmo cursor é reutilizado para diferentes valores do bind my_deptno, melhorando o desempenho e a escalabilidade.,

a SQL dinâmica nativa suporta tipos definidos pelo Utilizador

a SQL dinâmica nativa suporta todos os tipos suportados pela SQL estática em PL/SQL, incluindo tipos definidos pelo utilizador como objectos definidos pelo utilizador, colecções e REFs. O pacote DBMS_SQL não suporta estes tipos definidos pelo utilizador.

o SQL dinâmico nativo suporta obter em registros

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

No exemplo a seguir, as linhas de uma consulta são obtidas em emp_rec registo:

Vantagens do DBMS_SQL Pacote

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

DBMS_SQL é Suportado em Programas do Lado do Cliente

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

DBMS_SQL Suporta DESCREVER

DESCRIBE_COLUMNS procedimento de DBMS_SQL pacote pode ser usado para descrever as colunas para um cursor aberto e analisado através de DBMS_SQL. Esta característica é semelhante ao comando DESCRIBE em SQL*Plus., Native dynamic SQL does not have a DESCRIBE facility.

DBMS_SQL suporta múltiplas actualizações de linhas e apaga com uma cláusula de retorno

a DBMS_SQL pacote suporta declarações com uma RETURNING cláusula que actualiza ou apaga várias linhas. Native dynamic SQL suporta apenas uma cláusula RETURNING se uma única linha for devolvida.,

DBMS_SQL Supports SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

DBMS_SQL Lets You Reuse SQL Statements

The PARSE procedure in the DBMS_SQL package parses a SQL statement once., Após a análise inicial, você pode usar a declaração várias vezes com diferentes conjuntos de argumentos bind.

Native dynamic SQL prepara uma declaração SQL cada vez que a declaração é usada, o que normalmente envolve processamento, otimização e geração de plano. Embora as operações extra prepare incorrer em uma pequena penalidade de desempenho, a desaceleração é normalmente compensada pelos benefícios de desempenho do SQL dinâmico nativo.,

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

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

• a query
• a DML operation
• a DML returning operation

In general, the native dynamic SQL code is more readable and compact, which can improve developer productivity.,

Consulta Utilizando SQL Dinâmico: Exemplo

O exemplo a seguir inclui uma instrução de consulta dinâmica com um ligar variável (:jobname) e dois selecionar colunas (ename e sal):

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

Este exemplo de consulta para os funcionários com o trabalho de descrição SALESMAN job coluna emp tabela. A tabela 8-2 mostra o código de exemplo que realiza esta consulta usando o DBMS_SQL pacote e SQL dinâmico nativo.,

Tabela 8-2 Consultar Usando o Package DBMS_SQL e Nativo SQL Dinâmico

Executando instruções DML com o Dynamic SQL: Exemplo

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

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

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

Tabela 8-3 Operação DML Usando o Package DBMS_SQL e Nativo SQL Dinâmico

Executando instruções DML com RETORNANDO Cláusula Usando SQL Dinâmico: Exemplo

O exemplo a seguir usa uma dinâmica UPDATE instrução para atualizar a localização de um departamento, em seguida, retorna o nome do departamento:

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

Tabela 8-4 mostra o exemplo de código que realiza esta operação usando o DBMS_SQL pacote e nativo SQL dinâmico.,

Tabela 8-4 DML Retornando a Operação, Utilizando o Package DBMS_SQL e Nativo SQL Dinâmico

Usando SQL Dinâmico em Outros Idiomas Que não o de PL/SQL

Embora este capítulo discute PL/SQL suporte para SQL dinâmico, você pode chamar de SQL dinâmica de outras línguas:

• Se você usar C/C++, você pode chamar de SQL dinâmica com o Oracle Call Interface (OCI), ou você pode usar o Pro*C/C++ pré-compilador para adicionar SQL dinâmica de extensões para C de código.
• Se usar o COBOL, poderá usar o pré -ompilador Pro*COBOL para adicionar extensões SQL dinâmicas ao seu código COBOL.,
• Se você usar Java, você pode desenvolver aplicações que usam SQL dinâmico com JDBC.

Se tiver uma aplicação que use OCI, Pro * C / C++, ou Pro*COBOL para executar SQL dinâmico, deverá considerar mudar para SQL dinâmico nativo dentro dos procedimentos e funções armazenados em PL/SQL. As rondas de rede necessárias para realizar operações SQL dinâmicas a partir de aplicações do lado do cliente podem prejudicar o desempenho. Procedimentos armazenados podem residir no servidor, eliminando a sobrecarga da rede., Você pode chamar o PL/SQL procedimentos armazenados e funções armazenadas a partir da aplicação OCI, Pro*C/C++, ou Pro*COBOL.,br>Veja Também:

Para obter informações sobre como chamar procedimentos armazenado Oracle armazenados e funções de vários idiomas, consulte:

• Oracle Call Interface do Programador do Guia
• Pro*C/C++ pré-compilador Guia do Programador
• Pro*pré-compilador COBOL Programmer ‘ s Guide
• Oracle9i Java Procedimentos Armazenados Guia do Desenvolvedor

Usando o PL/SQL Registros no SQL Instruções INSERT e UPDATE

Embora você pode enumerar cada campo de uma PL/SQL registro quando inserir ou atualizar registros em uma tabela, o código resultante não é muito legível ou de fácil manutenção., Em vez disso, você pode usar registros PL/SQL diretamente nestas declarações. A técnica mais conveniente é declarar o registro usando um atributo % ROWTYPE, de modo que ele tem exatamente os mesmos campos que a tabela SQL.

embora esta técnica ajude a integrar as variáveis e tipos PL/SQL mais estreitamente com as declarações DML SQL, você não pode usar os registos PL/SQL como variáveis de ligação nas declarações SQL dinâmicas.