Plsql is a nice and simple programming langunage for oracle databases, but sometimes you can’t believe what you see. In the following example you can see the Schrödinger’s cat equivalent of oracle.
declare
grNumber number;
leNumber number;
testNumber number := 10;
begin
grNumber := greatest (null,testNumber);
leNumber := least (null,testNumber);
if grNumber = testNumber then
dbms_output.put_line(testNumber || ' is greater then null');
else
dbms_output.put_line('null is greater then ' || testNumber);
end if;
if leNumber = testNumber then
dbms_output.put_line(testNumber || ' is less then null');
else
dbms_output.put_line('null is less then ' || testNumber);
end if;
end;
The result is:
null is greater then 10 null is less then 10
In this case null is greater and less then any number.
To avoid this strange behavior you have to use nvl to avoid null in greatest und least functions.