Estruturas condicionais, sequenciais e comentários no Oracle

As estruturas de controle no Oracle são muitas. Aqui no portal já vimos algumas das mais utilizadas, como o Loop básico, no qual vimos a sua sintaxe básica e as formas de uso mais comuns.

Neste artigo veremos as estruturas do For Loop e o While Loop.

Trabalhando com Loops FOR

Inicialmente veremos a declaração do Loop For, a qual executa uma ou mais declarações dentro de um intervalo de índices numéricos especificados. A sintaxe básica para este tipo é apresentada na Listagem 1.

Listagem 1. Sintaxe da estrutura LOOP FOR.

[ label ] FOR index IN [ REVERSE ] menor_indice..indice_maximo LOOP Declarações END LOOP [ label ];

Podemos encontrar na sintaxe apresentada os seguintes elementos:

• Index: é o nome dado a variável declarada implicitamente que é utilizada localmente na declaração do laço FOR;
• Menor_indice (limite inferior) e maior_indice (limite superior): estes são valores inteiros que são avaliados dentro de um laço. Os valores que são postos estarão dentro desse limite e são temporários.
• Label: Um label identifica uma declaração dentro do laço FOR, como é o caso de instruções CONTINUE, EXIT que podem referenciar a label. A utilização destes normalmente melhora a legibilidade do código, principalmente quando as instruções do laço são instruções aninhadas.

Sem a utilização do REVERSE, o valor do índice começa com um menor_indice e vai crescendo a cada iteração do loop até chegar ao nível máximo, que é o maior_indice da estrutura. Em caso do menor_indice ser maior que o maior_indice, teremos que as declarações não serão executadas. Quando usamos a instrução REVERSE, teremos o início do fluxo sendo realizado de traz para a frente, ou seja, a cada iteração realizada será reduzido o valor do índice. No caso de termos o maior_indice menor que o menor_indice, as declarações não poderão ser executadas.

Lembre-se que juntamente com o Loop FOR podemos utilizar as instruções EXIT, CONTINUE e CONTINUE WHEN.

Vejamos mais alguns exemplos práticos na Listagem 2.

Listagem 2. Utilizando a estrutura de Loop FOR.

BEGIN DBMS_OUTPUT.PUT_LINE ('Trabalhando com o menor_indice de forma crescente'); FOR i IN 1..15 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; DBMS_OUTPUT.PUT_LINE ('Parando o processo quando o menor_indice for igual ao maior_indice'); FOR i IN 9..9 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; DBMS_OUTPUT.PUT_LINE ('Trabalhando em ordem decrescente, partindo do maior_indice até chegar ao menor_indice'); FOR i IN 25..5 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; END; /

Veja que temos uma demonstração simples de como podemos obter resultados com a estrutura de repetição FOR. Vejamos um exemplo dessa vez utilizando a instrução REVERSE, de acordo com a Listagem 3.

Listagem 3. Usando a instrução REVERSE no loop FOR.

BEGIN DBMS_OUTPUT.PUT_LINE ('Trabalhando com o menor_indice de forma crescente'); FOR i IN REVERSE 1..15 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; DBMS_OUTPUT.PUT_LINE ('Parando o processo quando o menor_indice for igual ao maior_indice'); FOR i IN REVERSE 9..9 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; DBMS_OUTPUT.PUT_LINE ('Trabalhando em ordem decrescente, partindo do maior_indice até chegar ao menor_indice'); FOR i IN REVERSE 25..5 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; END; /

O índice de uma declaração para o laço é implicitamente declarado como uma variável do tipo INTEGER, que é local para o loop. As instruções no loop podem ler o valor do índice, mas não pode alterá-lo. Declarações fora do laço não podem fazer referência ao índice. Muitas vezes consideramos que um índice de loop é na realidade um contador.

Trabalhando com Loops While

Além da utilização do laço FOR, podemos também trabalhar com os laços do tipo WHILE, que no caso é utilizado quando não sabemos quantas vezes teremos que executar o corpo do nosso laço. Vejamos como fica a sintaxe básica do WHILE de acordo com a Listagem 4.

Listagem 4. Sintaxe básica do Loop While.

WHILE condition LOOP {...statements...} END LOOP;

Veja que temos a nossa palavra-chave seguida da palavra “condition”, que no caso é a condição que será testada a cada iteração do loop. Caso essa condição seja avaliada como TRUE, o corpo do loop será executado; caso está condição seja FALSE, o loop será então encerrado.

O “statement” são as declarações do código que estaremos executando a cada passagem do loop. Como podemos perceber, a condição é realizada antes de entrarmos no loop, o que, em caso de ser FALSE, o código pode não ser executado nenhuma vez. Vejamos um exemplo simples de sua aplicação na Listagem 5.

Listagem 5. Contagem dos meses do ano.

WHILE mesTeste <= 24 LOOP mesTeste := dia * 31; END LOOP;

Como podemos perceber nesse simples exemplo, temos que a contagem será finalizada quando passarmos do valor 24 e o loop será encerrado. Veja que a condição é testada no começo do loop, então pode ser que nem haja a execução do trecho se a condição não for satisfatória, mas é possível realizarmos este teste ao menos uma vez, utilizando o EXIT WHEN juntamente com a condição no fim da declaração, ao invés de ser no início. Vejamos como fica a sintaxe desse modelo de acordo com a Listagem 6.

Listagem 6. Teste de condição no fim do laço WHILE.

LOOP sequence_of_statements EXIT WHEN boolean_expression END LOOP;

Em caso de querermos confirmar realmente que o WHILE será executado pelo menos uma vez, podemos utilizar uma variável booleana de inicialização na condição, como podemos no exemplo da Listagem 7.

Listagem 7. Utilizando uma variável booleana.

entrouAqui := FALSE; WHILE NOT entrouAqui LOOP Sequência de declarações que passarmos. entrouAqui := expressão_booleana END LOOP;

Como declaramos fora do loop que a variável é FALSE, quando o loop estiver sendo executado e entrar no nosso bloco de códigos, deverá atribuir um valor TRUE para a nossa variável internamente.

Estruturas de controle sequenciais

Diferentemente das estruturas de controle condicional e de repetição, as estruturas de controle sequenciais não são, de certa maneira, cruciais para programarmos com o PL/SQL.

As estruturas sequenciais mais conhecidas são a GOTO e a NULL, as quais veremos no decorrer do artigo.

Declaração GOTO

Com a declaração GOTO, o controle é transferido para a label incondicionalmente, onde temos também que o label deve ser único dentro de um escopo e deve ser posto antes de uma declaração executável ou mesmo de um bloco PL/SQL. Mas para que isso seja feito da melhor forma possível, precisamos tomar cuidado com algumas restrições de sua utilização, como as seguintes:

• Uma declaração GOTO não transfere o controle para declarações IF, LOOP, CASES ou mesmo sub-blocos;
• Não é possível a transferência de controle fora de um subprograma;
• Não é possível a transferência de controle para uma exceção.

A sintaxe do GOTO é bastante simples:

GOTO Label;

Onde o label tem por finalidade identificar tanto um bloco quanto uma declaração. Caso o nosso label não esteja dentro do bloco no qual estejamos trabalhando, a declaração GOTO irá transferir o controle para o primeiro bloco que contiver na declaração da label.

Este é o tipo de declaração a ser utilizada com cuidado, pois com a má utilização dela, podemos ter problemas futuros tanto para entendimento do código quanto para a manutenção do mesmo.

Vejamos então um exemplo simples na Listagem 8 de como podemos utilizar nossos blocos com uma declaração GOTO.

Listagem 8. Exemplo de utilização da declaração GOTO.

DECLARE justificativa VARCHAR2(100); valor PLS_INTEGER := 27; BEGIN FOR j in 2..ROUND(SQRT(valor)) LOOP IF valor MOD j = 0 THEN justificativa := 'este não é um número primo'; GOTO mensagem; END IF; END LOOP; justificativa := ' Este é um número primo'; <<mensagem>> DBMS_OUTPUT.PUT_LINE(TO_CHAR(valor) || justificativa); END; /

Como podemos observar, estamos interessados em saber se o número que definimos incialmente é primo ou não. Para isso, criamos o nosso código mostrando uma mensagem utilizando o GOTO no fim do processo. Além disso, a label pode aparecer apenas antes de um bloco ou antes de uma declaração, mas não dentro de uma declaração.

Agora vejamos um outro exemplo, dessa vez com a forma incorreta de utilização do GOTO, presente na Listagem 9.

Listagem 9. Forma incorreta de utilização do GOTO.

DECLARE completo BOOLEAN; BEGIN FOR i IN 1..50 LOOP IF completo THEN GOTO finalizaProcesso; END IF; << finalizaProcesso >> END LOOP; END; /

Dessa forma teremos a visualização de uma mensagem de erro indicando que foi encontrado um “END LOOP” quando ele ainda esperava uma continuação. Isso pode acontecer com frequência e, para resolver isso adicionamos a declaração NULL após a marcação << finalizaProcesso >>, como podemos ver apresentado na Listagem 10.

Listagem 10. Utilizando a declaração NULL.

DECLARE completo BOOLEAN; BEGIN FOR i IN 1..50 LOOP IF completo THEN GOTO finalizaProcesso; END IF; << finalizaProcesso >> NULL; END LOOP; END; /

Para que uma declaração GOTO possa realizar a transferência de controle entre blocos, esta deve ser feita da mesma forma que a apresentada na Listagem 11.

Listagem 11. Transferência de controle entre blocos.

DECLARE nomeFuncionario VARCHAR2(25); codFuncionario NUMBER(6) := 120; BEGIN <<pegaNomeFuncionario>> SELECT nomeCompleto INTO nomeFuncionario FROM funcionarios WHERE funcionarioID = codFuncionario; BEGIN DBMS_OUTPUT.PUT_LINE (nomeFuncionario); codFuncionario := codFuncionario + 8; IF codFuncionario < 20 THEN GOTO pegaNomeFuncionario; END IF; END; END; /

A declaração GOTO transfere o controle para o primeiro bloco, no qual existe uma referência a label que criamos.

Quanto as exceções ao uso da declaração GOTO, não é possível a transferência de controle para declarações IF. Vejamos o erro que obteríamos ao fazer isso, de acordo com a Listagem 12.

Listagem 12. Transferência de controle entre declarações IF.

DECLARE testeOk BOOLEAN := TRUE; BEGIN GOTO atualiza; IF testeOk THEN <<atualiza>> NULL; END IF; END; /

Vemos então que o erro apresentado será com relação a utilização do GOTO, onde obtemos a informação de que esta operação é impossível.

Declarações NULL

Ao utilizarmos a declaração NULL, esta não realiza nenhum processo muito sofisticado, apenas transfere o controle para a próxima declaração. Ele pode ser utilizado em determinados casos, como para prevenção do erro que obtivemos na Listagem 9 e para prover uma maior legibilidade, com criação de placeholders, que são pontos de inserção (tag) em um modelo de página para identificar onde há uma região de contribuição (ou seja, área editável) na página Web. Nenhuma ação é necessária nesse caso.

Vejamos um exemplo simples passando a declaração NULL, como apresentado na Listagem 13.

Listagem 13. Utilizando a declaração NULL.

DECLARE codFuncao VARCHAR2(10); codFuncionario NUMBER(6) := 8; BEGIN SELECT codigo_funcao INTO codFuncao FROM Funcionarios WHERE funcionarioId = codFuncionario; IF codFuncao = 'DESENVOLVEDOR' THEN UPDATE Funcionarios SET comissaoPorProjeto = comissaoPorProjeto * 1.2; ELSE NULL; -- Quando o funcionário não for um representante de vendas END IF; END; /

Além desse exemplo, no qual atribuímos uma comissão para um funcionário em caso deste ser desenvolvedor, vejamos com base na Listagem 14, como podemos passar a declaração NULL como sendo um placeholder no decorrer da criação de um subprograma.

Listagem 14. Declaração NULL como placeholder.

CREATE OR REPLACE PROCEDURE bonusExtra ( funcionarioId NUMBER, bonus NUMBER ) AS BEGIN NULL; -- utilizando o Placeholder -- em caso de haver código inacessível, uma exceção pode ser levantada, caso esteja habilitado. END bonusExtra; /

Utilizando comentários no Oracle

Agora que vimos os tipos de estruturas de controle, possivelmente perceberam que em alguns momentos no código inserimos alguns comentários para que fossem melhor apresentados aqueles determinados trechos.

Com base nisso, nesse momento falaremos um pouco sobre os tipos de comentários que podemos fazer com o Oracle. De início, o que podemos dizer é que temos dois tipos básicos de comentários que podem ser utilizados: são os comentários em comandos SQL e comentários associados a schemas. Vejamos a definição de cada um deles a seguir, retirados da documentação da Oracle:

• Os comentários postos nos comandos SQL são armazenados como sendo parte do código do aplicativo que irá executar os comandos SQL;
• Os comentários que são associados com esquemas ou objetos nonschema individuais são armazenados no dicionário de dados junto com os metadados referentes aos próprios objetos.

Trabalhando com comentários em declarações SQL

A utilização dos comentários torna mais fácil o entendimento do código do projeto, por exemplo, o que queremos que uma trigger ou uma stored procedure faça, descrevendo a finalidade do comando. Os comentários dentro das instruções SQL não afetam a sua execução. Para utilizarmos os comentários entre palavras-chave, parâmetros ou mesmo sinais de pontuação. Dito isso, podemos definir os comentários de duas formas possíveis, como:

• Utilizando barras e asteriscos (/* */): com essa forma, podemos abranger tanto um texto com várias linhas, como também uma única frase, colocando-o entre os delimitadores;
• Utilizando hífen duplo (--): com a utilização desse formato o texto deve ser apenas de uma única linha.

Dentro de uma mesma instrução SQL podemos ter vários comentários de ambos os estilos. Neles podemos ter quaisquer caracteres imprimíveis dentro do conjunto de caracteres do banco de dados, como podemos ver o exemplo simples da Listagem 15.

Listagem 15. Utilizando comentários de linha e bloco.

SELECT NomeFuncionario, funcionario_id, salarioFunc + NVL(comissaoPorProjeto, 0), ProfissaoId, func.departamento_id /* Seleção de todos os funcionários que tenham salários maiores que os de Edson. */ FROM funcionarios func, departamentos dep /*A tabela de departamentos é utilizada para que possamos pegar os nomes dos departamentos.*/ WHERE func.departamento_id = dep.departamento_id AND salarioFunc + NVL(comissaoPorProjeto,0) > (SELECT salarioFunc + NVL(comissaoPorProjeto,0) /* O valor total do funcionário será salarioFunc + comissaoPorProjeto */ FROM funcionarios WHERE NomeFuncionario = 'Edson') ORDER BY NomeFuncionario, funcionario_id; SELECT NomeFuncionario, -- Selecionando o nome do funcionário funcionario_id -- pegando o id do funcionario salarioFunc + NVL(comissaoPorProjeto, 0), -- valor total a ser compensado -- Aqui temos mais código para a execução da query, mas apenas mostramos os comentários ORDER BY NomeFuncionario funcionario_id; -- Ordenando pelo código do funcionário

Trabalhando com comentários sobre esquema e Objetos NonSchema

Para trabalharmos com comentários neste caso, podemos usar o comando COMMENT para associar um comentário com um objeto de esquema, podendo este ser uma View, uma tabela, um indexType, operador, dentre outras opções. Além disso, podemos usar esses comentários como sendo um objeto nonschema. Aqui também podemos criar comentários sobre uma coluna, que é parte de um objeto de esquema da tabela. Os comentários que são associados com esquema e objetos nonschema são armazenados no dicionário de dados.

Neste mesmo contexto temos a possibilidade de utilizarmos os Hints (sugestões), que são comentários presentes em uma instrução SQL que passam instruções para o otimizador de banco de dados Oracle. O otimizador usa essas “dicas” para escolher um plano de execução para a instrução que será usada. No entanto, o Oracle hoje oferece uma série de ferramentas, como o SQL Tuning Advisor e o SQL Performance Analyzer, para a resolução de problemas com relação a desempenho, caso os otimizadores não consigam resolver. A Oracle recomenda fortemente a utilização dessas ferramentas ao invés de utilizarmos os hints em nossos projetos, pois devido a superioridade das ferramentas em comparação aos hints, podemos fornecer soluções mais rápidas com bases de dados contínuas.

Devido a isso, os hints devem ser utilizados de forma cautelosa e após o recolhimento de estatísticas com relação as tabelas mais relevantes no projeto. As alterações das condições de banco de dados, bem como melhorias de desempenho de consulta em versões subsequentes podem ter um impacto significativo na forma como os hints em seu código podem afetar o desempenho.

Um bloco de instrução pode conter apenas um comentário contendo os hints e o comentário deve seguir as instruções de SELECT, UPDATE, INSERT, MERGE ou mesmo o DELETE, que são palavras-chave. A sintaxe básica dos comentários utilizando os hints são apresentados de duas formas, como podemos ver a seguir:

/*+ hint string */ ou --+hint string

O sinal de adição (+) faz com que a Oracle interprete o comentário como uma lista de sugestões e deve seguir imediatamente após o delimitador de comentário, onde nenhum espaço pode ser adicionado entre eles. O espaço entre o sinal de adição e a dica é opcional.

Se o comentário contém várias dicas (hints), podemos separá-los por um espaço. A string é o texto que pode intercalar as dicas.

Na Listagem 16 criaremos uma tabela e uma View. Em seguida, vejamos então uma pequena exibição de como podemos utilizar esse tipo de comentário na Listagem 17.

Listagem 16. Criando a View e a tabela.

CREATE VIEW viewTeste AS SELECT func.NomeCompleto, func.departamento_id, dep.localizacao_id FROM funcionarios func, departamento dep WHERE func.departamento_id = dep.departamento_id; CREATE TABLE tabela_teste AS SELECT * from funcionarios WHERE funcionario_id < 40;

Agora que temos nossa tabela de testes criada e a View vamos utilizar hints na Listagem 17.

Listagem 17. Utilizando hints – forma incorreta.

EXPLAIN PLAN SET STATEMENT_ID = 'Desenvolvimento' INTO tabela_teste FOR (SELECT /*+ LEADING(viewTeste.func viewTeste.dep teste) */ * FROM tabela_teste, viewTeste WHERE tabela_teste.departamento_id = viewTeste.departamento_id);

O exemplo apresenta uma instrução Explain Plan, que permite a exibição do plano de execução e a partir daí, determina se uma dica é utilizada ou ignorada. Neste exemplo, temos que a dica é ignorada na consulta, pois a mesma está se referindo a vários blocos de consulta, ou seja, a tabela de bloco de consulta principal e a view bloco de consulta.

Agora que vimos uma forma errada de utilizarmos as hints, vejamos agora de acordo com a Listagem 18 uma forma correta de sua utilização.

Listagem 18. Forma correta de utilizar hints

EXPLAIN PLAN SET STATEMENT_ID = 'Desenvolvimento' INTO tabela_teste FOR (SELECT /*+ LEADING(FUNC@SEL$2 DEP@SEL$2) */ * FROM tabela_teste, viewTeste WHERE tabela_teste.departamento_id = viewTeste.departamento_id);

Com isso finalizamos este artigo, onde tratamos da utilização de estruturas de controle de repetição e sequencial, além de termos uma breve explanação da utilização de comentários nas instruções SQL e também em Schemas e objetos NonSchemas no Oracle.

Até a próxima! =)