Composite Data types are of two types
1. PL/SQL Records
2. PL/SQL Collections
à Index By Table
à Nested Table
à VARRAY
* Composite data types are also known as collections
- they are RECORD,TABLE,NESTED TABLE and VARRAY
RECORD data type:-
· A RECORD is a group of related data items stored as fields each with its own name and data type.
· PL/SQL Records are similar to structures in 3GL’s
· A RECORD is not the same as Row in a database table
· RECORD treats a collection of fields as a logical unit.
· These are (RECORD type) convenient for fetching a row of data from a table for processing
· RECORDS also can be declared.
Syntax à TYPE type_name is RECORD
(filed declaration,…..);
identifier type_name;
Ex:- TYPE emp_record_type is RECORD
last_name varchar2(50),
job_id varchar2(10),
salary number*8,2));
emp_record emp_ record_type;
· fields declared as NOT NULL must be initialized.
INDEX BY Table data types:-
* This data type contains two components .
1. Primary key of data type BINARY_INTEGER
2. column of scalar or record data type.
* Objects of the TABLE type are called INDEX BY Tables
· they are modeled as (but not the same as ) data base tables.
· INDEX BY Table are a primary key to provide the user with array-like access to rows.
· INDEX BY table is similar to an ARRAY.
· It can be increased in size dynamically because they are un constrained.
* there are two steps involved in creating a INDEX BY table.
1. Declare a TABLE data type.
2. Declare a variable of that type.
· the size of the INDEX BY Table is un constrained increase dynamically so that INDEX BY Table an increase dynamically, so that INDEX BY Table grows as new rows are added.
· INDEX BY Tables can have one column and a unique identifier to that one column neither of which can be named.
· The column can belong to any scalar or record data type, but the primary key must be ling to type BINARY_INTEGER
· INDEX BY Tables cannot be initialized at the time of its declaration and also it cannot be populated at the time of declaration.
· An exploit executable statement is required to initialize (populate) the INDEX BY TABLE.
Employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
-this can be reterened by
INDEX Bytable_name (primary_key_value);
- The Following methods are used with INDEX BY Tables.
1. EXISTS
2. OUNT
3. FIRST AND LAST
4. PRIOR
5. NEXT
6. TRIM
7. DELETE
INDEX BY Table of Records:_
· At a given point of time. INDEX BY Table can store only the details of any one of the columns of a database table
· To store al the columns retried by a query,
INDEX BY Table of Records are used.
- Because only the table definition is needed to hold information about all of the fields of a data base table, the table of records greatly increases the functionality if INDEX BY Table.
Syntax à TYPE dept_table_type IS TABLE OF
Departments % ROWTYPE
INDEX BY BINARY_INTEGER;
Dept_table dept_table_type;
*% ROW TYPE attribute can be used to declare a record that represents a row in a database table.
*The difference between the % ROWTYPE attribute and the composite data type RECORD is that RECORD allows to specify the data types of fields in the record or to declare new fields with new data types.
Nested Tables
· Nested Table is an ordered group of items of type TABLE.
· Nested Table contain multiple columns and can be used as variables, parameters, results, attributes and columns.
· They can be thought of as one column data base tables.
· Rows of a nested table are not stored in any particular order.
· The size of a nested table can be increased dynamically i.e. nested tables are unbounded.
· Elements in a table initially have consecutive subscripts, but as elements are deleted, they can have non-consecutive subscripts.
· The range of values for nested table subscripts is 1 ..2147483647.
· To extend a nested table, the built-in procedure EXTEND must be used.
· To delete elements, the built-in procedure DELETE must be used.
· An un initialized nested table is automatically null, so the IS NULL comparison operator can be used to ses if nested table is null.
· The operators CAST, THE and MULTISET are used or manipulating nested tables.
1. Creation of a Nested Table
Defining an object type.
SQL> Create type ELEMENTS AS OBJECT
(ELEM_ID Number (6),
PRICE Number (7,2));
/
2. Create a table type ELEMENTS_TAB which stores ELEMENTS objects.
SQL> Create TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS
/
3. Create a data base table STORAGE having type ELEMENTS_TAB as one of its columns.
SQL> Create Table STORAGE
(Saleman number(4),
Elem_id number(6),
Ordered Date,
Items Elements_Tab)
NESTED TABLE ITEMS STORE AS ITEMS_TAB;
VARRAYS:-
· VARRAYS are ordered group of items of type VARRAY.
· VARRAYS can be used to associate a single identifier with an entire collection.
· This allows manipulation of the collection as a whole and easy reference of individual elements.
· The maximum size of VARRAY needs to be specified in its type definition.
· The range of values for the index of a VARRAY is from 1 to the maximum specified in its type definition.
· If no elements are in the (table) ARRAY, then the ARRAY is automatically null.
· The main use of VARRAY is to group small of uniform-sized collection of objects.
· Elements of a VARRAY cannot be accessed individually SQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript.
· The type of the element of a VARRAY can be any PL/SQL type except the following.
BOOLEAN, TABLE, VARRAY etc.
· VARRAYS can be used to retrieve an entire collection as a value.
· VARRAY data is stored in-line, in the table space as the other data in its row.
· When a VARRAY is declared, a constructor with the same name as the VARRAY is implicitly defined.
· The constructor creates a VARRAY from the elements passed to it.
· A VARRAY can be assigned to another VARRAY, provided the data types are the exact same type.
· TYPE my_VARRAY1 IS VARRAY (10) OF MY_Type;
· Is NULL comparison operator can be used to see if a VARRAY is null.
· VAARAYS cannot be compared for equality or in equality.
Creating a VARRAY:-
1. Defining object type ELEMENTS
SQL> Create TYPE MEDICINES AS OBJECT
(MED_ID NUMBER (6),
MED_NAME Varchar2 (14),
MANF_DATE DATE);
/
2. Define a VARRAY type MEDICINE_ARR which stores MEDICINES. objects
SQL> Create TYPE MEDICINE_ARR AS VARRAY (40)
OF MEDICNIES;
/
3. Creating a relational table MED_STORE which has MEDICINE_ARR as a column type
SQL> Create table MED_STORE(
Location varchar2 (15),
Store_Size number (7),
Employees number (6),
Med_Items Medicine_Arr);
Differences between nested tables and Varrays
· Nested Tables are unbounded, where as Varrays have a maximum size.
· *Individual elements can be deleted from a nested table, but not from a Varray.
· Therefore nested tables can be spares, where as Varrays always are dense.
· Varrays are stored by Oracle in-line (in the same table space), where as nested table data is out-of-line in a store table, which is a system generated data base table associated with the nested table.
· When stored in the data base, nested tables do not retain their ordering and subscripts, where as Varrays do.
· Nested tables support indexes while VARRAYS do not.
No comments:
Post a Comment