LOOP

Loops são utilizados para rodar instruções mais de uma vez.

Abaixo exemplos dos 3 tipos de Loops: Simple Loops, While Loops e For Loops

SIMPLE LOOPS

declare
  v_count number:= 0;
begin
  LOOP
    v_count := v_count + 1;
    dbms_output.put_line('v_count: '||v_count);
    EXIT WHEN v_count = 5;  
  END LOOP;
end;  

Resultado esperado:
v_count: 1
v_count: 2
v_count: 3
v_count: 4
v_count: 5

declare
  v_count number:= 0;
begin
  LOOP
    v_count := v_count + 1;
    if v_count <= 5 then
      dbms_output.put_line('v_count: '||v_count);
    else
      exit;
    end if;        
  END LOOP;
end;  

Resultado esperado:
v_count: 1
v_count: 2
v_count: 3
v_count: 4
v_count: 5

Muito importante não esquecer de inicializar a variável (v_count number:= 0) pois caso a mesma não seja inicializada, irá ocorrer um Loop infinito, o contador não realiza soma de uma variável nula, ou seja, null + 1 é null e o contador nunca irá atingir o valor para terminar o loop.

WHILE LOOPS

Declare
  v_count number:= 0;
begin
  WHILE v_count < 5 LOOP
    v_count := v_count + 1;
    dbms_output.put_line('v_count: '||v_count);
  END LOOP;
end;

Resultado esperado:
v_count: 1
v_count: 2
v_count: 3
v_count: 4
v_count: 5

Observe que o operador foi trocado para < (menor), se mantivermos o mesmo operador do exemplo do Loop Simples (<= menor ou igual) , irá trazer também v_count: 6 

FOR LOOPS

declare
  v_count number:= 0;
begin   
  FOR v_count IN 1..5 LOOP
    dbms_output.put_line('v_count: '||v_count);
  END LOOP;
end;  

Resultado esperado:
v_count: 1
v_count: 2
v_count: 3
v_count: 4
v_count: 5

No exemplo abaixo usamos REVERSE, que faz com que o loop se inicie no 5 e termine no 1

declare
  v_count number:= 0;
begin   
  FOR v_count IN REVERSE 1..5 LOOP
    dbms_output.put_line('v_count: '||v_count);
  END LOOP;
end; 


Resultado esperado:  
v_count: 5
v_count: 4
v_count: 3
v_count: 2
v_count: 1

Qual a melhor opção de loop a ser usada ?
Na minha opinião a melhor maneira é aquela que você se acostumar, pois uma vez que você se acostuma com uma maneira e entende bem o seu funcionamento, dificilmente irá mudar. 
Eu particularmente costumo utilizar For Loop, acho mais simples de ser escrito, principalmente quando utilizado com Cursor.
Qualquer das três opções irá trazer o mesmo resultado caso escrito corretamente.

LAG e LEAD

Nessa query, estão sendo usadas as funções LAG e LEAD para trazer os valores do registro anterior e posterior da coluna Valor na mesma linha corrente

select equipe
      ,vendedor
      ,sum(valor) valor
      ,LAG(SUM(valor), 1) OVER (ORDER BY equipe, vendedor) AS anterior
      ,LEAD(SUM(valor), 1) OVER (ORDER BY equipe, vendedor) AS posterior
from   vendas
group by equipe, vendedor
order by 1,2


        EQUIPE VENDEDOR  VALOR   ANTERIOR POSTERIOR
1 1 Joao      16,49           16,49
2 1 Pedro     16,49   16,49    25,79
3 1 Jose      25,79   16,49    62,33
4 1 Felipe    62,33   25,79    17,31
5 1 Marcos    17,31   62,33     8,01
6 1 Juliana    8,01   17,31    17,31
7 1 Paula     17,31    8,01    42,65
8 1 William   42,65   17,31    51,65
9 1 Priscila  51,65   42,65    42,65
10 1 Claudia   42,65   51,65    16,49

ACUMULAR VALORES DE UMA SOMA

Exemplo de uma query que realiza uma soma e acumula os valores somados em outra coluna ao lado.

select   equipe    
        ,vendedor
        ,sum(valor) valor
        ,SUM(SUM(valor)) OVER (ORDER BY equipe,vendedor ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
  from   vendas
  group by equipe, vendedor
  order by 1,2;


    EQUIPE VENDEDOR VALOR ACUMULADO
1 1 Jose     16,49 16,49
2 1 Paulo    16,49 32,98
3 1 Pedro    25,79 58,77
4 1 Joao     62,33 121,1
5 1 Antonio  17,31 138,41
6 1 Felipe    8,01 146,42
7        1 Oscar    17,31 163,73
8        1 Lucio    42,65 206,38
9 1 Marcelo  51,65 258,03

Livro Oracle Essencial: Base de Dados Oracle 11g

Hoje chegou meu livro Oracle Essencial: Base de Dados Oracle 11g. Embora minha experiência e atividade seja em desenvolvimento PL/SQL e não administração, acho importante aprender mais sobre a arquitetura e funcionamento do banco de dados Oracle.

Conforme for lendo o livro vou postando comentários sobre o mesmo.

COMPARAÇÃO DE VALORES NULOS

Uma situação que é muito comum em "enganar" programadores, principalmente os que estão começando em PL/SQL é a comparação com valores NULL.

Vamos verificar alguns exemplos abaixo: 

declare

  v_valor1 varchar2(10):= null;
  v_valor2 varchar2(10):= '10';
  
  begin
    
    -- Caso 1 
    if v_valor1 = v_valor2 then
      dbms_output.put_line('Caso 1 - Valor '|| v_valor1||' é igual a '||v_valor2);
    else
      dbms_output.put_line('Caso 1 - Valor '|| v_valor1||' é diferente de '||v_valor2);
    end if;
    
    -- Caso 2
    if v_valor1 != v_valor2 then
      dbms_output.put_line('Caso 2 - Valor '|| v_valor1||' é diferente de '||v_valor2);
    else
      dbms_output.put_line('Caso 2 - Valor '|| v_valor1||' é igual a '||v_valor2);
    end if; 
    
    -- Caso 3
    if nvl(v_valor1,-1) != nvl(v_valor2,-1) then
      dbms_output.put_line('Caso 3 - Valor '|| v_valor1||' é diferente de '||v_valor2);
    else
      dbms_output.put_line('Caso 3 - Valor '|| v_valor1||' é igual a '||v_valor2);
    end if;
    
    -- Caso 4
    if v_valor1 like v_valor2 then
      dbms_output.put_line('Caso 4 - Valor '|| v_valor1||' é igual de '||v_valor2);  
    else
      dbms_output.put_line('Caso 4 - Valor '|| v_valor1||' é diferente a '||v_valor2); 
    end if;   
    
    -- Caso 5
    if v_valor1 not like v_valor2 then
      dbms_output.put_line('Caso 5 - Valor '|| v_valor1||' é diferente de '||v_valor2);
    else
      dbms_output.put_line('Caso 5 - Valor '|| v_valor1||' é igual a '||v_valor2);
    end if;
    
      -- Caso 6
    if nvl(v_valor1,-1) not like nvl(v_valor2,-1) then
      dbms_output.put_line('Caso 6 - Valor '|| v_valor1||' é diferente de '||v_valor2);
    else
      dbms_output.put_line('Caso 6 - Valor '|| v_valor1||' é igual a '||v_valor2);
    end if;  
    
  end;
  
Caso 1 - Valor  é diferente a 10
Caso 2 - Valor  é igual a 10
Caso 3 - Valor  é diferente a 10
Caso 4 - Valor  é diferente a 10
Caso 5 - Valor  é igual a 10
Caso 6 - Valor  é diferente a 10


No caso 1, usando operador "=", o resultado está correto, nulo é diferente de 10.
No caso 2, usando operador "!=",o resultado está incorreto, nulo não é igual a 10.
No caso 3, para corrigir o caso 2, utilizamos um NVL, adicionando um valor qualquer para o valor nulo, trazendo a resposta correta.
Quando usamos o NVL, o campo deixa de ser nulo e então o Oracle compara um valor existente com outro valor existente, porém é preciso tomar cuidado com
essa prática, o que falarei mais abaixo.
No caso 4, usando operador like, o resultado está correto, nulo é diferente de 10.
No caso 5, usando operador not like, o resultado está incorreto, nulo não é igua a 10
No caso 6, usando operador not like com NVL,  o resultado está correto.


Devemos ter cuidado ao usar NVL. por exemplo, se de um lado temos um valor null e do outro temos por exemplo o valor 1, se colocarmos nvl(valor1,1) já teremos problemas na comparação
pois o oracle transformara o valor nulo em 1 e ira comparar com o outro valor que também é 1 dizendo que são valores iguais, "enganando" o programador.

declare

  v_valor1 varchar2(10):= null;
  v_valor2 varchar2(10):= '1';
  
  begin  
    
    if nvl(v_valor1,1) != nvl(v_valor2,1) then
      dbms_output.put_line('Valor '|| v_valor1||' é diferente a '||v_valor2);
    else
      dbms_output.put_line('Valor '|| v_valor1||' é igual a '||v_valor2);            
    end if; 
  
  end;  

  Valor  é igual a 1
  (Resultado errado)


  A melhor maneira de comparar valores nulos é utilizar:
        v_valor1 <> v_valor2 or (v_valor1 is null and v_valor2 is not null)or (v_valor1 is not null and v_valor2 is null)
  conforme abaixo:

  declare

  v_valor1 varchar2(10):= null;
  v_valor2 varchar2(10):= '1';
  
  begin 

    if v_valor1 <> v_valor2 or (v_valor1 is null and v_valor2 is not null)
        or (v_valor1 is not null and v_valor2 is null) then
      dbms_output.put_line('Caso 1 - Valor '|| v_valor1||' é diferente de '||v_valor2);
    else
      dbms_output.put_line('Caso 1 - Valor '|| v_valor1||' é igual a '||v_valor2);
    end if;

   end;

TRANSLATE

Translate é uma função que substitui caracteres de uma string por outros caracteres informados.

SELECT TRANSLATE('Marcio Alexandre',                 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',           'EFGHIJKLMNOPQRSTUVWXYZABCDefghijklmnopqrstuvwxyzabcd') altera_nome
FROM dual;
       ALTERA_NOME
       Qevgms Epiberhvi


Observar que para reverter a codificação, é necessário alterar a sequencia de caracteres, conforme abaixo (na query acima, após a primeira vírgula tinhamos ABC... e agora temos EFG...)
  
SELECT TRANSLATE('Qevgms Epiberhvi',                 'EFGHIJKLMNOPQRSTUVWXYZABCDefghijklmnopqrstuvwxyzabcd',           'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') altera_nome
FROM dual;
ALTERA_NOME
Marcio Alexandre


Pode ser usado também com números
SELECT TRANSLATE(658780,
                 1234567890,
                 6789012345) altera_numero
FROM dual;
       ALTERA_NUMERO
       103235

CUBE

A função CUBE é uma função utilizada junto a Group By onde traz o subtotal dos valores e 
o total geral no final.

SELECT equipe
      ,vendedor
      ,SUM(valor) total       
FROM   brd_pedido
WHERE  valor IS NOT NULL
and    equipe in (1,2)
GROUP BY cube(equipe,vendedor)
ORDER BY 1,2

        EQUIPE  VENDEDOR     TOTAL
1 1 Joao         217,6
2 1 Pedro        2459,2
3 1 Jose         649,6
4 1                   3326,4
5 2 Tiago        542,4
6 2 Paulo        217,6
7 2                   760
8 Joao         217,6
9 Pedro        2459,2
10 Jose         649,6
11 Tiago        542,4
12 Paulo        217,6
13                         4086,4

Observe que primeiramente a função mostra o subtotal por equipes e no final repete os valores individuais dos vendedores e a soma total.

ROLLUP

ROLLUP é uma função que se estende a cláusula GROUP BY para retornar uma linha que contém um subtotal para cada grupo de linhas, além de uma linha que contém um total geral para todos os grupos. 

No exemplo abaixo estamos realizando uma consulta para trazer os resultados das equipes de vendas, agrupando por equipe e vendedor

SELECT equipe
      ,vendedor
      ,SUM(valor) total       
FROM   vendas
WHERE  equipe in (1,2)
GROUP BY equipe,vendedor
ORDER BY 1,2

    EQUIPE VENDEDOR TOTAL
1 1 João     217,6
2 1 Pedro    2459,2
3 1 José     649,6
4 2 Tiago    542,4
5 2 Paulo    217,6


Porém, é possível continuar mostrando os resultados de cada vendedor da equipe e ainda mostrar a soma da equipe.
Observe que agora a cláusula group by contem apenas a coluna equipe e utilizamos ROLLUP para as colunas equipe e vendedor: 

SELECT equipe
      ,vendedor
      ,SUM(valor) total       
FROM   vendas
WHERE  equipe in (1,2)
GROUP BY equipe, ROLLUP(equipe,vendedor)
ORDER BY 1,2

    EQUIPE VENDEDOR   TOTAL
1 1 João       217,6
2 1 Pedro      2459,2
3 1 José       649,6
4 1                 3326,4
5 1                 3326,4
6 2 Tiago      542,4
7 2 Paulo      217,6
8 2                 760
9 2                 760

Observe ainda que existem colunas duplicadas para cada equipe, é possível eliminar essas colunas duplicadas utilizando a função GROUP_ID()

SELECT equipe
      ,vendedor
      ,GROUP_ID()
      ,SUM(valor) total       
FROM   vendas
WHERE  equipe in (1,2)
GROUP BY equipe, ROLLUP(equipe,vendedor)
HAVING GROUP_ID() = 0
ORDER BY 1,2


EQUIPE VENDEDOR   GROUP_ID()  TOTAL
1 1 João       0           217,6
2 1 Pedro      0           2459,2
3 1 José       0           649,6
4 1                 0           3326,4
5 2 Tiago      0           542,4
6 2 Paulo      0           217,6
7 2                 0           760

RANK e DENSE RANK

Vamos imaginar que você queira classificar os vendedores de uma empresa.

As funções RANK () e DENSE_RANK () são utilizadas para classificar itens em um agrupamento. 
A diferença entre estas duas funções está na forma como eles lidam com os resultados das colunas com o mesmo valor, RANK deixa uma lacuna na seqüência quando houverem valores iguais e DENSE_RANK () não deixa lacunas. 

No exemplo abaixo, os valores dos dois primeiros vendedores são iguais, Rank classifica os dois vendedores com resultados iguais com o valor 1 e adiciona 3 para o próximo vendedor, deixando uma lacuna na classificação, já Dense Rank também informa 1 para os dois primeiros vendedores, porém informa 2 para o próximo, não deixando lacunas na contagem de classificação.


SELECT vendedor, 
       SUM(valor) total,
       RANK() OVER (ORDER BY SUM(valor ) desc ) AS rank,
       DENSE_RANK() OVER (ORDER BY SUM(valor) desc ) AS dense_rank
FROM   vendas
WHERE  valor IS NOT NULL
and    equipe in (500,900,800,400)
GROUP BY vendedor
ORDER BY rank;

  VENDEDOR TOTAL RANK DENSE_RANK
1 500 1621,2 1 1
2 900 1621,2 1 1
3 800 1609 3 2
4 400 1407,6 4 3


Rank Using Partition

CREATE TABLE test_rank

(column1 NUMBER(2,0),

column2 VARCHAR2(1));

 INSERT INTO test_rank VALUES (1,'A');

INSERT INTO test_rank VALUES(1,'B');

INSERT INTO test_rank VALUES(1,'C');

INSERT INTO test_rank VALUES(2,'A');

INSERT INTO test_rank VALUES(2,'B');

INSERT INTO test_rank VALUES(3,'A');

INSERT INTO test_rank VALUES(3,'B');

INSERT INTO test_rank VALUES(3,'C');

INSERT INTO test_rank VALUES(3,'D');

INSERT INTO test_rank VALUES(4,'A');

 SELECT column1,

        column2,

        RANK() OVER (ORDER BY column1, column2) AS rank_1,

        RANK() OVER( PARTITION BY column1 ORDER BY column1, column2)  rank_2       

FROM test_rank;

EXEMPLOS FORMATAÇÃO DATAS

Format String                                                                 Resultado

MONTH DD, YYYY                      FEBRUARY 05, 1968
MM/DD/YYYY                          02/05/1968

MM-DD-YYYY                          02-05-1968

DD/MM/YYYY                          05/02/1968

DAY MON, YY AD                      MONDAY FEB, 68 AD

DDSPTH "of" MONTH, YEAR A.D.        FIFTH of FEBRUARY,
                                    NINETEEN SIXTY-EIGHT A.D.
CC, SCC                             20, 20
Q                                   1

YYYY, IYYY, RRRR, SYYYY, Y,YYY,     1968, 1968, 1968, 1968,1,968,

YYY, IYY, YY, IY, RR, Y, I,         968, 968, 68, 68, 68, 8, 8,

YEAR,                                                                               NINETEEN SIXTY-EIGHT,

Year                                Nineteen Sixty-Eight
MM, MONTH, Month,                   02, FEBRUARY, February,

MON, Mon, RM                        FEB, Feb, II

WW, IW, W                           06, 06, 1

DDD, DD, DAY,  0                    36, 05, MONDAY,

Day, DY, Dy, J                      Monday, MON, Mon, 2439892

ddTH, DDTH, ddSP, DDSP, DDSPTH      05th, 05TH, five, FIVE, FIFTH



SELECT TO_CHAR(SYSDATE, 'fmDDTH')||' of '||TO_CHAR
(SYSDATE, 'fmMonth')||', '||TO_CHAR(SYSDATE, 'YYYY') "Ides"
FROM DUAL;

9TH of September, 2015

EXEMPLOS DE FORMATAÇÃO DE VALORES

TO_CHAR() Function                                                         Resultado

TO_CHAR(12345.67, '99999.99')          12345.67

TO_CHAR(12345.67, '99,999.99')         12,345.67 

TO_CHAR(-12345.67, '99,999.99')        -12,345.67 
TO_CHAR(12345.67, '099,999.99')        012,345.67 
TO_CHAR(12345.67, '99,999.9900')       12,345.6700 
TO_CHAR(12345.67, '$99,999.99')        $12,345.67 
TO_CHAR(0.67, 'B9.99')                 .67 
TO_CHAR(12345.67, 'C99,999.99')        USD12,345.67

TO_CHAR(12345.67, '99999D99')          12345.67

TO_CHAR(12345.67, '99999.99EEEE')      1.23E+04

TO_CHAR(0012345.6700, 'FM99999.99')    12345.67

TO_CHAR(12345.67, '99999G99')          123,46

TO_CHAR(12345.67, 'L99,999.99')        $12,345.67

TO_CHAR(-12345.67, '99,999.99MI')      12,345.67

TO_CHAR(-12345.67, '99,999.99PR')      12,345.67

TO_CHAR(2007, 'RN')                    MMVII

TO_CHAR(12345.67, 'TM')                12345.67

TO_CHAR(12345.67, 'U99,999.99')        $12,345.67

TO_CHAR(12345.67, '99999V99')          1234567

TO_CHAR(12345.67,'FM999G990D00')       12.345,67
to_char(12345.67,'FM999990D00')        12345,67

TIPOS DE INSTRUÇÕES SQL

Existem 5 tipos de instruções SQL, conforme abaixo:

Query statements

Retorna as linhas armazenadas em uma tabela do banco de dados.

Uma consulta é realizada utilizando a instrução SQL “SELECT”.

Data Manipulation Language (DML) statements

Modifica o conteúdo das tabelas. São três instruções DML:
 

INSERT - Adiciona linhas em uma tabela.

UPDATE – Modifica linhas em uma tabela.

DELETE - Remove linhas de uma tabela.

 

 

Data Definition Language (DDL) statements  

Define a estrutura de dados, existem cinco tipo de  intruções DDL:
 

CREATE - Cria um objeto de banco de dados, por exemplo: Create table, Create or Replace procedure, Create trigger, Create user.

ALTER - Modifica um objeto do banco de dados, por exemplo: Alter table.

DROP - Remove um objeto do banco de dados. Por exemplo: Drop table, Drop view, Drop package.

RENAME - Muda o nome de uma tabela.

TRUNCATE - Apaga todas as linhas de uma tabela.

Transaction Control (TC) statements  

Grava definitivamente as operações realizadas ou desfaz as mesmas.

 

COMMIT - Grava permanentemente as alterações realizadas nas linhas da tabela.

ROLLBACK - Desfaz as alterações realizadas nas linhas da tabela.

SAVEPOINT - Define um "ponto de salvamento" onde você pode reverter as alterações.

 

Data Control Language (DCL) statements 

Altera as permissões em objetos do banco de dados

GRANT - Concede a outro usuário acesso a seu objeto do banco de dados.

REVOKE - Não permite que outro usuário tenha acesso a seu objeto do banco de dados.

CONFIGURAR TEXTPAD PARA PL/SQL

1 – Crie um arquivo plsql.syn com o conteúdo abaixo e salve na pasta C:\Program Files\TextPad 7\system; (A pasta “TextPad 7” pode variar conforme a versão do seu TextPad);

 

; TextPad syntax definitions for SQL

C=1

[Syntax]

Namespace1 = 3

IgnoreCase = Yes

CommentStart = /*

CommentEnd = */

SingleComment = --

StringsSpanLines = Yes

StringStart = '

StringEnd = '

[Keywords 1]

create

drop

as

declare

nocount

select

insert

delete

update

from

where

if

exists

begin

end

set

on

off

exec

procedure

function

[Keywords 2]

substring

len

ltrim

rtrim

isnumeric

str

replace

upper

lower

[Keywords 3]

not

in

null

and

or

is

 

2 – Vá em Configurar – Nova Classe de Documento 

 

3 – Preencha o campo conforme abaixo:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4 – Cole esse texto *.sql, *.fnc, *.pkb, *.pks, *.prc, *.trg, *.tbl na janela abaixo: 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5 – Habilite “Ativar realce de sintaxe” e no campo “Arquivo de definição de sintaxe” escolha o arquivo criado: plsql.syn; 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6 – Clique em concluir;