Home arrow Oracle arrow 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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions
  2. What are Parameter Default values?
  3. What are STORED PROCEDURES?
  4. What are STORED FUNCTIONS?
  5. PACKAGE and PACKAGE BODY
By: Jagadish Chatarji
Rating: starstarstarstarstar / 25
September 06, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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).



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: