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