Oracle Functions

---

In Oracle, you can create your own functions.

The syntax for create 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 Oracle procedure or function, you may define parameters. There are three types of parameters that can be declared:

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. 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. 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 oralce 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 Oracle SQL statement as follows:

select course_name, FindCourse(course_name) as course_id from courses where subject = 'Mathematics';