/******************************************************************************
   2 Examples of a CASE statement
******************************************************************************/
--------------------------------------------------------------------------
-- Example of a simple CASE statement
-- In a simple case, the test variable goes after the CASE keyword
--------------------------------------------------------------------------
CREATE or replace FUNCTION mth_name (mth_num integer) RETURN varchar2 is
    v_mth_name varchar2(50);
BEGIN
    CASE mth_num
    WHEN 1 THEN
        v_mth_name := 'January';
    WHEN 2 THEN
        v_mth_name := 'February';
    WHEN 3 THEN
        v_mth_name := 'March';
    WHEN 4 THEN
        v_mth_name := 'April';
    WHEN 5 THEN
        v_mth_name := 'May';
    WHEN 6 THEN
        v_mth_name := 'June';
    WHEN 7 THEN
        v_mth_name := 'July';
    WHEN 8 THEN
        v_mth_name := 'August';
    WHEN 9 THEN
        v_mth_name := 'September';
    WHEN 10 THEN
        v_mth_name := 'October';
    WHEN 11 THEN
        v_mth_name := 'November';
    WHEN 12 THEN
        v_mth_name := 'December';
    ELSE
        v_mth_name := 'invalid month number';
        v_mth_name := v_mth_name || ' re-execute with number 1-12';
    END CASE;
    return ( mth_num ||'='|| v_mth_name );
END; 
/

select mth_name(5) from dual                    -- should return 'May'
/

select mth_name(student_id) from student        -- trying all the months
/


--------------------------------------------------------------------------
-- Example of a searched CASE statement
-- In a searched case, the test expression goes after the WHEN keyword
--------------------------------------------------------------------------
CREATE or replace FUNCTION season (mth_num integer) return varchar2 is
    v_season varchar2(50);
BEGIN
    CASE 
    WHEN mth_num=12 or mth_num=1 or mth_num=2 THEN
        v_season := 'Winter';
    WHEN mth_num in (3,4,5) THEN
        v_season := 'Spring';
    WHEN mth_num between 6 and 8 THEN
        v_season := 'Summer';
    WHEN mth_num between 9 and 11 THEN
        v_season := 'Summer';
    ELSE
        v_season := 'invalid month number';
        v_season := v_season || ' re-execute with number 1-12';
    END CASE;
    return  'month' || mth_num ||'='|| v_season;
END; 
/

select season(7) from dual                      -- should return 'Summer'
/

select season(student_id) from student          -- trying all the seasons