Dicas e Truques PL/SQL - Parte 3

Function ASCII - retorna o código ASCII do caracter.

SQL> select ascii('A') from dual;

ASCII('A')
----------
        65


Function CHR - Retorna um caracter quando recebe seu valor ASCII.

SQL> select chr(65) from dual;

C
-
A


Function CONCAT - Concatena duas strings.

SQL> select concat('ORACLE ', 'PL/SQL') texto from dual;

TEXTO
-------------
ORACLE PL/SQL


Function INITCAP - Primeiro caracter de cada palavra de uma string em maiúsculo.

SQL> select initcap('everton de vargas agilar') my_name from dual;

MY_NAME
------------------------
Everton De Vargas Agilar


Function INSTR - Retorna a localização de uma string dentro de outra string.

EXEMPLO 1

SQL> select instr('ABCDEFGHIJLMNOPQRSTUVXZabcdefghijlmnopqrstuvxzABCDEFGHIJKLMNOPQRSTUVXZ', 'DEF') pos from dual;

       POS
----------
         4


EXEMPLO 2

SQL> select instr('ABCDEFGHIJLMNOPQRSTUVXZabcdefghijlmnopqrstuvxzABCDEFGHIJKLMNOPQRSTUVXZ', 'DEF', 5) pos from dual;

       POS
----------
        50

EXEMPLO 3


SQL> 
  1  create or replace procedure print_values(
  2    pValues in varchar2,
  3    pDesconsiderar in varchar2)
  4  is
  5    v char;
  6  begin
  7    for i in 1..length(pValues) loop
  8      v:= substr(pValues, i, 1);
  9      if instr(pDesconsiderar, v) = 0 then
 10        dbms_output.put_line(v);
 11      end if;
 12    end loop;
 13* end;
SQL> /

Procedimento criado.

SQL> execute print_values('12345', '4');
1
2
3
5

Procedimento PL/SQL concluÝdo com sucesso.


Function LPAD - Preenche uma string no lado esquerdo de caracteres com qualquer string especificada.

SQL> select lpad('789123', 13) codigo from dual;

CODIGO
-------------
       789123

SQL> select lpad('789123', 13, '0') codigo from dual;

CODIGO
-------------
0000000789123

SQL> select lpad('789123', 13, '01') codigo from dual;

CODIGO
-------------
0101010789123

SQL> select lpad('789123', 13, '*') codigo from dual;

CODIGO
-------------
*******789123



Function RPAD - Preenche uma string no lado direito de caracteres com qualquer string especificada.

SQL> select rpad('789123', 13) codigo from dual;

CODIGO
-------------
789123

SQL> select rpad('789123', 13, '0') codigo from dual;

CODIGO
-------------
7891230000000

SQL> select rpad('789123', 13, '01') codigo from dual;

CODIGO
-------------
7891230101010

SQL> select rpad('789123', 13, '-') codigo from dual;

CODIGO
-------------
789123-------

SQL> select rpad('789123', 13, '*') codigo from dual;

CODIGO
-------------
789123*******


Function LENGTH - Retorna o comprimento de uma string.

SQL> select length('ORACLE 10g') text from dual;

      TEXT
----------
        10


Function REPLACE - Substitui toda ocorrência de uma string por outra string.

SQL> select replace('ORACLE 10g', '10g', '11g') text from dual;

TEXT
----------
ORACLE 11g


Function TRANSLATE - Igual a REPLACE mas opera a nível de caracter. Veja um exemplo com REPLACE e TRANSLATE.

-- Converte ad para x. Somente uma ocorrência a ser encontrada.
SQL> select replace('abcdabada', 'ad', 'x') from dual;

REPLACE(
--------
abcdabxa

-- Converte o caracter "a" ou o caracter "d" para x. Várias ocorrências são encontradas.
SQL> select translate('abcdabada', 'ad', 'x') from dual;

TRANSLA
-------
xbcxbxx


Function SOUNDEX - Retorna a representação fonética de uma string. Útil nas palavras que têm ortografia diferente mas com som parecido.

SQL> 
  1  select * from (
  2      select 'Agilar' as nome from dual
  3      union
  4      select 'Aguilar' as nome from dual
  5      union
  6      select 'Stefanello' as nome from dual
  7      union
  8      select 'Agilare' as nome from dual
  9  )
 10* where soundex(nome) = soundex('Agilar')
SQL> /

NOME
----------
Agilar
Agilare
Aguilar


Function SUBSTR - Retorna uma parte de uma string de dentro de uma string.

SQL> select substr('ORACLE 11g', 8, 3) from dual;

SUB
---
11g


Function TRIM, LTRIM, RTRIM - Remove espaços de uma string em ambos lados, a esquerda ou a direita.

SQL> select trim('      8976       ') texto from dual;

TEXT
----
8976

SQL> select ltrim('      8976       ') text from dual;

TEXT
-----------
8976

SQL> select rtrim('      8976       ') text from dual;

TEXT
----------
      8976


Function UPPER, LOWER - Converte a string para maiúsculo ou minúsculo.


SQL> select upper('oracle') texto from dual;

TEXTO
------
ORACLE

SQL> select lower('Oracle') texto from dual;

TEXTO
------
oracle



Function ABS - Retorna o valor absoluto de um número.

SQL> select abs(-11) from dual;

  ABS(-11)
----------
        11

SQL> select abs(11) from dual;

   ABS(11)
----------
        11


Function FLOOR - Retorna o maior integer menor ou igual a um valor decimal.
Function CEIL - Retorna o menor integer maior ou igual a um valor decimal.

SQL> select floor(12345.56) from  dual;

FLOOR(12345.56)
---------------
          12345

SQL> select ceil(12345.56) from  dual;

CEIL(12345.56)
--------------
         12346


Function ROUND - Retorna um número arredondado para n casas.

SQL> select round(10.126, 2) from dual;

ROUND(10.126,2)
---------------
          10,13

SQL> select round(10.126, 2) value from dual;

     VALUE
----------
     10,13

SQL> select round(10.126, 3) value from dual;

     VALUE
----------
    10,126


Function Trunc - Retorna um número truncado em n casas.

SQL> select trunc(10.126, 3) from dual;

TRUNC(10.126,3)
---------------
         10,126

SQL> select trunc(10.126, 2) from dual;

TRUNC(10.126,2)
---------------
          10,12

SQL> select trunc(10.126, 1) from dual;

TRUNC(10.126,1)
---------------
           10,1

SQL> select trunc(123456, -1) from dual;

TRUNC(123456,-1)
----------------
          123450

SQL> select trunc(123456, -2) from dual;

TRUNC(123456,-2)
----------------
          123400

SQL> select trunc(123456, -3) from dual;

TRUNC(123456,-3)
----------------
          123000


Function SIGN - Determina se um número é positivo, negativo ou zero.

SQL> select sign(10) from dual;

  SIGN(10)
----------
         1

SQL> select sign(-10) from dual;

 SIGN(-10)
----------
        -1

SQL> select sign(0) from dual;

   SIGN(0)
----------
         0


Function ADD_MONTHS - Adiciona mês a uma data.

SQL> select add_months(to_date('31/01/2010', 'dd/mm/yyyy'), 1) from dual;

ADD_MONT
--------
28/02/10

SQL> select add_months(to_date('31/01/2010', 'dd/mm/yyyy'), -1) from dual;

ADD_MONT
--------
31/12/09


Function LAST_DAY - Retorna a data do último dia do mês.

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

LAST_DAY
--------
31/01/10

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

LAST_DAY
--------
28/02/10


Function MONTHS_BETWEEN - Calcula os meses entre duas datas.

SQL> select months_between(to_date('01/03/2010', 'dd/mm/yyyy'), to_date('01/04/2010', 'dd/mm/yyyy')) meses from dual;

     MESES
----------
        -1

SQL> select months_between(to_date('01/04/2010', 'dd/mm/yyyy'), to_date('01/03/2010', 'dd/mm/yyyy')) meses from dual;

     MESES
----------
         1


Function NEXT_DAY - Retorna a data do primeiro dia da semana especificado em uma string após a data inicial.

SQL> select next_day(sysdate, 'SEXTA') from dual;

NEXT_DAY
--------
22/01/10

SQL> select next_day(sysdate, 'DOMINGO') from dual;

NEXT_DAY
--------
24/01/10


Function TO_CHAR - Converte um número ou data para string.

EXEMPLOS COM DATA

SQL> select to_char(sysdate, 'Month') dt from dual;

DT
------------------------------------
Janeiro

SQL> select to_char(sysdate, 'MON') dt from dual;

DT
------------
JAN

SQL> select to_char(sysdate, 'MONTH') dt from dual;

DT
------------------------------------
JANEIRO

SQL> select to_char(sysdate, 'MM') dt from dual;

DT
--
01

SQL> select to_char(sysdate, 'YYYY') dt from dual;

DT
----
2010

SQL> select to_char(sysdate, 'YYY') dt from dual;

DT
---
010

SQL> select to_char(sysdate, 'YY') dt from dual;

DT
--
10

SQL> select to_char(sysdate, 'RR') dt from dual;

DT
--
10


SQL> select to_char(sysdate, '"Data: "DD/MM/YYYY') dt from dual;

DT
----------------
Data: 19/1/2010

SQL> select to_char(sysdate, 'FMDay, Month, YYYY') dt from dual;

DT
-------------------------------------------------------------------
Terþa-Feira, Janeiro, 2010

SQL> select to_char(sysdate, 'Day, Month, YYYY') dt from dual;

DT
-------------------------------------------------------------------
Terça-Feira  , Janeiro  , 2010


EXEMPLOS COM NÚMEROS

SQL> select to_char(12345.23, '99999999') from dual;

TO_CHAR(1
---------
    12345

SQL> select to_char(12345.23, '99999D99') from dual;

TO_CHAR(1
---------
 12345,23

SQL> select to_char(12345.23, '99999D999') from dual;

TO_CHAR(12
----------
 12345,230

SQL> select to_char(12345.23, '99999D000') from dual;

TO_CHAR(12
----------
 12345,230

SQL> select to_char(12345, '99999D000') from dual;

TO_CHAR(12
----------
 12345,000

SQL> select to_char(12345, '$99999D000') from dual;

TO_CHAR(123
-----------
 $12345,000


SQL> select to_char(12345, 'L99999D000') from dual;

TO_CHAR(12345,'L9999
--------------------
         R$12345,000

-- OBS: Se a máscara for menor que o número, o caracter # será impresso!
SQL> select to_char(12345, '999') from dual;

TO_C
----
####