truncate table output
/

/*****************************************************************************
/* Using a cursor
/* Fetching into multiple variables
/****************************************************************************/
DECLARE
    CURSOR student_cur IS  
    select fname, lname, ssn, sex, course_id 
      from student, class 
     where ssn = stu_ssn
           and sex = 'M';

    v_first student.fname%TYPE;                    -- same type as table.column
    v_last  student.lname%TYPE;
    v_ssn   student.ssn%TYPE;
    v_sex   student.sex%TYPE;
    v_course_id class.course_id%TYPE;

BEGIN
    insert into output values('***Fetch into Multiple Variables***'); 
    OPEN student_cur;
    LOOP
        FETCH student_cur 
            INTO v_first,v_last,v_ssn,v_sex,v_course_id;    -- Fetch into vars
        EXIT WHEN  student_cur%NOTFOUND;                    -- exit                 

        insert into output 
            values (v_first || v_last || v_ssn || v_course_id);      --access    
    END LOOP;
    CLOSE student_cur;
END;
/


/*****************************************************************************
/* Using a cursor
/* Fetching into a single variable defined to hold an entire row
/****************************************************************************/
DECLARE
    CURSOR student_cur IS  
        select fname, lname, ssn, sex, course_id as c_id 
          from student, class 
     where ssn = stu_ssn
       and sex = 'M';

    v_student_rec  student_cur%ROWTYPE;             -- declare a single record

BEGIN
    insert into output values(''); 
    insert into output values('***Fetch into a ROWTYPE***'); 
    OPEN student_cur;
    LOOP
        FETCH student_cur  INTO  v_student_rec;     -- Fetch into an entire row
        EXIT WHEN  student_cur%NOTFOUND;            -- exit

        insert into output 
            values (v_student_rec.fname || v_student_rec.c_id);     --access 
    END LOOP;
    CLOSE student_cur;
END;
/


/*****************************************************************************
/* Using a cursor
/* Fetching into a custom record type
/****************************************************************************/
DECLARE
    CURSOR student_cur IS  
    select fname, lname, ssn, sex, course_id
      from student, class 
     where ssn = stu_ssn
       and sex = 'M';

    TYPE student_rectype IS RECORD              -- declare a custom data type
        (fname   student.fname%TYPE,
     lname   student.lname%TYPE,
     ssn     student.ssn%TYPE,
     sex     student.sex%TYPE,
     crs_id  class.course_id%TYPE );

    v_student_record  student_rectype;        -- declare a var using that type

BEGIN
    insert into output values(''); 
    insert into output values('***Fetch into a RECORD Type***'); 
    OPEN student_cur;
    LOOP
        FETCH student_cur  INTO  v_student_record;  -- Fetch into custom record
        EXIT WHEN  student_cur%NOTFOUND;            -- exit

        insert into output 
            values (v_student_record.fname || v_student_record.crs_id); --access 
    END LOOP;
    CLOSE student_cur;
END;
/


/*****************************************************************************
/* Another example of fetching into a custom record type
/*  (in this case, I am using a function with an alias)
/****************************************************************************/
DECLARE
    CURSOR student_cur IS
    select lname, fname, count(course_id) as cnt  -- using alias for a function
      from student, class
     where ssn=stu_ssn
     group by(lname, fname);

    TYPE student_rectype IS RECORD                -- declare a custom data type
    ( lname     student.lname%TYPE,
      fname     student.fname%TYPE,
      cnt       number );

    v_student_record  student_rectype;          -- declare a var using that type
BEGIN
    insert into output values(''); 
    insert into output values('***Fetch into a RECORD Type***'); 
    OPEN student_cur;
    LOOP
        FETCH student_cur  INTO  v_student_record;
        EXIT WHEN  student_cur%NOTFOUND;

        insert into output 
            values (v_student_record.fname || v_student_record.cnt);   --access 
    END LOOP;
    CLOSE student_cur;
END;
/


/*****************************************************************************
/* Using a cursor FOR loop
/*    (best approach)
/* No declaration of %rowtype variable, no open cursor, no fetch, 
/*                                      no loop exit, no close cursor
/****************************************************************************/
DECLARE
    CURSOR student_cur IS  
    select fname, lname, ssn, sex, course_id as c_id
      from student, class 
     where ssn = stu_ssn
       and sex = 'M';
BEGIN
    insert into output values(''); 
    insert into output values('***Automatic Fetch with FOR loop***'); 
    FOR  row  IN  student_cur  LOOP
        insert into output                   -- row is automatically fetched 
            values (row.lname || row.fname || row.c_id);    -- access 
    END LOOP;
END;



/
select * from output