Oracle Application Developer

YES YOU CAN USE DML(SELECT) WITHIN User defined function(UDF). BUT KEEP IN MIND THAT UDF CAN ONLY RETURN ONE SINGLE VALUE. BUT YOU CANT USE DDL(CREATE/ALTER/DROP) WITHIN UDF. TO DO THAT YOU HAVE TO USE SP(Stored Procedure).

DML EXAMPLE:

CREATE OR REPLACE Function IncomeLevel

( name_in IN varchar2 )

RETURN varchar2

IS

monthly_value number(6);

ILevel varchar2(20);

cursor c1 is

select monthly_income

from employees

where name = name_in;

BEGIN

open c1;

fetch c1 into monthly_value;

close c1;

IF monthly_value <= 4000 THEN

ILevel := 'Low Income';

ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN

ILevel := 'Avg Income';

ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN

ILevel := 'Moderate Income';

ELSE

ILevel := 'High Income';

END IF;

RETURN ILevel;

END;