Declare
type rRecord is record (nome varchar2(20));
type tRecord is table of rRecord index by binary_Integer;
type aRecord tRecord;
Begin
aRecord(1).Nome := 'Camila';
aRecord(2).Nome := 'Almir';
aRecord(3).Nome := 'Jussara';
aRecord(4).Nome := 'Eduardo';
dbms_outp ...
end;
/* baseado em "index by binary_Integer", o cursor sera listado pelo numero de sua posição porém necessito que seja por Nome.
Este array poderá ter outra ordem também, dependento da necessidade.
Necessito de uma instrução que me ordene o cursor na ordem desejada.
Não da para fazer do tipo "index by Nome"?
*/
Só pra constar, citarei também que PL/SQL ** implica ** que você está
usando banco de dados, e (naturalmente) bancos de dados JÁ POSSUEM
rotina de sort interna muito eficiente, poderia-se simplesmente se
fazer um select da pl/sql table, como o mostrado abaixo : eu nunca
usei isso (já que sempre trabalho com volumes GRANDES, que
inviabilizam totalmente uso de arrays, sempre usei global temporary
tables pra coisas do tipo), mas fica a dica pra ser objeto de testes.
[]s
Chiappa
======================================================================
======================
I'll show you how to use SQL to sort the table in 7.x and another,
easier way in
8.x.
First the 7.x way. You can use the 'call plsql from sql' feature to
make your
plsql an in memory SQL table as follows:
SQL> create or replace package demo_sort
2 as
3 type myArray is table of varchar2(30) index by
binary_integer;
4
4 function get_array_element( p_index in number ) return
varchar2;
5 pragma restrict_references( get_array_element, WNDS, RNDS,
WNPS );
6
6 function get_array_count return number;
7 pragma restrict_references( get_array_count, WNDS, RNDS,
WNPS );
8
8 procedure do_demo;
9
9 pragma restrict_references( demo_sort, wnds, rnds, wnps,
rnps );
10 end;
11 /
Package created.
SQL> create or replace view my_demo_view
2 as
3 select demo_sort.get_array_element(rownum) data
4 from all_objects
5 where rownum <= ( select demo_sort.get_array_count from dual )
6 /
View created.
so, the view based on a 'big' table (all_objects) can now be used to
index into
your plsql table in the demo_sort package by calling the function
get_array_element. We provided a function get_array_count to let
this view know
when to 'stop' calling get_array_element....
SQL> create or replace package body demo_sort
2 as
3
3
3 g_my_plsql_table myArray;
4 g_my_count number := 0;
5
5 function get_array_element( p_index in number ) return varchar2
6 is
7 begin
8 return g_my_plsql_table(p_index);
9 end;
10
10 function get_array_count return number
11 is
12 begin
13 return g_my_count;
14 end;
15
15
15 procedure do_demo
16 is
17 begin
18 for x in ( select username from all_users where rownum < 10 )
19 loop
20 g_my_plsql_table( g_my_count+1 ) := x.username;
21 g_my_count := g_my_count+1;
22 end loop;
23
23 for x in ( select data from my_demo_view order by data )
24 loop
25 dbms_output.put_line( x.data );
26 end loop;
27 end;
28
28 end;
29 /
Package body created.
Now, we can see it at work. the select on the view selects from the
plsql table
-- we can sort it, we can where on it, we could use it in an IN
statement and so
on...
SQL> exec demo_sort.do_demo
DBSNMP
SYS
SYSTEM
TRACESVR
WEB$GBRADSHA
WEB$GDEYOUNG
WEB$JBROTHER
WEB$KKISER
WEB$RPPRASAD
PL/SQL procedure successfully completed.
Now, if I had Oracle8, i might use the following, easier way:
SQL> create or replace type myTableType as table of varchar2(25);
2 /
Type created.
SQL>
SQL> declare
2 l_x myTableType := myTableType();
3
3 begin
4 for x in ( select username from all_users where rownum < 10 )
5 loop
6 l_x.extend;
7 l_x(l_x.count) := x.username;
8 end loop;
9
9 for x in ( select a.column_value data
10 from THE ( select cast( l_x as mytableType )
from dual ) a
11 order by data )
12 loop
13 dbms_output.put_line( x.data );
14 end loop;
15
15 end;
16 /
DBSNMP
SYS
SYSTEM
TRACESVR
WEB$GBRADSHA
WEB$GDEYOUNG
WEB$JBROTHER
WEB$KKISER
WEB$RPPRASAD
PL/SQL procedure successfully completed.
