Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Collection Methods Example In Oracle

Leave a Comment / COLLECTIONS / By

Collection Methods Example In Oracle

EXISTS:

--Collection Methods Example In Oracle: EXISTS

set serveroutput on;
declare
type t_num is table of number;
v_num t_num := t_num(1, 3, 5);
begin
if v_num.exists(1) then 
dbms_output.put_line('Element 1 exists.');
end if;
if v_num.exists(4) then
dbms_output.put_line('Element 4 exists.');
else
dbms_output.put_line('Element 4 does not exists.');
end if;
if v_num.exists(2) = TRUE then
dbms_output.put_line('Element 2 exists.');
else
dbms_output.put_line('Element 2 does not exists.'); 
end if;
end;
/
Output:
Collection Methods Example In Oracle
Collection Methods Example In Oracle: Example Output

COUNT:

--Collection Methods Example In Oracle: COUNT

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(1, 3, 5, 8);
begin
  dbms_output.put_line('No of elements in the List -> '||v_num.count);
  for i in 1..v_num.count
  loop
    dbms_output.put_line('Element at '||i||' is -> '||v_num(i));
  end loop;
end;
/
Output:
No of elements in the List -> 4
Element at 1 is -> 1
Element at 2 is -> 3
Element at 3 is -> 5
Element at 4 is -> 8

LIMIT:

--Collection Methods Example In Oracle: LIMIT

set serveroutput on;
declare
  type t_num is varray(10) of number;
  v_num t_num := t_num(1, 3, 5, 8);
begin
  dbms_output.put_line('No of elements in the List -> '||v_num.limit);
  dbms_output.put_line('No of elements in the List -> '||v_num.count);
  for i in 1..v_num.count
  loop
    dbms_output.put_line('Element at '||i||' is -> '||v_num(i));
  end loop;
end;
/
Output:
No of elements in the List -> 10
No of elements in the List -> 4
Element at 1 is -> 1
Element at 2 is -> 3
Element at 3 is -> 5
Element at 4 is -> 8

FIRST & LAST:

--Collection Methods Example In Oracle: FIRST & LAST

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(10, 3, 5, 8);
begin
  dbms_output.put_line('First Position is '||v_num.first);
  dbms_output.put_line('Last Position is '||v_num.last);
  for i in v_num.first..v_num.last
  loop
    if i = v_num.first then
      dbms_output.put_line('First element is '||v_num(i));
    end if;
    dbms_output.put_line('Element at '||i||' is ->'||v_num(i));
    if i = v_num.last then
      dbms_output.put_line('Last element is '||v_num(i));
    end if;
  end loop; 
end;
/
First Position is 1
Last Position is 4
First element is 10
Element at 1 is ->10
Element at 2 is ->3
Element at 3 is ->5
Element at 4 is ->8
First element is 8

Output:

PRIOR & NEXT:

--Collection Methods Example In Oracle: PRIOR & NEXT

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(1, 3, 5, 8);
begin
  if v_num.prior(v_num.first) is null then
    dbms_output.put_line('Cannot go prior first element.');
  end if;
  if v_num.next(v_num.last) is null then
    dbms_output.put_line('Cannot go beyond last element.');
  end if;
  dbms_output.put_line('Second element is ->'||v_num.next(1));
end;
/
Output:
Cannot go prior first element.
Cannot go beyond last element.
Second element is ->2
Note:
When there is a gap between consecutive elements then we will use While loop
Use Prior & Next for traversing through the loop
--Collection Methods Example In Oracle: PRIOR & NEXT

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(1, 3, 5, 8);
  v_cnt integer;
begin
  v_cnt := v_num.first;
  while v_cnt is not null
  loop
    dbms_output.put_line('Element at '||v_cnt||' is ->'||v_num(v_cnt));
    v_cnt := v_num.next(v_cnt);
  end loop;

  --Reverse order
  dbms_output.put_line('Reverse Order');

  v_cnt := v_num.last;
  while v_cnt is not null
  loop
    dbms_output.put_line('Element at '||v_cnt||' is ->'||v_num(v_cnt));
    v_cnt := v_num.prior(v_cnt);
  end loop;
end;
/
Output:
Element at 1 is ->1
Element at 2 is ->3
Element at 3 is ->5
Element at 4 is ->8
Reverse Order
Element at 4 is ->8
Element at 3 is ->5
Element at 2 is ->3
Element at 1 is ->1
Note:
When traversing through elements Prior & Next skip deleted elements
--Collection Methods Example In Oracle: PRIOR & NEXT

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(1, 3, 5, 8);
  v_cnt integer;
begin
  v_num.delete(2); --Deleted 2nd element
  v_cnt := v_num.first;
  while v_cnt is not null
  loop
    dbms_output.put_line('Element at '||v_cnt||' is ->'||v_num(v_cnt));
    v_cnt := v_num.next(v_cnt);
  end loop; 
end;
/
Output:
Element at 1 is ->1
Element at 3 is ->5
Element at 4 is ->8

EXTENDS:

EXTENDS WITH NO ARGUMENTS NESTED TABLE:

--Collection Methods Example In Oracle: EXTENDS WITH NO ARGUMENTS NESTED TABLE

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num();
begin
  v_num.extend;
  v_num(1) := 100;
  dbms_output.put_line('Value of v_num -> '||v_num(1));
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num -> 100

EXTENDS WITH NO ARGUMENTS VARRAY: 

--Collection Methods Example In Oracle: EXTENDS WITH NO ARGUMENTS VARRAY

set serveroutput on;
declare
  type t_num is varray(10) of number;
  v_num t_num := t_num();
begin
  v_num.extend;
  v_num(1) := 100;
  dbms_output.put_line('Value of v_num -> '||v_num(1));
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num -> 100

EXTEND WITH SINGLE ARGUMENT:

--Collection Methods Example In Oracle: EXTENDS WITH SINGLE ARGUMENT

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num();
begin
  v_num.extend(3);
  v_num(1) := 100;
  v_num(2) := 200;
  v_num(3) := 300;
  for i in 1..v_num.count
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num (1) -> 100
Value of v_num (2) -> 200
Value of v_num (3) -> 300

EXTEND WITH TWO ARGUMENTS:

--Collection Methods Example In Oracle: EXTENDS WITH TWO ARGUMENTS

set serveroutput on;
declare
  type t_num is table of number;
 v_num t_num := t_num(10, 15);
begin
 v_num.extend;
 v_num(3) := 20;
  --
  for i in 1..v_num.count
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
  --
  dbms_output.put_line('Extending 5 copies of 3td element');
  dbms_output.new_line;
  v_num.extend(5, 3);
  --
  for i in 1..v_num.count
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Extending 5 copies of 3td element

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 20
Value of v_num (5) -> 20
Value of v_num (6) -> 20
Value of v_num (7) -> 20
Value of v_num (8) -> 20

DELETE:

DELETE SPECIFIC ELEMENT:

--Collection Methods Example In Oracle: DELETE

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(10, 15, 20, 25, 30);
begin
  --
  for i in 1..v_num.last
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
  --
  dbms_output.new_line;
  dbms_output.put_line('Deleting 3rd element');
  dbms_output.new_line;
  v_num.delete(3);
  --
  for i in 1..v_num.last
  loop
    if v_num.exists(i) then
      dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
    end if;
  end loop;
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 25
Value of v_num (5) -> 30

Deleting 3rd element

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (4) -> 25
Value of v_num (5) -> 30

DELETING RANGE ELEMENTS:

--Collection Methods Example In Oracle: DELETING RANGE ELEMENTS

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(10, 15, 20, 25, 30, 35);
begin
  --
  for i in 1..v_num.last
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
  --
  dbms_output.new_line;
  dbms_output.put_line('Deleting 2nd to 4th elements');
  dbms_output.new_line;
  v_num.delete(2,3);
  --
  for i in 1..v_num.last
  loop
    if v_num.exists(i) then
      dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
    end if;
  end loop;
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 25
Value of v_num (5) -> 30
Value of v_num (6) -> 35

Deleting 2nd to 4th elements

Value of v_num (1) -> 10
Value of v_num (4) -> 25
Value of v_num (5) -> 30
Value of v_num (6) -> 35

DELETING ALL ELEMENTS:

--Collection Methods Example In Oracle: DELETING ALL ELEMENTS

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(10, 15, 20, 25, 30, 35);
begin
  --
  for i in 1..v_num.last
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
  --
  dbms_output.new_line;
  dbms_output.put_line('Deleting all elements');
  dbms_output.new_line;
  v_num.delete;
  --
  begin
    for i in 1..v_num.last
    loop
      if v_num.exists(i) then
        dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
      end if;
    end loop;
  exception
    when others then
      dbms_output.put_line('Error -> No elements are exists');
  end;
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 25
Value of v_num (5) -> 30
Value of v_num (6) -> 35

Deleting all elements

Error -> No elements are exists

TRIM:

TRIM LAST ELEMENT:

--Collection Methods Example In Oracle: TRIMING LAST ELEMENT

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(10, 15, 20, 25, 30, 35);
begin
  --
  for i in 1..v_num.last
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
  --
  dbms_output.new_line;
  dbms_output.put_line('Trim last elements');
  dbms_output.new_line;
  v_num.trim;
  --
  begin
    for i in 1..v_num.last
    loop
      if v_num.exists(i) then
        dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
      else
        dbms_output.put_line('No element is exists');
      end if;
    end loop;
  exception
    when others then
      dbms_output.put_line('Error -> No elements are exists');
  end;
end;
/
OUTPUT:
PL/SQL procedure successfully completed.
Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 25
Value of v_num (5) -> 30
Value of v_num (6) -> 35

Trim last elements

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 25
Value of v_num (5) -> 30

TRIM LAST TWO ELEMENTS:

--Collection Methods Example In Oracle: TRIMING LAST TWO ELEMENTS

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(10, 15, 20, 25, 30, 35);
begin
  --
  for i in 1..v_num.last
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
  --
  dbms_output.new_line;
  dbms_output.put_line('Trim last 2 elements');
  dbms_output.new_line;
  v_num.trim(2);
  --
  begin
    for i in 1..v_num.last
    loop
      if v_num.exists(i) then
        dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
      else
        dbms_output.put_line('No element is exists');
      end if;
    end loop;
  exception
    when others then
      dbms_output.put_line('Error -> No elements are exists');
  end;
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 25
Value of v_num (5) -> 30
Value of v_num (6) -> 35

Trim last 2 elements

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 25

TRIM ALL ELEMENTS:

--Collection Methods Example In Oracle: TRIMING ALL ELEMENTS

set serveroutput on;
declare
  type t_num is table of number;
  v_num t_num := t_num(10, 15, 20, 25, 30, 35);
begin
  --
  for i in 1..v_num.last
  loop
    dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
  end loop;
  --
  dbms_output.new_line;
  dbms_output.put_line('Trim all elements');
  dbms_output.new_line;
  v_num.trim(v_num.count);
  --
  begin
    for i in 1..v_num.last
    loop
      if v_num.exists(i) then
        dbms_output.put_line('Value of v_num ('||i||') -> '||v_num(i));
      else
        dbms_output.put_line('No element is exists');
      end if;
    end loop;
  exception
    when others then
      dbms_output.put_line('Error -> No elements are exists');
  end;
end;
/
OUTPUT:
PL/SQL procedure successfully completed.

Value of v_num (1) -> 10
Value of v_num (2) -> 15
Value of v_num (3) -> 20
Value of v_num (4) -> 25
Value of v_num (5) -> 30
Value of v_num (6) -> 35

Trim all elements

Error -> No elements are exists

Author

Leave a Comment

Your email address will not be published.