This proposal is to augment the syntax for defining a function to support a declaration that *guarantees* that a function cannot return NULL. In other words, if the function happens to return NULL, a runtime exception should be raised.
There are two purposes for this idea.
1) Fail early
Allow developers to assert/guarantee their function does not return NULL, so that code that calls their function will not continue without exception if an undetected NULL might cause subtle bugs later in the code. For example:
function category (class in varchar2) return varchar2 is
begin
return case class
when 'CONTRACTOR' then 'CONTRACT'
when 'SALESPERSON' then 'COMMISSION'
when 'EXECUTIVE-SALARY' then 'SALARIED'
when 'REGIONAL-SALARY' then 'SALARIED'
else default_cat
end;
end category;
The developer's intent is that this function must always return a non-null value. The developers have missed a scenario where this function may still return NULL (if the "default_cat" function, declared elsewhere, returns NULL). Perhaps at the time they wrote the function, default_cat was already written in a way that causes it to always return a non-null value; but subsequently it was changed so in some scenarios it returns NULL. If the developer could declare the function "NOT NULL" it would cause this scenario to be picked up early in testing, and at the right point (it would be easier to track down the source of the problem) and would serve as documentation for future maintainers.
Currently, it is the developer's responsibility to add suitable assertions (or perhaps, call NVL in the final RETURN statement) to ensure that the function never returns NULL.
2) SQL performance
For certain builtin functions, the CBO knows that they are guaranteed to return a non-null result (or, in some cases, as long as the input value is not null, the return value will be not null).
For example, the query
SELECT COUNT(*) FROM mytable WHERE UPPER(col) = 'JEFF';
can potentially be optimised by via a range scan on a function-based index on "UPPER(col)". Even though a row might have a NULL for this column and therefore not exist in the index, the CBO knows that UPPER() always returns a non-null given a non-null parameter, and optimises the query plan accordingly.
For user-defined functions, however, the CBO cannot know this and therefore must operate on the assumption that they may return NULL. The SQL could be updated to include a superfluous "x(y) IS NOT NULL" predicate, and it is possible to put a NOT NULL constraint on a virtual column based on the function; but these workarounds are not always practical. It would be much more preferable to declare this in the function itself.
c.f.: NOT NULL Constraints affect performance in the Oracle Database
For these reason, some kind of declaration added to a PL/SQL function to denote that it must return a non-null value is desirable.
Suggested Syntax
function category (class in varchar2) return varchar2 NOT NULL is ...
- If the function tries to return NULL, it would instead raise an exception, e.g. FUNCTION_RETURNED_NULL.
- The declaration would be considered part of the signature of the function, so for package functions it should appear in both the package spec and body.
- For functions that return a non-scalar type (e.g. a record) the "not null" declaration should probably be considered invalid and cause a compilation error.