SQL & PL/SQL

Hi All;

Why max in a query return One record although there is no data? I want to demonstrate an example below.

Regards...

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as HR


SQL> set serveroutput on;
SQL> 
SQL> DECLARE
  2    a VARCHAR2(12) := '';
  3    b NUMBER;
  4  BEGIN
  5    SELECT COUNT(a.dummy)
  6      INTO b
  7      FROM (SELECT * FROM dual) a
  8     WHERE a.dummy = 'T';
  9    dbms_output.put_line('COUNT(a.dummy) is ' || b);
 10  
 11    SELECT COUNT(*)
 12      INTO b
 13      FROM (SELECT MAX(a.dummy)
 14              FROM (SELECT * FROM dual) a
 15             WHERE a.dummy = 'T');
 16    dbms_output.put_line('COUNT(a.dummy) with MAX is ' || b);
 17  
 18    SELECT MAX(a.dummy)
 19      INTO a
 20      FROM (SELECT * FROM dual) a
 21     WHERE a.dummy = 'T';
 22  
 23    dbms_output.put_line('MAX(a.dummy) passed..');
 24  
 25    SELECT a.dummy INTO a FROM (SELECT * FROM dual) a WHERE a.dummy = 'T';
 26    dbms_output.put_line('a.dummy passed..');
 27  
 28  EXCEPTION
 29    WHEN no_data_found THEN
 30      dbms_output.put_line('no_data_found exception...');
 31  
 32  END;
 33  /

COUNT(a.dummy) is 0
COUNT(a.dummy) with MAX is 1
MAX(a.dummy) passed..
no_data_found exception...

PL/SQL procedure successfully completed

SQL>