I have an Oracle Stored Procedure which is responsible for query data paginated. It receives the text to search, the offset and the page number. Now I want to add two new parameters to sort the query result before paginating it. My question is, how can I specify the dynamic order and direction.
For now, I now that I should use CASE clause to specify the column but I need to add if is ASC or DESC.
CREATE OR REPLACE PROCEDURE search_city_paged (
in_search_key IN VARCHAR2,
in_offset IN NUMBER,
in_num_elements IN NUMBER,
in_polling_place IN VARCHAR2,
in_order_by IN NUMBER,
in_order_dir IN VARCHAR2,
out_city OUT SYS_REFCURSOR
) AS
BEGIN
OPEN out_citizens FOR
SELECT COUNT(*) OVER (),
cit.city_code,
cit.city_name,
cit.city_zip,
cit.city_cities
FROM city cit
WHERE CATSEARCH(cit.FULL_TEXT_SEARCH, l_in_search_key, NULL)>0
ORDER BY ???
OFFSET in_offset ROWS FETCH FIRST in_num_elements ROWS ONLY;
END search_city_paged;
If I receive in the new parameters 1 and ASC, the result is sorted by city_code ASC. If I receive in the new parameters 1 and DESC, the result is sorted by city_code DESC. If I receive in the new parameters 2 and ASC, the result is sorted by city_name ASC. If I receive in the new parameters 2 and DESC, the result is sorted by city_name DESC.
And... in the same way on each column that I must allow sorting results.
Order by two outer CASE expressions, one ASC one DESC. In the first one check if you want to sort ASC and if not let the expression return a constant or NULL, i.e. it doesn't change the order. For the case you want to sort ASC add an inner CASE expression, that returns the column to sort by. Analogously handle the DESC case.
...
ORDER BY CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 1 THEN
city_code
WHEN in_order_by = 2 THEN
city_name
...
END
END ASC,
CASE
WHEN in_order_dir = 'DESC' THEN
CASE
WHEN in_order_by = 1 THEN
city_code
WHEN in_order_by = 2 THEN
city_name
...
END
END DESC;
Maybe you have to adapt it because of type incompatibilities between the columns, I can't tell that from what you posted. But it should convey the general concept.
Edit:
On the type problem:
One possibility is to convert the columns to compatible data types in a way the order is kept. For instance, if you have a char_column (of the type char), a date_column and an integer_column (of date and integer) you could do
to_char(date_column, 'YYYYMMDDHH24MISS')
to convert the date and
lpad(11, 38, '0')
to convert the integer to char.
The cleaner thing to do, but the one with more work to do (for the programmer, in terms of performance there should be no significant difference whatsoever) is to split the expressions once more. I.e. have an outer CASE for each type your columns are for each direction.
Like
ORDER BY CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 1 THEN
char_column1
WHEN in_order_by = 2 THEN
char_column2
...
END
END ASC,
CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 3 THEN
date_column1
WHEN in_order_by = 4 THEN
date_column2
...
END
END ASC,
and so on for all types and analogously for DESC. Remember, if the column isn't meant to be sorted after such CASE will yield NULL and sorting by that won't influence the order.