FUNCIONES EN ORACLE
FUNCIONES EN ORACLE
***********************
CREATE OR REPLACE FUNCTION f_sumar(n1 number, n2 number)
return number
IS
rpta number;
BEGIN
rpta:=n1+n2;
return (rpta);
END;
select f_sumar(52,121) from dual;
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_cubos(num number)
return number
IS
v_cubo number;
BEGIN
return (num*num*num);
END;
select f_cubos(2) from dual;
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_promedio(nt1 number, nt2 number, nt3 number, nt4 number)
return number
IS
BEGIN
return ((nt1+nt2+nt3+nt4)/4);
END;
select f_promedio(14,15,13,11) from dual;
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_maximo(num1 number, num2 number)
return number
IS
may number;
BEGIN
IF (num1>num2) THEN
may:= num1;
ELSE
may:= num2;
END IF;
RETURN may;
END;
select f_maximo(14,15)from dual;
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_anio(fecha DATE)
RETURN NUMBER
AS
v_anio NUMBER(4);
BEGIN
v_anio := TO_NUMBER(TO_CHAR(fecha, 'YYYY'));
RETURN v_anio;
END anio;
select f_anio(to_date('07/02/1985','DD/MM/YYYY'))from dual;
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_sueldo(cargo varchar2)
return number
IS
v_sueldo number;
BEGIN
IF upper(cargo)='ANALISTA' THEN
v_sueldo:=3500;
ELSIF upper(cargo)='PROGRAMADOR' THEN
v_sueldo:=3000;
ELSIF upper(cargo)='SOPORTE' THEN
v_sueldo:=1500;
ELSE
v_sueldo:=0;
END IF;
RETURN (v_sueldo);
END;
select f_sueldo('programador') from dual;
-------------------------------------------------------------------
CREATE OR REPLACE FUNCTION factorial (pNum number)
RETURN number
IS
BEGIN
if pNum = 0 then
return 1;
else
return pNum * factorial(pNum - 1);
end if;
END;
-------------------------------------------------------------------
--by Ing. Jhonatan Abal Mejia
***********************
CREATE OR REPLACE FUNCTION f_sumar(n1 number, n2 number)
return number
IS
rpta number;
BEGIN
rpta:=n1+n2;
return (rpta);
END;
select f_sumar(52,121) from dual;
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_cubos(num number)
return number
IS
v_cubo number;
BEGIN
return (num*num*num);
END;
select f_cubos(2) from dual;
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_promedio(nt1 number, nt2 number, nt3 number, nt4 number)
return number
IS
BEGIN
return ((nt1+nt2+nt3+nt4)/4);
END;
select f_promedio(14,15,13,11) from dual;
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_maximo(num1 number, num2 number)
return number
IS
may number;
BEGIN
IF (num1>num2) THEN
may:= num1;
ELSE
may:= num2;
END IF;
RETURN may;
END;
select f_maximo(14,15)from dual;
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_anio(fecha DATE)
RETURN NUMBER
AS
v_anio NUMBER(4);
BEGIN
v_anio := TO_NUMBER(TO_CHAR(fecha, 'YYYY'));
RETURN v_anio;
END anio;
select f_anio(to_date('07/02/1985','DD/MM/YYYY'))from dual;
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_sueldo(cargo varchar2)
return number
IS
v_sueldo number;
BEGIN
IF upper(cargo)='ANALISTA' THEN
v_sueldo:=3500;
ELSIF upper(cargo)='PROGRAMADOR' THEN
v_sueldo:=3000;
ELSIF upper(cargo)='SOPORTE' THEN
v_sueldo:=1500;
ELSE
v_sueldo:=0;
END IF;
RETURN (v_sueldo);
END;
select f_sueldo('programador') from dual;
-------------------------------------------------------------------
CREATE OR REPLACE FUNCTION factorial (pNum number)
RETURN number
IS
BEGIN
if pNum = 0 then
return 1;
else
return pNum * factorial(pNum - 1);
end if;
END;
-------------------------------------------------------------------
--by Ing. Jhonatan Abal Mejia
Comentarios