Collection Types in PL/SQL

/*Scripts to practice or test it*/

DROP TABLE DEMO.STUDENT_MARKS;
CREATE TABLE DEMO.STUDENT_MARKS(SNO NUMBER,SUBJECT VARCHAR2(10),MARKS NUMBER);
/
INSERT INTO DEMO.STUDENT_MARKS VALUES(1,'MATHS',100);
INSERT INTO DEMO.STUDENT_MARKS VALUES(1,'SCIENCE',100);
INSERT INTO DEMO.STUDENT_MARKS VALUES(1,'SOCIAL',99);
INSERT INTO DEMO.STUDENT_MARKS VALUES(2,'MATHS',97);
INSERT INTO DEMO.STUDENT_MARKS VALUES(2,'SCIENCE',89);
INSERT INTO DEMO.STUDENT_MARKS VALUES(2,'SOCIAL',79);
INSERT INTO DEMO.STUDENT_MARKS VALUES(3,'MATHS',99);
INSERT INTO DEMO.STUDENT_MARKS VALUES(3,'SCIENCE',96);
INSERT INTO DEMO.STUDENT_MARKS VALUES(3,'SOCIAL',94);

SELECT * FROM DEMO.STUDENT_MARKS;

/* Record Type using Type */
DECLARE
TYPE STUDENT_REC IS RECORD (SNO DEMO.STUDENT_MARKS.SNO%TYPE,SUBJECT DEMO.STUDENT_MARKS.SUBJECT%TYPE, MARKS DEMO.STUDENT_MARKS.MARKS%TYPE);
STUDENT STUDENT_REC;
BEGIN
    SELECT SNO,SUBJECT,MARKS INTO STUDENT FROM DEMO.STUDENT_MARKS WHERE SUBJECT='SOCIAL' AND MARKS>95;
    DBMS_OUTPUT.PUT_LINE(STUDENT.SNO||' '||STUDENT.SUBJECT||' '||STUDENT.MARKS);
END;



/* Record Type using ROWTYPE */
DECLARE
STUDENT DEMO.STUDENT_MARKS%ROWTYPE;
BEGIN
    SELECT SNO,SUBJECT,MARKS INTO STUDENT FROM DEMO.STUDENT_MARKS WHERE SUBJECT='SOCIAL' AND MARKS>95;
    DBMS_OUTPUT.PUT_LINE(STUDENT.SNO||' '||STUDENT.SUBJECT||' '||STUDENT.MARKS);
END;



/*Collections:
============
1.Varray */


DECLARE
TYPE MY_VARRAY IS VARRAY(9) OF DEMO.STUDENT_MARKS.SNO%TYPE;
STUDENT MY_VARRAY;
BEGIN
    SELECT DISTINCT SNO BULK COLLECT INTO STUDENT FROM DEMO.STUDENT_MARKS;
    for i in 1..STUDENT.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(STUDENT(i));
    END LOOP;
END;

/*2.a. Nested Table with single dimension */
DECLARE
TYPE MY_NESTED_TABLE IS TABLE OF DEMO.STUDENT_MARKS.SNO%TYPE;
STUDENT MY_NESTED_TABLE;
BEGIN
    SELECT DISTINCT SNO BULK COLLECT INTO STUDENT FROM DEMO.STUDENT_MARKS;
    for i in 1..STUDENT.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(STUDENT(i));
    END LOOP;
END;


/*2.b. Nested table with 2D*/

DECLARE
TYPE STUDENT_REC IS RECORD (SNO DEMO.STUDENT_MARKS.SNO%TYPE,SUBJECT DEMO.STUDENT_MARKS.SUBJECT%TYPE,MARKS DEMO.STUDENT_MARKS.MARKS%TYPE);
TYPE MY_NESTED_TABLE IS TABLE OF STUDENT_REC;
STUDENT MY_NESTED_TABLE;
BEGIN
    SELECT SNO,SUBJECT,MARKS BULK COLLECT INTO STUDENT FROM DEMO.STUDENT_MARKS;
    FOR i in 1..STUDENT.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(STUDENT(i).sno||' '||STUDENT(i).SUBJECT||' '||STUDENT(i).MARKS);
    END LOOP;
END;

/*3 Associate Array*/

DECLARE
TYPE MY_ASS_ARRAY IS TABLE OF DEMO.STUDENT_MARKS.MARKS%TYPE INDEX BY VARCHAR2(10);
STUDENT MY_ASS_ARRAY;
BEGIN

        STUDENT('Maths') := 97;
        STUDENT('Science') := 89;
        STUDENT('Social') := 79;
        DBMS_OUTPUT.PUT_LINE('Maths Marks:'||STUDENT('Maths')||' Science Marks:'||STUDENT('Science')||' Social Marks:'||STUDENT('Social'));
END;