Creating Function

Functions: Function is similar to a procedure except that a function must return a value
to the statement from which it is called. The syntax for creating a function is given
below.
Syntax: SQL> CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name[IN | OUT | IN OUT] type [,….])]
RETURN type;
{IS | AS}
BEGIN
Function_body
END function_name;
Ex: create or replace function haritha_func(p_empno number)
return varchar2 is
v_ename varchar2(20);
v_sal number;
begin
select ename,sal into v_ename, v_sal from emp where empno=p_empno;
if v_sal>=6000 then
return 'TRUE';
else
return 'FALSE';
end if;
end haritha_func;
Execution: SQL> select haritha_func(7788) from dual;
Ex: SQL> create or replace function haritha_fun(p_radius number)
return number as
v_pi number := 3.141;
v_area number;
begin
v_area := v_pi * POWER(p_radius,2);
return v_area;
end haritha_fun;

Execution: SQL> select haritha_fun(7) from dual;
Dropping Function and Procedure: When we want to drop the function or procedure
then we use this DROP statement the syntax for the DROP is given below.
Syntax for Dropping Function:
DROP FUNCTION function_name;
Syntax for Dropping Procedure:
DROP PROCEDURE procedure_name;