Introduction

Functions are code modules that are “self-contained” and perform a single task. Functions frequently “take in” data, process it, and “return” a result. Once you’ve written a function, you can use it repeatedly. Other functions can “call” functions from within them.

Advantages of writing a function:

1. They enable us to think of our program as a series of steps. (Each sub-step can be treated as a separate function.) If a program appears to be too difficult, simply break it down into sub-steps)
2. They enable us to reuse code rather than rewrite it.
3. We can keep our variable namespace tidy by using functions (local variables only “live” as long as the function does). In other words, function 1 and function 2 can both utilize I as a variable without causing any confusion. Each variable I exists only during the execution of the specified function by the computer.
4. We can test tiny pieces of our software separately from the rest using functions. This is notably true in interpreted languages like Matlab, but it can also be useful in C, Java, ActionScript, and other scripting languages.

What are Oracle Functions

Functions are a very powerful feature of Oracle. They can be used for many things, including:

• Perform calculations on data
• Modify individual data items
• Manipulate output for groups of rows
• Format dates and numbers for display
• Convert column data types

SQL functions take parameters on occasionally but always return a value.

Types of Oracle Functions

There are two types of functions

1) Single-row functions

Syntax :

Function name (arg1,agr2);

A. Character functions: It accepts character input and returns both character and numerical values

Example:

CONCAT(‘Hello’, ‘World’)

Output:

HelloWorld

B. Number functions: Accept numeric data and return numeric results.

Example:

ROUND(45.926, 2)

Output:

45.93

C. Date functions: Use the DATE data type to perform operations. (Except for the MONTHS BETWEEN function, which produces a number, all date functions return a DATE data type value.)

Example:

MONTHS_BETWEEN

(’01-SEP-95′,’11-JAN-94′);

Output:

19.6774194

D. General functions:

i) NVL:

Example:

SELECT  NVL(100,200) FROM dual;

Output:

100 (Because the first argument is not null)

ii) NVL2:

Example:

SELECT NVL2(NULL, 1, 2) FROM dual;

Output:

2(Because the first argument is null)

iii) NULLIF:

Example:

SELECT NULLIF (100,100) FROM dual;

Output:

NULL(the above statement returns a null value because the first argument equals the second one)

iv) COALESCE:

Example:

SELECT COALESCE(NULL, NULL, NULL, ‘Oracle’, NULL, ‘Example.com’);

Output:

Oracle

v) CASE:

Example:

SELECT ‘The job id for ‘||UPPER(last_name)||’ is ‘||LOWER(job_id) AS “EMPLOYEE DETAILS” FROM employees;

Output:

The job id for DAVIES is st_clerk

vi) DECODE:

Example:

SELECT employee_id, salary

FROM employees

ORDER BY DECODE(‘S’, ‘F’,first_name, ‘L’,last_name,’S’,salary);

2) Multiple-row functions

When working with the group by clause, the multi-row function in SQL is utilized to get data per set of rows.

Types of Multiple-row functions

A. Maximum (MAX)

From the given data, returns the maximum value.

Example:

Select max (Salary)

from employees;

Output:

10,0000

B. Minimum (MIN)

From the given data, returns the minimum value

Example:

Select min (Salary)

from employees;

Output:

1000

C. Average (AVG)

It calculates the average value of the provided expression.

Select avg (sal) from emp;

Output:

2250.725

D. SUM

The sum of the “Quantity” field in the “Order Details” table should be returned.

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

Output:

E. COUNT

Counts the items from a table/column

Example:

Select count(empno) from employee;

Output:

100(Consider that employees table have 100 employees)

How to create Functions in Oracle

You can write your own functions in Oracle, just like you can in other programming languages.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]
   RETURN return_datatype
IS | AS
   [declaration_section]
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [function_name];

Explanation of all the parameters:

IN – The procedure or function can refer to the parameter. The procedure or function cannot overwrite the value of the parameter.

OUT – The parameter cannot be referenced by the procedure or function, but the parameter’s value can be overwritten.

IN OUT – The procedure or function can refer to the parameter, and the parameter’s value can be rewritten by the process or function.

Example:

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;

FindCourse is the name of this function. It takes only one parameter, name in, and returns a number. If a match is found based on the course name, the method will return the course number. Otherwise, a 99999 is returned.

Oracle Recursive Function

A recursive FUNCTION in PL/SQL is a function that either calls itself or is part of a possible cycle of function calls. There are two sorts of recursive functions, as stated in the definition. A recursive call occurs when a subprogram calls itself, and the process is known as recursion.

Example:

create or replace function mul (n number) return number as
begin
      IF n <= 1 THEN
            dbms_output.put_line(to_char (n) ||' x 3 = '|| to_CHAR (3*n));
            RETURN 1;   
      ELSE
            dbms_output.put_line(to_char (n) ||' x 3 = '|| to_CHAR (3*n));   
            return mul(n-1);
      end if;
end;

Output:

select mul(10) from dual;

10 x 3 = 30
9 x 3 = 27
8 x 3 = 24
7 x 3 = 21
6 x 3 = 18
5 x 3 = 15
4 x 3 = 12
3 x 3 = 9
2 x 3 = 6
1 x 3 = 3

How to drop a function in Oracle

You might need to remove your function from the database after you’ve built it in Oracle.

Syntax:

DROP FUNCTION function_name;

Example:

DROP FUNCTION FindCourse;