-- empl-count-funct.sql
-- last modified: 1-28-13
--
-- uses table from set-up-ex-tbls.sql

--------
-- signature: function: empl_count: void -> integer
-- purpose: expects nothing, returns the number of rows currently
--     in the empl table
-- example:
--     If empl currently has 14 rows, then the following
--     expression (in a PL/SQL block) would be true:
--     empl_count = 14
--------

create or replace function empl_count return integer is
   num_empls integer;
begin
    select count(*)
    into num_empls
    from empl;

    return num_empls;
end;
/

show errors

-- but how do you test this? 
-- in the PAST, I thought one could not call a function from
--     SQL*Plus -- but turns out it CAN be done!?

prompt "====================================================="
prompt "TESTING empl_count; should see num_empls value of 14"
prompt "====================================================="

-- here's how you can declare a local variable at the SQL*Plus level

var num_empls number

-- here's how you can execute a function, storing what that
--     function returns in that local variable (notice the : and := )

exec :num_empls := empl_count();

-- and here's how you can output the value of a local SQL*Plus
--     variable

print num_empls

-- end of empl-count-funct.sql