Collections in plsql - syntax [message #652291] |
Mon, 06 June 2016 07:25 |
|
adfnewbie
Messages: 54 Registered: January 2016
|
Member |
|
|
Hi guys...
I really get confused over how to define a variable of a collection type being created.
Eg 1:
declare
TYPE orders_test_tab IS TABLE OF orders_all%ROWTYPE;
l_tab orders_test_tab;
...
...
end;
Eg 2:
declare
TYPE orders_test_tab IS TABLE OF orders_all%ROWTYPE;
l_tab orders_test_tab:=orders_test_tab();
...
...
end;
--------------------------------------------------------------
What is the difference in the above two declarations of the variable l_tab? And how to decide which declaration to be used and when?
Please clarify.
|
|
|
Re: Collections in plsql - syntax [message #652293 is a reply to message #652291] |
Mon, 06 June 2016 07:49 |
ThomasG
Messages: 3211 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
In the first example the variable only gets declared. In the second example something else happens.
Observe these different error messages for example:
SQL> declare
2 TYPE orders_test_tab IS TABLE OF integer;
3 l_tab orders_test_tab;
4 BEGIN
5 Dbms_Output.put_line(l_tab(1));
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5
SQL>
SQL>
SQL> declare
2 TYPE orders_test_tab IS TABLE OF integer;
3 l_tab orders_test_tab:=orders_test_tab();
4 BEGIN
5 Dbms_Output.put_line(l_tab(1));
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
SQL>
[Updated on: Mon, 06 June 2016 07:49] Report message to a moderator
|
|
|
Re: Collections in plsql - syntax [message #652304 is a reply to message #652291] |
Mon, 06 June 2016 14:03 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
A collection can be declared and initialized separately or it can be declared and initialized at the same time or, depending on how you plan to populate it, you may not need to initialize it. Please see the demonstrations below that all produce the same output. In general, the last one is less code to write and runs faster, but you may not always be able to use that method, depending on where the data that you are using to populate your collection comes from. In addition, whatever your whole process is, if you can do it in SQL without PL/SQL, that is generally preferable.
-- separate declaration and initialization:
SCOTT@orcl_12.1.0.2.0> declare
2 TYPE orders_test_tab IS TABLE OF dept%ROWTYPE;
3 -- declare:
4 l_tab orders_test_tab;
5 begin
6 -- initialize:
7 l_tab := orders_test_tab();
8 -- populate:
9 for r in (select * from dept order by deptno) loop
10 l_tab.extend;
11 l_tab(l_tab.last).deptno := r.deptno;
12 l_tab(l_tab.last).dname := r.dname;
13 l_tab(l_tab.last).loc := r.loc;
14 end loop;
15 -- output:
16 for i in 1 .. l_tab.count loop
17 dbms_output.put_line
18 (l_tab(i).deptno || ' ' ||
19 RPAD (l_tab(i).dname, 14) || ' ' ||
20 l_tab(i).loc);
21 end loop;
22 end;
23 /
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
PL/SQL procedure successfully completed.
-- declaration and initialization at the same time:
SCOTT@orcl_12.1.0.2.0> declare
2 TYPE orders_test_tab IS TABLE OF dept%ROWTYPE;
3 -- declare and initialize:
4 l_tab orders_test_tab := orders_test_tab();
5 begin
6 -- populate:
7 for r in (select * from dept order by deptno) loop
8 l_tab.extend;
9 l_tab(l_tab.last).deptno := r.deptno;
10 l_tab(l_tab.last).dname := r.dname;
11 l_tab(l_tab.last).loc := r.loc;
12 end loop;
13 -- output:
14 for i in 1 .. l_tab.count loop
15 dbms_output.put_line
16 (l_tab(i).deptno || ' ' ||
17 RPAD (l_tab(i).dname, 14) || ' ' ||
18 l_tab(i).loc);
19 end loop;
20 end;
21 /
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
PL/SQL procedure successfully completed.
-- declaration, with no initialization necessary due to population method:
SCOTT@orcl_12.1.0.2.0> declare
2 TYPE orders_test_tab IS TABLE OF dept%ROWTYPE;
3 -- declare:
4 l_tab orders_test_tab;
5 begin
6 -- populate (no separate initialization is necessary):
7 select * bulk collect into l_tab from dept order by deptno;
8 -- output:
9 for i in 1 .. l_tab.count loop
10 dbms_output.put_line
11 (l_tab(i).deptno || ' ' ||
12 RPAD (l_tab(i).dname, 14) || ' ' ||
13 l_tab(i).loc);
14 end loop;
15 end;
16 /
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
PL/SQL procedure successfully completed.
|
|
|
|
|
|