Database Programming Using Oracle
11g
Collection
Collection
Collection
• Similar to
Arrays with
some
difference.
• Collection can
hold multiple
data of similar
datatype.
• Data is
accessed
through index.
• Size of
Collection
PL/SQL Tables
or Associated
Array
• Associate
Array is a pair
of key / value
pair
• Key is string or
integer index.
• Value is
accessed
through key
Collection
PL/SQL Tables
or Associated
Array
• Index can be
or can’t be
sequential
• Size is
dynamic or
with very high
limit
• Index can be
Collection
Syntax of
PL/SQL Tables
Records
Step-1:
TYPE table_type_name IS TABLE OF
datatype [NOT NULL] INDEX BY
BINARY_INTEGER;
Binary_integer: It store less than
number
Range: -2147483647 to
2147483647
Step-2:
Variable of type Table_type_name
Collection
Syntax of
PL/SQL Tables
• Value of
PL/SQL Table is
accessed :
• PL/SQL Table
name (i)
• i:=Index.
Collection
Implementing
PL/SQL Tables -I
Records
DECLARE
TYPE JobTabTyp IS TABLE OF
[Link]%type
INDEX BY BINARY_INTEGER;
job_tab JobTabTyp; -- declare
local PL/SQL table
job_title [Link]%TYPE;
designation varchar2(16):='Prog';
counter number(10):=0;
BEGIN
Records
loop
job_tab(counter) :=designation;
dbms_output.put_line
(job_tab(counter));
counter:=counter+1;
exit when counter >100;
end loop;
END;
Collection
PL/SQL Tables
and Attributes
• Attributes are
available with
PL/SQL tables
which make
them easy to
use.
• There are 7
attributes
Collection
PL/SQL Tables
and Attributes
• Attributes are
available with
PL/SQL tables
which make
them easy to
use.
• There are 7
attributes
• Some need
parameters
• Some act like
Collection
Syntax of using
PL/SQL Table
with attributes
Records
plsql_table_name{
. FIRST |
. NEXT |
. DELETE[(index[,
index])] .
EXISTS(index) |
. COUNT |
. NEXT(index) |
. PRIOR(index)}
Collection
PL/SQL Tables
and Attributes
• Any or no
attribute can
be used with
PL/SQL tables
Collection
PL/SQL Table
and First and
Next Attribute
• First return
first index
number in the
PL/SQL table
• Last attribute
return index
last number of
the PL/SQL
table.
Collection
PL/SQL Table
and Count
Attribute
• Count is a
numeric
attribute
which return
total number
of index
created.
• Useful because
index are not
Collection
PL/SQL Table
and Exist
Attribute
• Index of the
PL/SQL table
can be dense
or sparse
• Exists return
true if there is
value on the
particular
index
Collection
PL/SQL Table
and Delete
Attribute
• It can delete
particular
entry from
PL/SQL table
• Can be used
with name of
PL/SQL table
Collection
Implementing
PL/SQL Table -II
Collection
DECLARE
cursor c1 is select ename from emp
where sal <3000;
type c2 is table of [Link]
%type index by binary_integer;
c3 c2;
counter number(10):=0;
begin
Collection
for i in c1 loop
counter :=counter+2;
c3(counter):=[Link];
dbms_output.put_line
(c3(counter));
end loop;
end;
Collection
Implementing
PL/SQL Table -III
Collection
DECLARE
cursor c1 is select ename from
emp;
type c2 is table of [Link]
%type index by binary_integer;
c3 c2;
counter number(10):=0;
b number(10):=0;
begin
for i in c1 loop
counter :=counter+2;
Collection
c3(counter):=[Link];
--dbms_output.put_line
(c3(counter));
end loop;
dbms_output.put_line ('Value of
counter : ' || counter);
dbms_output.put_line ([Link]());
while (b<counter) loop
if [Link](b) then
Collection
dbms_output.put_line ('Value exists
at Index : '|| b);
end if ;
b:=b+1;
end loop;
end;
Collection
Implementing
PL/SQL Table -IV
Collection
Write a PL/SQL block to load all the
Employee names into PL/SQL Table
and copy the values in another
PL/SQL Table before deleting those
values from first PL/SQL Table
Collection
DECLARE
cursor c1 is select ename from emp;
type c2 is table of [Link]%type
index by binary_integer;
c3 c2;
c4 c2;
counter number(10):=0;
b number(10):=0;
begin
for i in c1 loop
counter :=counter+2;
Collection
c3(counter):=[Link];
--dbms_output.put_line
(c3(counter));
end loop;
dbms_output.put_line ('Value of
counter : ' || counter);
dbms_output.put_line ([Link]());
while (b<counter) loop
if [Link](b) then
dbms_output.put_line ('Value exists
at Index : '|| b);
Collection
c4(b):=c3(b);
[Link](b);
dbms_output.put_line ('Value is
deleted at Index '|| b);
dbms_output.put_line ('Value
copied in new PL/SQL Table '||
c4(b));
end if ;
b:=b+1;
end loop;
end;
Collection
Implementing
PL/SQL Table
and SQL-I
Collection
Write a PL/SQL block to load all the
Employee names into PL/SQL Table
and insert the values into another
table along with employee number
and currentdate
Collection
DECLARE
cursor c1 is select ename from
emp;
type c2 is table of [Link]
%type index by binary_integer;
c3 c2;
c4 c2;
counter number(10):=0;
b number(10):=0;
begin
for i in c1 loop
Collection
counter :=counter+2;
c3(counter):=[Link];
--dbms_output.put_line
(c3(counter));
end loop;
dbms_output.put_line ('Value of
counter : ' || counter);
dbms_output.put_line ([Link]());
while (b<counter) loop
if [Link] (b) then
Collection
insert into history values
(b,c3(b),sysdate());
dbms_output.put_line ('Row is
inserted using PL/SQL Table');
end if ;
b:=b+1;
end loop;
end;