-- null-into-clarif.sql
-- last modified: 1-30-13
--
-- uses a table from set-up-ex-tbls.sql

--------
-- purpose: seeking to clarify when select-into inserts a NULL value,
--    and when it throws a NO_DATA_FOUND exception
--------

-- the key: what is select projecting?
-- LOOKS LIKE: if you project a COMPUTATION, and either all columns
--    involved in the computation are null OR are no rows,
--    then NULL is assigned;
--
-- BUT if you project a COLUMN, and no rows are selected,
--    then you get a NO_DATA_FOUND exception

-- example: first run set-up-ex-tbls.sql (provided with this example)
--    if you do not have empl table currently

set serveroutput on

create or replace procedure null_clarif(p_job_type varchar2) as
    commission_sum number;
    name_found     varchar2(30);
begin
    select sum(commission)
    into commission_sum
    from empl
    where job_title = p_job_type;

    if (commission_sum is null) then
        dbms_output.put_line('NULL assigned - no commissions for ' 
                             || p_job_type);
    else
        dbms_output.put_line('Commission sum for ' || p_job_type || ' is $' 
                             || commission_sum);
    end if;

    select empl_last_name
    into name_found
    from empl
    where job_title = p_job_type;

    if (name_found is null) then
        dbms_output.put_line('NULL assigned - no name found  for ' 
                             || p_job_type);
    else
        dbms_output.put_line('Name found for ' || p_job_type || ' is ' 
                             || name_found);
    end if;

EXCEPTION
    when NO_DATA_FOUND then
        dbms_output.put_line('NO_DATA_FOUND exception thrown!');

    when TOO_MANY_ROWS then
        dbms_output.put_line('TOO_MANY_ROWS exc - found TOO MANY names ' 
                             || 'for ' || p_job_type);
end;
/

show errors

-- salesmen have commissions, but are more than one ---
--     should see a commissions sum, but TOO_MANY_ROWS message

prompt
prompt running exec null_clarif('Salesman'):
prompt (   should see TOO_MANY_ROWS message)
prompt ==================================
exec null_clarif('Salesman')

-- more than one clerk exists, but have null commission;
--     should see NULL-assigned message for commissions,
--     TOO_MANY_ROWS message for names found

prompt
prompt running exec null_clarif('Clerk'):
prompt (   should see TOO_MANY_ROWS message)
prompt ==================================
exec null_clarif('Clerk')

-- no rows have job_title of Juiceman;
--     should see NULL-assigned message for commission 
--         (projecting computation),
--     NO_DATA_FOUND message when tried to assign into name_found:

prompt
prompt running exec null_clarif('Juiceman'):
prompt (   should see NO_DATA_FOUND message)
prompt ==================================
exec null_clarif('Juiceman')

-- exactly one president, with NO commission;
--    should see NULL-assigned message for commission,
--    name_found of King

prompt
prompt running exec null_clarif('President'):
prompt (   should see NULL-assigned message for commission)
prompt ==================================
exec null_clarif('President')