CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Random guy
Random guy

Reputation: 923

How to use PL/SQL Record Datatype in nested table?

I have a nested table which is holding a record datatype. So I tried:

DECLARE
  TYPE my_nested_table   IS TABLE OF ot.employee%ROWType;
  var_nt  my_nested_table :=  my_nested_table ();

  cursor cur is 
  select * from ot.employee;

BEGIN
 for i in cur
 loop
 var_nt.extend;
  end loop;

END;
/

The table employee has empno,empname,job columns. Is the process I am folowing is wrong or i can store this record in nested table?I am not getting any error compiling this code.how to add all those cursor values to the nested table variable and print them?

Upvotes: 0

Views: 3648

Answers (2)

XING
XING

Reputation: 9886

how to add all those cursor values to the nested table variable and print them?

Using LOOP you can assign values one by one as below: Using Nested Table

DECLARE
  TYPE my_nested_table IS  TABLE OF employee%rowtype;
  cursor cur is 
        select *
        from employee;

  var_nt   my_nested_table := my_nested_table ();
BEGIN
  for i in cur 
  loop
    var_nt.extend;        
    var_nt(var_nt.count)   := i; -- Assignment 
    --Printing. You can add the columns you want
    dbms_output.put_line(i.empno ||'-'|| i.ename||'-'||i.sal||'-'|| i.deptno);
  end loop;
END;
/

Using Associative Array: - Which could save you from initializing and extending

DECLARE
  TYPE my_nested_table IS  TABLE OF employee%rowtype index by pls_integer;
  cursor cur is 
        select *
        from employee;

  var_nt   my_nested_table; -- No need to initialize
BEGIN
  for i in cur loop
    --var_nt.extend; -- No need to extend
    var_nt(var_nt.count)   := i;
    dbms_output.put_line(i.empno ||'-'|| i.ename||'-'||i.sal||'-'|| i.deptno);
  end loop;
END;
/

Secondly You can avoid the loop and fetch the values directly in Nested table as shown below:

DECLARE
  TYPE my_nested_table IS  TABLE OF employee%rowtype;
  var_nt   my_nested_table := my_nested_table ();
BEGIN
  select * 
  BULK COLLECT INTO  var_nt
  from employee;

  for i in 1..var_nt.count Loop
    dbms_output.put_line('Employee No. - '||var_nt(i).empno||'  '||'Employee Name - '||var_nt(i).ename);
  end loop;

END;
/

Upvotes: 1

VBoka
VBoka

Reputation: 9083

Not sure if I understand but you can do this:

DECLARE

  CURSOR c_data IS
    SELECT *
    FROM employee;
  tmp_event c_data%rowtype;

BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data INTO tmp_event;
    EXIT WHEN c_data%NOTFOUND;
    dbms_output.put_line(tmp_event.id);
  END LOOP;
  CLOSE c_data;
END;
/

Here is the DEMO

Upvotes: 0

Related Questions