Neste artigo vou falar sobre Funções de uma linha do Oracle SQL que são incorporadas no Oracle. Esse tipo de função são utilizadas dentro do PL/SQL e do SQL é claro, geralmente em instruções DML. Se você não sabe o que é SQL recomento a leitura deste artigo.
Quando nos aprofundamos no desenvolvimento em PL/SQL aprendemos como fazer essas funções, porém para o momento vamos apenas focar nas principais funções que já são nativas do Oracle. Existem quatro tipos de funções SQL nativas do Oracle que são Funções Numéricas, Funções de Carácter, Funções de Data e Funções de Conversão. Segue abaixo uma descrição de cada tipo das funções Oracle.
O que vamos falar nesse artigo!
Tipos de Funções Oracle SQL
Funções SQL Numéricas: Essas funções basicamente recebem um número e retornam um número, por exemplo para fazer uma média, ou uma soma existem funções que serão descritas logo abaixo.
Funções SQL de Carácter: Este tipo de função aceitam somente carácteres como parâmetros de entrada, mas elas retornam tanto carácteres como números, um exemplo é se você deseja contar a quantidade de carácteres de uma determinada string ou a posição de um carácter dentro de uma string.
Funções SQL de Data: Essa variação de função recebe parâmetros do tipo DATE e retornam parâmetros dos tipos DATE e NUMBER, este tipo retorna por exemplo o tipo de data atual do sistema ou a quantidade de meses entre duas datas
Funções SQL de Conversão: Este é o quarto e ultimo tipo de função SQL que temos nativas do Oracle, neste tipo conseguimos converter um valor de entrada em outro valor na sua saída após executar a função, temos um número e queremos converte-lo em uma string, para isso usamos uma função de conversão.
Estes são os quatro tipos de funções SQL nativas do Oracle que temos, mas antes vou explicar uma tabela virtual nativa do Oracle, que é a DUAL, basicamente ela é uma tabela que usamos para testes, sejam eles para cálculos, retorno de funções entre outros, que provavelmente faremos ao longo do curso. Abaixo seguem alguns exemplos da utilização da tabela DUAL, com alguns SELECTs.
Funções Numéricas Oracle
ABS(X)
Retorna o valor absoluto de um número, ou seja se você entrar com um valor negativo ele vai retornar um valor positivo e se o valor de entrada for positivo ele vai retornar um valor positivo. Por exemplo se colocar select ABS(-30) from dual; ou select ABS(30 from dual; o resultado será o mesmo “30”
CEIL(x)
Vai retornar o número arredondado para cima, por exemplo: select ceil (3.45) from dual o seu retorno vai ser 4. Preste bastante atenção, pois se o valor for negativo o maior valor seria o menor, por causa disso neste exemplo select ceil(-3.45) from dual; retornará o valor -3.
FLOOR
Essa função FLOOR é o contrário da última, ela vai retornar sempre o número arredondado para baixo, então usando os mesmos exemplos da última função select floor(3.45) from dual; o retorno vai ser 3 e quando executarmos select floor (-3.45) from dual; o retorno vai ser 4.
TRUNC
O Trunc retira os valores que vem após a virgula até a quantidade de casas decimais definida em y, se y não for definido então ele vai retirar todos os números após a virgula, isto sem fazer nenhum arredondamento. Exemplo: select TRUNC(3.45, 1) from dual; irá retornar 3.4, agora se fizermos assim select TRUNC(3.45) from dual; irá retornar apenas 3
ROUND(x, y)
A função Round faz algo semelhante ao TRUNC, porém com uma pequena variação, ele ao invés de apenas retirar os valores como o TRUNC, ele arredonda os valores seguindo as regras de maior ou igual a 5 ele arredonda para cima e menor que 5 ele arredonda para baixo. Exemplos: select round(3.45, 1) from dual; teremos como retorno 3.4 , já se fizermos select round(3.45) from dual teremos como retorno 3.
Funções de Caracter Oracle
LOWER
Esta função colocará todos os carácteres de uma string em caixa baixa (letras minúsculas). Exemplo: select LOWER(‘Fazendo Teste’) from dual; teremos retorno de “fazendo teste”.
UPPER
Ela faz exatamente o oposto da LOWER, ou seja todos os carácteres da string ficarão em caixa alta. Exemplo: select UPPER(‘Fazendo Teste’) from dual; eremos retorno de “FAZENDO TESTE”.
INITCAP
O INITCAP faz algo semelhante ao UPPER e ao LOWER, porém com uma diferença, ele vai colocar o primeiro carácter de qualquer palavra em caixa alta e o restante dos caracteres da palavra em caixa baixa. Exemplo: select INITCAP (‘fazeNdo Teste’) from dual; terá o seguinte retorno “Fazendo Teste”.
LTRIM
Esta função vai retirar da primeira string o valor definido em valor_string_2, isso da esquerda para a direita. Exemplo select LTRIM(‘fazendo Teste fazendo’, ‘fazendo’) from dual; ao executar este comando teremos o seguinte retorno “Teste fazendo”
RTRIM
O RTRIM faz exatamente o mesmo processo do LTRIM, porém faz da direita para esquerda, então se executarmos a query select LTRIM(‘fazendo Teste fazendo’, ‘fazendo’) from dual; teremos o seguinte resultado “fazendo Teste”.
TRIM
Esta função irá retirar o primeiro carácter definido no valor_string_retirado do valor definido em valor_string. Exemplo: select TRIM(‘a’ from ‘a fazendo teste’) from dual; terá o seguinte retorno “fazendo teste”
SUBSTR Oracle
O Oracle Substr irá retornar a quantidade y de caracteres definidos a partir da posição x. Exemplo: select SUBSTR(‘fazendo teste’, 9, 5) from dual; ele irá retornar 5 caracteres a partir da posição nove, sendo assim teremos o retorno de “teste”
LENGTH
O LENGTH vai retornar a quantidade de caracteres que temos na string. Exemplo select LENGTH(‘fazendo teste’) from dual; teremos o retorno de 13.
LPAD
Esta função irá definir um tamanho que a string será retornada, a partir deste valor, caso o valor_string for menor que o valor definido em x ele completará o restante deste valor com o carácter definido em valor_pad, isso sendo feito da esquerda para a direita. Se este valor for maior ele cortará os caracteres a mais da direita para esquerda. Exemplos: select LPAD(‘fazendo teste’, 10, ‘*’) from dual; temos o seguinte retorno: “fazendo te”, agora se fizermos a seguinte query select LPAD(‘fazendo teste’, 15, ‘*’) from dual; o Oracle irá retornar “**fazendo teste”
RPAD
O RPAD vai fazer o mesmo que o LPAD a única diferença é que nos casos onde a quantidade de caracteres da string alor_string for menor que o definido em x o valor definido em valor_pad será preenchido na direita. Exemplos: select RPAD(‘fazendo teste’, 10, ‘*’) from dual; temos o seguinte retorno: “fazendo te”, agora se fizermos a seguinte query select RPAD(‘fazendo teste’, 15, ‘*’) from dual; o Oracle irá retornar “fazendo teste**”
Funções SQL de Data
ADD_MONTHS
Irá adicionar a quantidade de meses definido em x à data definida em valor_data. Exemplo select ADD_MONTHS (to_date(’16/08/2013′,’DD/MM/RRRR’), 3) from dual; teremos o seguinte retorno ’16/11/2013′. p.s. O TO_DATE vocês vão aprender logo mais, só usei porque não sei que padrão de data foi instalado no Oracle de vocês.
MONTHS_BETWEEN
Retorna a quantidade de meses entre a valor_data_1 e a valor_data_2. Exemplo select MONTHS_BETWEEN (to_date(’16/11/2013′,’DD/MM/RRRR’), to_date(’16/08/2013′,’DD/MM/RRRR’)) from dual; teremos o retorno de 3.
SYSDATE
O SYSDATE retorna a data atual do servido, sendo que se adicionar algum número inteiro ou subtrairmos ele vai adicionar / subtrair a quantidade de dias definidos neste número. Exemplo: select sysdate from dual; como hoje é dia 07/11/2013 ele irá retorna examente esta data, agora se eu adicionar select sysdate + 1 from dual; ele irá retornar 08/11/2013
NEXT_DAY
Esta função retornará o próximo dia da semana descrito em dia_semana “Este campo deve ser inserido em inglês” em relação ao valor definido em valor_data. Exemplo: Vamos descobrir quando é o próximo domingo em relação a hoje. select NEXT_DAY(sysdate, ‘Sunday’) from dual; como hoje é dia 07/11/2013 teremos como retorno o dia 10/11/2013
LAST_DAY
Retorna o ultimo dia do mês da data definida em valor_data. Exemplo: select last_day (sysdate) from dual; como novembro tem apenas 30 dias teremos o retorno de 30/11/2013.[/well] p.s. As funções de carácter ROUND e TRUNC também funcionam para parâmetros de data, com as diretrizes idênticas às definidas acima. Exemplos: select trunc(to_date(’07/11/2013 23:57′, ‘dd/mm/rrrr hh24:mi’)) from dual; temos o retorno de “07/11/2013” já neste caso select round(to_date(’07/11/2013 23:57′, ‘dd/mm/rrrr hh24:mi’)) from dual;, temos o retorno de “08/11/2013”.
Funções SQL de Conversão.
TO_CHAR
Converte o valor definido em x para carácter, podemos inserir um valor numérico ou de data em x e y é opcional, sendo a mascara de retorno deste valor. Exemplos: select to_char(‘4000’, ‘$9999’) from dual; temos o retorno de $4000, select to_char(sysdate, ‘DD/MM/YYYY’) from dual; temos o retorno da data atual, no meu caso 07/11/2013.
TO_DATE
Esta função converte o valor definido em x para data na máscara definida em formato_data. Exemplo: select to_date(’03/12/2013′, ‘DD/MM/RRRR’) + 3 from dual; teremos o retorno de “06/11/2013”, porque estamos adicionando 3 dias à data definida na função
NVL
O NVL vai retornar o valor de y caso x seja um valor nulo, caso contrário irá retornar x. Exemplo: select NVL(NULL, 10) from dual; temos o retorno de 10, agora neste caso select NVL(2, 10) from dual; temos o retorno de 2
DECODE Oracle
Estão função é o IF dentro do select, ela pega como padrão o primeiro valor definido em a e partir daí vê. Se “a” for igual a “b” então ele retornará “c”, no caso de “a” for igual “d” então retorna “e”, se “a” não for igual a nenhum valor definido ele vai retornar o valor_padrão. Exemplo: select DECODE(2, 2, 10, 3, 20, 100) from dual; vai retornar 10, select DECODE(3, 2, 10, 3, 20, 100) from dual; irá retornar 20, select DECODE(4, 2, 10, 3, 20, 100) from dual; irá retornar 100.
Conclusão
Agora fechamos a nossa aula com algumas funções SQL nativas do Oracle, lembrando que esta são as principais funções, com as suas principais variações. Se vocês gostaram, tem dúvidas ou gostariam de acrescentar algo, comentem :).
Link permanente
Muito bom!!!!
Link permanente
Muito bom!!!!
Link permanente
Muito bom!!!!
Link permanente
Manero… to revisando tudo que ja conhecia… e aqui estou aprofundando isto. valews.
Link permanente
Muito bom!!!!
Link permanente
Excelente o material e a forma de abordagem, super completo e de forma que fica cansativo a leitura.
Recomendo o aprendaplsql.com para todos.
Link permanente
Oi Igor,
Muito obrigado pelo seu comentário, fico muito feliz mesmo por você recomendar o nosso material, se você puder compartilhar ele com os seus amigos vai nos ajudar muito a disseminar o nosso material.
abs
William Miranda
Link permanente
Muito bom essa aula . Uma forma bem prática para o aprendizado.
Link permanente
Oi Paulo,
Muito obrigado mesmo pela seu comentário, são comentários assim que nos incentivam a continuar produzindo material.
abs
William Miranda
Link permanente
Muito bom, você poderia dar um exemplo com explicação sobre OVER PARTITION BY
Link permanente
Oi Nilson,
Esta é realmente é uma boa, mas como não abordamos funções analíticas neste artigo então ela acabou não sendo abordado. Mas quem sabe em breve não escrevemos algo sobre ela na nossa wiki que estamos para lançar.
abs
William Miranda
Link permanente
Muito bom o conteúdo. ..mas precisava de uma ajuda…poderia falar sobre o random para gerar números aleatórios…
Obrigado. .
Link permanente
Oi Luís muito obrigado mesmo pelo comentário!
E obrigado pela dica em breve lançaremos uma WIKI com várias informações de funções Oracle.
abs
William Miranda
Link permanente
Link permanente
Parabéns pela iniciativa. Essas informações são muito importantes no meu dia-a-dia de trabalho.
Link permanente
Oi Veronildo,
Muito obrigado pela sua mensagem. Espero que este material realmente tenha te ajudado.
Um grande abraço!
William Miranda
Link permanente
No seu exemplo da função round tem: “select round(3.45, 1) from dual; teremos como retorno 3.4 ”
O resultado não será 3.5?
Link permanente
Oi George, tudo bem?
Isso mesmo!
abs
William Miranda
Link permanente
LPAD(F_CALCSTPR(1, GIT_COD_ITEM, DATETO_RMS7(SYSDATE), 1),5,’0′) PODE ME AJUDAR NÃO ENTENDO OQUE SERIA ESSE F_CALCSTPR(1, GIT_COD_ITEM, DATETO_RMS7(SYSDATE), 1),5,’0′ ISTO SERIA UMA FUNÇÃO “F_CALCSTPR”??? PODERIA ME AJUDAR
Link permanente
Oi Nicolas, tudo bem?
Essa função na verdade é uma função criada por algum programador, para entender a lógica dela seria necessário ver o código que foi escrito.
abs
William Miranda
Link permanente
Parabéns Willian, sempre ajudando!
Link permanente
Oi Rafael, tudo bem?
Muito obrigado pelo seu comentário, fico feliz que você tenha gostado do nosso conteúdo.
abs
William Miranda