Funções – Functions no Oracle
Ao contrário das procedures as Funções (ou Functions) são blocos PL/SQL que devem obrigatoriamente retornar ao menos um valor. Uma função manipula o conteúdo de uma coluna em uma declaração SQL. Ao se usar uma função em uma declaração SQL, o valor da coluna sobre o qual a função é executada é alterado quando apresentado.
Sintaxe Básica:
CREATE [OR REPLACE] FUNCTION nome_da_função
[( parameter1 [ mode1] datatype1,
parameter2 [ mode2] datatype2,
. . .)]
RETURN tipo_de_dado
IS|AS
Bloco PL/SQL;Ao contrário das procedures as funções tem que retornar ao menos um valor.
CREATE OR REPLACE FUNCTION pega_sal
(p_id IN emp.empno%TYPE)
RETURN NUMBER
IS
v_sal emp.sal%TYPE :=0;
BEGIN
SELECT sal
INTO v_sal
FROM scott.emp
WHERE empno = p_id;
RETURN v_sal;
END pega_sal;
/Executando
VARIABLE g_sal NUMBER
EXECUTE :g_sal := pega_sal(7839)
PRINT g_sal
Função para calcular CPMF
CREATE OR REPLACE FUNCTION cpmf(p_value IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (p_value * 0.038);
END cpmf;
/
SELECT empno, ename, sal, cpmf(sal)
FROM scott.emp
WHERE deptno = 10;Funções de valores simples:
ABS(n)= Devolve o valor absoluto de (n).
CEIL(n)=Obtém o valor inteiro imediatamente superior ou igual a “n”.
FLOOT(n) = Devolve o valor inteiro imediatamente inferior ou igual a “n”.
MOD (m, n)= Devolve o resto resultante de dividir “m” entre “n”.
NVL (valor, expressão)= Substitui um valor nulo por outro valor.
POWER (m, exponente)= Calcula a potência de um número.
ROUND (numero [, m])= Arredonda números com o número de dígitos de precisão indicados.
SIGN (valor)= Indica o signo do “valor”.
SQRT(n)= Devolve a raiz quadrada de “n”.
TRUNC (numero, [m])= Trunca números para que tenham uma certa quantidade de dígitos de precisão.
VAIRANCE (valor)= Devolve a média de um conjunto de valores.
Funções de grupos de valores:
AVG(n)= Calcula o valor médio de “n” ignorando os valores nulos.
COUNT (* | Expressão)= Conta o número de vezes que a expressão avalia algum dado com valor não nulo. A opção “*” conta todas as filas selecionadas.
MAX (expressão)= Calcula o máximo.
MIN (expressão)= Calcula o mínimo.
SUM (expressão)= Obtém a soma dos valores da expressão.
GREATEST (valor1, valor2…)= Obtém o maior valor da lista.
LEAST (valor1, valor2…)= Obtém o menor valor da lista.
Funções que devolvem valores de caracteres:
CHR(n) = Devolve o caractere cujo valor em binário é equivalente a “n”.
CONCAT (cad1, cad2)= Devolve “cad1” concatenada com “cad2”.
LOWER (cad)= Devolve a cadeia “cad” em minúsculas.
UPPER (cad)= Devolve a cadeia “cad” em maiúsculas.
INITCAP (cad)= Converte a cadeia “cad” a tipo título.
LPAD (cad1, n[,cad2])= Adiciona caracteres à esquerda da cadeia até que tenha uma certa longitude.
RPAD (cad1, n[,cad2])= Adiciona caracteres à direita até que tenha uma certa longitude.
LTRIM (cad [,set])= Suprime um conjunto de caracteres à esquerda da cadeia.
RTRIM (cad [,set])= Suprime um conjunto de caracteres à direita da cadeia.
REPLACE (cad, cadeia_busca [, cadeia_substitucao])= Substitui um caractere ou caracteres de uma cadeia com 0 ou mais caracteres.
SUBSTR (cad, m [,n])= Obtém parte de uma cadeia.
TRANSLATE (cad1, cad2, cad3)= Converte caracteres de uma cadeia em caracteres diferentes, segundo um plano de substituição marcado pelo usuário.
Funções que devolvem valores numéricos:
ASCII(cad)= Devolve o valor ASCII da primeira letra da cadeia “cad”.
INSTR (cad1, cad2 [, comeco [,m]])= Permite uma busca de um conjunto de caracteres em uma cadeia, mas não suprime nenhum caractere depois.
LENGTH (cad)= Devolve o número de caracteres de cad.
Funções para o manejo de datas:
SYSDATE= Devolve a data do sistema.
ADD_MONTHS (data, n)= Devolve a data “data” incrementada em “n” meses.
LASTDAY (data)= Devolve a data do último dia do mês que contém “data”.
MONTHS_BETWEEN (data1, data2)= Devolve a diferença em meses entre as datas “data1” e “data2”.
NEXT_DAY (data, cad)= Devolve a data do primeiro dia da semana indicado por “cad” depois da data indicada por “data”.
Funções de conversão:
TO_CHAR= Transforma um tipo DATE ou NUMBER em uma cadeia de caracteres.
TO_DATE= Transforma um tipo NUMBER ou CHAR em DATE.
TO_NUMBER= Transforma uma cadeia de caracteres em NUMBER.