Tópico 2 – Restricting and Sorting Data

  • Subtópico 2.1 – Limit the rows that are retrieved by a query (Limitar as linhas retornadas por uma query)
  • Subtópico 2.2 – Sort the rows that are retrieved by a query (Ordenar as linhas retornadas por uma query)
  • Subtópico 2.3 – Use ampersand substitution to restrict and sort output at runtime (Usar o & para restringir e ordenar a saida em tempo de execução)

Neste post, falarei um pouco sobre o segundo tópico oficial da prova de SQL Básico (1Z0-051). Um pouco já foi nos tópicos anteriores sobre algumas funcionalidades abordadas neste tópico, como ordenação das linhas, e limitação de linhas retornadas. Farei uma recapitulação destes itens e também falarei sobre o caractere “&”, que serve como substituição de variáveis em nível de execução.

Ainda usaremos as tabelas CERTIFICACAO.TAB_EMPREGADOS e CERTIFICACAO.TAB_DEPARTAMENTOS como forma de exemplificar os conceitos aqui abordados.

Subtópico 2.1 – Limitar as linhas retornadas por uma query

Basicamente, para limitar as linhas retornadas por uma query, nós precisamos filtrar as linhas que são realmente necessárias para o nosso objetivo, seja a geração de relatórios e etc. Para isso, usamos o operador WHERE. Exemplos:

 --abaixo, se esta exibindo apenas as linhas que possuem o valor da
 --coluna ID_EMP igual a 1000

 SELECT ID_EMP AS ID_EMPREGADO,
 SALARIO "Salário (R$)",
 DATA_ADMISSAO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 WHERE ID_EMP > 1000

 --abaixo, eu busco os empregados que possuam salario entre 
 --R$1000,00 e R$2000,00. lembrando que o BETWEEN é inclusivo

 SELECT * 
 FROM CERTIFICACAO.TAB_EMPREGADOS 
 WHERE SALARIO BETWEEN 1000 AND 2000
 ORDER BY ID_EMP

Uma outra forma de limitar as linhas retornadas é usando a cláusula ROWNUM. Esta cláusula faz com que minha query retorne no máximo X linhas. Ela poderia ser útil, por exemplo, para exibir apenas uma amostra da tabela (que também pode ser feito com o SAMPLE). Exemplo:

 --se eu desejar exibir apenas 10 linhas da minha tabela de 
 --empregados, posso usar o rownum da seguinte forma

 SELECT * 
 FROM CERTIFICACAO.TAB_EMPREGADOS
 WHERE ROWNUM <= 10

É importante destacar o seguinte caso: Se eu precisar exibir os 10 maiores salários da tabela de empregados, eu inicialmente poderia fazer da seguinte forma:

 --vamos chamar essa query de QU01

 SELECT * 
 FROM CERTIFICACAO.TAB_EMPREGADOS
 WHERE rownum <= 10
 ORDER BY SALARIO DESC

Quando se insere uma linha em determinada tabela, o Oracle atribui um ROWNUM a essa linha. Cada linha possui um ROWNUM único que é atribuído a cada nova linha inserida em determinada tabela. Caso eu execute a seguinte query:

 SELECT ROWNUM, 
 ID_EMP
 FROM CERTIFICACAO.TAB_EMPREGADOS

… ela vai me retornar a ordem que os empregados foram salvos na tabela.

Agora, voltando a query QU01. Ela executará da seguinte forma: Serão buscadas as 10 primeiras linhas, por ordem de inserção, da tabela CERTIFICACAO.TAB_EMPREGADOS, e depois esse subgrupo de 10 linhas será ordenado. Na verdade, não é isso que quero, mas sim os 10 maiores salários entre todos os empregados. Para atingir esse objetivo, eu devo alterar a minha query, para que fique da seguinte forma:

 SELECT * FROM (
 SELECT *
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY SALARIO DESC
 )
 WHERE ROWNUM <= 10

O que eu estou fazendo acima: Primeiro eu busco todos os empregados e ordeno de forma decrescente pelo salário, para depois filtrar os 10 registros, que já estarão ordenados do jeito que eu preciso. A query mais interna, neste caso, é chamada de Inline View. Ela funciona como uma VIEW temporária. Falaremos sobre Views em uma outra oportunidade.

Subtópico 2.2 – Ordenar as linhas retornadas por uma query

Para se ordenar as linhas retornadas por uma query, usa-se o ORDER BY (que vocês já devem estar bem familiarizados). Tomando como exemplos, queries usadas no post anterior:

 --ordenando o resultado pelo id de cada empregado, ordenando 
 --de forma ascendente.
 --a palavra ASC pode ser omitida, já que ordenar de forma 
 --ascendente é o default

 SELECT * 
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY ID_EMP ASC;

 --ordenando o resultado pelo id de cada empregado, ordenando de 
 --forma descendente

 SELECT * 
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY ID_EMP DESC;

 --ao inves de usar o nome das colunas, e possivel usar a posicao 
 --das colunas na lista de colunas do select
 --abaixo eu estou ordenando pelo nome do empregado (2º coluna da 
 --lista de colunas do select)

 SELECT ID_EMP,
 NOME_EMP,
 IDADE
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY 2 DESC;

É importante mencionar que quando se usa a ordenação por nome de coluna, eu posso colocar nomes de colunas que não estão dentro da lista de colunas de meu SELECT (mas que obviamente existem na tabela). No exemplo abaixo, eu estou ordenando pelo salário, mas reparem que não busco a coluna SALARIO:

 SELECT ID_EMP, 
 NOME_EMP,
 IDADE
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY SALARIO DESC

Subtópico 2.3 – Usar o & para restringir e ordenar a saída em tempo de execução

No SQL*Plus, podemos usar o “e comercial”, ou “&” para trabalhar com variáveis e dinamicamente fornecer valores para definir filtros e colunas a serem buscadas. Abaixo é possível ver um exemplo bem simples. Para executa-lo, basta acessar o SQL*Plus, digitando no terminal o comando “sqlplus“, ou caso o ORACLE_HOME não esteja definido na variável PATH, digitar “ORACLE_HOME/bin/sqlplus”, onde ORACLE_HOME é o diretório de instalação do Oracle:

 SQL> SELECT ID_EMP, NOME_EMP, SALARIO, FROM CERTIFICACAO.TAB_EMPREGADOS WHERE ID_EMP = &ID;

…quando eu pressionar enter, o SQL*Plus irá pedir para que eu forneça o valor para a variável “ID”, através da seguinte mensagem:

Enter value for id: 200000
 old 1: SELECT ID_EMP, NOME_EMP, SALARIO, 
        FROM CERTIFICACAO.TAB_EMPREGADOS WHERE ID_EMP = &ID;
 new 1: SELECT ID_EMP, NOME_EMP, SALARIO, 
        FROM CERTIFICACAO.TAB_EMPREGADOS WHERE ID_EMP = 200000;

Eu também posso fornecer dinamicamente as colunas que eu desejo buscar, inserindo essas “variáveis” na lista de colunas do SELECT:

 SQL> SELECT ID_EMP, &coluna2, &coluna3, FROM CERTIFICACAO.TAB_EMPREGADOS WHERE ID_EMP = &ID;

 Enter value for coluna2: nome_emp
 Enter value for coluna3: idade
 Enter value for id: 210000
 old 1: SELECT ID_EMP, &coluna2, &coluna3, 
        FROM CERTIFICACAO.TAB_EMPREGADOS WHERE ID_EMP = &ID;
 new 1: SELECT ID_EMP, NOME_EMP, IDADE, 
        FROM CERTIFICACAO.TAB_EMPREGADOS WHERE ID_EMP = 210000;

Também é possível definir em tempo de execução, o nome da tabela a ser buscada:

 SQL> SELECT * FROM &tabela;
 Enter value for tabela: CERTIFICACAO.TAB_EMPREGADOS
 old 1: SELECT * FROM &tabela;
 new 1: SELECT * FROM CERTIFICACAO.TAB_EMPREGADOS;

Basicamente é possível usar essas variáveis temporárias em todas as partes de uma query. Inclusive da ordenação:

 SQL> SELECT ID_EMP, NOME_EMP 
      FROM CERTIFICACAO.TAB_EMPREGADOS 
      ORDER BY &coluna_ordenar &tipo_ordenacao;
 Enter value for &coluna_ordenar: SALARIO
 Enter value for &tipo_ordenacao: DESC
 old 1: SELECT ID_EMP, NOME_EMP 
        FROM CERTIFICACAO.TAB_EMPREGADOS 
        ORDER BY &coluna_ordenar &tipo_ordenacao;
 new 1: SELECT ID_EMP, NOME_EMP 
        FROM CERTIFICACAO.TAB_EMPREGADOS ORDER BY SALARIO DESC;

Só devemos ter cuidado, no momento de inserir um varchar, e este contenha o caractere “&”. Caso eu não use um escape, esse caractere será interpretado pelo SQL*Plus como uma variavel a ser definida. Ex:

 SQL> INSERT INTO CERTIFICACAO.TAB_EMPREGADOS 
      VALUES (310000, 'Teste & Teste', 50, SYSDATE, 6,1);
 Enter value for teste: ops
 old 1: INSERT INTO CERTIFICACAO.TAB_EMPREGADOS 
        VALUES (310000, 'Teste & Teste', 50, SYSDATE, 6,1);
 new 1: INSERT INTO CERTIFICACAO.TAB_EMPREGADOS 
        VALUES (310000, 'Teste ops', 50, SYSDATE, 6,1);

Para evitarmos isso, usamos um escape, que eu prefiro chamar de “Gambiarra da Oracle”:

 SQL> INSERT INTO CERTIFICACAO.TAB_EMPREGADOS 
      VALUES (310000, 'Teste &' || ' Teste', 50, SYSDATE, 6,1);

 1 row created.

… ou podemos desativar o prompt de variáveis no SQL*Plus, assim, o caractere “&” antes de qualquer palavra será interpretado como uma string. Ex:

 SQL> SET DEFINE OFF
 SQL> INSERT INTO CERTIFICACAO.TAB_EMPREGADOS 
      VALUES (310000, 'Teste & Teste', 50, SYSDATE, 6,1);

 1 row created.

Também é possível definir uma variável no SQL*Plus, mas para isso, é necessário reverter o passo anterior:

 SQL> SET DEFINE ON
 SQL> DEFINE tabela = CERTIFICACAO.TAB_EMPREGADOS
 SQL> SELECT * FROM &tabela;
 old 1: SELECT * FROM &tabela;
 new 1: SELECT * FROM CERTIFICACAO.TAB_EMPREGADOS;

Por último, mas não menos importante, é possível ativar o caractere de escape, ex:

 SQL> SET ESCAPE ON
 SQL> INSERT INTO CERTIFICACAO.TAB_EMPREGADOS VALUES (310000, 'Teste & Teste', 50, SYSDATE, 6,1);

 1 row created.

É isso ai pessoal. Até o próximo post.