composite Datatype

COMPOSITE DATATYPE ARE TWO TYPES

--PL/SQL Records
--PL/SQL collection
-->INDEX BY table
-->Nested Tables
-->VARRAY
-------------------------------------------------------------

***************************
DECLARATION OF PL/SQL RECORDS
***************************

DECLARE
TYPE t_Rec1Type IS RECORD (
Field1 NUMBER,
Field2 VARCHAR2(5));
TYPE t_Rec2Type IS RECORD (
Field1 NUMBER,
Field2 VARCHAR2(5));
v_Rec1 t_Rec1Type;
v_Rec2 t_Rec2Type;
BEGIN
/* Even though v_Rec1 and v_Rec2 have the same field names
and field types, the record types themselves are different.
This is an illegal assignment which raises PLS-382.
v_Rec1 := v_Rec2;*/

v_Rec1.Field1 := v_Rec2.Field1;
v_Rec2.Field2 := v_Rec2.Field2;
END;
/
----------------------------------------------------------------

***************************
RECORD USING SELECT STATEMENT
***************************

DECLARE
TYPE EMPRecord IS RECORD (
A EMP.ENAME%TYPE,
B EMP.SAL%TYPE,
C EMP.MGR%TYPE);
EMPL EMPRecord;
BEGIN
SELECT ENAME,SAL,MGR
INTO EMPL
FROM EMP
WHERE COMM = 0;
DBMS_OUTPUT.PUT_LINE('ENAME:>'||EMPL.A);
DBMS_OUTPUT.PUT_LINE('SALARY:>'||EMPL.B);
DBMS_OUTPUT.PUT_LINE('MANAGER:>'||EMPL.C);
END;

----------------------------------------------------------------

***************************

RETRIEVING DATA USING ROWTYPE
***************************

DECLARE
TYPE EMPRecord IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
EMPL EMPRecord;
BEGIN
SELECT *
INTO EMPL(100)
FROM EMP
WHERE COMM = 0;
DBMS_OUTPUT.PUT_LINE('ENAME:>'||EMPL(100).ENAME);
END;
------------------------------------------------------------------

***************************
USING COUNT ATTRIBUTE
***************************

DECLARE
TYPE NUMBRec IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
Numbers NUMBRec;
Total NUMBER;
BEGIN
FOR Counter IN 1..50 LOOP
Numbers(Counter) := Counter;

END LOOP;
Total := Numbers.COUNT;
DBMS_OUTPUT.PUT_LINE('THE COUNT IS :>'||TOTAL);

END;
------------------------------------------------------------------

***************************
"DELETE" STATEMENT
***************************

DECLARE
TYPE t_ValueTable IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
Val t_ValueTable;
BEGIN
Val(1) := 'One';
Val(3) := 'Three';
Val(-2) := 'Minus Two';
Val(0) := 'Zero';
Val(100) := 'Hundred';
DBMS_OUTPUT.PUT_LINE('Before DELETE, COUNT=' || Val.COUNT);
Val.DELETE(100); -- Removes 'Hundred'
DBMS_OUTPUT.PUT_LINE('After first DELETE, COUNT=' || Val.COUNT);
Val.DELETE(1,3); -- Removes 'One' and 'Three'
DBMS_OUTPUT.PUT_LINE('After second DELETE, COUNT=' || Val.COUNT);
Val.DELETE; -- Removes all remaining values
DBMS_OUTPUT.PUT_LINE('After last DELETE, COUNT=' || Val.COUNT);
END;
/
------------------------------------------------------------------

***************************
INSERTING THROUGH PL/SQL RECORD
***************************

CREATON OF TABLE BEFORE EXAMPLE
CREATE or REPLACE TABLE temp_table(CAR_COL VARCHAR2(40));

/*INSERTING using "EXIST" ATTRIBUTE*/
DECLARE
TYPE NameTable IS TABLE OF EMP.ENAME%TYPE
INDEX BY BINARY_INTEGER;
EMPL NameTable;
BEGIN
-- Insert rows into the table.
EMPL(1) := 'Scott';
EMPL(3) := 'Joanne';

-- Check to see if rows exist.
IF EMPL.EXISTS(1) THEN
INSERT INTO temp_table(char_col) VALUES('Row 1 exists!');
ELSE
INSERT INTO temp_table (char_col) VALUES('Row 1 doesn''t exist!');
END IF;

IF EMPL.EXISTS(2) THEN
INSERT INTO temp_table (char_col) VALUES('Row 2 exists!');
ELSE
INSERT INTO temp_table (char_col) VALUES('Row 2 doesn''t exist!');
END IF;
END;
/

---------------------------------------------------------------------------

***************************
USING 'FIRST'&'LAST'&'NEXT' ATTRIBUTE
***************************

DECLARE
TYPE NameTable IS TABLE OF EMP.Ename%TYPE INDEX BY BINARY_INTEGER;
Names NameTable;
IndX BINARY_INTEGER;
BEGIN
Names(43) := 'Mason';
Names(50) := 'Junebug';
Names(47) := 'Taller';

-- Assigns 43 to v_Index.
Indx := Names.FIRST;
DBMS_OUTPUT.PUT_LINE('INDEX='||Indx );

-- Assigns 47 to v_Index.
Indx := Names.NEXT(Indx);
DBMS_OUTPUT.PUT_LINE('INDEX='||Indx );

-- Assigns 47 to v_Index.
Indx := Names.PRIOR(Indx);
DBMS_OUTPUT.PUT_LINE('INDEX='||Indx );

-- Assigns 50 to v_Index.
Indx := Names.LAST;
DBMS_OUTPUT.PUT_LINE('INDEX='||Indx );

END;
---------------------------------------------------------------------------

***************************
A MORE PRACTICAL EXAMPLE
***************************

DECLARE
TYPE EMPTABLE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
MYEMPTABLE EMPTABLE;
COUNTER NUMBER(3):=104;
BEGIN
FOR I IN 100..COUNTER
LOOP
SELECT * INTO MYEMPTABLE(I) FROM EMP WHERE COMM=0;
END LOOP;
FOR I IN MYEMPTABLE.FIRST..MYEMPTABLE.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(MYEMPTABLE(I).ENAME);
END LOOP;
END;
---------------------------------------------------------------------------