Reputation: 1458
I have written a boolean function in PL/SQL which takes a date as parameter and check the parameter against a table which contains the holiday dates and if the parameter is in the table the function returns a TRUE otherwise a FALSE. The code looks like this:
CREATE OR REPLACE
FUNCTION isholiday (p_holiday DATE)
RETURN BOOLEAN
IS
CURSOR c_holiday is SELECT holiday_date FROM dbp_holiday;
BEGIN
FOR r1 IN c_holiday
LOOP
IF p_holiday = r1.holiday_date
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END loop;
END;
even if I enter a valid holiday (e.g to_date('01-01-2013', 'dd-mm-yyy') it is in the table) as an argument the function is returning a false.
And the table is Oracle table its name is DBP_HOLIDAY and it has only one column with the type DATE and its name is Holiday_date.
Please give some advices. Thanks
Upvotes: 0
Views: 463
Reputation: 10525
If the first record in your cursor doesn't match with the input date, it straightaway returns FALSE, without checking other records. You don't need a cursor here. You can simplify it like this.
CREATE OR REPLACE FUNCTION isholiday (p_holiday DATE)
RETURN BOOLEAN
IS
x NUMBER(1);
BEGIN
SELECT 1
INTO x
FROM DBP_HOLIDAY
WHERE holiday_date = p_holiday;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN FALSE;
END;
Upvotes: 3