Pages

Tuesday, March 19, 2013

What is functions in PL/SQL?


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 cannot be overwritten by the procedure or function.
  2. OUT - The parameter cannot 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’;

No comments:

Post a Comment