Examples of Collections

Example:
1- Define Record type
2- Define PLSQL table type
3- Define the PLSQL table.

1- TYPE Seasolo_ERP_Call_Rec_Type IS RECORD(
PART_NO VARCHAR2(50),
C_M_FLAG VARCHAR2(50),
QUANTITY VARCHAR2(50),
REQ_DOCK_DATE VARCHAR2(50),
LINE_KEY VARCHAR2(50),
SEA_PART_NO seaeng_ccitemnumber.ccitemnumber%TYPE,
SEA_PART_DESC VARCHAR2(240),
SEA_MOD_NO seaeng_ccfamilymodelinfo.stmodelnumber%TYPE,
CUST_PART_NO VARCHAR2(30),
LIST_PRICE VARCHAR2(50),
SPA_NO VARCHAR2(50),
SPA_ID VARCHAR2(50),
WARR_PRICE VARCHAR2(50),
WARR_MONTH VARCHAR2(50),
TOTAL_WARR_MONTH VARCHAR2(50),
CPT_NAME VARCHAR2(6),
BILLING_FLAG VARCHAR2(50),
PRICING_RULE VARCHAR2(50),
DESCRIPTION VARCHAR2(50),
MIN_ORDER_QTY VARCHAR2(50),
MIN_ORDER_QTY_MULTI VARCHAR2(50),
ERROR_CODE VARCHAR2(50),
ERROR_MSG VARCHAR2(1000),
FREIGHT_TERM VARCHAR2(50),
FREIGHT_QUAL VARCHAR2(50),
REGION seaoe_eta_zones.zone_description%TYPE);

2- TYPE Chg_Req_Tab_Type IS TABLE OF Seasolo_ERP_Call_Rec_Type
INDEX BY PLS_INTEGER;

3- l_tab Chg_Req_Tab_Type;






Manipulation à

l_tab.delete;
l_ctr := 1;

For cur1 in cur_abc
loop
if l_ctr = 1 then
l_tab(1).c1 := cur1.c1;
l_tab(1).c2 := cur1.c2;
l_ctr := l_ctr + 1;
else
l_tab(l_tab.last + 1).c1 := cur1.c1;
l_tab(l_tab.last + 1).c2 := cur1.c2;
end if;
end loop;

for indx in 1..l_tab.count
loop
dbms_output.put_line( 'avg sal = ', l_tab(indx).c1 + 20);
end loop;



Also bulk collect to a PLSQL table...

SELECT * BULK COLLECT
INTO l_chg_req_tab
FROM seacmgt_change_request_log
WHERE erp_interfaced_flag = 'N'
AND error_code IS NULL
AND error_message IS NULL
AND return_status IS NULL
AND operation_code IN ('ADD_ORDER', 'ADD_LINE')
AND active_flag = 'Y'
AND cart_key IN
( SELECT cart_key
FROM seacmgt_change_request_log
WHERE erp_interfaced_flag = 'N'
AND error_code IS NULL
AND error_message IS NULL
AND return_status IS NULL
AND operation_code = 'ADD_ORDER'
AND active_flag = 'Y')
ORDER BY change_request_key ASC;