Tópico 3 – Using Single-Row Functions to Customize Output

  • Subtópico 3.1 – Describe various types of functions available in SQL
  • Subtópico 3.2 – Use character, number, and date functions in SELECT statements

Continuando a nossa saga, neste post falarei sobre mais um tópico da prova 1Z0-051, que aborda as diversas funções disponíveis para uso no Oracle.

1) Funções disponíveis no SQL

Funções, de uma forma geral, são usadas processar uma entrada, resultando em uma saída processada de acordo com o tipo e propósito dessa função. Eu costumo fazer a seguinte comparação meio esdrúxula: Funções são como pratos de comida, ou seja, nós entramos com os ingrediêntes (parâmetros) e o resultado é um prato pronto (informação processada pela função). As funções mais comuns e possívelmente mais usadas no mundo Oracle são as seguintes:

* Funções de caracteres

– UPPER(S1)

Retorna a string S1, inteiramente em letras maiúsculas.

--retorna os nomes de todos os empregados, em letras maiúsculas
SELECT UPPER(NOME_EMP)
 FROM CERTIFICACAO.TAB_EMPREGADOS

– LOWER(S1)

Retorna a string S1, inteiramente em letras minusculas.

--retorna os nomes de todos os empregados, em letras minúsculas
SELECT LOWER(NOME_EMP)
 FROM CERTIFICACAO.TAB_EMPREGADOS

– INITCAP(S1)

Transforma as iniciais de cada palavra de S1 em maiusculas. Retorna a string S1.

--transforma a primeira letra de cada nome para maiusculas
SELECT INITCAP(NOME_EMP)
 FROM CERTIFICACAO.TAB_EMPREGADOS

– CONCAT(S1, S2)

Concatena as strings S1 e S2 em uma única string.

--une 'A' e 'B' em uma unica string
SELECT CONCAT('A', 'B') FROM DUAL

– LPAD(S1, N1, [S2])

Preenche a esquerda da string S1, com o caractere determinado pela string S2, até que a string S1 fique do tamanho determinado em N1.

-- preenche a esquerda de cada nome de empregado, com o pontos (.) 
-- até que a soma de pontos + caracteres seja igual a 40
SELECT LPAD(NOME_EMP, 40, '.')
 FROM CERTIFICACAO.TAB_EMPREGADOS

– RPAD(S1, N1, [S2])

Preenche a direita da string S1, com o caractere determinado pela string S2, até que a string S1 fique do tamanho determinado em N1.

-- preenche a direita de cada nome de empregado, com o pontos (.)
-- até que a soma de pontos + caracteres seja igual a 40

SELECT RPAD(NOME_EMP, 40, '.')
 FROM CERTIFICACAO.TAB_EMPREGADOS

– LTRIM(S1, [S2])

Remove ocorrências da string S2, da esquerda da string S1. Caso S2 seja omitido, a função remove os brancos a esquerda.

-- remove os traços (-) a esquerda da frase passada no primeiro 
-- parâmetro

SELECT LTRIM('-----ORACLE', '-') FROM DUAL;

– RTRIM(S1, [S2])

Remove ocorrências da string S2, da direita da string S1. Caso S2 seja omitido, a função remove os brancos a direita.

--remove os traços (-) a direita da string

SELECT RTRIM('ORACLE------', '-') FROM DUAL;

– TRIM(info char FROM fonte)

Remove caracteres a esquerda, direita ou de ambos os lados de uma string. Também pode ser usado para remover caracteres em branco das extremidades de uma string. Onde:

  • info: Pode ser LEADING (Caracteres a esquerda), TRAILING (Caracteres a direita), BOTH(Caracteres de ambos os lados). Caso omitido, o padrão é BOTH.
  • char: Caractere a ser removido (Só pode ser um caractere)
  • source: A string fonte. Essa função retorna NULL caso esse parâmetro seja omitido.
--remove os traços (-) presentes a direita da string

SELECT TRIM(TRAILING '-' FROM '------ORACLE------') FROM DUAL;

--remove os traços (-) presentes a esquerda da string

SELECT TRIM(LEADING '-' FROM '------ORACLE------') FROM DUAL;

--remove os traços (-) presentes em ambos os lados da string

SELECT TRIM(BOTH '-' FROM '------ORACLE------') FROM DUAL;

--remove os caracteres em brando de ambas as extremidades da string

SELECT TRIM(' ORACLE ') FROM DUAL;

– LENGTH(S1)

Retorna o tamanho da string S1. Ex:

-- retorna o número de caracteres (mais espaços) da string. 
-- No caso abaixo, retorna 6

SELECT LENGTH('ORACLE') FROM DUAL;

– INSTR(S1, S2, [POS], [N1])

Localiza uma String (S2) dentro de outra string (S1). O parâmetro POS é a posição inicial de S1 onde se iniciará a procura da string S2. N1 é a ocorrência de S2 em S1. Essa função retorna a posição da string S2 dentro da string S1.

-- Retorna a posicao da string 'C' dentro da palavra 'ORACLE'. 
-- Esse exemplo retornara 4

SELECT INSTR('ORACLE', 'C', 1, 1) FROM DUAL

– SUBSTR(S1, POS, [TAM])

Extrai uma substring de S1, iniciando da posição POS, e continuando por TAM caracteres. Caso TAM seja omitido, S1 será verificada por completo até seu fim.

--a partir da posicao

SELECT SUBSTR('ORACLE', 3, 2) FROM DUAL;

OBS1: Pode-se selecionar os últimos caracteres de uma string, usando números negativos.

--retorna os 2 últimos caracteres

SELECT SUBSTR('ORACLE', -2) FROM DUAL;

* Funções numéricas

– ROUND(N, [I])

Arredonda o número N até I casas decimais. Caso I seja omitido, o valor default é 0, arredondando assim N para o número inteiro mais próximo. Caso I seja negativo, N é arredondado I casas para a esquerda.:

SELECT ROUND(100.9996, 2) || ' --- ' || ROUND(25.65) || ' --- ' || ROUND(463.9976, -2) FROM DUAL;

– TRUNC(N, [I])

Remove as casas decimais de N, deixando apenas I casas decimais. Caso I seja omitido, remove todas as casas decimais. É importante mencionar que essa cláusula não arredonda, mas apenas remove as casas decimais:

SELECT TRUNC(100) || ' --- ' || TRUNC(67.5742345, 2) || ' --- ' || TRUNC(23.85485) FROM DUAL;

– MOD(N1, N2)

Retorna o resto da divisão de N1 por N2. Ex:

SELECT MOD(100, 3) || ' --- ' || 
       MOD(100, 6) || ' --- ' || 
       MOD(20, 5) 
  FROM DUAL

* Funções de datas

– SYSDATE

Retorna a data/hora baseada no sistema operacional cujo ORACLE está instalado.

SELECT SYSDATE FROM DUAL;

– ROUND(D1, [FOR])

Arredonda a data D1 para o valor de data mais próximo, especificado pelo formato FOR. O FOR especifica o nível de detalhes cuja data D1 será arredondado.:

-- caso a data atual seja mais da metade ( > 15), arredonda para o 
-- ultimo dia do mes, senão, arredonda para o primeiro dia do mes

SELECT ROUND(SYSDATE, 'DD') FROM DUAL

--arredonda para o primeiro dia do primeiro mes do ano

SELECT ROUND(CURRENT_TIMESTAMP, 'YYYY') FROM DUAL

– TRUNC(D1, [FOR])

Igual ao ROUND, com a diferença que de sempre se arredonda para baixo.

SELECT TRUNC(SYSDATE, 'YY') FROM DUAL;

--retira as horas do resultado.

SELECT TRUNC(SYSDATE) FROM DUAL;

– NEXT_DAY(D1, DS)

Retorna uma data válida representando a primeira ocorrência do dia DS a partir da data D1.

 -- Consulta executada em 05/11/2010(SEXTA-FEIRA)
 -- Ou seja, o proximo domingo, a partir de 05/11, será 07/11

SELECT NEXT_DAY(SYSDATE, 'DOMINGO') FROM DUAL;

– LAST_DAY(D1)

Retorna o último dia do mês, a partir da data D1.

SELECT LAST_DAY(SYSDATE) FROM DUAL;

– ADD_MONTHS(D1, M)

Adiciona M meses a data D1. Para subtrair meses usando o ADD_MONTHS, basta inserir um número negativo em M.

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 5), 'DD/MM/YYYY HH24:MI:SS') 
  FROM DUAL;

– MONTHS_BETWEEN(D1, D2)

Mostra o número de meses entre as datas D1, e D2. Ex:

SELECT MONTHS_BETWEEN(TO_DATE('01/12/2010', 'dd/MM/YYYY'), 
                      TO_DATE('01/11/2010', 'DD/MM/YYYY')) 
  FROM DUAL

 -- A funcao não chega a arredondar os valores. No exemplo abaixo,
 -- existe menos de 1 mês entre as datas.

SELECT MONTHS_BETWEEN(TO_DATE('01/12/2010', 'dd/MM/YYYY'), 
                      TO_DATE('08/11/2010', 'DD/MM/YYYY')) 
  FROM DUAL;

– NUMTOYMINTERVAL(N1, UI)

Transforma o número N1 em um valor que represente a quantidade de tempo representada por UI. UI pode ter os seguintes valores: YEAR e MONTH. O valor retornado pela função é do tipo INTERVAL YEAR TO MONTH.

--30 meses equivales a 2 anos e 6 meses

SELECT NUMTOYMINTERVAL(30, 'MONTH') FROM DUAL;

– NUMTODSINTERVAL(N1, UI)

Transforma o número N1 em um valor que represente a quantidade de tempo representada por UI. UI pode ter os seguintes valores: DAY, HOUR, MINUTE e SECOND. O valor retornado pela função é do tipo INTERVAL DAY TO SECOND.

SELECT NUMTODSINTERVAL(10, 'SECOND') FROM DUAL;
SELECT NUMTODSINTERVAL(30, 'DAY') FROM DUAL;

– CURRENT_DATE

Retorna a data atual no tipo DATE dentro do Timezone da sessão

– CURRENT_TIMESTAMP

Retorna a data atual no tipo TIMESTAMP WITH TIMEZONE, dentro do Timezone da sessão.

– LOCALTIMESTAMP(T)

Retorna a hora local da sessão do usuário, sem o TIMEZONE, no formato TIMESTAMP. Retorna com a precisão de T milisegundos (0 a 9)

– SYSTIMESTAMP

Retorna a data do sistema operacional cujo banco de dados está instalado. O retorno é do tipo TIMESTAMP WITH TIME ZONE.

– NEW_TIME(D1, T1, T2)

Converte a data D1 do Timezone T1 para o Timezone T2.

Então é isso. Até o próximo post.