TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

Oracle Functions

Oracle Functions for beginners and professionals with examples on insert, select, update, delete, table, view, join, key, functions, procedures, indexes, cursor etc.

<< Back to ORACLE

Oracle Function

A function is a subprogram that is used to return a single value. You must declare and define a function before invoking it. It can be declared and defined at a same time or can be declared first and defined later in the same block.

CREATE function in Oracle

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];

You must have define some parametrs before creating a procedure or a function. These parameters are

  • IN: It is a default parameter. It passes the value to the subprogram.
  • OUT: It must be specified. It returns a value to the caller.
  • IN OUT: It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.

Oracle Function Example

Let's see a simple example to create a function.

create or replace function adder(n1 in number, n2 in number)  
return number  
is   
n3 number(8);  
begin  
n3 :=n1+n2;  
return n3;  
end;  
/  

Now write another program to call the function.

DECLARE  
   n3 number(2);  
BEGIN  
   n3 := adder(11,22);  
   dbms_output.put_line('Addition is: ' || n3);  
END;  
/  

Output:

Addition is: 33
Statement processed.
0.05 seconds

Another Oracle Function Example

Let's take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL function which will compute and return the maximum of two values.

DECLARE
   a number;
   b number;
   c number;
FUNCTION findMax(x IN number, y IN number) 
RETURN number
IS
    z number;
BEGIN
   IF x > y THEN
      z:= x;
   ELSE
      Z:= y;
   END IF;

   RETURN z;
END; 
BEGIN
   a:= 23;
   b:= 45;

   c := findMax(a, b);
   dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/

Output:

Maximum of (23,45): 45
Statement processed.
0.02 seconds

Oracle function example using table

Let's take a customer table. This example illustrates creating and calling a standalone function. This function will return the total number of CUSTOMERS in the customers table.

Create customers table and have records in it.

Customers
Id Name Department Salary
1 alex web developer 35000
2 ricky program developer 45000
3 mohan web designer 35000
4 dilshad database manager 44000

Create Function:

CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
   total number(2) := 0;
BEGIN
   SELECT count(*) into total
   FROM customers;
    RETURN total;
END;
/

After the execution of above code, you will get the following result.

Function created. 

Calling Oracle Function:

DECLARE
   c number(2);
BEGIN
   c := totalCustomers();
   dbms_output.put_line('Total no. of Customers: ' || c);
END;
/

After the execution of above code in SQL prompt, you will get the following result.

Total no. of Customers: 4
PL/SQL procedure successfully completed.

Oracle Recursive Function

You already know that a program or a subprogram can call another subprogram. When a subprogram calls itself, it is called recursive call and the process is known as recursion.

Example to calculate the factorial of a number

Let's take an example to calculate the factorial of a number. This example calculates the factorial of a given number by calling itself recursively.

DECLARE
   num number;
   factorial number;

FUNCTION fact(x number)
RETURN number 
IS
   f number;
BEGIN
   IF x=0 THEN
      f := 1;
   ELSE
      f := x * fact(x-1);
   END IF;
RETURN f;
END;

BEGIN
   num:= 6;
   factorial := fact(num);
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/

After the execution of above code at SQL prompt, it produces the following result.

Factorial 6 is 720 
PL/SQL procedure successfully completed.

Oracle Drop Function

If you want to remove your created function from the database, you should use the following syntax.

Syntax:

DROP FUNCTION function_name;
Next TopicOracle Cursor




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf