| any way to sort the data in a table type [message #41336] |
Wed, 18 December 2002 14:20  |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
Hi all,
Is there any way to sort (order) the data in a table type.
For eg.
TYPE TMP_TBL IS RECORD (RECORD_TYPE NUMBER, RECORD_DATE DATE );
TYPE TEST_TBL IS TABLE OF TMP_TBL INDEX BY BINARY_INTEGER;
Say after inserting 10 rows in TEST_TBL, is there anyway to get the data out from TEST_TBL ordered by RECORD_TYPE.
Thanks
Suresh.
|
|
|
|
| Re: any way to sort the data in a table type [message #41371 is a reply to message #41336] |
Mon, 23 December 2002 09:20  |
Adrian Billington
Messages: 139
Registered: December 2002
|
Senior Member |
|
|
Suresh
Not in an index-by table unless you use the record_type as the array index - in which case they will be assigned pre-sorted ascending (though you can access them descending). You have to loop through the array to access the values. Example of the code you'd have to write (untested):-
DECLARE
TYPE tmp_tbl...
TYPE test_tbl...
ibt_demo TEST_TBL;
v_indx BINARY_INTEGER;
BEGIN
-- Load up the index-by table in "order"...
FOR rec_data IN (SELECT record_type, record_date
FROM source_table)
LOOP
ibt_demo(rec_data.record_type).record_type := rec_data.record_type;
ibt_demo(rec_data.record_type).record_date := rec_data.record_date;
END LOOP;
-- Now access the data in ascending order...
v_indx := ibt_demo.FIRST;
LOOP
EXIT WHEN v_indx IS NULL;
DBMS_OUTPUT.PUT_LINE('Record date = '||ibt_demo(v_indx));
v_indx := v_indx.NEXT;
END LOOP;
... and so on. To descend, use the LAST and PRIOR methods.
Now I personally don't like this method. If you use a nested table SQL type instead then you can declare a local nested table in your PL/SQL but use SQL to ORDER BY record_type. See demo I wrote some time ago below:-
816> -----------------------------------------------------------------------
816> --
816> -- Author: A Billington
816> -- Date: 23 June 2002
816> -- Purpose: Simple demo of ordering a collection by data value rather
816> -- than by subscript.
816> --
816> -- Creates: myObjectType
816> -- myTableType
816> -- myFunction
816> --
816> -----------------------------------------------------------------------
816>
816> --
816> -- Create an object type...
816> --
816> create or replace type myObjectType as object
2 ( owner varchar2(30)
3 , object_name varchar2(128)
4 , object_id number
5 );
6 /
Type created.
816>
816> --
816> -- Create a table type...
816> --
816> create or replace type myTableType
2 as table of myObjectType;
3 /
Type created.
816>
816> --
816> -- Load up a table or records in PL/SQL and order them on return...
816> --
816> declare
2 nt_recs myTableType;
3 begin
4
5 /* Load some data into our table of records... */
6 select myObjectType(owner,object_name,object_id)
7 bulk collect into nt_recs
8 from all_objects
9 where rownum < 21;
10
11 /* Now order the way they are presented back to you... */
12 for j in (select *
13 from table(cast(nt_recs as myTableType))
14 order by object_id)
15 loop
16 dbms_output.put_line(to_char(j.object_id)||' '||j.object_name);
17 end loop;
18 end;
19 /
1414322 /10948dc3_PermissionImpl
1414816 /1033c8a_SqlTypeWithMethods
1415300 /10076b23_OraCustomDatumClosur
1415632 /108549fd_SessionReference
1415972 /10804ae7_Constants
1417498 /106faabc_BasicTreeUIKeyHandle
1417882 /10501902_BasicFileChooserUINe
1421479 /103a2e73_DefaultEditorKitEndP
1423325 /10d0c623_drawBytesIndexed
1423479 /109cbb8e_SpanShapeRendererSim
1424663 /1079c94d_NumberConstantData
1425077 /10b74838_SecurityManagerImpl
1427671 /10322588_HandlerRegistryHelpe
1427691 /1001a851_ConstantDefImpl
1428307 /10a793fd_LocaleElements_iw
1428533 /105072e7_HttpSessionBindingEv
1428763 /108343f6_MultiColorChooserUI
1428765 /1095ce9b_MultiComboBoxUI
1429184 /104b85c5_LogFileOutputStream
1429590 /107a9fdd_AttributeDefHolder
PL/SQL procedure successfully completed.
816>
816> --
816> -- You can also do this is SQL by having a function return a collection...
816> --
816> create function myFunction return myTableType as
2 nt_recs myTableType;
3 begin
4 select myObjectType(owner,object_name,object_id)
5 bulk collect into nt_recs
6 from all_objects
7 where rownum < 21;
8 return nt_recs;
9 end;
10 /
Function created.
816>
816> select *
2 from table(cast(myFunction() as myTableType))
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
SYS /10948dc3_PermissionImpl 1414322
SYS /1033c8a_SqlTypeWithMethods 1414816
SYS /10076b23_OraCustomDatumClosur 1415300
SYS /108549fd_SessionReference 1415632
SYS /10804ae7_Constants 1415972
SYS /106faabc_BasicTreeUIKeyHandle 1417498
SYS /10501902_BasicFileChooserUINe 1417882
SYS /103a2e73_DefaultEditorKitEndP 1421479
SYS /10d0c623_drawBytesIndexed 1423325
SYS /109cbb8e_SpanShapeRendererSim 1423479
SYS /1079c94d_NumberConstantData 1424663
SYS /10b74838_SecurityManagerImpl 1425077
SYS /10322588_HandlerRegistryHelpe 1427671
SYS /1001a851_ConstantDefImpl 1427691
SYS /10a793fd_LocaleElements_iw 1428307
SYS /105072e7_HttpSessionBindingEv 1428533
SYS /108343f6_MultiColorChooserUI 1428763
SYS /1095ce9b_MultiComboBoxUI 1428765
SYS /104b85c5_LogFileOutputStream 1429184
SYS /107a9fdd_AttributeDefHolder 1429590
20 rows selected.
Hope this helps.
Regards
Adrian
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
