Oracle / PLSQL: Functions
This Oracle tutorial explains how to create and drop functions in Oracle/PLSQL with syntax and examples.
Create Function
Just as you can in other languages, you can create your own functions in Oracle.
Syntax
The syntax to create a function in Oracle 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:
- 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.
Example
Let's look at an example of how to create a function in Oracle.
The following is a simple example of an Oracle 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 a SQL statement as follows:
SELECT course_name, FindCourse(course_name) AS course_id
FROM courses
WHERE subject = 'Mathematics';
Drop Function
Once you have created your function in Oracle, you might find that you need to remove it from the database.
Syntax
The syntax to a drop a function in Oracle is:
DROP FUNCTION function_name;
- function_name
- The name of the function that you wish to drop.
Example
Let's look at an example of how to drop a function in Oracle.
For example:
DROP FUNCTION FindCourse;
This example would drop the function called FindCourse.
