Navigate

Collection Methods Example In Oracle

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: 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

Related Post
  1. How to fetch multiple columns from cursor in Oracle?
  2. How to fetch data from ref cursor in Oracle using for loop?
  3. Oracle function return Array of Varchar2