Dicas e Truques PL/SQL - Parte 1

1 - Nunca esqueça de usar o ; para finalizar uma bloco PL/SQL.


SQL> begin
2     dbms_output('não esqueça de usar ;');
3  end
4  /
end
*
ERRO na linha 3:
ORA-06550: linha 3, coluna 3:
PLS-00103: Encontrado o símbolo "end-of-file" quando um dos seguintes símbolos
era esperado:
; 

O símbolo ";" foi substituído por "end-of-file" para continuar.


SQL> begin
2     dbms_output.put_line('o ; finaliza um bloco PL/SQL');
3* end;
SQL> /

Procedimento PL/SQL concluído com sucesso.

SQL>


2 - Uma tipo VARCHAR2 tem 4000 bytes de comprimento no banco mas em PL/SQL o limite 
sobe para 32767 bytes.


3 - Existem dois subtipos para o tipo VARCHAR2: VARCHAR e STRING. A ORACLE recomenda
que não se usem estes dois subtipos, pois existe apenas para compatibilidade com
outras marcas de banco de dados e também com o padrão SQL.

SQL> 
1  declare
2    x varchar2(100);
3    y string(100);
4    z varchar(100);
5  begin
6    x:= 'Use sempre o tipo VARCHAR2 ';
7    y:= 'em vez de subtipos STRING ';
8    z:= 'ou VARCHAR.';
9    dbms_output.put_line(x || y || z);
10* end;
SQL> /
Use sempre o tipo VARCHAR2 em vez de subtipos STRING ou VARCHAR.

Procedimento PL/SQL concluído com sucesso.

SQL>


4 - Ao declarar uma variável VARCHAR2 dentro de um procedimento 
armazenado sempre especifique o comprimento ou um erro ocorrerá.


5 - Ao invocar uma função que não retorne valor um erro será levantado. 


6 - A comparação entre variáveis VARCHAR2 e CHAR pode retornar falso, pois 
o tipo CHAR é fixo.

SQL> declare
2  x1 varchar2(10);
3  x2 char(10);
4  begin
5     x1:= 'agilar';
6     x2:= 'agilar';
7     if x1 = x2 then
8             dbms_output.put_line('exatamente iguais');
9     else
10             dbms_output.put_line('diferente!');
11     end if;
12  end;
13  /

Procedimento PL/SQL concluído com sucesso.

SQL> set serveroutput on
SQL> /
diferente!

Procedimento PL/SQL concluído com sucesso.


7 - Use funções e procedimentos aninhados para obter modularidade.

SQL> 
1  declare
2     x1 number;
3     x2 number;
4    procedure troca(x in out number, y in out number)
5    as
6      temp number;
7    begin
8      temp:= x;
9      x:= y;
10      y:= temp;
11    end;
12  begin
13     x1:= 100;
14     x2:= 200;
15     dbms_output.put_line(x1);
16     dbms_output.put_line(x2);
17     troca(x1, x2);
18     dbms_output.put_line(x1);
19     dbms_output.put_line(x2);
20* end;
SQL> /
100
200
200
100

Procedimento PL/SQL concluído com sucesso.


8 - Não use os tipos obsoletos: LONG, RAW e LONG RAW.


9 - Use PLS_INTEGER em vez de BINARY_INTEGER em aplicativos novos para obter performance.


10 - Cuidado com o escopo das variáveis. Uma variável pode ser referenciada
apenas dentro do bloco em que foi declarado.

SQL> 
1  declare
2     x varchar2(10):= 'everton';
3  begin
4     declare
5             y varchar2(10):= 'agilar';
6     begin
7             dbms_output.put_line(x);
8             dbms_output.put_line(y);
9     end;
10     dbms_output.put_line(y);   -- erro pois y está declarado dentro do bloco anterior
11* end;
SQL> /
dbms_output.put_line(y);
*
ERRO na linha 10:
ORA-06550: linha 10, coluna 23:
PLS-00357: A referência 'Y' Ó Tabela, View ou Seqência não permitida neste
contexto
ORA-06550: linha 10, coluna 2:
PL/SQL: Statement ignored

SQL> 


11 - Exemplos de uso da função CAST


SQL> select cast(123.1254 as number(12,3)) as num from dual;

NUM
----------
123,125

SQL> select cast(123.1254 as number(12,2)) as num from dual;

NUM
----------
123,13

SQL> select cast(123.1254 as number(12,1)) as num from dual;

NUM
----------
123,1

SQL> select cast(123.1254 as number(12,0)) as num from dual;

NUM
----------
123

SQL> select cast(123.1254 as number(12,-1)) as num from dual;

NUM
----------
120

SQL> select cast(123.1254 as number(12,-2)) as num from dual;

NUM
----------
100

SQL> select cast(123.1254 as number(12,-3)) as num from dual;

NUM
----------
0

SQL>


12 - Você pode usar o operador de exponenciação em PL/SQL mas não em SQL.

SQL> l
1  declare
2     x number:= 10;
3     y number:= 2;
4  begin
5     dbms_output.put_line(x**y);
6* end;
SQL> /
100

Procedimento PL/SQL concluído com sucesso.

SQL> select 10**2 from dual;
select 10**2 from dual
*
ERRO na linha 1:
ORA-00936: expressão não encontrada


13 - É possível atribuir um valor durante a declaração da variável.

SQL> l
1  declare
2     pi number:= 3.14;
3     x number;
4     y number:= 10;
5  begin
6     dbms_output.put_line('O número PI is '|| to_char(pi));
7* end;
SQL>


14 - Operadores de desigualdade da PL/SQL: <>, != e ~=

1  begin
2     if 1 != 2 then
3             dbms_output.put_line('diferente');
4     end if;
5     if 1 <> 2 then
6             dbms_output.put_line('diferente');
7     end if;
8     if 1 ~= 2 then
9             dbms_output.put_line('diferente');
10     end if;
11* end;
SQL>


15 - Expressões envolvendo NULL em PL/SQL

SQL> 
1  declare
2     a number;
3     b number;
4  begin
5    -- Atribui 10 a variável A mas deixa B null.
6    a:= 10;
7    -- A lógica dos três valores diz que o resultado
8    -- de uma expressão pode ser true, false ou null.
9    if a = b then
10      dbms_output.put_line('a = b is true');
11    elsif a <> b then
12     dbms_output.put_line('a = b is not true');
13    else
14      dbms_output.put_line('a = b is null');
15    end if;
16    -- Eis uma expressão que muitos esperam ser true
17    if (a = b) or (a <> b) then
18      dbms_output.put_line('true');
19    else
20     dbms_output.put_line('is not true');
21    end if;
22* end;
SQL> /
a = b is null
is not true

Procedimento PL/SQL concluído com sucesso.

SQL>


16 - Oracle considera uma string vazia como sendo null.

SQL> 
1  declare
2     x varchar2(10);
3     y varchar2(10);
4  begin
5     -- Oracle considera uma string vazia como sendo null
6     x:= '';
7     if x = '' then
8             dbms_output.put_line('x is vazio');
9     elsif x is null then
10             dbms_output.put_line('x is null');
11     else
12             dbms_output.put_line('x contém algo?');
13     end if;
14     -- Comparação entre duas variáveis vazias
15     y:= '';
16     if x = y then
17             dbms_output.put_line('x = y');
18     elsif x is null then
19             dbms_output.put_line('x is null');
20     else
21             dbms_output.put_line('x <> y');
22     end if;
23* end;
SQL> /
x is null
x is null

Procedimento PL/SQL concluído com sucesso.

SQL>

17 - NULL não se propaga na concatenação de strings em ORACLE. Em outros
RDBMS o valor retornado é NULL.

SQL> 
1* select 'everton ' || 'agilar' || null from dual;

'EVERTON'||'AG
--------------
everton agilar

SQL> begin
2     dbms_output.put_line('everton ' || 'agilar' || null);
3  end;
4  /
everton agilar

Procedimento PL/SQL concluído com sucesso.

SQL>


18 - Use a função NVL ou COALESCE para trocar valores nulls por outro valor.


SQL> select nvl(null, 10) from dual;

NVL(NULL,10)
------------
10

SQL> select coalesce(null, 10) from dual;

COALESCE(NULL,10)
-----------------
10



19 - Como comparar duas datas em PL/SQL para saber se o ano é o mesmo ?

SQL> 
1  declare
2    d1 date;
3    d2 date;
4  begin
5    d1:= to_date('30/03/2010', 'dd/mm/yyyy');
6    d2:= sysdate;
7    if trunc(d1, 'yyyy') = trunc(d2, 'yyyy') then
8      dbms_output.put_line('ano igual...');
9    else
10      dbms_output.put_line('ano diferente...');
11    end if;
12* end;
SQL> /
ano igual...

Procedimento PL/SQL concluído com sucesso.

SQL>



20 - Obtendo as partes de uma data através da função TO_CHAR.

SQL> select to_char(to_date('10/01/2010', 'dd/mm/yyyy'), 'dd') from dual;

TO
--
10

SQL> select to_char(to_date('10/01/2010', 'dd/mm/yyyy'), 'mm') from dual;

TO
--
01

SQL> select to_char(to_date('10/01/2010', 'dd/mm/yyyy'), 'yyyy') from dual;

TO_C
----
2010

SQL>