Dear Tom,

One of my developers asked me a very interesting question today; If we have a procedure as such (Please excuse the use of the DUAL table, I couldn't think of a better one):

FUNCTION foo (p_parameter_i IN dual.dummy%TYPE;)
   RETURN VARCHAR2
IS
   CURSOR v_Cur
   IS
      SELECT 'Y'
        FROM dual a
       WHERE a.dummy = p_parameter_i;

   v_ReturnMe dual.dummy%TYPE;
BEGIN
   OPEN v_Cur;
   FETCH v_Cur INTO v_ReturnMe;
   CLOSE v_Cur;
   
   RETURN v_ReturnMe;
END;

If the code is invoked by passing a NULL value for p_parameter_i, the PLSQL engine will evaluate the SQL unnecessarily because the result will be NULL.

To go around it, one would have to create the procedure as:

FUNCTION foo (p_parameter_i IN dual.dummy%TYPE;)
   RETURN VARCHAR2
IS
   CURSOR v_Cur
   IS
      SELECT 'Y'
        FROM dual a
       WHERE a.dummy = p_parameter_i;

   v_ReturnMe dual.dummy%TYPE;
BEGIN
   IF p_parameter_i IS NULL
   THEN 
      RETURN;
   END IF;

   OPEN v_Cur;
   FETCH v_Cur INTO v_ReturnMe;
   CLOSE v_Cur;
   
   RETURN v_ReturnMe;
END;

Is there a way to create a function as one that will return NULL automatically when its only parameters value is NULL?

PS. I have researched Oracle Docs and haven't found anything in reference to this question.

begin
   if (parm is null) then return; end if;
   select 'y' into v_returnme from dual where dummy = parm;
   return v_returnme;
end