Function
Function means:
- It is a PL/SQL sub program (block) used to compute values by accepting parameters.
- It is a named PL/SQL block that can accept parameters and can be called from SQL as well.
- It must return a value
Components of Function?
- Header
- Declaration
- Executable statements
- Exception-handling (Optional)
Function helps in reusability and
maintainability. It can be called as part of a SQL expression or as part of a PL/SQL
expression. In a SQL expression, a function must obey specific rules to control
side effects. In a PL/SQL expression, the function identifier acts like a
variable whose value depends on the parameters passed to it.
Function stores in the database as a
schema object for repeated execution.
4. What is the syntax of the Function?
FUNCTION name [ ( parameter [, parameter
... ] ) ]
RETURN return_datatype
IS
[ declaration statements ]
BEGIN
executable statements
[ EXCEPTION
exception handler statements ]
END [ name ];
Where each component is used in the
following ways:
name
The name of the procedure comes directly
after the keyword FUNCTION.
parameters
An optional list of parameters that you
define to both pass information into the procedure and send information out of
the procedure, back to the calling program.
return_datatype
The datatype of the value returned by the
function. This is required in the function header and is explained in more
detail in the next section.
declaration statements
The declarations of local identifiers for
that function. If you do not have any declarations, then there will not be any
statements between the IS and BEGIN statements.
executable statements
The statements the function executes when
it is called. You must have at least one executable statement after the BEGIN
and before the END or EXCEPTION keywords.
exception handler statements
The optional exception handlers for the
function. If you do not explicitly handle any exceptions, then you can leave
out the EXCEPTION keyword and simply terminate the execution section with the
END keyword.
The below figure illustrates the PL/SQL
function and its different sections. Notice that the tot_sales function does
not have an exception section.
Parameter
|
Description
|
function_name
|
Name of the function
|
parameter
|
Name of a
PL/SQL variable whose value is passed into the function
|
Mode
|
The type of
the parameter; only IN parameters should be declared
|
data type
|
Data type of
the Parameter
|
RETURN
datatype
|
Data type of
the RETURN value that must be output by the function
|
PL/SQL block
|
Procedural
body that defines the action performed by the function
|
Returning a Value
A function must have at least one
RETURN statement in its execution section of statements. It can have more than
one RETURN, but only one of those statements is executed each time the function
is called. The RETURN statement that is executed by the function determines the
value that is returned by that function. When a RETURN statement is processed,
the function terminates immediately and returns control to the calling PL/SQL
block.
The RETURN clause in the header of the
function is different from the RETURN statement in the execution section of the
body of the function. While the RETURN clause indicates the datatype of the
return or result value of the function, the RETURN statement specifies the
actual value that is returned. You have to specify the RETURN datatype in the
header, but then also include at least one RETURN statement in the function.
Multiple
RETURNs
In the tot_sales function shown in Figure 15.10,
I used two different RETURN statements to handle different situations in the
function, as follows:
IF sales_cur%NOTFOUND
THEN
CLOSE sales_cur;
RETURN NULL;
ELSE
CLOSE sales_cur;
RETURN return_value;
END IF;
In other words, if I could not obtain
sales information from the cursor, I will return NULL (which is different from
zero). If I do get a value from the cursor, I return it to the calling program.
In both of these cases the RETURN statement passes back a value; in one case
the NULL value, and in the other the return_value variable.
RETURN
any valid expression
The RETURN statement can accept any
expression for evaluation and return. This expression can be composed of calls
to other functions, complex calculations, and even data conversions. All of the
following usages of RETURN are valid:
RETURN 'buy me lunch';
RETURN POWER (max_salary, 5);
RETURN (100 - pct_of_total_salary
(employee_id));
RETURN TO_DATE ('01' || earliest_month ||
initial_year, 'DDMMYY');
An expression in the RETURN statement is
evaluated when the RETURN is executed. When control is passed back to the
calling form, the result of the evaluated expression is passed along, too.
No
RETURN is executed
What happens when you include one or any
number of RETURN statements in your functions but none of them is executed?
PL/SQL raises an error.
The following function:
FUNCTION company_type (type_code_in IN
VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF type_code_in = 'S'
THEN
RETURN 'SUBSIDIARY';
ELSIF type_code_in = 'P'
THEN
RETURN 'PARTNER';
END IF;
END;
is then called in this executable
statement:
type_description := company_type ('R');
Because the RETURN statements are
executed only when the type code is `S' or `P', the function never hits a
RETURN. It does, however, execute to the end of the
function and then raise an error, as follows:
ORA-6503: PL/SQL: Function returned
without value
You can avoid this kind of problem (which
you may never encounter in testing since you always pass a sensible value to
the function) by restructuring your use of the RETURN statement.
RETURN
as last executable statement
Generally, the best way to make sure that
your function always returns a value is to make the last executable statement
in the function your RETURN statement. Declare a variable named return_value,
which clearly indicates that it will contain the return value for the function,
write all the code to come up with that value, and then at the very end of the
function RETURN the return_value:
FUNCTION do_it_all (parameter_list)
RETURN NUMBER
IS
return_value NUMBER;
BEGIN
... lots of executable statements ...
RETURN return_value;
END;
The company_type function, for example,
can be converted easily to this structure:
FUNCTION company_type (type_code_in IN
VARCHAR2)
RETURN VARCHAR2
IS
return_value VARCHAR2 (25) := NULL;
BEGIN
IF type_code_in = 'S'
THEN
return_value := 'SUBSIDIARY';
ELSIF type_code_in = 'P'
THEN
return_value := 'PARTNER';
END IF;
RETURN return_value;
END;
Notice that, because I provided the return_value
variable with a default value of NULL, I didn't have to code an ELSE clause in
the IF statement to explicitly make that assignment (though doing so would
probably make the code more readable). If the type_code_in does not match any
of the values in the IF statement, there is no problem because each IF and
ELSIF no longer performs its own RETURN. Instead, they just assign a value and
then leave the RETURNing to the little RETURN section at the end of the
function.
NOTE : -
RETURN statement in a procedure
Believe it or not, RETURN statements can also be used in procedures.
The procedure version of the RETURN does not take an expression; it cannot,
therefore, pass a value back to the calling program unit. The RETURN simply
halts execution of the procedure and returns control to the calling code.
You do not (should not, in any case) see
this usage of RETURN very often, and for good reason. Use of the RETURN in a
procedure usually leads to very unstructured code that is hard to understand
and maintain. Avoid using both RETURN and GOTO to bypass proper control
structures and process flow in your program units.
Parameters:
Procedures
and functions can both use parameters to pass information back and forth
between the module and the calling PL/SQL block.
The parameters of a
module are at least as important as the code that implements the module (the
module's body). Sure, you have to make certain that your module fulfills its
promise. But the whole point of creating a module is that it can be called, you
hope by more than one other module. If the parameter list is confusing or badly
designed, it will be very difficult for programmers to make use of the module.
The result will be that few people will use that module. And it doesn't much
matter how well you implemented a program that no one uses.
Many developers do
not give enough attention to a module's set of parameters. Considerations
regarding parameters include:
·
The number of parameters: Too few parameters can limit the reusability of your program. Too
many parameters and no one will want to reuse your program.
·
The types of parameters: read-only (IN), write-only (OUT), or read-write (IN OUT)
parameters?
·
The names of parameters: With proper naming convention their purpose in a module is properly
and easily understood.
·
Default values for
parameters: You can provide a default value for IN
parameters. If an IN parameter has a default value, you do not need to include
that parameter in the call to the program. You must, of course, include an
actual parameter for any IN OUT parameters, even if they have default values. A
parameter's default value is used by the program only if the call to that
program does not include that parameter in the list.
No comments:
Post a Comment