----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> Neil Conway <neilc@samurai.com> writes:
> > On Fri, 2003-01-10 at 20:28, Tom Lane wrote:
> >> Clearly, RETURN NEXT with an undefined record variable shouldn't dump
> >> core, but what should it do? Raise an error, or perhaps be a no-op?
>
> > I'd vote for making it a no-op. Raising an error is too severe for a
> > fairly routine occurence, IMHO. If we make it a no-op, it's consistent
> > with how I understand a SELECT INTO of 0 rows -- it doesn't produce an
> > "undefined value", but an "empty result set" (like the difference
> > between "" and a NULL pointer).
>
> There's a consistency issue here, though. If the SELECT INTO target
> is non-record variable(s), the behavior is to set them to NULL. Then
> if you do RETURN NEXT on that, you'd emit a row full of NULLs.
>
> It seems inconsistent that SELECT INTO a record variable produces an
> undefined result rather than a row of NULLs, when there are no rows
> in the SELECT result. This would be an easy change to make, I think.
> We do have a tupledesc available for the SELECT, we're just not using
> it.
>
> Does Oracle's PL/SQL have a concept of record variables? If so, what
> do they do in this situation?
In Oracle 8, a row of NULLs:
1 CREATE OR REPLACE FUNCTION foo(t IN NUMBER) 2 RETURN NUMBER 3 IS 4 emp_rec employees%ROWTYPE; 5 BEGIN 6
SELECTemployees.* INTO emp_rec 7 FROM employees 8 WHERE employees.id = t; 9 RETURN(emp_rec.id);10* END;
SQL> /
Function created.
SQL> select * from employees;
no rows selected
SQL> insert into employees values (1, 'Mike');
1 row created.
SQL> select foo(1) from dual;
FOO(1)
---------- 1
SQL> select foo(2) from dual;
FOO(2)
----------
SQL> select nvl(foo(2), 0) from dual;
NVL(FOO(2),0)
------------- 0
Mike Mascari
mascarm@mascari.com