Variáveis de Substituição

As Variáveis de Substituição permitem que a consulta se torne bastante dinâmica, para que o Oracle reconheça a sua utilização, basta utilizar o E comercial único (&) ou duplo (&&) junto a uma variável (sem espaço), que será então substituída pelo valor de entrada solicitado ao executar a consulta.

Exemplo na imagem abaixo:

Como é possível ver na imagem, a consulta faz a projeção pelo NOME, DEPARTAMENTO_ID e DESCRICAO, para o funcionário cujo o CPF será definido na execução da consulta. Ao executar-la, o servidor verifica se a variável foi definida na seção (comando DEFINE), caso o valor para essa variável não tenha sido definido o servidor solicita que seja inserido o valor pelo usuário.

O resultado da consulta:

Se a variável em questão se tratar de um valor de data ou caractere, é necessário delimitar o valor de entrada com aspas simples, ou ocorrerá erro. Como no exemplo abaixo:

A maneira correta seria informar o valor delimitado com aspas, ou delimitar a variável com aspas simples diretamente na consulta, ex: AND F.NOME = '&NOME'; 

Abaixo um exemplo usando as aspas ao passar o valor:

O exemplo acima, fez uso do apenas do E comercial simples (&), significa que a cada execução da consulta, será solicitado ao usuário ou aplicação que informe qual o número do CPF que ele deve buscar na consulta.

Caso não seja essa a intenção, é possível usar o E comercial duplo (&&) que solicitará apenas uma vez o valor da variável durante a sessão. Como na imagem abaixo:

Caso deseje-se "limpar" o valor da variável, você pode utilizar o comando UNDEFINE nome_da_variável. No caso acima, poderíamos limpar a variável DEPARTAMENTO_NUM:

UNDEFINE DEPARTAMENTO_NUM;

As variáveis de substituição podem ser usadas para complementar:

• Condições WHERE;
• Cláusulas ORDER BY;
• Expressões de Coluna;
• Nomes de Tabelas;
• Instruções SELECTS inteiras.

IMPORTANTE: Uma variável de substituição pode ser usada em qualquer local na instrução SELECT, exceto como a primeira palavra especificada no prompt de comando.

Os primeiros exemplos do post são referentes ao uso para complementar as condições WHERE. É possível usar as variáveis na cláusula ORDER BY, deixando o relatório dinâmico quanto a ordenação, ou seja, ao executar o usuário poderá especificar por qual coluna desejará ordenar:

ORDER BY &coluna_ou_posição_numérica;

É possível também utilizar as variáveis de substituição para definir que coluna seja exibida de acordo com a necessidade do usuário, por exemplo:

SELECT F.NOME, D.DEPARTAMENTO_ID, D.DESCRICAO, &COLUNA_DINAMICA

FROM   FUNCIONARIOS F, DEPARTAMENTOS D

WHERE  F.DEPARTAMENTO_ID = D.DEPARTAMENTO_ID;

Será solicitado ao usuário qual a coluna que deverá ser exibida por último, podendo o usuário escolher quaisquer coluna que existam nas tabelas mencionadas na cláusula FROM.

Por último segue um exemplo sobre como utilizar variáreis de substituição para construir uma cláusula SELECT inteira:

SELECT &COLUNAS

FROM   &TABELAS

WHERE  &CONDICOES;

Será que funciona? Vamos ver! Abaixo seguem as imagens dos valores inseridos em casa variável:

Observações interessantes sobre a utilização das variáveis cabem aqui, perceba que ao definir as colunas eu usei o alias que eu defini depois ao listar as tabelas, outra observação é que mais de uma coluna foi listada na variável &COLUNAS, assim como na variável &TABELAS, e na condição WHERE a variável foi substituída por uma expressão completa, referente ao relacionamento entre essas tabelas D.DEPARTAMENTO_ID = F.DEPARTAMENTO_ID. Na imagem a seguir, temos o resultado da consulta:

Por hoje é só, espero que o conteúdo possa ajuda-lo. =)