Funções SQL Nativas do Oracle – Curso Oracle PL/SQL

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.

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 :).