What Is Collection And Use Of Collection?

Anchor Type Declarations :
--------------------------
%type - Used to give specific column data type to a Variable
%rowtype - Used to give entire row data type to a Variable

Examples : %type
----------
declare
v_vendor_name ap_suppliers.vendor_name%type;
begin
select vendor_name
into v_vendor_name
from ap_suppliers
where vendor_id = 600;
dbms_output.put_line('Vendor Name : '||v_vendor_name);
end;


Examples : %rowtype
----------
declare
v_vendor_type ap_suppliers%rowtype;
begin
select vendor_name,
        segment1,
terms_id,
PAY_GROUP_LOOKUP_CODE
into v_vendor_type.vendor_name,
v_vendor_type.segment1,
v_vendor_type.terms_id,
v_vendor_type.PAY_GROUP_LOOKUP_CODE
from ap_suppliers
where vendor_id = 600;
dbms_output.put_line('Vendor Name : '||v_vendor_type.vendor_name);
dbms_output.put_line('Vendor Number : '||v_vendor_type.segment1);
dbms_output.put_line('Terms ID : '||v_vendor_type.terms_id);
dbms_output.put_line('Pay Group Lookup Code : '||v_vendor_type.PAY_GROUP_LOOKUP_CODE);
end;


PLSQL Collections :
-------------------
-- Composite Data Types / User defined Data types
-- Collection of multiple data types as a Data Type

PLSQL Record Types
PLSQL Table Types (Index by Table)
Nested Table
VARRAY

PLSQL Record Type :
-------------------
-- Collection of Multiple Data Types each with its own name and own attribute
values

Syntax :
--------
TYPE <type_name> is RECORD (col list with Data Type
   );

Ex :
----
TYPE xx_supp_rec IS RECORD (vendor_name ap_suppliers.vendor_name%type,
           segment1 varchar2(100),
   terms_name ap_terms_tl.name%type,
   PAY_GROUP_LOOKUP_CODE varchar2(100)
  );

v_supp_rec xx_supp_rec;

Examples : (RECORD TYPE)
----------
declare
TYPE xx_supp_rec IS RECORD (vendor_name ap_suppliers.vendor_name%type,
           segment1 varchar2(100),
   terms_name ap_terms_tl.name%type,
   PAY_GROUP_LOOKUP_CODE varchar2(100)
  );

v_supp_rec xx_supp_rec;

begin
select vendor_name,
        segment1,
(select name from ap_terms_tl where term_id=aps.terms_id) terms_name,
PAY_GROUP_LOOKUP_CODE
into v_supp_rec.vendor_name,
v_supp_rec.segment1,
v_supp_rec.terms_name,
v_supp_rec.PAY_GROUP_LOOKUP_CODE
from ap_suppliers aps
where vendor_id = 600;
dbms_output.put_line('Vendor Name : '||v_supp_rec.vendor_name);
dbms_output.put_line('Vendor Number : '||v_supp_rec.segment1);
dbms_output.put_line('Terms Name : '||v_supp_rec.terms_name);
dbms_output.put_line('Pay Group Lookup Code : '||v_supp_rec.PAY_GROUP_LOOKUP_CODE);
end;


Examples : (RECORD TYPE with Loop)
----------
declare
cursor c_supp_cur
is
select vendor_name,
        segment1,
(select name from ap_terms_tl where term_id=aps.terms_id) terms_name,
PAY_GROUP_LOOKUP_CODE
from ap_suppliers aps
where rownum < 11;

TYPE xx_supp_rec IS RECORD (vendor_name ap_suppliers.vendor_name%type,
           segment1 varchar2(100),
   terms_name ap_terms_tl.name%type,
   PAY_GROUP_LOOKUP_CODE varchar2(100),
   DESCRIPTION  ap_terms_tl.DESCRIPTION%type
  );

v_supp_rec xx_supp_rec;

begin
for i in c_supp_cur
loop
v_supp_rec.vendor_name := i.vendor_name;
v_supp_rec.segment1 := i.segment1;
v_supp_rec.terms_name := i.terms_name;
v_supp_rec.PAY_GROUP_LOOKUP_CODE := i.PAY_GROUP_LOOKUP_CODE;

select DESCRIPTION
into    v_supp_rec.DESCRIPTION
from ap_terms_tl
where name = i.terms_name;

dbms_output.put_line('Vendor Name : '||v_supp_rec.vendor_name);
dbms_output.put_line('Vendor Number : '||v_supp_rec.segment1);
dbms_output.put_line('Terms Name : '||v_supp_rec.terms_name);
dbms_output.put_line('Terms Desc : '||v_supp_rec.DESCRIPTION);
dbms_output.put_line('Pay Group Lookup Code : '||v_supp_rec.PAY_GROUP_LOOKUP_CODE);
end loop;
end;


PLSQL Table Types :
-------------------
-- Object of Type Table is called PLSQL Table
-- Referenced with Index By

Syntax :
--------
TYPE <type_name> IS TABLE OF <data type>
INDEX BY DATATYPE;

Index By :
----------
pls_interger
varchar2
date


Ex :
----
declare
type xx_emp_tab is TABLE of varchar2
index by pls_integer;

v_emp_tab xx_emp_tab;
begin
select ename
into v_emp_tab
from emp
where empno = 7839;
end;


Ex :
----
declare
TYPE xx_supp_rec IS RECORD (vendor_name ap_suppliers.vendor_name%type,
           segment1 varchar2(100),
   terms_name ap_terms_tl.name%type,
   PAY_GROUP_LOOKUP_CODE varchar2(100)
  );

type xx_sup_tab is TABLE of xx_supp_rec
index by pls_integer;

v_supp_tab xx_sup_tab;

begin

select vendor_name,
        segment1,
(select name from ap_terms_tl where term_id=aps.terms_id) terms_name,
PAY_GROUP_LOOKUP_CODE
into v_supp_tab(1).vendor_name,
v_supp_tab(1).segment1,
v_supp_tab(1).terms_name,
v_supp_tab(1).PAY_GROUP_LOOKUP_CODE
from ap_suppliers aps
where vendor_id = 600;
dbms_output.put_line('Vendor Name : '||v_supp_tab(1).vendor_name);
dbms_output.put_line('Vendor Number : '||v_supp_tab(1).segment1);
dbms_output.put_line('Terms Name : '||v_supp_tab(1).terms_name);
dbms_output.put_line('Pay Group Lookup Code : '||v_supp_tab(1).PAY_GROUP_LOOKUP_CODE);
end;


Table Type Attributes :
-----------------------
FIRST
LAST
COUNT
DELETE
EXTEND
etc..

if v_supp_tab.COUNT >0 then
v_supp_tab.delete;
end if;