Creating Functions

In Oracle, you can create your own functions.

The syntax for a function is:

CREATE [OR REPLACE] FUNCTION function_name

[ (parameter [,parameter]) ]

RETURN return_datatype

IS | AS

[declaration_section]

BEGIN

executable_section

[EXCEPTION

exception_section]

END [function_name];

When you create a procedure or function, you may define parameters. There are three

types of parameters that can be declared:

1.

IN

- The parameter can be referenced by the procedure or function. The value of

the parameter can not be overwritten by the procedure or function.

2.

OUT

- The parameter can not be referenced by the procedure or function, but the

value of the parameter can be overwritten by the procedure or function.

3.

IN OUT

- The parameter can be referenced by the procedure or function and the

value of the parameter can be overwritten by the procedure or function.

The following is a simple example of a function:

CREATE OR REPLACE Function FindCourse

( name_in IN varchar2 )

RETURN number

IS

cnumber number;

cursor c1 is

select course_number

from courses_tbl

where course_name = name_in;

BEGIN

open c1;

fetch c1 into cnumber;

if c1%notfound then

cnumber := 9999;

end if;

close c1;

RETURN cnumber;

EXCEPTION

WHEN OTHERS THEN

raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-

'||SQLERRM);

END;

This function is called FindCourse. It has one parameter called

name_in

and it returns a

number. The function will return the course number if it finds a match based on course

name. Otherwise, it returns a 99999.

You could then reference your new function in an SQL statement as follows:

select course_name, FindCourse(course_name) as course_id

from courses

where subject = 'Mathematics'; Download Here Click Here