Ordering Records in a Block
INTRODUCTION
============
Often times, a user may wish to reorder records in a block
to view them in a different order under a column. If the records
are purely queried records, then you can re-issue the same query in a
different order by condition, but if the records are new or changed
records, then this simple reorder scheme will not work. Suppose that
the user wants to view such records in some order before they actually
commit or rollback, then we will have to come up with some other way
to implement this ordering task. Here, we examine two general
approaches for implementing this, each requiring different sets of
performance and resource requirements.
IMPLEMENTATION
==============
CASE 1. Running a local sorting algorithm in case all records
in the block are newly inserted records, either input manually by
users or populated by a cursor.
If you have only a few records with a reasonable amount of columns
to handle in the block, you can implement a simple bubble sort
algorithm to reorder the records. The execution time will be
proportional to the square of the number of records; it is not
the best algorithm to use, but simplest to implement. Below is
an example of how it may be used to sort non-queried records in a
block (take DEPT table as an example.) Note that this first part
is strictly for new records only. The second part will deal with
pure queried records.
Suppose that you create a button called REORDER in your block,
and its trigger will bring up a text item called ORDER_BY and
a button called ORDER_NEW on a separate block called ORDER_BLOCK on
a stacked canvas. Here, you type the column name to reorder by
ORDER_BY and press the ORDER_NEW button to execute sorting. You
execute the following code in WHEN_BUTTON_PRESSED trigger for the
ORDER_NEW button. Note that you can properly order character type
data. Look at later part in this bulletin to see how we can
incorporate number and date datatypes as well.
-----------------------------
declare
tmp_deptno number;
tmp_dname varchar2(100);
tmp_loc varchar2(100);
tmp1_deptno number;
tmp1_dname varchar2(100);
tmp1_loc varchar2(100);
cur varchar2(300);
aft varchar2(300);
tmp number;
counter1 number;
counter2 number;
begin
go_item('deptno');
first_record;
tmp:=0;
loop
if :system.last_record='TRUE' then
tmp:=tmp+1;
exit;
end if;
synchronize;
next_record;
tmp:=tmp+1;
end loop;
/* Now, tmp will have the number of records to handle */
counter1:=1;
counter2:=1;
synchronize;
first_record;
loop /* outer loop for bubble sort*/
if counter2=tmp then
exit;
end if;
loop /* inner loop for bubble sort */
if counter1=tmp+1 then
synchronize;
first_record;
exit;
end if;
/* we can do better here by taking care of null values */
cur:=name_in(:order_by);
tmp_deptno:=:deptno;
tmp_dname:=:dname;
tmp_loc:=:loc;
synchronize;
next_record;
aft:=name_in(:order_by);
synchronize;
tmp1_deptno:=:deptno;
tmp1_dname:=:dname;
tmp1_loc:=:loc;
if cur>aft then /* swap two consecutive records if needed */
:deptno:=tmp_deptno;
:dname:=tmp_dname;
:loc:=tmp_loc;
previous_record;
:deptno:=tmp1_deptno;
:dname:=tmp1_dname;
:loc:=tmp1_loc;
end if;
counter1:=counter1+1;
end loop;
counter1:=1;
counter2:=counter2+1;
first_record;
end loop;
end;
-------------------------
CASE 2. Requerying records in a different order-by
condition if all of the records in the block are of status QUERY.
Suppose that you have entered and executed a query to bring back
some records in a certain order. Now, if you want to view
the same records again in a different order, then you can
use the system.last_query variable with a modified ORDER BY
clause. Now, when you press the button REORDER, it will ask you
for an ordering column. Suppose the text_item ORDER_BY has the
ordering column. Now, on the ORDER_BLOCK, in addition to the
ORDER_NEW button, we add ORDER_QUERIED button, and in its trigger,
add this. Note that this is strictly for reordering records of
status QUERY only.
--------------
declare
where_part varchar2(1000);
order_part varchar2(100);
tmp_index number;
begin
where_part:=Last_Where_Clause; /* this function is in the manual */
/* Look at SYSTEM.LAST_QUERY section under Reference Manual */
tmp_index:=instr(where_part,'ORDER',1,1);
/* order by can start with upper or lower case */
if tmp_index=0 then
tmp_index:=instr(where_part,'order',1,1);
end if;
if tmp_index >0 then
where_part:=substr(where_part,1, temp_index-1);
end if;
set_block_property('dept', default_where, where_part);
set_block_property('dept', order_by, :order_by);
go_item('deptno');
execute_query;
end;x
---------------
This way, you can simply retrieve the same set of records just
in a different order.
WHAT MORE CAN BE DONE
=====================
The first implementation currently only handles the CHAR datatype
column. However, number and data datatypes can also be included
if the code checks for the datatype of the reordering column using
GET_ITEM_PROPERTY and assigns the result of the NAME_IN function to a
local variable the of appropriate datatype. Then, an IF-ELSE
structure can be used to use appropriate local variables in carrying
out the comparison step. Also, it should be noted why it is
difficult to order records that are mixture of queried, changed, and
new records. Using the local sorting algorithm, it is almost
impossible to keep track of this record status and implement commit
after reordering. This can be implemented if desired, but it will
involve extra steps for checking each record status and doing your
own commit processing, etc. Usually, users wish to view records,
either newly populated or queried, in a certain order before they
start changing them, so this assumption should be reasonable. Also,
before executing these codes, maybe you want to check the block
status and make sure that your block is in an appropriate status
because for instance, if the records are just queried from the
database, but if you do ORDER_NEW, then reordered records will
be treated as NEW records instead. Also, in case the ordering
column has null values, we must incorporate what it means to sort
null values in our code!
Tri des colonnes au choix
Répondre avec citation
Partager