What are STORED FUNCTIONS? - Oracle

I hope you can understand the STORED FUNCTIONS by now. The FUNCTIONS which get stored inside the database are called STORED FUNCTIONS. The concept is very similar to that of STORED PROCEDURES. Let us see an example for stored functions:

create or replace function getDname(p_empno emp.empno%type) return varchar2 is
  v_dname  dept.dname%type;
 begin 
  select dname into v_dname
  from dept
  where deptno = (select deptno from emp
    where empno = p_empno);
  
  return v_dname;
 end;

The above function receives an ‘empno’ as a parameter and returns the employee’s  department name.  Now, how do we execute the stored function?  You cannot execute a function through the ‘execute’ statement of SQL. Instead you can use it directly with the SELECT statement as follows:

SELECT getDname(7369) FROM dummy;

The above SQL statement should return you ‘RESEARCH’ as the result. What is ‘dummy’?  It is a default table which gets installed automatically when you install Oracle (unless anyone deletes it). The primary use of ‘dummy’ is to provide calculations within the SELECT statement.  As the scope of this article is limited to PL/SQL, I will not go in depth about the ‘dummy’ table.

Now, can we use the above function without using a dummy table?  The answer to this question is yes. You can use it just like a pre-defined function (like TO_UPPER, TO_DATE and so on) within any SQL statement. The following example demonstrates this.

select empno,ename, getDname(empno) as deptname from emp;

The output of the above query would be all employee numbers, names and department names from the table ‘emp’ (without using any join). That is the beauty of stored function. You can use stored functions to hide any huge complex calculations and just use it on the fly using any SQL command (of course generally DML commands).