Using Functions in Oracle

This article is written about how to create and drop features in Oracle/PLSQL with syntax and examples.

Create Function

Just as you can in different languages, you can create your very 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 outline parameters. There are three sorts of parameters that can be declared:

IN – The parameter can be referenced through the manner or function. The cost of the parameter can not be overwritten by using the manner or function. OUT – The parameter can now not be referenced with the aid of the technique or function, but the cost of the parameter can be overwritten by using the method or function. IN OUT – The parameter can be referenced with the aid of the method or feature and the price of the parameter can be overwritten by way of the manner or function.

Example

Let’s seem to be at an example of how to create a function in Oracle.

The following is a easy 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 characteristic is known as FindCourse. It has one parameter known as name_in and it returns a number. The feature will return the course range if it finds a fit primarily based on route name. Otherwise, it returns a 99999.

You ought to then reference your new feature in a SQL announcement as follows:

SELECT course_name, FindCourse(course_name) AS course_id
FROM courses
WHERE subject = 'Mathematics';

Drop Function

Once you have created your characteristic in Oracle, you might locate 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 title of the characteristic that you desire to drop.

Example

Let’s seem to be at an instance of how to drop a feature in Oracle.

For example:

DROP FUNCTION FindCourse;

This instance would drop the feature referred to as FindCourse.