What is a Function in PL/SQL?A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
PL/SQL function is a named block that returns a
value. PL/SQL functions are also known as subroutines or subprograms.
To create a PL/SQL function, you use the following syntax:
CREATE [OR REPLACE] FUNCTION {function_name} [(
{parameter_1} [IN] [OUT] {parameter_data_type_1},
{parameter_2} [IN] [OUT] {parameter_data_type_2},...
{parameter_N} [IN] [OUT] {parameter_data_type_N} )]
RETURN {return_datatype} IS
--the declaration statements
BEGIN
-- the executable statements
RETURN {return_data_type};
EXCEPTION
-- the exception-handling statements
END;
/
The {function_name} is the name of the function. Function name should start with a verb for example function convert_to_number.
{parameter_name}
is the name of parameter being passed to function along with
parameter’s data type {parameter_data_type}. There are three modes for
parameters: IN,OUT and IN OUT.
The IN mode is the default
mode. You use the IN mode when you want the formal parameter is
read-only. It means you cannot alter its value in the function. The IN
parameter behaves like a constant inside the function. You can assign
default value to the IN parameter or make it optional.
The OUT
parameters return values to the caller of a subprogram. An OUT parameter
cannot be assigned a default value therefore you cannot make it
optional. You need to assign values to the OUT parameter before exiting
the function or its value will be NULL. From the caller subprogram, you
must pass a variable to the OUT parameter.
In the IN OUT mode,
the actual parameter is passed to the function with initial values. And
then inside the function, the new value is set for the IN OUT parameter
and returned to the caller. The actual parameter must be a variable.
The
function must have at least one RETURN statement in the execution part.
The RETURN clause in the function header specifies the data type of
returned value.
Examples of PL/SQL Function
We are going to create a function
that parses a string and returns a number if the string being passed is
a number otherwise it returns NULL.
CREATE OR REPLACE FUNCTION try_parse(
iv_number IN VARCHAR2)
RETURN NUMBER IS
BEGIN
RETURN TO_NUMBER(iv_number);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
The input parameter is iv_number that is a varchar2 type.
We can pass any string to the function try_parse().
We use built-in function to_number to convert a string into a number.
If any exception occurs, the function will return NULL in the exception
section of the function block.
Thanks for the very informative blog and I extremely grateful that you perform this piece of writing very simply, I mean to say that it's quite simple to read and understand. Web Design Bangalore | Web Designing Companies Bangalore
ReplyDelete