HomeOracle Page 4 - Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions
What are STORED FUNCTIONS? - Oracle
This is part 16 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we worked with PL/SQL TABLE types in between sub-programs. In this article, we will look into Named Notation, default values of parameters, stored procedures, stored functions and finally introduce the concepts of package and package body.
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).