Constructor in Associative array
Rajasekhar, June 30, 2020 - 1:52 pm UTC
Hello Tom,
Can you please help in understanding the values assignment to associative array using constructor?
create table test2(a_id number(10), a_name varchar2(30), a_class varchar2(30), a_rollno number(10));
insert into test2 values(1,'test','first',10);
insert into test2 values(2,'test2','first2',10);
declare
b varchar2(30):='test';
cn number:=0;
type t_test is table of test2%rowtype index by pls_integer;
var_to_assign t_test:=t_test();
begin
for i in (select a_id,a_name,a_rollno from test2)
loop
cn:=cn+1;
b:=b||cn;
var_to_assign(cn).a_id:=i.a_id;
var_to_assign(cn).a_name:=i.a_name;
var_to_assign(cn).a_class:=b;
var_to_assign(cn).a_rollno:=i.a_rollno;
--var_to_assign(cn):=var_to_assign(i.a_id,i.a_name,d,i.a_rollno); how to assign values to associate array using constructor
end loop;
for j in 1..var_to_assign.count
loop
dbms_output.put_line('the value is'||var_to_assign(j).a_class);
end loop;
exception
when others then
raise_application_error(-20000,'The erorr is '||SQLERRM);
end;
/
July 02, 2020 - 4:07 pm UTC
You can't use %rowtype with record constructors. And the constructor is the name of the type, not the variable ;)
Something like this should do the job:
declare
b varchar2(30):='test';
cn number:=0;
type t_test is record (
a_id number(10), a_name varchar2(30),
a_class varchar2(30), a_rollno number(10)
);
type t_test_arr is table of t_test index by pls_integer;
var_to_assign t_test_arr;
begin
for i in (select a_id,a_name,a_rollno from test2)
loop
cn:=cn+1;
b:=b||cn;
var_to_assign(cn) :=
t_test (
a_id => i.a_id, a_name => i.a_name,
a_class => b, a_rollno => i.a_rollno
);
end loop;
for j in 1..var_to_assign.count
loop
dbms_output.put_line('the value is'||var_to_assign(j).a_class);
end loop;
end;
/
the value istest1
the value istest12
PL/SQL
aashi ahmad, July 03, 2020 - 7:13 am UTC
Record Constructors
rajasekhar, July 04, 2020 - 2:54 pm UTC
Hello Tom,
Thank you so much for your answers.
can you explain more about records constructors and how declaring records type with all column names explicitly will be useful instead of defining record with %rowtype?
July 06, 2020 - 5:40 am UTC
We didn't say it would be better....it is just for different use cases.
If you record maps to a row in a table, or a row from a cursor, then by all means....use %rowtype
But if your record is a customised object that needs to store specific data that is not mapped to a table, then you can define it explicitly.
Both have uses where appropriate.
Record constructor
Rajasekhar, July 06, 2020 - 11:37 am UTC
Hello Chris,
Why are you defining the record type with all table columns(4 columns) explicitly for values assignment?
And when we can use %rowtype for values assignmnet?
create table test2(a_id number(10), a_name varchar2(30), a_class varchar2(30), a_rollno number(10));
Something like this should do the job:
declare
b varchar2(30):='test';
cn number:=0;
type t_test is record (
a_id number(10), a_name varchar2(30),
a_class varchar2(30), a_rollno number(10)
);
type t_test_arr is table of t_test index by pls_integer;
var_to_assign t_test_arr;
begin
for i in (select a_id,a_name,a_rollno from test2)
loop
cn:=cn+1;
b:=b||cn;
var_to_assign(cn) :=
t_test (
a_id => i.a_id, a_name => i.a_name,
a_class => b, a_rollno => i.a_rollno
);
end loop;
for j in 1..var_to_assign.count
loop
dbms_output.put_line('the value is'||var_to_assign(j).a_class);
end loop;
end;
/