· Index-by tables or Associative array
· Nested table
· Variable-size array or Varray
PL/SQL provides three collection types:
· Index-by tables or Associative array
· Nested table
· Variable-size array or Varray
Index-By Tables:
The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.
The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.
This can only be used in PL/SQL
Nested Table Collections:
Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column but index-by tables cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation the collection must be dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXT method overcomes the problems of traversing sparse collections.
Varray Collections:
A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.
Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column but index-by tables cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation the collection must be dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXT method overcomes the problems of traversing sparse collections.
Varray Collections:
A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.
Index-by tables or Associative array
Nested Table Collections:
Example 1 : 
Insert data into table:
INSERT INTO nested_table VALUES (1, my_tab_dev('A'));
INSERT INTO nested_table VALUES (2, my_tab_dev('B', 'C'));
INSERT INTO nested_table VALUES (3, my_tab_dev('D', 'E', 'F'));
COMMIT;
Select data from nested_table:
SELECT * FROM nested_table; -- using F9
ID COL1
--------------------
1 (DATASET)
2 (DATASET)
3 (DATASET)
COL1 DATA is displayed as (DATASET) this will not be displayed as this is nested table
SELECT * FROM nested_table; -- using F5 ( Will not display in grid form)
ID COL1(ELEMENT)
-- ---------------------------------------------------
1 MY_TAB_DEV(A)
2 MY_TAB_DEV(B,C)
3 MY_TAB_DEV(D,E,F)
3 rows selected.
How to display complete data in grid form (UNNESTING the table )
SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.col1) t2;
ID COLUMN_VALUE
---------- ------------------------
1 A
2 B
2 C
3 D
3 E
3 F
6 rows selected.
--Extract data with PL/SQL:
declare
v_vcarray my_tab_dev;
begin
for c1 in (select * from nested_table) loop
dbms_output.put_line('Row fetched...');
FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP
dbms_output.put_line('...property fetched: '|| c1.col1(i));
END LOOP;
end loop;
end;
/
/* OUTPUT */
Row fetched...
...property fetched: A
Row fetched...
...property fetched: B
...property fetched: C
Row fetched...
...property fetched: D
...property fetched: E
...property fetched: F
Example 2 : Complex one
INSERT INTO customers VALUES (1,
address_tab(
address_t('101 First', 'Redwood Shores', 'CA', '94065'),
address_t('123 Maple', 'Mill Valley', 'CA', '90952')
) );
INSERT INTO customers VALUES (2,
address_tab(
address_t('124 dev', 'hinjewadi', 'MH', '41057')
));
select * from customers; -- using F9
CUSTID
|
ADDRESS
|
1
|
(DATASET)
|
2
|
(DATASET)
|
This will not give the out as the whole object as DATASET for Address field.
select * from customers; -- using F5
CUSTID
----------
ADDRESS(STREET,CITY,STATE,ZIP)
-----------------------------------------------------------------------------
1
ADDRESS_TAB(ADDRESS_T(101 First,Redwood Shores,CA,94065),ADDRESS_T(123 Maple,Mil
l Valley,CA,90952))
2
ADDRESS_TAB(ADDRESS_T(124 dev,hinjewadi,MH,41057))
2 rows selected.
This will give the output in text form will not be in form of grid
SELECT CUSTID , STREET,CITY, STATE, ZIP
FROM customers2 C1, TABLE (C1.ADDRESS)
CUSTID STREET CITY STATE ZIP
-------------------------------------
101 123 SHAH1 GJ 45446
101 101 DEV 1 MH 94065
102 124 HINJ MH 4474
Detailed Output as its stored in DB
CUSTID
|
ADDRESS
| ||||||||||||
101
|
| ||||||||||||
102
|
|
There are two rows in address nested table while only single value for CUSTID =”101”
O/P will have CUSTID “101” will be cross join between CUSTID and ADDRESS
SCALAR COLLECITON
Scalar variable is any datatype that holds one thing at a time
Scalar collection returns with a “COLUMN_VALUE” column name, this can be used in output. Multi-dimensional or aggregate table return with an explicitly described record type information is converted In database for VARRAY and Nested table. Similarly this is not true for PL/SQL record type you need to convert using pipelined table.
VARRAY
You can use VARRAY in SQL as well as PL/SQL , can be used in anonymous as well as named block at schema level.
--USING VARRAY IN ANNONYMOUS BLOCK
DECLARE
varr1 SCALAR_VARRAY := SCALAR_VARRAY('ONE','TWO','THREE');
varr2 SCALAR_VARRAY := SCALAR_VARRAY();
BEGIN
FOR I IN 1..varr1.COUNT LOOP
varr2.EXTEND ; -- Allocated space to the colleciton
varr2(I):=varr1(I); -- assign a value
END LOOP;
FOR I IN 1..varr2.count LOOP
dbms_output.put_line('varr2 :(' || i ||') : ' || varr2(I));
END LOOP;
END;
OUTPUT
varr2 :(1) : ONE
varr2 :(2) : TWO
varr2 :(3) : THREE
-- DROPPING CREATED TYPE
DROP TYPE SCALAR_VARRAY;
-- USING VARRAY IN NAMED BLOCK
CREATE OR REPLACE FUNCTION GET_ARRAY(VAL1 VARCHAR2,VAL2 VARCHAR2,VAL3 VARCHAR2)
RETURN SCALAR_VARRAY IS
varr3 SCALAR_VARRAY := scalar_varray();
i NUMBER(2):=0;
BEGIN
IF VAL1 IS NOT NULL THEN
varr3.extend;
i := i+1;
varr3(i):=VAL1;
end if ;
IF VAL2 IS NOT NULL THEN
varr3.extend;
i := i+1;
varr3(i):=VAL2;
end if ;
IF VAL3 IS NOT NULL THEN
varr3.extend;
i := i+1;
varr3(i):=VAL3;
end if ;
IF i =0 THEN
varr3(1):='NO VALUE PASSED';
end if;
return varr3;
END;
/
select * from table(GET_ARRAY(1,2,3));
-- OUTPUT
COLUMN_VALUE
------------
1
2
3
select * from table(GET_ARRAY(NULL,NULL,5));
-- OUTPUT
COLUMN_VALUE
------------
5
Few methods for VARRAY
· COUNT / LAST – this gives maximum index of the array
· LIMIT – Returns maximum number ( this only works with VARRAY)
INDEX-BY TABLE: ASSOCIATIVE ARRAY
An index-by table (also called an 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.
An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name whose keys will be of subscript_type and associated values will be of element_type
DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('dev') := 62000;
salary_list('shah') := 75000;
salary_list('d1') := 10v0000;
salary_list('d2') := 78000;
-- printing the table
name := salary_list.FIRST;
WHILE name IS NOT null LOOP
dbms_output.put_line
('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;
/
--When the above code is executed at SQL prompt, it produces the following result:
-------------- OUTPUT-------------------------
Salary of dev is 62000
Salary of shah is 75000
Salary of d1 is 100000
Salary of d2 is 78000
-------------- OUTPUT-------------------------
PL/SQL procedure successfully completed.
Example:
Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:
Select * from customers;
+----+----------+-----+-----------+------------------------------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+-------------------------------+
| 1 | Dev | 32 | Ahmedabad | 2000.00 |
| 2 | Shah | 25 | Delhi | 1500.00 |
| 3 | devshah | 23 | Kota | 2000.00 |
+----+----------+-----+-----------+------------------------------+
DECLARE
CURSOR c_customers is
select name from customers;
TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;
name_list c_list;
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list(counter) := n.name;
dbms_output.put_line('Customer('||counter|| '):'||name_list(counter));
END LOOP;
END;
/
--When the above code is executed at SQL prompt, it produces the following result:
-------------- OUTPUT-------------------------
Customer(1): Dev
Customer(2): Shah
Customer(3): devshah
| ollection Type | Number of Elements | Subscript Type | Dense or Sparse | Where Created | Can Be Object Type Attribute |
|---|---|---|---|---|---|
| Associative array (or index-by table) | Unbounded | String or integer | Either | Only in PL/SQL block | No |
| Nested table | Unbounded | Integer | Starts dense, can become sparse | Either in PL/SQL block or at schema level | Yes |
| Variable-size array (Varray) | Bounded | Integer | Always dense | Either in PL/SQL block or at schema level | Yes |
No comments:
Post a Comment