CODEWITHSUNDEEP
oracle-databasefunctionplsql
broadband
broadband

Reputation: 3488

Oracle documentation on return statement

Where does it say that oracle function on just "return" statement returns null. For example, here is simple function:

create or replace FUNCTION FOO RETURN NUMBER AS 
BEGIN
  RETURN;
  -- is return null the same as return?
END FOO;

EDIT

Above function won't compile like Alex Poole reported. Function must therefore always have return value e.g. number, date, blob, clob, varchar2 or null. It can't contain only "return". Return is therefore in oracle not the same as return null.

What I'm really wondering is where does it state that if function has the signature of returning some datatype (number, date, varchar2, blob or clob) and its body contains "return null" statement, why is that allowed, why it can return null. I suppose this is the same as in scripting languages e.g. PHP:

function foo($i)
{
  if( $i > 0 )
    return $i;
  return null; // return is the same as return null. Tested in PHP.
}

Of course this behaviour isn't allowed in C# for example:

static int foo() {return null;}
Error 1 Cannot convert null to 'int' because it is a non-nullable value type

Upvotes: 2

Views: 1692

Answers (2)

Alex Poole
Alex Poole

Reputation: 191295

Where does it say that oracle function on just "return" statement returns null

It doesn't say that anywhere, because it isn't true. You can only have a simple return rather then return <expression> in a function if it is pipelined, i.e. the data has already been returned by the pipe mechanism and the return is just to indicate that control (rather than data) should return to the caller. You can also have a plain return in a procedure. Your sample function would not compile - you'd get PLS-00503: RETURN <value> statement required for this return from function as there is no expression.

The behaviour of return from a function is described in the PL/SQL subprogram documentation, and there is more detail about the return statement, which says of the expression:

Optional when the RETURN statement is in a pipelined table function. Required when the RETURN statement is in any other function. Not allowed when the RETURN statement is in a procedure or anonymous block.

The RETURN statement assigns the value of expression to the function identifier. Therefore, the data type of expression must be compatible with the data type in the RETURN clause of the function.

So your amended question is really asking about that last sentence; why you are allowed to assign null regardless of the declared return data type. But you can assign null to most data types, and as Boneist pointed out, variables of most data types are initialised to null. It's valid to have set a number variable to null. A line from earlier in that documentation page says:

the RETURN statement assigns a specified value to the function identifier

... so effectively when you call a function the return statement is assigning whatever value your expression evaluates as to the caller's variable. (Obviously that's a simplistic view; there may be several levels of caller, and you might be calling this from plain SQL so there's no PL/SQL assignment, and the internals are probably more complicated anyway).

As long as the variable is a type that can be null, there is no violation. It isn't set out as explicitly as you were hoping for though - it's more that there is nothing saying you can't return null. If that were the case, it would be a restriction on the function return on top of the normal data type restrictions.

Going a bit off-topic, there are exceptions; if you have a data type which is restricted to not-null values, such as simple_integer, then a function returning null is valid:

create or replace FUNCTION FOO RETURN NUMBER AS 
BEGIN
  RETURN NULL;
END FOO;
/

FUNCTION FOO compiled

... but calling it and trying to assign the value to a constrained variable will fail:

declare
  n simple_integer := 0;
begin
  n := foo;
end;
/

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 4

And if you use a constrained data type for the function declaration you can't explicitly return null:

create or replace FUNCTION FOO RETURN SIMPLE_INTEGER AS 
BEGIN
  RETURN NULL;
END FOO;
/

FUNCTION FOO compiled
Errors: check compiler log

and show_errors reveals:

3/10           PLS-00382: expression is of wrong type
3/3            PL/SQL: Statement ignored

You can still return an expression that evaluates to null, which the compiler can't catch (e.g. RETURN 1 * NULL), but you'd still then get errors when running it:

declare
  n number;
begin
  n := foo;
end;
/

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "STACKOVERFLOW.FOO", line 3
ORA-06512: at line 4

Upvotes: 1

Boneist
Boneist

Reputation: 23588

I don't think you'll find such a statement in the documentation explicitly for functions. However, if you see in this bit of the documentation about declarations that By default, variables are initialized to NULL.... Therefore, if you don't initialize the return value, it will automatically be NULL.

ETA: haha, I should have tested the function first, as per Alex's comment, it doesn't compile. But I think the general principle of not defining variables being null still stands elsewhere *{:-)

Upvotes: 1

Related Questions