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

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;