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:
- 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)
- They enable us to reuse code rather than rewrite it.
- 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.
- 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
Function name (arg1,agr2);
A. Character functions: It accepts character input and returns both character and numerical values
B. Number functions: Accept numeric data and return numeric results.
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.)
D. General functions:
SELECT NVL(100,200) FROM dual;
100 (Because the first argument is not null)
SELECT NVL2(NULL, 1, 2) FROM dual;
2(Because the first argument is null)
SELECT NULLIF (100,100) FROM dual;
NULL(the above statement returns a null value because the first argument equals the second one)
SELECT COALESCE(NULL, NULL, NULL, ‘Oracle’, NULL, ‘Example.com’);
SELECT ‘The job id for ‘||UPPER(last_name)||’ is ‘||LOWER(job_id) AS “EMPLOYEE DETAILS” FROM employees;
The job id for DAVIES is st_clerk
SELECT employee_id, salary
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.
Select max (Salary)
B. Minimum (MIN)
From the given data, returns the minimum value
Select min (Salary)
C. Average (AVG)
It calculates the average value of the provided expression.
Select avg (sal) from emp;
The sum of the “Quantity” field in the “Order Details” table should be returned.
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
Counts the items from a table/column
Select count(empno) from employee;
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.
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.
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.
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;
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.
DROP FUNCTION function_name;
DROP FUNCTION FindCourse;