PL/SQL - Functions


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
   [ declaration statements ]
   executable statements
   exception handler statements ]
END [ name ];

Where each component is used in the following ways:

The name of the procedure comes directly after the keyword FUNCTION.

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.

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.

Figure 15.10

Name of the function
Name of a PL/SQL variable whose value is passed into the function
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
   CLOSE sales_cur;
   CLOSE sales_cur;
   RETURN return_value;
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)
   IF type_code_in = 'S'
   ELSIF type_code_in = 'P'
   END IF;
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
   return_value NUMBER;

   ... lots of executable statements ...

   RETURN return_value;
The company_type function, for example, can be converted easily to this structure:
FUNCTION company_type (type_code_in IN VARCHAR2)
   return_value VARCHAR2 (25) := NULL;
   IF type_code_in = 'S'
      return_value := 'SUBSIDIARY';

   ELSIF type_code_in = 'P'
      return_value := 'PARTNER';
   END IF;

   RETURN return_value;
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.

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