What is types/collections in Pl/SQL?
Collection Types in PL/SQL
Types: Type is nothing but user_defined datatype. In oracle we can create our own datatype. We can create user_defined datatype by using type keyword.There are various types of collection in Pl/SQL following lines.
1)PL/SQL record
2) index by table (or) pl/sql table (or) associative array
3) nested table
4) Varray
5)ref cursor* (will be in next)
Note: where index by table ,nested table and Varray are also known as collections.
Index by table:
It is a type. Which stores number of values into a single unit. It improves application performance because it stored in RAM MEMORY AREA.Index by table has key-value pair. Where key behaves like a primary key. which stores index. Key doesn't have any duplicate value in it. Where value is actual data. To improve performance of the index by table oracle provided binary_integer datatype for key field. Index by table,nested table, Varray are also called as collections. Oracle provided predefined collection methods.Index by table having exists, first, last, prior, next, count, delete(index), delete(index,another_index) , delete collection methods.
We create index by table by two steps. First step we create type with appropriate syntax and second step is after creating type then we declare variable from that type.
Syntax:
Step1: Type typename is table of number (10)
index by binary_integer;
Step 2: variablename typename;
Example A:------->
_______________
declare
type t1 is table of number(10)
index by binary_integer;
a t1;
begin
a(1):=1;
a(2):=2;
a(3):=3;
a(4):=4;
a(5):=5;
dbms_output.put_line(a(1)||' '||a(2));
dbms_output.put_line (a.first);
dbms_output.put_line (a.last);
dbms_output.put_line (a.prior(2));
dbms_output.put_line (a.next(2));
dbms_output.put_line (a.count);
a.delete(2);
dbms_output.put_Line (a.count);
a.delete(3,5);
dbms_output.put_line (a.count);
end;
/
Example B----->
declare
type t2 is table of number(10)
index by binary_integer;
a t2;
b number(10):=1;
begin
for i in 0..4
loop
a(i):=b;
dbms_output.put_line(a(i));
b:=b+1;
end loop;
end;
/
Write a pl/sql program which is use to transfer all enames from emp table and storing into index by table and also display contents from index by table.
declare
cursor c1 is select ename from emp;
v_t ppp.t1;
b number(10):=1;
begin
for i in c1
loop
v_t(b):=i.ename;
dbms_output.put_line (v_t(b));
b:=b+1;
end loop;
end;
/
Example:::::b::>
________________
declare
type t1 is table of varchar2(10)
index by binary_integer;
a t1;
b number(10):=1;
i emp%rowtype;
cursor c1 is select * from emp;
begin
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
a(b):=i.ename;
dbms_output.put_line(a(b));
b:=b+1;
end loop;
close c1;
end;
/
Bulk collect clause:Whenever table has large amount of data and we send this data into collections through cursors then oracle server returns d-grade performance because cursor is a record by record process,it means cursor sends records one by one . to overcome this problem oracle provided bulk collect clause it sends whole data into collections at a time.
through for loop
Syntax: select * bulk collect into collectionvariablename from tablename where condition;
declare
type t1 is table of varchar2(20)
index by binary_integer;
v_t t1;
begin
select ename bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line (v_t(i));
end loop;
end;
/
_________________________________________________________________________________
Through simple loop
declare
type t1 is table of varchar2(20)
index by binary_integer;
a t1;
i number(10):=1;
begin
select ename bulk collect into a from emp;
loop
dbms_output.put_line(a(i));
i:=i+1;
exit when i=a.last;
end loop;
end;
/
Program--------> write a pl/sql program which is use to store next 10 dates into index by table. and also display content from index by table.
Example 1):::::::>
__________________
declare
type t1 is table of date
index by binary_integer;
v_t t1;
begin
for i in 1..10
loop
v_t(i):=sysdate+i;
dbms_output.put_line (v_t(i));
end loop;
end;
/
Example 2)
declare
type t1 is table of date
index by binary_integer;
a t1;
i number(10):=1;
begin
loop
a(i):=sysdate+i;
dbms_output.put_line(a(i));
i:=i+1;
exit when i=10;
end loop;
end;
/
_________________________________________________________________________________
IN INDEX BY TABLE WE CAN ALSO USE CHARACTER DATATYPE IN KEY FIELD BUT IN THIS CASE WE ARE NOT ALLOWED TO USE FOR LOOPS TO DISPLAY ALL DATA FROM INDEX BY TABLE BECAUSE IN FOR LOOPS INDEX VARIABLE BEHAVES LIKE A INTEGER VARIABLE.
declare
type t1 is table of number(30)
index by varchar2(20);
v_t t1;
x varchar2(20);
begin
v_t('a'):=12;
V_T('b'):=45;
v_t('c'):=87;
v_t('d'):=33;
x:='a';
loop
dbms_output.put_line(v_t(x));
x:=v_t.next(x);
exit when x is null;
end loop;
end;
/
Using record type variable (%rowtype) in index by table:
THROUGH BULK COLLECT::::::>
___________________________
declare
type t1 is table of emp%rowtype
index by binary_integer;
v_t t1;
begin
select * bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line (v_t(i).ename||' '||v_t(i).job);
end loop;
end;
/
__________________________________________________________________________
THROUGH CURSOR:::::::::::::>
____________________________
declare
type t1 is table of emp%rowtype
index by binary_integer;
a t1;
i number(10):=1;
cursor c1 is
select * from emp;
begin
open c1;
loop
fetch c1 into a(i);
exit when c1%notfound;
dbms_output.put_line(a(i).ename||' '||a(i).sal||' '||a(i).job);
i:=i+1;
end loop;
close c1;
end;
/
______________________________________________________________________________
In oracle if you want to return large amount of data from database server into client application then first must be develop database server application which returns large amount of data then only we are allow to execute this application by using client applications. In oracle we are returning large amount of data either using procedure out parameter or by using function.
Return type. In this cases we must use user defined datatypes generally, in oracle return large amount of data into client application by using following two methods these are.
Method 1> using index by table.
method 2. using refcursor
WRITE A PL/SQL PROGRAM WHICH IS USED TO RETURN LARGE AMOUNT OF DATA FROM EMP TABLE INTO CLIENT APPLICAION USING INDEX BY TABLE.
Create or replace package pj1
is
type t1 is table of emp%rowtype
index by binary_integer;
function f1 return t1;
end;
/
create or replace package body pj1
is
function f1
return t1
is
v_t t1;
begin
select * bulk collect into v_t from emp;
return v_t;
end f1;
end;
/
v_p pj1.t1;
begin
v_p:=pj1.f1;
for i in v_p.first..v_p.last
loop
dbms_output.put_line(v_p(i).ename||' '||v_p(i).sal);
end loop;
end;
/
syntax: collection_var_name.exists(index)
___________________________
declare
type t1 is table of emp%rowtype
index by binary_integer;
v_t t1;
begin
select * bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line (v_t(i).ename||' '||v_t(i).job);
end loop;
end;
/
__________________________________________________________________________
THROUGH CURSOR:::::::::::::>
____________________________
declare
type t1 is table of emp%rowtype
index by binary_integer;
a t1;
i number(10):=1;
cursor c1 is
select * from emp;
begin
open c1;
loop
fetch c1 into a(i);
exit when c1%notfound;
dbms_output.put_line(a(i).ename||' '||a(i).sal||' '||a(i).job);
i:=i+1;
end loop;
close c1;
end;
/
______________________________________________________________________________
Return result set:
In oracle if you want to return large amount of data from database server into client application then first must be develop database server application which returns large amount of data then only we are allow to execute this application by using client applications. In oracle we are returning large amount of data either using procedure out parameter or by using function.
Return type. In this cases we must use user defined datatypes generally, in oracle return large amount of data into client application by using following two methods these are.
Method 1> using index by table.
method 2. using refcursor
Method 1: index by table
WRITE A PL/SQL PROGRAM WHICH IS USED TO RETURN LARGE AMOUNT OF DATA FROM EMP TABLE INTO CLIENT APPLICAION USING INDEX BY TABLE.
Create or replace package pj1
is
type t1 is table of emp%rowtype
index by binary_integer;
function f1 return t1;
end;
/
create or replace package body pj1
is
function f1
return t1
is
v_t t1;
begin
select * bulk collect into v_t from emp;
return v_t;
end f1;
end;
/
Execution:
declarev_p pj1.t1;
begin
v_p:=pj1.f1;
for i in v_p.first..v_p.last
loop
dbms_output.put_line(v_p(i).ename||' '||v_p(i).sal);
end loop;
end;
/
Exists collection method used in index by table:
It is collection method which is used in collections.This method always returns boolean values only either true or false. It is used to check requesting data available or not.syntax: collection_var_name.exists(index)
EXP:
declare
type t1 is table of number(10)
index by binary_integer;
v_t t1;
begin
v_t(1):=13;
v_t(2):=3;
v_t(3):=55;
v_t(4):=33;
v_t.delete(3); // deleting third index data
for i in v_t.first..v_t.last
loop
dbms_Output.put_line(v_t(i));
end loop;
end;
/
output: 13
3
declare
type t1 is table of number(10)
index by binary_integer;
v_t t1;
begin
v_t(1):=13;
v_t(2):=3;
v_t(3):=55;
v_t(4):=33;
v_t.delete(3); // deleting third index data
for i in v_t.first..v_t.last
loop
dbms_Output.put_line(v_t(i));
end loop;
end;
/
output: 13
3
Error no data found Pl/SQL :
This error occured because we have deleted third index from index by table that's why loop condition was being false. To overcome this problem we will use exist collection method.
declare
type t1 is table of number(10)
index by binary_integer;
v_t t1;
begin
v_t(1):=13;
v_t(2):=3;
v_t(3):=55;
v_t(4):=33;
v_t.delete(3);
for i in v_t.first..v_t.last
loop
if v_t.exists(i)=true then
dbms_Output.put_line(v_t(i));
else
dbms_Output.put_line('no data found');
end if;
end loop;
end;
/
Before storing data into nested table first we create constructor this constructor name also same name as type name.
To create nested table we use two step process.
Syntax: type typename is table of datatype (size);
variablename typename:=typename(); // where typename() is a constructor.
ExP:
declare
type t1 is table of varchar2(20);
v_t t1:=t1('RAM','MOHAN','SHYAM');
begin
dbms_output.put_line(v_t(1));
end;
/
OUTPUT. RAM
declare
type t1 is table of varchar2(20);
v_t t1:=t1('RAM','MOHAN','SHYAM');
begin
V_T.EXTEND(4):='india';
dbms_output.put_line(v_t(1)||' '||V_T(2)||' '||V_T(4));
end;
/
OUTPUT. Error
declare
type t1 is table of varchar2(20);
v_t t1:=t1('RAM','MOHAN','SHYAM');
begin
V_T.EXTEND(4);
v_t(4):='india';
dbms_output.put_line(v_t(1)||' '||V_T(2)||' '||V_T(4));
end;
/
output. RAM MOHAN india
declare
type t1 is table of number(10);
v_t1 t1;
v_t2 t1:=t1();
begin
if v_t1 is null then
dbms_output.put_line ('v_t1 is null');
else
dbms_output.put_line ('v_t1 is not null');
end if;
if v_t2 is null then
dbms_output.put_line ('v_t2 is null');
else
dbms_output.put_line ('v_t2 is not null');
end if;
end;
/
declare
type t1 is table of number (10);
v_t t1:=t1(10,20,30,40);
begin
dbms_Output.put_line(v_t.first);
dbms_Output.put_line(v_t.last);
dbms_Output.put_line(v_t.prior(3));
dbms_Output.put_line(v_t.next(3));
v_t.extend;
dbms_Output.put_line(v_t.count);
v_t(5):=50;
v_t.trim;
for i in v_t.first..v_t.last
loop
dbms_output.put_Line (v_t(i));
end loop;
end;
/
type t1 is table of number(10);
v_t t1:=t1(10,20,30,40,50,60,70);
begin
v_t.trim(2);
dbms_output.put_line( 'after triming left two elements');
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
v_t.delete(2);
dbms_output.put_line ('after deleting second element');
for i in v_t.first..v_t.last
loop
if v_t.exists(i) then
dbms_output.put_line (v_t(i));
end if;
end loop;
end;
/
Write a pl/sql program which is used to transfer whole employee names from emp table into nested table and also display from nested table
declare
type t1 is table of varchar2(10);
v_t t1:=t1();
cursor c1 is select ename from emp;
n number(10):=1;
begin
for i in c1
loop
v_t.extend;
v_t(n):=i.ename;
n:=n+1;
end loop;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;
/
declare
type t1 is table of number(10)
index by binary_integer;
v_t t1;
begin
v_t(1):=13;
v_t(2):=3;
v_t(3):=55;
v_t(4):=33;
v_t.delete(3);
for i in v_t.first..v_t.last
loop
if v_t.exists(i)=true then
dbms_Output.put_line(v_t(i));
else
dbms_Output.put_line('no data found');
end if;
end loop;
end;
/
Nested Table
Nested table is also comes under collection. It is also used to store number of values into single unit. Index by table does not store data permanently into database server and does not add and remove indexes. But in case of nested table it stores data permanently into database server and it adds and removes index in nested table by extend() and trim() collection methods.Before storing data into nested table first we create constructor this constructor name also same name as type name.
To create nested table we use two step process.
Syntax: type typename is table of datatype (size);
variablename typename:=typename(); // where typename() is a constructor.
ExP:
declare
type t1 is table of varchar2(20);
v_t t1:=t1('RAM','MOHAN','SHYAM');
begin
dbms_output.put_line(v_t(1));
end;
/
OUTPUT. RAM
declare
type t1 is table of varchar2(20);
v_t t1:=t1('RAM','MOHAN','SHYAM');
begin
V_T.EXTEND(4):='india';
dbms_output.put_line(v_t(1)||' '||V_T(2)||' '||V_T(4));
end;
/
OUTPUT. Error
declare
type t1 is table of varchar2(20);
v_t t1:=t1('RAM','MOHAN','SHYAM');
begin
V_T.EXTEND(4);
v_t(4):='india';
dbms_output.put_line(v_t(1)||' '||V_T(2)||' '||V_T(4));
end;
/
output. RAM MOHAN india
declare
type t1 is table of number(10);
v_t1 t1;
v_t2 t1:=t1();
begin
if v_t1 is null then
dbms_output.put_line ('v_t1 is null');
else
dbms_output.put_line ('v_t1 is not null');
end if;
if v_t2 is null then
dbms_output.put_line ('v_t2 is null');
else
dbms_output.put_line ('v_t2 is not null');
end if;
end;
/
declare
type t1 is table of number (10);
v_t t1:=t1(10,20,30,40);
begin
dbms_Output.put_line(v_t.first);
dbms_Output.put_line(v_t.last);
dbms_Output.put_line(v_t.prior(3));
dbms_Output.put_line(v_t.next(3));
v_t.extend;
dbms_Output.put_line(v_t.count);
v_t(5):=50;
v_t.trim;
for i in v_t.first..v_t.last
loop
dbms_output.put_Line (v_t(i));
end loop;
end;
/
Difference between trim , delete collection methods
declaretype t1 is table of number(10);
v_t t1:=t1(10,20,30,40,50,60,70);
begin
v_t.trim(2);
dbms_output.put_line( 'after triming left two elements');
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
v_t.delete(2);
dbms_output.put_line ('after deleting second element');
for i in v_t.first..v_t.last
loop
if v_t.exists(i) then
dbms_output.put_line (v_t(i));
end if;
end loop;
end;
/
Write a pl/sql program which is used to transfer whole employee names from emp table into nested table and also display from nested table
declare
type t1 is table of varchar2(10);
v_t t1:=t1();
cursor c1 is select ename from emp;
n number(10):=1;
begin
for i in c1
loop
v_t.extend;
v_t(n):=i.ename;
n:=n+1;
end loop;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;
/
Varray in Pl/SQL:
Varray is another user-defined datatype.which is used to store multiple values into single unit. Which stores upto 2GB data into it. It does not allow delete(index),delete(index,index) method bul allows delete method to delete all indexes from type. rest of things are same like as nested table;
It uses limit method to count length of the varray.
syntax: collection_var_name.limit;
Syntax: type type_name is varray(size) of datatype(size);
collection_var_name type_name:=type_name();
Example:
declare
type t1 is varray(4) of number(10);
v_t t1:= t1(10,20,30);
begin
dbms_output.put_line (v_t.limit);
for i in v_t.first..v_t.last
loop
dbms_output.put_line (v_t(i));
end loop;
v_t.extend;
v_t(4):=40;
dbms_output.put_line (v_t(4));
end;
/
It uses limit method to count length of the varray.
syntax: collection_var_name.limit;
Syntax: type type_name is varray(size) of datatype(size);
collection_var_name type_name:=type_name();
Example:
declare
type t1 is varray(4) of number(10);
v_t t1:= t1(10,20,30);
begin
dbms_output.put_line (v_t.limit);
for i in v_t.first..v_t.last
loop
dbms_output.put_line (v_t(i));
end loop;
v_t.extend;
v_t(4):=40;
dbms_output.put_line (v_t(4));
end;
/
Bulk Collect Clause:
We can use bulk collect clause with select into clause and cursor fetch statement.
1) bulk collect with select into clause
1) bulk collect with select into clause
syntax:
select * bulk collect into collection_var_name from table_name;
2) bulk collect with cursor fetch statement
fetch cursor_var_name bulk collect into collection_var_name;
Declare
type t1 is table of varchar2(20);
v_t t1:=t1();
cursor c1 is select ename from emp;
begin
open c1;
fetch c1 bulk collect into v_t;
close c1;
for i in v_t.first..v_t.last
loop
dbms_output.put_line (v_t(i));
end loop;
end;
/
Ref- Cursor (Coming Soon)
select * bulk collect into collection_var_name from table_name;
2) bulk collect with cursor fetch statement
fetch cursor_var_name bulk collect into collection_var_name;
Declare
type t1 is table of varchar2(20);
v_t t1:=t1();
cursor c1 is select ename from emp;
begin
open c1;
fetch c1 bulk collect into v_t;
close c1;
for i in v_t.first..v_t.last
loop
dbms_output.put_line (v_t(i));
end loop;
end;
/
Ref- Cursor (Coming Soon)
PL/SQL gives you the control to create your own sub data type that are inherit from predefined base type. Sub types can increase reliability and provide compatibility with ANSI/ISO type. Several predefined subtypes are in STANDARD package.
ReplyDeleteYou can read here User-Defined Subtypes: http://way2tutorial.com/plsql/plsql_data_types.php#userdefine
PL/SQL gives you the control to create your own sub data type that are inherit from predefined base type. Sub types can increase reliability and provide compatibility with ANSI/ISO type. Several predefined subtypes are in STANDARD package.
ReplyDeleteYou can read here User-Defined Subtypes