I'm looking for the optimal way to create a function that can accept no parameters and return all results, but also accepts parameters and return those results.
The standard I've been dealing with at my job is this:
FUNCTION get_records (
i_code IN records.code%type := NULL,
i_type IN records.type%type := NULL
) RETURN results
The problem is that I want to return records that have a type of NULL as well, and using:
WHERE type = nvl(i_type, type)
It only returns records with actual types and not the null records.. for obvious reasons. I was just wondering if there is a standard way of doing this that could be implemented across all functions we use. Coincidentally, if I provide a parameter... I don't want the NULL values of that field.
why not simply what you have with the addition of
type = i_type OR (i_type IS NULL AND type IS NULL)
that way, when the passed in param is null it looks for everything (including nulls) or the specified value. Now, if you just want the nulls...
example (change the value from null to 5 and you will see the output)
WITH TESTDATA AS (
SELECT LEVEL dataId
FROM DUAL
CONNECT BY LEVEL <= 100
UNION
SELECT NULL
from dual
)
SELECT *
FROM TESTDATA
where dataId = :n or (:n is null AND dataId is null) ;
Whereas :n = 6 the results will be
DATAID
----------------------
6
(deleted a new post, misread a param) But I like the new approach and avoiding the NVL
here's a nifty way to do it if you are not adverse to dynamic sql
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html
The standard way to solve this issue is to overload the function, instead of using default values:
FUNCTION get_records (
i_code IN records.code%type,
i_type IN records.type%type
) RETURN results;
FUNCTION get_records (
i_code IN records.code%type
) RETURN results;
FUNCTION get_records RETURN results;
Note: If you also need a version i_type
by itself, you might have trouble if it has the same underlying type as i_code
- in which case you'd need to use a different name for the function.
Right off the top of my head, I would guess the use of the DEFAULT
keyword would do the trick, doesn't it? (The following link will give some further details.)
Using the DEFAULT keyword.
CREATE OR REPLACE FUNCTION get_records (
i_code IN records.code%type DEFAULT NULL,
i_type IN records.type%type DEFAULT NULL
) RETURN results
EDIT #1
If I understand the question correctly, you want to return all of the records when the i_type
parameter is NULL. In absence of further details, my guess would be the following.
CREATE OR REPLACE FUNCTION get_records (
i_code IN records.code%TYPE DEFAULT NULL,
i_type IN records.type%TYPE DEFAULT NULL
) RETURN results
BEGIN
IF (i_type IS NULL) THEN
select *
from table
ELSE
select *
from table
where type = NVL(i_type, type)
END IF
EXCEPTION
WHEN OTHERS THEN
NULL
END
That is all I can do with the information provided, although the function body is commented below.
EDIT #2
I am a bit rusty from Oracle, so I consulted some documentation as linked below:
Oracle/PLSQL: NVL Function
As I have read it, you had better use the NVL
function within your SELECT
instruction, and not your WHERE
clause.
In the end, what is your question exactly? Could you make it crystal clear?
To reiterate my understanding of the issue: You can't take the default value of NULL
to mean "return all records", because the current expected behavior is that it will return only those records where the value is actually NULL.
One possibility is to add a boolean parameter corresponding to each lookup parameter, which indicates whether it should actually be used to filter results. A potential issue with this is that a caller might specify a lookup value but fail to set the flag to true, producing unexpected results. You can guard against this at runtime by raising an exception if the flag is false for any lookup value other than NULL
.
Another possibility is to define an out-of-domain value for each lookup column -- e.g. if the string 'ANY' is passed for the parameter, it will not filter values on that column. This should work fine as long as you can find a worthwhile sentry value for each column. I'd suggest declaring the sentry values as constants in some package, so that calls to the function could look like get_records( PKG.ALL_CODES, 'type1' )
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With