This article contains information about Oracle PL\/SQL Collections, types such as Associative Array, Nested Table and Varray, also collection methods.<\/p>\n
What is Collection Oracle?<\/h3>\n
Collections are data types where values with a similar data type are stored and the capacity of the variables grows dynamically.<\/p>\n
Why do we use collections in Oracle?<\/h3>\n
Collections are used to store data of the same data type in a variable, such as arrays contained in PL \/ SQL.<\/p>\n
The difference of the collections from the arrays is that their dimensions can be changed when new elements are added or requested.<\/p>\n
Collection Types in Oracle PL\/SQL<\/h3>\n
There are 3 types of PL \/ SQL collection structures.<\/p>\n
\n
Associative (Index-By Table)<\/li>\n
Nested Table<\/li>\n
Varray<\/li>\n<\/ul>\n
Associative Array in Oracle PL\/SQL with Example<\/h3>\n
A collection type that stores data as a key-value pair.<\/p>\n
The collection size increases as data is added. Associative Array definition is as follows.<\/p>\n
TYPE array_name IS TABLE OF value_data_type [NOT NULL] INDEX BY key_data_type;<\/pre>\n
Sample Associative Array usage is as follows.<\/p>\n
DECLARE\r\n TYPE PERSON_LIST IS TABLE OF NUMBER INDEX BY VARCHAR2(20);\r\n PERSON PERSON_LIST;\r\n PERSON_NAME VARCHAR2(20);\r\nBEGIN\r\n PERSON('Yusuf') := 123;\r\n PERSON('Ramazan') := 456;\r\n PERSON('Sinan') := 789;\r\n PERSON('Mehmet') := 987;\r\n\r\n PERSON_NAME := PERSON.FIRST;\r\n WHILE PERSON_NAME IS NOT NULL LOOP\r\n DBMS_OUTPUT.put_line(PERSON_NAME || ' - ' || PERSON(PERSON_NAME));\r\n PERSON_NAME := PERSON.NEXT(PERSON_NAME);\r\n END LOOP; \r\nEND;<\/pre>\n
If you want to make the key value a numerical value, a numeric data type such as PLS_INTEGER, NUMBER can be specified instead of VARCHAR2 (20).<\/p>\n
Nested Table in Oracle PL\/SQL with Example<\/h3>\n
It is a type of collection that allows us to store data as in arrays.<\/p>\n
The most important feature that distinguishes this collection from the arrays is that its capacity can be increased.<\/p>\n
TYPE array_name IS TABLE OF value_data_type [NOT NULL]<\/pre>\n
Unlike the Associative Array type, INDEX BY cannot be used.<\/p>\n
DECLARE\r\n TYPE PERSON_LIST IS TABLE OF VARCHAR2(20);\r\n PERSON PERSON_LIST;\r\n PERSON_COUNT PLS_INTEGER;\r\nBEGIN\r\n PERSON := PERSON_LIST('Yusuf', 'Ramazan', 'Sinan', 'Ramazan');\r\n\r\n PERSON_COUNT := PERSON.COUNT;\r\n FOR I IN 1 .. PERSON_COUNT LOOP\r\n DBMS_OUTPUT.put_line(I || ' - ' || PERSON(I));\r\n END LOOP;\r\nEND;<\/pre>\n
Adding value to the collection is done by expanding the collection with EXTEND method.<\/p>\n
DECLARE\r\n TYPE PERSON_LIST IS TABLE OF VARCHAR2(20);\r\n PERSON PERSON_LIST;\r\n PERSON_COUNT PLS_INTEGER;\r\nBEGIN\r\n PERSON := PERSON_LIST('Yusuf', 'Ramazan', 'Sinan', 'Ramazan');\r\n\r\n PERSON.EXTEND;\r\n PERSON(PERSON.COUNT) := 'New value added.';\r\n\r\n PERSON_COUNT := PERSON.COUNT;\r\n FOR I IN 1 .. PERSON_COUNT LOOP\r\n DBMS_OUTPUT.put_line(I || ' - ' || PERSON(I));\r\n END LOOP;\r\nEND;<\/pre>\n
Varray in Oracle PL\/SQL with Example<\/h3>\n
Arrays in many programming languages \u200b\u200bare also available in PL\/SQL.<\/p>\n
Unlike other programming languages, PL \/ SQL arrays start from 1. Array definition is as follows.<\/p>\n
CREATE OR REPLACE TYPE array_name IS VARRAY(array_dimension) OF array_data_type<\/pre>\n
TYPE array_name IS VARRAY(array_dimension) OF array_data_type<\/pre>\n
Sample array usage is as follows.<\/p>\n
SET SERVEROUTPUT ON;\r\nDECLARE\r\n TYPE PERSON IS VARRAY(4) OF VARCHAR2(20);\r\n MYLIST PERSON;\r\n PERSON_COUNT PLS_INTEGER := 0;\r\nBEGIN\r\n MYLIST := PERSON('Yusuf', 'Ramazan', 'Sinan', 'Mehmet');\r\n PERSON_COUNT := MYLIST.COUNT;\r\n\r\n FOR i IN 1..PERSON_COUNT LOOP\r\n DBMS_OUTPUT.put_line(MYLIST(i));\r\n END LOOP;\r\n\r\nEND;<\/pre>\n
Collection Methods in Oracle PL\/SQL<\/h3>\n
There are several methods for using collections flexibly.<\/p>\n
EXISTS (n):<\/strong> Checks if the specified element exists in the collection.<\/p>\n
COUNT:<\/strong> Returns the number of elements in the collection.<\/p>\n
LIMIT:<\/strong> Returns the collection capacity.<\/p>\n
FIRST:<\/strong> Returns the first index value in the collection.<\/p>\n
LAST:<\/strong> Returns the last index value in the collection.<\/p>\n
PRIOR (n):<\/strong> Returns the sequence number before n.<\/p>\n
NEXT (n):<\/strong> Returns the sequence number after n.<\/p>\n
EXTEND:<\/strong> Extends the collection by appending a single null element to the collection.<\/p>\n
EXTEND (n):<\/strong> Extends the collection by appending n single null element to the collection.<\/p>\n
EXTEND (n1, n2):<\/strong> Extends the collection by appending n1 copies of the n2th element to the collection.<\/p>\n
TRIM:<\/strong> Removes the last element in the collection.<\/p>\n
TRIM (n):<\/strong> Removes elements as much as n from the end of the collection.<\/p>\n
DELETE:<\/strong> Removes all elements from the collection.<\/p>\n
DELETE (n):<\/strong> Removes element n from the collection.<\/p>\n
DELETE (n1, n2):<\/strong> Removes all elements from n1 to n2 from the collection.<\/p>\n
You can find more detailed information about below topics in the below link.<\/p>\n