ORACLE FUNCTION: A function in Oracle can be simply understood as a subprogram. It is mainly used to return a single value. There are mainly three subprocesses for a function to execute successfully: Declaration, Definition and Calling. The declaration and definition processes of a function must be done before invoking it. A function can be declared and defined simultaneously or it can be done in two steps within the same block: declared first and defined later.
There are three types of parameters that must be specified while creating a function.
- IN: Being a default parameter, it is used to pass a value to the subprogram.
- OUT: When specified, it is used to return a value to the caller.
- IN OUT: When specified, it is used to get an updated value to the caller by passing an initial value to the subprogram.
Syntax: To create or replace a function.
CREATE OR REPLACE FUNCTION func_name (parameter) RETURN return_datatype IS | AS declaration_section BEGIN executable_section EXCEPTION exception_section END func_name;
Syntax: To drop a function.
DROP FUNCTION func_name;
Example 1: Creating a simple function and calling it later. Creating Function:
CREATE OR REPLACE FUNCTION subtractor (a in number, b in number) RETURN number IS c number(8); BEGIN c :=a-b; return c; END; |
Output:
Function created.
Calling the Function:
DECLARE c number(2); BEGIN c := subtractor (10, 5); dbms_output.put_line('Subtraction is: ' || c); END; |
Output:
Subtraction is: 5 Statement processed. 0.03 seconds
Explanation: Here we are creating a simple function for subtraction. There are three variables a, b and c which are declared and defined along with their data types. The next step is to call the function by passing the values to the variables. The result thus received is the value of c as the result of subtraction for a and b.
Example 2: Creating a function by declaring, defining and calling it in the same block.
DECLARE n1 number; n2 number; n3 number; FUNCTION findMin (a IN number, b IN number) RETURN number IS c number; BEGIN IF a < b THEN c:= a; ELSE c:= b; END IF; RETURN c; END; BEGIN n1:= 650; n2:= 651; n3 := findMin (n1, n2); dbms_output.put_line(' Minimum of (650, 651): ' || n3); END; |
Output:
Minimum of (650, 651): 650 Statement processed. 0.03 seconds
Explanation: Here we are creating a function for finding the minimum value by declaring, defining and calling it in the same block. There are three variables which are declared and defined along with their data types. The next step is to call the function by passing the values to the variables. The result thus received is the value of n3 equal to the minimum of n1 and n2.
Example 3: Creating a function using table. Students Table:
ID NAME AGE 1 Joy 20 2 Smiley 19 3 Happy 21 4 James 22 5 Bond 25
Creating Function:
CREATE OR REPLACE FUNCTION num_students RETURN number IS num number(2) := 0; BEGIN SELECT count(*) into num FROM students; RETURN num; END; |
Output:
Function created.
Calling Function:
DECLARE n number(2); BEGIN n := num_students(); dbms_output.put_line('Number of Students: ' || n); END; |
Output:
Number of Students: 5 PL/SQL procedure successfully completed.
Explanation: The ‘students’ is an already existing table. Here we are creating a function for finding the total number of students from the ‘students’ table. The next step is to call the function by passing the values to the variables. The result thus received is the value of n equal to the total number of students.
Example 3: Creating a recursive function.
DECLARE n number; fact number; FUNCTION factorial(a number) RETURN number IS x number; BEGIN IF a=0 THEN x := 1; ELSE x := a * fact(a-1); END IF; RETURN x; END; BEGIN n:= 5; fact := factorial (n); dbms_output.put_line(' Factorial of '|| n || ' is ' || fact); END; |
Output:
Factorial of 5 is 120 PL/SQL procedure successfully completed.
Explanation: The process of calling a subprogram by the subprogram itself is known as Recursion. This type of call is often known as Recursive Call. Here we are creating a recursive function for finding the factorial value by declaring, defining and calling it in the same block. There are two variables which are declared and defined along with their data types. The next step is to call the function by passing the values to the variables. The result thus received is the value of fact equal to the factorial of n.
Example: Eliminating a function.
DROP FUNCTION subtractor; |
Explanation: The ‘subtractor’ is an already created function. The above code will eliminate the ‘subtractor’ function from the Oracle database.
