Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Declaring and Calling Functions
Oracle Tips by Burleson
 

A function is very much like a procedure.  The blocks in a function follow the same structure as procedures and anonymous blocks:  declaration, execution and exception. 

The syntax for a function is:

CREATE OR REPLACE FUNCTION <function name>[(<variable list>)]
  RETURN <data type>
AS
  [<declarations>]
BEGIN
  <executable statements>
  RETURN <expression>;
[EXCEPTION
  <exceptions>]
END;

As you can see, the syntax looks much like a procedure but instead of the PROCEDURE keyword, it uses the FUNCTION keyword and includes a return data type. 

The RETURN in a function is not optional.  It is required and is an unconditional exit from that function.  The return statement is the method a function uses to return a value to the calling program.

This is the simple form of a function following our early NULL logic:

CREATE OR REPLACE FUNCTION null_func
  RETURN VARCHAR2
AS 
BEGIN 

  RETURN NULL;
END;

The above function doesn't do much beyond compile.  It is a valid function though.  No function does much until you call it. 

Functions are a little bit different from a procedure.  There are two ways to call a function.  You can call it from a select statement:

SELECT null_func
  FROM DUAL;


Alternatively, you can call it from a SPL block:

DECLARE
  v_null VARCHAR2(10);
BEGIN
  -- Called as an expression
  DBMS_OUTPUT.PUT_LINE( null_func ); 

  -- Called as a variable assignment
  v_null := null_func; 

  DBMS_OUTPUT.PUT_LINE( v_null ); 

END;

The output from both of these calls is null values.  You should try to replace the NULL expression in the function and see what results you get.

Below is a little function that is more useful.  It is a re-write of the procedure above.  This function accepts two parameters: varchar2 and number.  The number is added to 10 and the results are returned to the calling program:

CREATE OR REPLACE FUNCTION show_stuff_f(
   p_char_data IN VARCHAR2,
   p_number_data IN NUMBER ) 

  RETURN VARCHAR2 

AS
BEGIN 

  RETURN  (p_char_data || to_char( p_number_data + 10 ) ); 

END;

I call my function:

BEGIN
  DBMS_OUTPUT.PUT_LINE( show_stuff_f( 'My number is: ', 15) );
END;

I see my output:

INFO:  My number is: 25

It looks just like the output from the procedure but we can call it from almost anywhere. 

* If you are returning a single value and you want to use that value in an expression or in a SQL statement, use a function.  Otherwise, use a procedure.

In some instances, it is preferable to have both a function and a procedure that does the same thing (for calling in different places) but it is not preferable to have duplicate code.  You can achieve this via packages and overloading.

        
     

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter