Creating Functions in Oracle

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;