After reading the book on PL / SQL, I did not fully understand what type of collection is better to use if it does not need to use it in SQL commands, you do not need to apply the operations on sets and so on. Because the main advantage of the associative collection in front of the situation invested in the above, as a rule, is positioned that it is not necessary to expand. But I would like to understand how it affects productivity, and which collection will be more perfect. I would also like to understand what pluses gives varray compared to the table.
The question can be reformulated like this: why in any situation just do not use nested tables, as they are the most functional?
Answer 1, Authority 100%
It all depends on where and how the collection is used, there is no universal.
In PL / SQL code, it is better to use an associative collection. A little more productive, do not extend, not only numerical indunts, but not available in SQL and no designer. Also, with the words oracle , they are effective in the host variables.
in SQL, if you need to save as a column in tables, Varrays and Nested Tables are somewhat different – Varrays are saved as LOB Nested Tables in its own table (hence and name). Some DML are available for Nested Tables , but not available for Varrays .
An example of an associative collection with Varchar2 key and initialization from the table
(Scheme of examples of sh ):
declare
Type Items_Type IS Table of Varchar2 (64) Index by Varchar2 (64);
ITEMS ITEMS_TYPE;
FUNCTION ITEMS_CHOR (STMT VARCHAR2) RETURN ITEMS_TYPE IS
Type pair_row is record (
Key Varchar2 (64),
Value Varchar2 (64)
);
Type Refcur_Type is Ref Cursor; --Return pair_row;
RC Refcur_Type;
Type pairs_type is table of pair_row index by pls_integer;
pairs pairs_type;
RET ITEMS_TYPE;
Begin.
Open RC for STMT;
Fetch RC Bulk Collect Into Pairs;
for idx in pairs.first..pairs.Last Loop
RET (PARS (IDX )KEY): = pairs (IDX) .Value;
End Loop;
RETURN RET;
end;
Begin.
Items: = Items_CTOR (Q '[
SELECT 'E' || to_char (PROD_ID) PROD_CODE, PROD_NAME
from Products.
WHERE PROD_NAME LIKE 'E%'
] ');
& lt; & lt; foreach & gt; & gt; DECLARE KEY VARCHAR2 (32): = items.first;
Begin While Key Is Not Null Loop
dbms_output.put_line ('Item (' || Key || ') =' || Items (Key));
Key: = Items.Next (Key);
End Loop; end foreach;
end;
/