Composite datatype:-
There are different types of composite data types. Once again look at the diagram below:-
In composite
datatype we have records and collection. Collection again consist of three other
composite datatype i.e. index by tables, varray (variable size array) and
nested tables. Each one of them has different meaning and different usage. It’s
an advance programming technique. Composite datatype contains internal
component which can be manipulated individually or as a unit. Let’s look at the
composite datatype in detail:-
Records:-
Record is a
group of related data items stored in a field which have its own name and its
datatype. Each record defined can have as many fields as necessary. We can
assign initial values to records or it can be null as well. Record types and
user define records can be used in any block, subprogram or in a package.
Syntax of the records is:-
TYPE RECORD_TYPE_NAME IS RECORD (FIRST_FIELD_NAME DATATYPE, SECOND_FIELD_NAME COLUMM_ DATATYPE...);
Here:-
- record_type_name – it is
the name of the composite type you want to define.
- First_field_name, second_field_name:- are the
names of the columns within the record.
- Column_datatype defines
the scalar datatype of the fields.
We can also use %rowtype as datatype of the record element
if the record will be associated with one table.
We can use %rowtype like: - TABLE_NAME%ROWTYPE
Suppose we have employee table then it would be: - EMPLOYEE%ROWTYPE;
Sample example of the record type (%ROWTYPE) can be like below:-
DECLARE
REC_VARIABLE
EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO REC_VARIABLE FROM EMPLOYEE WHERE EMPNO=4;
DBMS_OUTPUT.PUT_LINE
('THE ROW IS '||REC_VARIABLE.EMPNO||', '||REC_VARIABLE.EMPNAME);
END;
Similarly you can use record to insert values into the
table:-
We can also use cursor based record.
Cursor
based records:-
Cursor based records work similar to the %ROWTYPE but instead
of table/view name in the type declaration we need to use cursor name like
shown below:-
CURSOR CUR_NAME AS SELECT_QUERY;
V_VARIABLE
CUR_NAME%ROWTYPE;
In above
case we are using rowtype which is selected by cursor. So the example can be:-
DECLARE
CURSOR
EMP_CUR IS
SELECT
EMPNO, EMPNAME, DEPTNO
FROM
EMPLOYEE;
REC_VARIABLE
EMP_CUR%ROWTYPE;
BEGIN
OPEN
EMP_CUR;
LOOP
FETCH
EMP_CUR INTO REC_VARIABLE;
EXIT WHEN
EMP_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC_VARIABLE.EMPNO
|| ',' || REC_VARIABLE.EMPNAME);
END LOOP;
END;
Above we
have used cursor name and whatever number of columns that cursor is holding.
We have used %ROWTYPE to display and insert values in the
table. But this record type (%ROWTYPE) most of the time would be usable when we
do manipulation on a particular (single) table, but what if we want to fetch or
manipulate data from multiple table/ views. To achieve this we can declare
record and can use that record in the anonymous block or any subprograms. So
let’s try to declare and use user defined record type.
User
defined record:-
User defined record type is a type which is created by user which
contains the list of column which are required by the user. Suppose you need to
create two varchar columns and two number columns in a record type and you have
no option but declaring record which will contain all above things. So let’s
create the same.
Syntax of the user defined record is:-
TYPE RECORD_TYPE_NAME IS RECORD
(
FIELDNAME_1
DATATYPE_1,
FIELDNAME_2
DATATYPE_2,
FIELDNAME_3
DATATYPE_3,
……………………
);
V_VARIABLE_NAME
RECORD_TYPE_NAME;
Let’s look
at the example:-
DECLARE
TYPE
EMP_RECT IS RECORD
(REC_EMPNO NUMBER,
REC_EMPNAME
VARCHAR2(20),
REC_DEPTTYPE
VARCHAR2(20));
V_VARIABLE
EMP_RECT;
BEGIN
V_VARIABLE.REC_EMPNO:=100;
V_VARIABLE.REC_EMPNAME:='CHANCHALL';
V_VARIABLE.REC_DEPTTYPE:='IT';
DBMS_OUTPUT.PUT_LINE('EMPLOYEE
NUMBER IS '||V_VARIABLE.REC_EMPNO||' EMPLOYEE NAME IS '
||V_VARIABLE.REC_EMPNAME||'
DEPARTMENT IS '||V_VARIABLE.REC_DEPTTYPE);
END;
Another example can be like below:-
DECLARE
TYPE
EMP_RECT IS RECORD
(REC_EMPNO NUMBER,
REC_EMPNAME
VARCHAR2(20),
REC_DEPTTYPE
VARCHAR2(20));
CURSOR CUR
IS SELECT A.EMPNO,A.EMPNAME,B.DEPTTYPE
FROM
EMPLOYEE A, DEPARTMENT B
WHERE
A.DEPTNO=B.DEPTNO;
V_VARIABLE
EMP_RECT;
BEGIN
OPEN CUR;
FETCH CUR
INTO V_VARIABLE;
CLOSE CUR;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE
NUMBER IS '||V_VARIABLE.REC_EMPNO||' EMPLOYEE NAME IS '
||V_VARIABLE.REC_EMPNAME||'
DEPARTMENT IS '||V_VARIABLE.REC_DEPTTYPE);
END;
Below are
the screenshots of above examples:-
Collection:-
A
collection is an ordered group of elements having the same data type. Each
element is identified by a unique subscript that represents its position in the
collection.
PL/SQL
has three collection types:-
· Index-by tables or Associative array
·
Nested
table
·
Variable-size
array or Varray
Index by tables:-
An
index-by table is also called as
associative array is a set of key-value
pairs. Each key is unique and is used to locate the corresponding value. The
key can be either an integer or a string.
Syntax of the
index by table is:-
TYPE
TYPE_NAME IS TABLE OF ELEMENT_TYPE INDEX BY SUBSCRIBE_TYPE;
Let’s look
at the example of the index by table:-
DECLARE
TYPE
TAB_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
V_VARIABLE
TAB_TYPE;
NAME
VARCHAR2 (20);
BEGIN
V_VARIABLE
('CHANCHAL') :=1000;
V_VARIABLE
('SCOTT') :=2000;
V_VARIABLE
('TIGER') :=3000;
V_VARIABLE
('JOHN') :=4000;
NAME: =
V_VARIABLE.FIRST;
WHILE NAME
IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('NAME
OF EMPLOYEE IS ' ||NAME||
‘AND
SALARY IS '|| TO_CHAR(V_VARIABLE(NAME)));
NAME:=V_VARIABLE.NEXT(NAME);
END LOOP;
END;
Nested tables:-
A
nested table is like a
one-dimensional array with an arbitrary number of elements. However, there is a
difference between array and nested tables:-
· An
array has a specific number of elements but nested table does not have the
same. The size of a nested table can increase dynamically but we cannot
increase the size of the array.
· An
array is always dense, it always has consecutive subscripts. A nested array is
initially dense, but it can become sparse when elements are deleted from it.
Syntax of
the nested table:-
TYPE
TYPE_NAME IS TABLE OF ELEMENT_TYPE [NOT NULL];
VARIABLE_NAME
(TABLE_NAME) TYPE_NAME;
Above syntax looks similar to the index by table but if you
see in the above syntax there is no index by clause.
Let’s look at the example:-
DECLARE
TYPE REC_TAB IS
TABLE OF VARCHAR2(10);
TYPE REC_TAB_1 IS
TABLE OF INTEGER;
V_VARIABLE REC_TAB;
V_VARIABLE_1
REC_TAB_1;
V_VARIABLE_2
INTEGER;
BEGIN
V_VARIABLE := REC_TAB('CHANCHAL','JOHN','JON','SCOTT','TIGER');
V_VARIABLE_1 :=
REC_TAB_1(10,20,30,40,50);
V_VARIABLE_2
:=V_VARIABLE.COUNT;
FOR REC IN 1 ..
V_VARIABLE_2 LOOP
DBMS_OUTPUT.PUT_LINE('NAME OF THE EMPLOYEE IS: '||V_VARIABLE(REC));
END LOOP;
END;
Varray:-
We
can specify the maximum size and the type of elements stored in the varray.
Syntax
of the varray is:-
CREATE
OR REPLACE TYPE VARRAY_TYPE_NAME IS VARRAY (N) OF <ELEMENT_TYPE>;
Here:-
varray_type_name is a valid attribute name,
n is the number of elements (maximum) in the varray,
element_type is the data type of the elements of the array.
Varray best example can be our calendar. We have 365 days in a year so its fixed and we also know
it. For that we can use varray like shown below:-
CREATE
OR REPLACE TYPE VARRAY_TYPE_NAME IS VARRAY (365) OF NUMBER;
Read Also:-
Read Also:-
No comments:
Post a Comment