SQL IN PL/SQL
The only statements allowed directly in pl/sql are
DML and TCL.
BINDING
Binding a variable is
the process of identifying the storage location associated with an identifier
in the program.
Types of binding
Ø Early binding
Ø Late binding
Ø Binding during the
compiled phase is early binding.
Ø Binding during the
runtime phase is late binding.
Ø In early binding compile
phase will take longer because of binding work but the
execution is faster.
Ø In late binding it will
shorten the compile phase but lengthens the execution time.
Ø PL/SQL by default uses early
binding.
Ø Binding also involves
checking the database for permissions to access the object
Referenced.
DYNAMIC SQL
Ø If you use DDL in pl/sql it validates
the permissions and existence if requires during compile time which makes
invalid.
Ø We can avoid this by
using Dynamic SQL.
Ø Dynamic SQL allows you
to create a SQL statement dynamically at runtime.
Two techniques are available for Dynamic SQL.
Ø Native Dynamic SQL
Ø DBMS_SQL package
USING NATIVE
DYNAMIC SQL
USING EXECUTE
IMMEDIATE
Ex:
BEGIN
Execute immediate ‘create table
student(no number(2),name varchar(10))’;
or
Execute immediate (‘create table student(no
number(2),name varchar(10))’);
END;
USING EXECUTE
IMMEDIATE WITH PL/SQL VARIABLES
Ex:
DECLARE
v varchar(100);
BEGIN
v := 'create table student(no number(2),name
varchar(10))';
execute immediate v;
END;
USING EXECUTE
IMMEDIATE WITH BIND VARIABLES AND USING CLAUSE
Ex:
DECLARE
v varchar(100);
BEGIN
v := 'insert into student
values(:v1,:v2,:v3)';
execute immediate v using 6,'f',600;
END;
EXECUTING
QUERIES WITH OPEN FOR AND USING CLAUSE
Ex:
CREATE OR REPLACE PROCEDURE
P(smarks in
number) IS
s varchar(100) := 'select *from student
where marks > :m';
type t is ref cursor;
c t;
v student%rowtype;
BEGIN
open c for s using smarks;
loop
fetch c into v;
exit when c%notfound;
dbms_output.put_line('Student Marks
= ' || v.marks);
end loop;
close c;
END;
Output:
SQL> exec p(100)
Student Marks = 200
Student Marks = 300
Student Marks = 400
QUERIES WITH EXECUTE
IMMEDIATE
Ex:
DECLARE
d_name
dept.dname%type;
lc
dept.loc%type;
v
varchar(100);
BEGIN
v
:= 'select dname from dept where deptno = 10';
execute
immediate v into d_name;
dbms_output.put_line('Dname
= '|| d_name);
v
:= 'select loc from dept where dname = :dn';
execute immediate v into lc using d_name;
dbms_output.put_line('Loc
= ' || lc);
END;
Output:
Dname = ACCOUNTING
Loc = NEW YORK
VARIABLE NAMES
Ex:
DECLARE
Marks number(3) := 100;
BEGIN
Delete student where marks = marks; -- this will delete all the rows in the
-- student table
END;
This can be
avoided by using the labeled blocks.
<<my_block>>
DECLARE
Marks number(3) := 100;
BEGIN
Delete student where marks =
my_block.marks; -- delete rows which has
-- a marks of 100
END;
GETTING DATA
INTO PL/SQL VARIABLES
Ex:
DECLARE
V1 number;
V2 varchar(2);
BEGIN
Select no,name into v1,v2 from student
where marks = 100;
END;
DML AND RECORDS
Ex:
CREATE OR REPLACE
PROCEDURE P(srow in student%rowtype) IS
BEGIN
insert into student
values srow;
END P;
DECLARE
s
student%rowtype;
BEGIN
s.no
:= 11;
s.name
:= 'aa';
s.marks
:= 100;
p(s);
END;
RECORD BASED
INSERTS
Ex:
DECLARE
srow student%rowtype;
BEGIN
srow.no := 7;
srow.name := 'cc';
srow.marks
:= 500;
insert into student values srow;
END;
RECORD BASED
UPDATES
Ex:
DECLARE
srow student%rowtype;
BEGIN
srow.no := 6;
srow.name := 'cc';
srow.marks := 500;
update student set row=srow where no =
srow.no;
END;
USING RECORDS
WITH RETURNING CLAUSE
Ex:
DECLARE
srow student%rowtype;
sreturn student%rowtype;
BEGIN
srow.no := 8;
srow.name := 'dd';
srow.marks := 500;
insert into student values srow returning
no,name,marks into sreturn;
dbms_output.put_line('No = ' ||
sreturn.no);
dbms_output.put_line('No = ' ||
sreturn.name);
dbms_output.put_line('No = ' ||
sreturn.marks);
END;
Output:
No = 8
No = dd
No = 500
USING DBMS_SQL
PACKAGE
DBMS_SQL is
used to execute dynamic SQL from with in PL/SQL. Unlike native dynamic SQL, it
is not built directly into the language, and thus is less efficient. The
DBMS_SQL package allows you to directly control the processing of a statement
within a cursor, with operations such as opening and closing a cursor, parsing
a statement, binding input variable, and defining output variables.
Ex1:
DECLARE
cursor_id number;
flag number;
v_stmt varchar(50);
BEGIN
cursor_id := dbms_sql.open_cursor;
v_stmt := 'create table stud(sno
number(2),sname varchar(10))';
dbms_sql.parse(cursor_id,v_stmt,dbms_sql.native);
flag := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);
dbms_output.put_line('Table created');
END;
Output:
Table created
SQL> desc stud
Name
Null? Type
-----------------------------------------------------------------
-------- --------
SNO
NUMBER(2)
SNAME
VARCHAR2(10)
Ex2:
CREATE OR
REPLACE PROCEDURE DBMS_SQL_PROC(v1 student.no%type,
v2 student.marks%type) is
cursor_id number;
flag number;
v_update varchar(50);
BEGIN
cursor_id :=
dbms_sql.open_cursor;
v_update := 'update
student set marks = :smarks where no = :sno';
dbms_sql.parse(cursor_id,v_update,dbms_sql.native);
dbms_sql.bind_variable(cursor_id,':sno',v1);
dbms_sql.bind_variable(cursor_id,':smarks',v2);
flag :=
dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);
END
DBMS_SQL_PROC;
Output:
SQL> select * from student; -- before execution
NO NA
MARKS
---- ------ -- ----------
1 a
100
2 b
200
3 c
300
SQL> exec
dbms_sql_proc(2,222)
SQL> select * from student; -- after execution
NO NA
MARKS
---- ------ -- ----------
1 a
100
2 b
222
3 c
300
FORALL STATEMENT
This can be used to get the data from the database
at once by reducting the number of context switches which is a transfer of
control between PL/SQL and SQL engine.
Syntax:
Forall index_var in
[ Lower_bound..upper_bound |
Indices of indexing_collection |
Values of indexing_collection ]
SQL statement;
FORALL WITH
NON-SEQUENTIAL ARRAYS
Ex:
DECLARE
type t is table of student.no%type index
by binary_integer;
ibt t;
BEGIN
ibt(1) := 1;
ibt(10) := 2;
forall i in ibt.first..ibt.last
update student set marks = 900
where no = ibt(i);
END;
The above
program will give error like ‘element at index [2] does not exists.
You can rectify
it in one of the two following ways.
USGAGE OF
INDICES OF TO AVOID THE ABOVE BEHAVIOUR
This will be
used when you have a collection whose defined rows specify which rows in the
binding array you would like to processed.
Ex:
DECLARE
type t is table of student.no%type index
by binary_integer;
ibt t;
type t1 is table of boolean index by
binary_integer;
ibt1 t1;
BEGIN
ibt(1) := 1;
ibt(10) := 2;
ibt(100) := 3;
ibt1(1) := true;
ibt1(10) := true;
ibt1(100)
:= true;
forall i in indices of ibt1
update student set marks = 900
where no = ibt(i);
END;
Ouput:
SQL> select * from student -- before execution
NO NA
MARKS
---------- ------------
1 a 100
2 b
200
3 c
300
SQL> select * from student -- after execution
NO NA
MARKS
---------- ------------
1 a
900
2 b
900
3 c
900
USGAGE OF VALUES
OF TO AVOID THE ABOVE BEHAVIOUR
This will be
used when you have a collection of integers whose content identifies the
position in the binding array that you want to be processed by the FORALL statement.
Ex:
DECLARE
type
t is table of student.no%type index by binary_integer;
ibt
t;
type
t1 is table of pls_integer index by binary_integer;
ibt1
t1;
BEGIN
ibt(1)
:= 1;
ibt(10)
:= 2;
ibt(100)
:= 3;
ibt1(11)
:= 1;
ibt1(15)
:= 10;
ibt1(18)
:= 100;
forall
i in values of ibt1
update student set marks = 567 where no = ibt(i);
END;
Ouput:
SQL> select * from student -- before execution
NO NA
MARKS
---------- ------------
1 a
100
2 b
200
3 c
300
SQL> select * from student -- after execution
NO NA
MARKS
---------- ------------
1 a
900
2 b
900
3 c
900
POINTS ABOUT BULK BINDS
Ø Passing the entire PL/SQL table to the SQL engine in one step is
known as bulk bind.
Ø Bulk binds are done
using the forall statement.
Ø If there is an error
processing one of the rows in bulk DML operation, only that row is rolled back.
POINTS ABOUT RETURING CLAUSE
Ø This will be used only
with DML statements to return
data into PL/SQL variables.
Ø This will be useful in
situations like , when performing insert or update or delete if you want to
know the data of the table which has been effected by the DML.
Ø With out going for
another SELECT using RETURNING clause we will get the data which will avoid a call to RDBMS kernel.
COLLECTIONS
Collections are also
composite types, in that they allow you to treat several variables as a unit. A
collection combines variables of the same type.
TYPES
Ø Varrays
Ø Nested tables
Ø Index - by tables
(Associate arrays)
VARRAYS
A varray is datatype
very similar to an array. A varray has a fixed limit on its size, specified as
part of the declaration. Elements are inserted into varray starting at index 1,
up to maximum lenth declared in the varray type. The maximum size of the varray
is 2 giga bytes.
Syntax:
Type <type_name>
is varray | varying array (<limit>)
of <element_type>;
Ex1:
DECLARE
type t is varray(10) of varchar(2);
va t := t('a','b','c','d');
flag boolean;
BEGIN
dbms_output.put_line('Limit = ' ||
va.limit);
dbms_output.put_line('Count = ' ||
va.count);
dbms_output.put_line('First Index = ' ||
va.first);
dbms_output.put_line('Last Index = ' ||
va.last);
dbms_output.put_line('Next Index = ' ||
va.next(2));
dbms_output.put_line('Previous Index = '
|| va.prior(3));
dbms_output.put_line('VARRAY ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
flag := va.exists(3);
if flag = true then
dbms_output.put_line('Index 3 exists
with an element ' || va(3));
else
dbms_output.put_line('Index 3 does
not exists');
end if;
va.extend;
dbms_output.put_line('After extend of one
index, Count = ' || va.count);
flag := va.exists(5);
if flag = true then
dbms_output.put_line('Index 5 exists
with an element ' || va(5));
else
dbms_output.put_line('Index 5 does
not exists');
end if;
flag := va.exists(6);
if flag = true then
dbms_output.put_line('Index 6 exists
with an element ' || va(6));
else
dbms_output.put_line('Index 6 does
not exists');
end if;
va.extend(2);
dbms_output.put_line('After extend of two
indexes, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
va(5) := 'e';
va(6) := 'f';
va(7) := 'g';
dbms_output.put_line('AFTER ASSINGNING VALUES
TO EXTENDED ELEMENTS,
VARRAY ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
va.extend(3,2);
dbms_output.put_line('After extend of
three indexes, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
va.trim;
dbms_output.put_line('After trim of one
index, Count = ' || va.count);
va.trim(3);
dbms_output.put_line('After trim of three
indexs, Count = ' || va.count);
dbms_output.put_line('AFTER TRIM, VARRAY
ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
va.delete;
dbms_output.put_line('After delete of
entire varray, Count = ' || va.count);
END;
Output:
Limit = 10
Count = 4
First Index
= 1
Last Index =
4
Next Index =
3
Previous
Index = 2
VARRAY
ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
Index 3
exists with an element c
After extend
of one index, Count = 5
Index 5
exists with an element
Index 6 does
not exists
After extend
of two indexes, Count = 7
VARRAY
ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
va[5] =
va[6] =
va[7] =
AFTER
ASSINGNING VALUES TO EXTENDED ELEMENTS, VARRAY ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
va[5] = e
va[6] = f
va[7] = g
After extend
of three indexes, Count = 10
VARRAY
ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
va[5] = e
va[6] = f
va[7] = g
va[8] = b
va[9] = b
va[10] = b
After trim
of one index, Count = 9
After trim
of three indexs, Count = 6
AFTER TRIM,
VARRAY ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
va[5] = e
va[6] = f
After delete
of entire varray, Count = 0
Ex2:
DECLARE
type
t is varray(4) of student%rowtype;
va t := t(null,null,null,null);
BEGIN
for i in 1..va.count loop
select * into va(i) from student
where sno = i;
dbms_output.put_line('Sno = ' ||
va(i).sno || ' Sname = ' || va(i).sname);
end loop;
END;
Output:
Sno = 1
Sname = saketh
Sno = 2
Sname = srinu
Sno = 3
Sname = divya
Sno = 4
Sname = manogni
Ex3:
DECLARE
type t is varray(4) of
student.smarks%type;
va t := t(null,null,null,null);
BEGIN
for i in 1..va.count loop
select smarks into va(i) from student
where sno = i;
dbms_output.put_line('Smarks
= ' || va(i));
end loop;
END;
Output:
Smarks = 100
Smarks = 200
Smarks = 300
Smarks = 400
Ex4:
DECLARE
type r is record(c1
student.sname%type,c2 student.smarks%type);
type t is varray(4) of r;
va t := t(null,null,null,null);
BEGIN
for i in 1..va.count loop
select sname,smarks into va(i)
from student where sno = i;
dbms_output.put_line('Sname = ' ||
va(i).c1 || ' Smarks = ' || va(i).c2);
end loop;
END;
Output:
Sname =
saketh Smarks = 100
Sname =
srinu Smarks = 200
Sname =
divya Smarks = 300
Sname =
manogni Smarks = 400
Ex5:
DECLARE
type t is varray(1) of addr;
va t := t(null);
cursor c is select * from employ;
i number := 1;
BEGIN
for v in c loop
select address into va(i) from
employ where ename = v.ename;
dbms_output.put_line('Hno = ' ||
va(i).hno || ' City = ' || va(i).city);
end loop;
END;
Output:
Hno = 11
City = hyd
Hno = 22
City = bang
Hno = 33
City = kochi
Ex6:
DECLARE
type t is varray(5) of varchar(2);
va1 t;
va2 t := t();
BEGIN
if va1 is null then
dbms_output.put_line('va1 is null');
else
dbms_output.put_line('va1 is not
null');
end if;
if va2 is null then
dbms_output.put_line('va2 is null');
else
dbms_output.put_line('va2 is not
null');
end if;
END;
Output:
va1 is null
va2 is not
null
NESTED TABLES
A nested table is
thought of a database table which has no limit on its size. Elements are
inserted into nested table starting at index 1. The maximum size of the varray
is 2 giga bytes.
Syntax:
Type <type_name>
is table of <table_type>;
Ex1:
DECLARE
type t is table of varchar(2);
nt t := t('a','b','c','d');
flag boolean;
BEGIN
if nt.limit is null then
dbms_output.put_line('No limit to
Nested Tables');
else
dbms_output.put_line('Limit = ' ||
nt.limit);
end if;
dbms_output.put_line('Count = ' ||
nt.count);
dbms_output.put_line('First Index = ' ||
nt.first);
dbms_output.put_line('Last Index = ' ||
nt.last);
dbms_output.put_line('Next Index = ' ||
nt.next(2));
dbms_output.put_line('Previous Index = '
|| nt.prior(3));
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
flag := nt.exists(3);
if flag = true then
dbms_output.put_line('Index 3 exists
with an element ' || nt(3));
else
dbms_output.put_line('Index 3 does
not exists');
end
if;
nt.extend;
dbms_output.put_line('After extend of
one index, Count = ' || nt.count);
flag := nt.exists(5);
if flag = true then
dbms_output.put_line('Index 5 exists
with an element ' || nt(5));
else
dbms_output.put_line('Index 5 does not
exists');
end if;
flag := nt.exists(6);
if flag = true then
dbms_output.put_line('Index 6 exists
with an element ' || nt(6));
else
dbms_output.put_line('Index 6 does
not exists');
end if;
nt.extend(2);
dbms_output.put_line('After extend of
two indexes, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
nt(5) := 'e';
nt(6) := 'f';
nt(7) := 'g';
dbms_output.put_line('AFTER ASSINGNING VALUES
TO EXTENDED ELEMENTS, NESTED
TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i
|| '] = ' || nt(i));
end loop;
nt.extend(5,2);
dbms_output.put_line('After extend of
five indexes, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
nt.trim;
dbms_output.put_line('After trim of one
index, Count = ' || nt.count);
nt.trim(3);
dbms_output.put_line('After trim of
three indexs, Count = ' || nt.count);
dbms_output.put_line('AFTER TRIM, NESTED
TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i
|| '] = ' || nt(i));
end loop;
nt.delete(1);
dbms_output.put_line('After delete of
first index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 2..nt.count+1 loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
nt.delete(4);
dbms_output.put_line('After delete of
fourth index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 2..3 loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
for i in 5..nt.count+2 loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
nt.delete;
dbms_output.put_line('After delete of
entire nested table, Count = ' ||
nt.count);
END;
Output:
No limit to
Nested Tables
Count = 4
First Index
= 1
Last Index =
4
Next Index =
3
Previous
Index = 2
NESTED TABLE
ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
Index 3
exists with an element c
After extend
of one index, Count = 5
Index 5
exists with an element
Index 6 does
not exists
After extend
of two indexes, Count = 7
NESTED TABLE
ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] =
nt[6] =
nt[7] =
AFTER
ASSINGNING VALUES TO EXTENDED ELEMENTS, NESTED TABLE ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] = e
nt[6] = f
nt[7] = g
After extend
of five indexes, Count = 12
NESTED TABLE
ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] = e
nt[6] = f
nt[7] = g
nt[8] = b
nt[9] = b
nt[10] = b
nt[11] = b
nt[12] = b
After trim
of one index, Count = 11
After trim
of three indexs, Count = 8
AFTER TRIM,
NESTED TABLE ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] = e
nt[6] = f
nt[7] = g
nt[8] = b
After delete
of first index, Count = 7
NESTED TABLE
ELEMENTS
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] = e
nt[6] = f
nt[7] = g
nt[8] = b
After delete
of fourth index, Count = 6
NESTED TABLE
ELEMENTS
nt[2] = b
nt[3] = c
nt[5] = e
nt[6] = f
nt[7] = g
nt[8] = b
After delete
of entire nested table, Count = 0
Ex2:
DECLARE
type t is table of student%rowtype;
nt t := t(null,null,null,null);
BEGIN
for i in 1..nt.count loop
select * into nt(i) from student
where sno = i;
dbms_output.put_line('Sno = ' ||
nt(i).sno || ' Sname = ' || nt(i).sname);
end loop;
END;
Output:
Sno = 1
Sname = saketh
Sno = 2
Sname = srinu
Sno = 3
Sname = divya
Sno = 4
Sname = manogni
Ex3:
DECLARE
type t is table of student.smarks%type;
nt t := t(null,null,null,null);
BEGIN
for i in 1..nt.count loop
select smarks into nt(i) from
student where sno = i;
dbms_output.put_line('Smarks = '
|| nt(i));
end loop;
END;
Output:
Smarks = 100
Smarks = 200
Smarks = 300
Smarks = 400
Ex4:
DECLARE
type r is record(c1
student.sname%type,c2 student.smarks%type);
type t is table of r;
nt t := t(null,null,null,null);
BEGIN
for i in 1..nt.count loop
select sname,smarks into nt(i)
from student where sno = i;
dbms_output.put_line('Sname = ' ||
nt(i).c1 || ' Smarks = ' || nt(i).c2);
end loop;
END;
Output:
Sname =
saketh Smarks = 100
Sname =
srinu Smarks = 200
Sname =
divya Smarks = 300
Sname =
manogni Smarks = 400
Ex5:
DECLARE
type t is table of addr;
nt t := t(null);
cursor c is select * from employ;
i number := 1;
BEGIN
for v in c loop
select address into nt(i) from
employ where ename = v.ename;
dbms_output.put_line('Hno = ' ||
nt(i).hno || ' City = ' || nt(i).city);
end loop;
END;
Output:
Hno = 11
City = hyd
Hno = 22
City = bang
Hno = 33
City = kochi
Ex6:
DECLARE
type t is varray(5) of varchar(2);
nt1 t;
nt2 t := t();
BEGIN
if nt1 is null then
dbms_output.put_line('nt1 is null');
else
dbms_output.put_line('nt1 is not
null');
end if;
if nt2 is null then
dbms_output.put_line('nt2 is null');
else
dbms_output.put_line('nt2 is not
null');
end if;
END;
Output:
nt1 is null
nt2 is not
null
SET OPERATIONS IN NESTED
TABLES
You can perform set
operations in the nested tables. You can also perform equality comparisions
between nested tables.
Possible operations are
Ø UNION
Ø UNION DISTINCT
Ø INTERSECT
Ø EXCEPT ( act like MINUS)
Ex:
DECLARE
type t is table of varchar(2);
nt1 t := t('a','b','c');
nt2 t := t('c','b','a');
nt3 t := t('b','c','a','c');
nt4 t := t('a','b','d');
nt5 t;
BEGIN
nt5 := set(nt1);
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i ||
' ] = ' || nt5(i));
end loop;
nt5 := set(nt3);
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt1 multiset union nt4;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt1 multiset union nt3;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt1 multiset union distinct nt3;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt1 multiset except nt4;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt4 multiset except nt1;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
END;
Output:
NESTED TABLE ELEMENTS
nt5[ 1 ] = a
nt5[ 2 ] = b
nt5[ 3 ] = c
NESTED TABLE ELEMENTS
nt5[ 1 ] = b
nt5[ 2 ] = c
nt5[ 3 ] = a
NESTED TABLE ELEMENTS
nt5[ 1 ] = a
nt5[ 2 ] = b
nt5[ 3 ] = c
nt5[ 4 ] = a
nt5[ 5 ] = b
nt5[ 6 ] = d
NESTED TABLE ELEMENTS
nt5[ 1 ] = a
nt5[ 2 ] = b
nt5[ 3 ] = c
nt5[ 4 ] = b
nt5[ 5 ] = c
nt5[ 6 ] = a
nt5[ 7 ] = c
NESTED TABLE ELEMENTS
nt5[ 1 ] = a
nt5[ 2 ] = b
nt5[ 3 ] = c
NESTED TABLE ELEMENTS
nt5[ 1 ] = c
NESTED TABLE ELEMENTS
nt5[ 1 ] = d
INDEX-BY TABLES
An index-by table has no
limit on its size. Elements are inserted into index-by table whose index may
start non-sequentially including negative integers.
Syntax:
Type <type_name>
is table of <table_type> index
by binary_integer;
Ex:
DECLARE
type t is table of varchar(2) index by
binary_integer;
ibt t;
flag boolean;
BEGIN
ibt(1) := 'a';
ibt(-20) := 'b';
ibt(30) := 'c';
ibt(100) := 'd';
if ibt.limit is null then
dbms_output.put_line('No limit to
Index by Tables');
else
dbms_output.put_line('Limit = ' ||
ibt.limit);
end if;
dbms_output.put_line('Count = ' ||
ibt.count);
dbms_output.put_line('First Index = ' ||
ibt.first);
dbms_output.put_line('Last Index = ' ||
ibt.last);
dbms_output.put_line('Next Index = ' ||
ibt.next(2));
dbms_output.put_line('Previous Index = '
|| ibt.prior(3));
dbms_output.put_line('INDEX BY TABLE
ELEMENTS');
dbms_output.put_line('ibt[-20] = ' ||
ibt(-20));
dbms_output.put_line('ibt[1] = ' ||
ibt(1));
dbms_output.put_line('ibt[30] = ' ||
ibt(30));
dbms_output.put_line('ibt[100] = ' ||
ibt(100));
flag := ibt.exists(30);
if flag = true then
dbms_output.put_line('Index 30 exists
with an element ' || ibt(30));
else
dbms_output.put_line('Index 30 does
not exists');
end
if;
flag := ibt.exists(50);
if flag = true then
dbms_output.put_line('Index 50 exists
with an element ' || ibt(30));
else
dbms_output.put_line('Index 50 does
not exists');
end if;
ibt.delete(1);
dbms_output.put_line('After delete of
first index, Count = ' || ibt.count);
ibt.delete(30);
dbms_output.put_line('After delete of
index thirty, Count = ' || ibt.count);
dbms_output.put_line('INDEX BY TABLE
ELEMENTS');
dbms_output.put_line('ibt[-20] = ' ||
ibt(-20));
dbms_output.put_line('ibt[100] = ' ||
ibt(100));
ibt.delete;
dbms_output.put_line('After delete of
entire index-by table, Count = ' ||
ibt.count);
END;
Output:
No limit to
Index by Tables
Count = 4
First Index
= -20
Last Index =
100
Next Index =
30
Previous
Index = 1
INDEX BY
TABLE ELEMENTS
ibt[-20] = b
ibt[1] = a
ibt[30] = c
ibt[100] = d
Index 30
exists with an element c
Index 50
does not exists
After delete
of first index, Count = 3
After delete
of index thirty, Count = 2
INDEX BY
TABLE ELEMENTS
ibt[-20] = b
ibt[100] = d
After delete
of entire index-by table, Count = 0
DIFFERENCES AMONG
COLLECTIONS
Ø Varrays has limit,
nested tables and index-by tables has no limit.
Ø Varrays and nested tables
must be initialized before assignment of elements, in index-by tables we can
directly assign elements.
Ø Varrays and nested
tables stored in database, but index-by tables can not.
Ø Nested tables and index-by
tables are PL/SQL tables, but varrays can not.
Ø Keys must be positive in
case of nested tables and varrays, in case of index-by tables keys can be
positive or negative.
Ø Referencing nonexistent
elements raises SUBSCRIPT_BEYOND_COUNT in both nested tables and varrays, but in case of index-by
tables NO_DATA_FOUND raises.
Ø Keys are sequential in
both nested tables and varrays, non-sequential in index-by tables.
Ø Individual indexes can
be deleted in both nested tables and index-by tables, but in varrays can not.
Ø Individual indexes can
be trimmed in both nested tables and varrays, but in index-by tables can not.
Ø Individual indexes can
be extended in both nested tables and varrays, but in index-by tables can not.
MULTILEVEL COLLECTIONS
Collections of more than
one dimension which is a collection of collections, known as multilevel
collections.
Syntax:
Type <type_name1> is
table of <table_type> index by
binary_integer;
Type <type_name2> is
varray(<limit>) | table | of
<type_name1> | index by
binary_integer;
Ex1:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is varray(5) of t1;
va t2 := t2();
c number := 97;
flag boolean;
BEGIN
va.extend(4);
dbms_output.put_line('Count = ' ||
va.count);
dbms_output.put_line('Limit = ' ||
va.limit);
for i in 1..va.count loop
for j in 1..va.count loop
va(i)(j) := chr(c);
c := c + 1;
end loop;
end loop;
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
dbms_output.put_line('First index = '
|| va.first);
dbms_output.put_line('Last index = ' ||
va.last);
dbms_output.put_line('Next index = ' ||
va.next(2));
dbms_output.put_line('Previous index =
' || va.prior(3));
flag := va.exists(2);
if flag = true then
dbms_output.put_line('Index 2
exists');
else
dbms_output.put_line('Index 2
exists');
end if;
va.extend;
va(1)(5)
:= 'q';
va(2)(5) := 'r';
va(3)(5) := 's';
va(4)(5) := 't';
va(5)(1) := 'u';
va(5)(2) := 'v';
va(5)(3) := 'w';
va(5)(4) := 'x';
va(5)(5) := 'y';
dbms_output.put_line('After extend of
one index, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
va.trim;
dbms_output.put_line('After trim of one
index, Count = ' || va.count);
va.trim(2);
dbms_output.put_line('After trim of two
indexes, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
va.delete;
dbms_output.put_line('After
delete of entire varray, Count = ' || va.count);
END;
Output:
Count = 4
Limit = 5
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[1][3] = c
va[1][4] = d
va[2][1] = e
va[2][2] = f
va[2][3] = g
va[2][4] = h
va[3][1] = i
va[3][2] = j
va[3][3] = k
va[3][4] = l
va[4][1] = m
va[4][2] = n
va[4][3] = o
va[4][4] = p
First index
= 1
Last index =
4
Next index =
3
Previous
index = 2
Index 2
exists
After extend
of one index, Count = 5
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[1][3] = c
va[1][4] = d
va[1][5] = q
va[2][1] = e
va[2][2] = f
va[2][3] = g
va[2][4] = h
va[2][5] = r
va[3][1] = i
va[3][2] = j
va[3][3] = k
va[3][4] = l
va[3][5] = s
va[4][1] = m
va[4][2] = n
va[4][3] = o
va[4][4] = p
va[4][5] = t
va[5][1] = u
va[5][2] = v
va[5][3] = w
va[5][4] = x
va[5][5] = y
After trim
of one index, Count = 4
After trim
of two indexes, Count = 2
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[2][1] = e
va[2][2] = f
After delete
of entire varray, Count = 0
No comments:
Post a Comment