-- param-exs.sql
-- last modified: 1-30-13

----------
-- signature: param_play: date OUT:integer IN OUT: integer -> void
-- purpose: to demonstrate in, out, and in out parameters;
--     expects a date, an argument that can hold an integer,
--     and an argument that holds an integer and can be changed,
--     and returns nothing, BUT has the side-effects of
--     setting the 2nd argument to the number of the current month,
--     and setting the 3rd argument to the sum of its initial value and
--         the number of the current month
--
-- example: the call param_play(sysdate, curr_month, next_month),
--     if called on 1-25-12 with a next_month value of 3, 
--     will have the result of setting curr_month to 1
--     and next_month to 4 (3 + 1)
--
-- by: Sharon Tuttle
-- last modified: 1-30-13

create or replace procedure param_play(desired_date date, 
                                       desired_month OUT integer,
                                       new_month IN OUT integer) as
begin
    -- did you know the extract function can be used to grab
    --    pieces from a date as numbers!? (to_char grabs them
    --    as strings...)

    --dbms_output.put_line('HEY LOOKIT HERE' || desired_month);

    -- (recall: dual table in Oracle is always there, and you
    --    can use it when you need to project something but
    --    not really from a particular table...!)

    select extract(month from desired_date)
    into desired_month
    from dual;
 
    -- now increase current value of new_month by desired_month

    new_month := new_month + desired_month;
end;
/
show errors

set serveroutput on

prompt 
prompt TESTING param_play:
prompt ==========================================

var curr_month number;
var next_month number;

exec :next_month := 3;

prompt ========================================== 
prompt BEFORE CALL 1 (sysdate as 1st argument):
prompt curr_month not yet set,

prompt next_month is:
print next_month

exec param_play(sysdate, :curr_month, :next_month);

prompt AFTER CALL 1:
prompt curr_month is: (should be current month number) 
print curr_month

prompt next_month is: (should be 3 + current month number)
print next_month

exec :curr_month := 125;

prompt ==========================================
prompt BEFORE CALL 2 (25-July-1997 as first argument):
prompt curr month is:
print curr_month

prompt next_month is:
print next_month

exec param_play('25-July-1997', :curr_month, :next_month);

prompt AFTER CALL 2:
prompt curr_month is: (should be 7, the number for July)
print curr_month
prompt next_month is: (should be 4 plus 7 or 11
print next_month

-- end of param-exs.sql