| Funções para conversão de tipos | |
A linguagem SQL fornece várias funções para controlar a conversão de tipos de dados. Estas funções convertem um valor de um tipo para outro tipo.
TO_DATE(string, máscara de data)
A função TO_DATE() converte do formato cadeia de caracteres para data, seguindo a máscara escolhida pelo utilizador. Os valores possíveis para a mascara estão descritos nesta página.
alter session set nls_territory='PORTUGAL';
alter session set nls_language='PORTUGUESE';
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select
to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss')+1
from dual;
|
alter session set succeeded.
alter session set succeeded.
alter session set succeeded.
TO_DATE('2006-07-1712:15:45','YYYY-MM-DDHH24:MI:SS')+1
-------------------------
2006-07-18 12:15:45
1 rows selected
|
TO_CHAR(data, máscara_data, [nls_date_format] )
A função TO_CHAR(data) converte do formato data para cadeia de caracteres, seguindo a máscara escolhida pelo utilizador. Os valores possíveis para a mascara estão descritos nesta página.
alter session set nls_territory='PORTUGAL'; alter session set nls_language='PORTUGUESE'; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select ename, hiredate, to_char(hiredate,'day, dd "de" Month "de" YYYY') from emp; |
alter session set succeeded. alter session set succeeded. alter session set succeeded. ENAME HIREDATE TO_CHAR(HIREDATE,'DAY,DD"DE"MONTH"DE"YYYY') ---------- ------------------------- ------------------------------------------- SMITH 1980-12-17 00:00:00 quarta-feira , 17 de Dezembro de 1980 ALLEN 1981-02-20 00:00:00 sexta-feira , 20 de Fevereiro de 1981 WARD 1981-02-22 00:00:00 domingo , 22 de Fevereiro de 1981 JONES 1981-04-02 00:00:00 quinta-feira , 02 de Abril de 1981 MARTIN 1981-09-28 00:00:00 segunda-feira, 28 de Setembro de 1981 BLAKE 1981-05-01 00:00:00 sexta-feira , 01 de Maio de 1981 CLARK 1981-06-09 00:00:00 terça-feira , 09 de Junho de 1981 SCOTT 1982-12-09 00:00:00 quinta-feira , 09 de Dezembro de 1982 KING 1981-11-17 00:00:00 terça-feira , 17 de Novembro de 1981 TURNER 1981-09-08 00:00:00 terça-feira , 08 de Setembro de 1981 ADAMS 1983-01-12 00:00:00 quarta-feira , 12 de Janeiro de 1983 JAMES 1981-12-03 00:00:00 quinta-feira , 03 de Dezembro de 1981 FORD 1981-12-03 00:00:00 quinta-feira , 03 de Dezembro de 1981 MILLER 1982-01-23 00:00:00 sábado , 23 de Janeiro de 1982 14 rows selected |
Colocar a máscara em maiúsculas ou minúsculas influencia o resultado:
alter session set nls_territory='PORTUGAL'; alter session set nls_language='PORTUGUESE'; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select ename, hiredate, to_char(hiredate,'DAY, DD "de" MONTH "de" YYYY') from emp; |
alter session set succeeded. alter session set succeeded. alter session set succeeded. ENAME HIREDATE TO_CHAR(HIREDATE,'DAY,DD"DE"MONTH"DE"YYYY') ---------- ------------------------- ------------------------------------------- SMITH 1980-12-17 00:00:00 QUARTA-FEIRA , 17 de DEZEMBRO de 1980 ALLEN 1981-02-20 00:00:00 SEXTA-FEIRA , 20 de FEVEREIRO de 1981 WARD 1981-02-22 00:00:00 DOMINGO , 22 de FEVEREIRO de 1981 JONES 1981-04-02 00:00:00 QUINTA-FEIRA , 02 de ABRIL de 1981 MARTIN 1981-09-28 00:00:00 SEGUNDA-FEIRA, 28 de SETEMBRO de 1981 BLAKE 1981-05-01 00:00:00 SEXTA-FEIRA , 01 de MAIO de 1981 CLARK 1981-06-09 00:00:00 TERÇA-FEIRA , 09 de JUNHO de 1981 SCOTT 1982-12-09 00:00:00 QUINTA-FEIRA , 09 de DEZEMBRO de 1982 KING 1981-11-17 00:00:00 TERÇA-FEIRA , 17 de NOVEMBRO de 1981 TURNER 1981-09-08 00:00:00 TERÇA-FEIRA , 08 de SETEMBRO de 1981 ADAMS 1983-01-12 00:00:00 QUARTA-FEIRA , 12 de JANEIRO de 1983 JAMES 1981-12-03 00:00:00 QUINTA-FEIRA , 03 de DEZEMBRO de 1981 FORD 1981-12-03 00:00:00 QUINTA-FEIRA , 03 de DEZEMBRO de 1981 MILLER 1982-01-23 00:00:00 SÁBADO , 23 de JANEIRO de 1982 14 rows selected |
alter session set nls_territory='PORTUGAL';
alter session set nls_language='PORTUGUESE';
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select
to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'hh:mi:ss') "Hora Minutos Segundos",
to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'scc' ) "Século",
to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'syear' ) "Ano por extenso",
to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'q' ) "Trimestre",
to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'HH24' ) "Hora em formato 24",
to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'HH' ) "Hora formato 12"
from dual;
|
alter session set succeeded. alter session set succeeded. alter session set succeeded. Hora Minutos Segundos Século Ano por extenso Trimestre Hora em formato 24 Hora formato 12 --------------------- ------ ----------------- --------- ------------------ --------------- 12:15:45 21 two thousand six 3 12 12 1 rows selected |
É possível alterar a lingua com que são escritos os meses e dias da semana usando o parâmetro NLS_DATE_FORMAT dentro da própria função:
select to_char(sysdate,'Mon dd hh24:mi:ss','nls_date_language=american') from dual; |
Dec 19 16:30:07 |
EXTRACT(YEAR|MONTH|WEEK|DAY|HOUR|MINUTE|TIMEZONE FROM DATE|TIMESTAMP)
A função EXTRACT surgiu na versão 9i e permite extrair as componentes de uma data. A sua sintaxe genérica é:
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )
|
Tem as seguintes limitações:
Seguem alguns exemplos de utilização:
SELECT EXTRACT(YEAR FROM DATE '2012-05-01') FROM dual; SELECT EXTRACT(MONTH FROM DATE '2012-05-01') FROM dual; SELECT EXTRACT(DAY FROM DATE '2012-05-01') FROM dual; Select extract(year from sysdate) from dual; Select extract(month from sysdate) from dual; Select extract(day from sysdate) from dual; Select extract(year from systimestamp) from dual; Select extract(month from systimestamp) from dual; Select extract(day from systimestamp) from dual; Select extract(hour from systimestamp) from dual; Select extract(minute from systimestamp) from dual; Select extract(second from systimestamp) from dual; Select extract(timezone_hour from systimestamp) from dual; Select extract(timezone_minute from systimestamp) from dual; Select extract(timezone_region from systimestamp) from dual; Select extract(timezone_abbr from systimestamp) from dual; |
TO_CHAR(número, máscara de número)
A função TO_CHAR(número) converte do formato numérico para cadeia de caracteres, seguindo a máscara escolhida pelo utilizador. Os valores possíveis para a mascara estão descritos nesta página.
A mascara '$999,999.99' representa uma quantia monetária que possui parte décimal e separador de milhares. O resultado aparece sempre em formato americano, independentemente das variáveis NLS:
select to_char(sal,'$999,999.99') from emp; |
TO_CHAR(SAL,'$999,999.99')
--------------------------
$800.00
$1,600.00
$1,250.00
$2,975.00
$1,250.00
$2,850.00
$2,450.00
$3,000.00
$5,000.00
$1,500.00
$1,100.00
$950.00
$3,000.00
$1,300.00
14 rows selected
|
A mascara 'L999G999D99' representa a mesma quantia monetária, mas agora a forma como o número é apresentado varia com os valores das variáveis NLS:
alter session set nls_territory='AMERICA'; alter session set nls_language='AMERICAN'; select to_char(sal,'L999G999D99') from emp; alter session set nls_territory='PORTUGAL'; alter session set nls_language='PORTUGUESE'; select to_char(sal,'L999G999D99') from emp; |
alter session set succeeded.
alter session set succeeded.
TO_CHAR(SAL,'L999G999D99')
--------------------------
$800.00
$1,600.00
$1,250.00
$2,975.00
$1,250.00
$2,850.00
$2,450.00
$3,000.00
$5,000.00
$1,500.00
$1,100.00
$950.00
$3,000.00
$1,300.00
14 rows selected
alter session set succeeded.
alter session set succeeded.
TO_CHAR(SAL,'L999G999D99')
--------------------------
¿800,00
¿1.600,00
¿1.250,00
¿2.975,00
¿1.250,00
¿2.850,00
¿2.450,00
¿3.000,00
¿5.000,00
¿1.500,00
¿1.100,00
¿950,00
¿3.000,00
¿1.300,00
14 rows selected
|
TO_NUMBER(string)
A função TO_NUMBER converte de cadeia de caracteres para em número.
select empno, ename, job, sal
from emp
where sal> TO_NUMBER('1500');
|
EMPNO ENAME JOB SAL ---------------------- ---------- --------- ---------------------- 7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7902 FORD ANALYST 3000 7 rows selected |
MASCARA_DATA
A tabela abaixo mostra os valores possíveis para a máscara da data:
| FORMATO | SIGNIFICADO |
|---|---|
| SCC ou CC | Século; 'S' faz preceder de '-' as datas 'BC' (Antes de Cristo) |
| YYYY ou SYYYY | Século; 'S' faz preceder de '-' as datas 'BC' (Antes de Cristo) |
| YYY ou YY ou Y | Ultimos 3 digitos do ano ou 2 ultimos ou último digito do ano |
| Y,YYY | Ano com separador dos milhares nessa posição |
| RR | Ver descrição abaixo |
| SYEAR ou YEAR | Ano por extenso (em inglês); 'S' faz preceder de '-' as datas 'BC' (Antes de Cristo) |
| BC ou AD | Indicador de BC/AD (Antes de Cristo / Depois de Cristo) |
| B.C. ou A.D. | Indicador de BC/AD com ponto |
| Q | Trimestre do ano |
| MM | Mês em número de 1..12 |
| MONTH | Nome do mês preenchido com espaços até 9 caracteres (Setembro) |
| MON | Nome do mês com três letras (JAN, FEB, MAY,...) |
| WW, IW ou W | Semana do ano formato Oracle, semana do ano formato ISO ou semana do mês |
| DDD ou DD ou D | dia do ano, mês ou semana |
| DAY | Nome do dia da semana, por extenso, preenchido com espaços até 9 caracteres |
| DY | Nome do dia da semana com dois caracteres |
| J | Dia do calendário Juliano; o número de dias desde 31 de Dezembro de 4713 A.C. |
| AM ou PM | Indicador de After Moon (AM) ou Post Moon (PM) |
| A.M.ou P.M. | Indicador de After Moon ou Post Moon com pontos |
| HH ou HH12 | Hora do dia em formato 1-12 |
| HH24 | Hora do dia em formato 0-23 |
| MI | Minutos |
| SS | Segundos |
| SSSSS | Segundos decorridos desde a meia-noite |
| /.,: | Os símbolos de pontuação são reproduzidos no resultado |
| "string" | A string é reproduzida no resultado mas tem que vir entre aspas |
| FM | Prefixo que pode ser acrescentado aos códigos anteriores; utilizado em MONTH ou DAY suprime o preenchimento de espaços; usado com números suprime os zeros à esquerda |
| TH, SP, SPTH, THSP | Sufixos que converte um número em ordinal ou por extenso; usando os dois fica número por extenso ordinal |
Quando a máscara é escrita em maiúsculas, o resultado aparece em maiúsculas. Quando a máscara é escrita em minusculas, o resultado aparece em minúsculas.
Para mais informação consultar este link
O ORACLE e o ano 2000
Se utilizar o elemento de formato de data RR em vez de YY, o século no valor produzido varia de acordo com os dois dígitos que especificam o ano e os dois digitos do ano actual.
| Ano devolvido por SYSDATE | Valor formulado (DD-MON-RR) |
Ano interpretado |
|---|---|---|
| 1994 | 27-OUT-95 | 1995 |
| 1994 | 27-OUT-17 | 2017 |
| 2001 | 27-OUT-95 | 1995 |
| 2001 | 27-OUT-17 | 2017 |
Como funciona:
| Se o ano especificado for | |||
|---|---|---|---|
| 0..49 | 50..99 | ||
| Ano Actual | 00..49 | A data devolvida é do século actual | A data devolvida é do século anterior ao actual |
| Ano Actual | 50..99 | A data devolvida é do século seguinte | A data devolvida é do século actual |
Máscara para Número
O quadro abaixo mostra os formatos que estão disponíveis para números.
| Formato | Significado | Exemplo | Resultado |
|---|---|---|---|
| 9 | Posição numérica - o número de 9s determina a largura de visualização | 999999 | 1234 |
| 0 | Zeros antes do número | 099999 | 001234 |
| $ | Símbolo de moeda americana | $999999 | $1234 |
| L | Símbolo de moeda que varia com NLS_TERRITORY | L999999 | ?1234 |
| . | Usar o ponto como separador das casas décimais | 999999.99 | 1234.00 |
| , | Usar a virgula como separador dos milhares (também conhecido por símbolo de grupo) | 999,999 | 1,234 |
| D | Usar um separador décimal que varia com NLS_TERRITORY. Nos países anglo-saxónicos é o ponto. Em Portugal é a virgula | 999999.99 | 1234.00 ou 1234,00 |
| G | Separador dos milhares que varia com NLS_TERRITORY. Nos países anglo-saxónicos é a virgula. Em Portugal é o ponto | 999,999 | 1,234 ou 1.234 |
| MI | Coloca sinal menos à direita nos valores negativos | 999999MI | 1234- |
| PR | Coloca números negativos entre parentisis | 999999PR | (1234) |
| EEEE | Notação científica. O número de 9s depois do ponto define a precisão | 9.9999EEEE | 1.2340E+03 |
| V | Multiplicar por 10n. n=número de 9s depois de V | 9999V99 | 123400 |
| B | Converte os nulos em espaços em branco | B9999.99 | 1234.00 |
| FM | Remover os espaços em branco à esquerda e direita | L999999 | ?1234 |
Quando o número não cabe na máscara o SQL coloca ### em seu lugar.
select to_char(1234,'999') from dual; |
TO_CHAR(1234,'999') ------------------- #### 1 rows selected |
CHR(v1)
Devolve o caracter representado por v1 no código WE8DEC, ISO8859P1 ou outro.
select chr(49) as "CHR(49)", chr(97) as "CHR(97)", chr(38) as "CHR(38)" from dual; |
C C C - - - 1 a & 1 row selected. |
NVL(v1,v2)
Se o valor de v1 for nulo, a função devolve v2.
select sal, comm, sal*14+NVL(comm,0) from emp; |
SAL COMM SAL*14+NVL(COMM,0) ---------------------- ---------------------- ---------------------- 800 11200 1600 300 22700 1250 500 18000 2975 41650 1250 1400 18900 2850 39900 2450 34300 3000 42000 5000 70000 1500 0 21000 1100 15400 950 13300 3000 42000 1300 18200 14 rows selected |
GREATEST(v1,v2,v3)
Devolve o maior dos valores v1, v2 ou v3. A função pode receber mais que 3 argumentos.
select greatest(1,2), greatest(1,2,3), greatest(1,2,3,4) from dual; |
GREATEST(1,2) GREATEST(1,2,3) GREATEST(1,2,3,4) ---------------------- ---------------------- ---------------------- 2 3 4 1 rows selected |
DECODE
A sintaxe do comando DECODE é a seguinte:
DECODE (v, v1, r1 v2, r2 r_default);
Se a expressão v assumir o valor v1 então é colocado o resultado r1, se v2 então r2. Se nenhum desses valores ocorrer então é colocado r_default. Simula um comando if-then-else ou um comando CASE com sinal de igual (=). O número de pares de valores (v1,r1) pode variar. O valor r_default é facultativo.
select
grade,
decode (grade,
1,'15%',
2,'10%',
3,'8%',
'5%') "Bonus"
from salgrade;
|
GRADE Bonus ---------------------- ----- 1 15% 2 10% 3 8% 4 5% 5 5% 5 rows selected |
select
ename,
job,
decode(job,
'CLERK', 'Empregado de escritório',
'ANALYST', 'Analista programador',
'MANAGER', 'Gestor',
'SALESMAN', 'Vendedor',
'PRESIDENT', 'Presidente',
'UNKNOWN') "Job2"
from emp;
|
ENAME JOB Job2 ---------- --------- ----------------------- SMITH CLERK Empregado de escritório ALLEN SALESMAN Vendedor WARD SALESMAN Vendedor JONES MANAGER Gestor MARTIN SALESMAN Vendedor BLAKE MANAGER Gestor CLARK MANAGER Gestor SCOTT ANALYST Analista programador KING PRESIDENT Presidente TURNER SALESMAN Vendedor ADAMS CLERK Empregado de escritório JAMES CLERK Empregado de escritório FORD ANALYST Analista programador MILLER CLERK Empregado de escritório 14 rows selected |
CASE
A instrução CASE é semelhante ao DECODE mas permite comparações com >, >=, <, <=. O exemplo abaixo mostra como substituir o DECODE do ponto anterior por um CASE:
select
ename,
job,
case when job='CLERK' then 'Empregado de escritório'
when job='ANALYST' then 'Analista programador'
when job='MANAGER' then 'Gestor'
when job='SALESMAN' then 'Vendedor'
when job='PRESIDENT' then 'Presidente'
else 'Outra função'
end as "Função"
from emp;
|
ENAME JOB Função ---------- --------- ----------------------- SMITH CLERK Empregado de escritório ALLEN SALESMAN Vendedor WARD SALESMAN Vendedor JONES MANAGER Gestor MARTIN SALESMAN Vendedor BLAKE MANAGER Gestor CLARK MANAGER Gestor SCOTT ANALYST Analista programador KING PRESIDENT Presidente TURNER SALESMAN Vendedor ADAMS CLERK Empregado de escritório JAMES CLERK Empregado de escritório FORD ANALYST Analista programador MILLER CLERK Empregado de escritório 14 rows selected |
A expressão CASE é mais flexivel que DECODE pois suporta outros operadores de comparação além de =:
select sal,
case when sal < 800 then 'baixo'
when sal < 1500 then 'médio'
when sal < 2500 then 'médio alto'
else 'alto'
end as Salario
from emp;
|
SAL SALARIO
---------- ----------
800 médio
1600 médio alto
1250 médio
2975 alto
1250 médio
2850 alto
2450 médio alto
3000 alto
5000 alto
1500 médio alto
1100 médio
950 médio
3000 alto
1300 médio
14 rows selected
|
Realizado por Turtle Learning ®. Última alteração em 2012-05-01
Topo