Here is a small description about creating and dropping functions in oracle.
Creating functions in oracle is just similar to creating functions in other languages.
Syntax
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];
As we know that while creating a function we always define parameters: IN, OUT and INOUT.
Example
CREATE OR REPLACE Function FindAuthor
( name_in IN varchar2 )
RETURN number
IS
Anumber number;
cursor c1 is
SELECT Author_number
FROM Authors_tbl
WHERE Author_name = name_in;
BEGIN
open c1;
fetch c1 into Anumber;
if c1%notfound then
Anumber := 9999;
end if;
close c1;
RETURN Anumber;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Now, to drop a Function we write:
Syntax
DROP FUNCTION function_name;
Example
DROP FUNCTION FindAuthor;