Learn how to create and drop functions in Oracle/PLSQL with syntax and examples.
Just as you can in other languages, you can create your own functions in Oracle.
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:
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';
Once you have created your function in Oracle, you might find that you need to remove it from the database.
The syntax to a drop a function in Oracle is:
DROP FUNCTION function_name;
function_name is the name of the function that you wish to drop.
Let's look at an example of how to drop a function in Oracle.
DROP FUNCTION FindCourse;
This example would drop the function called FindCourse.