Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions - What are STORED FUNCTIONS?
(Page 4 of 5 )
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).
Next: PACKAGE and PACKAGE BODY >>
More Oracle Articles
More By Jagadish Chatarji
/ 21 
