Download View this document as PDF

CHAPTER 6
SQLJ Functions and Stored Procedures
throws SQLException {
if (jc==1) return “Admin”;
else if (jc==2) return “Sales”;
else if (jc==3) return “Clerk”;
else return “unknown jobcode”;
}
Handling nulls when creating the function
If null values are expected, you can include the returns null on null input clause
when you create the function. For example:
create function job_of(jc integer)
returns varchar(20)
returns null on null input
language java parameter style java
external name 'SQLJExamples.job(int)'
You can then call job_of in this way:
select name, job_of(jobcode)
from sales_emp
where job_of(jobcode) <> "Admin"
When the SQL system evaluates the call job_of(jobcode) for a row of
sales_emps in which the jobcode column is null, the value of the call is set to
null without actually calling the Java method SQLJExamples.job. For rows with
non-null values of the jobcode column, the call is performed normally.
Thus, when a SQLJ function created using the returns null on null input clause
encounters a null argument, the result of the function call is set to null and the
function is not invoked.
Note If you include the returns null on null input clause when creating a SQLJ
function, the returns null on null input clause applies to all function parameters,
including nullable parameters.
If you include the called on null input clause (the default), null arguments for
non-nullable parameters generates an exception.
Handling nulls in the function call
You can use a conditional function call to handle null values for non-nullable
parameters. The following example uses a case expression:
Java in Adaptive Server Enterprise
101