背景:
在一个存储过程中,用了两个参数用来做排序的决定参数
v_sort_by
表示按哪一列排序,(id、name、age 三选一)
v_sort_dir
表示是升序还是降序(desc、asc 二选一)
根据传参的不同实现不同的排序效果
FUNCTION fun_get_persons(
v_sort_by VARCHAR2,
v_sort_dir VARCHAR2) RETURN CLOB
AS
v_OUTPUT clob;
BEGIN
WITH temp_t AS (SELECT * FROM table_1 (fun_get_person_helper())),
results AS (SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'id' VALUE temp_t.id,
'name' VALUE temp_t.name,
'age' VALUE temp_t.age
RETURNING CLOB
)
ORDER BY
CASE WHEN v_sort_by = 'id' AND v_sort_dir = 'desc' THEN temp_t.id END DESC,
CASE WHEN v_sort_by = 'id' AND v_sort_dir = 'asc' THEN temp_t.id END ASC,
CASE WHEN v_sort_by = 'name' AND v_sort_dir = 'desc' THEN temp_t.name END DESC,
CASE WHEN v_sort_by = 'name' AND v_sort_dir = 'asc' THEN temp_t.name END ASC,
CASE WHEN v_sort_by = 'age' AND v_sort_dir = 'desc' THEN temp_t.age END DESC,
CASE WHEN v_sort_by = 'age' AND v_sort_dir = 'asc' THEN temp_t.age END ASC
RETURNING CLOB) records
FROM temp_t
)
SELECT json_object('records' VALUE NVL(records, '[]') format json returning clob) data
INTO v_OUTPUT
FROM orders;
RETURN v_OUTPUT;
END fun_get_persons;
补充:
原本是把这两个参数放到fun_get_person_helper(v_sort_by,v_sort_dir)
里面做排序
排序语句是这样式儿的:
SELECT * FROM table_1
ORDER BY case v_sort_dir when 'desc' then -1 else 1 end * row_number()
OVER (
ORDER BY
CASE v_sort_by
WHEN 'id' THEN id
WHEN 'name' THEN name
WHEN 'age' THEN age
ELSE id
END
)
先根据v_sort_by
参数决定按哪一列排,然后再根据v_sort_dir
参数决定升序还是倒序,这里调用了row_number()
函数获取第一次排序后的数据的行号,升序就是row_number() × 1
,降序就是row_number() × -1
但是因为后面用了JSON_ARRAYAGG()
函数,导致这里的排序丢失了(参考:JSON_ARRAYAGG()函数导致排序丢失的问题),白排,所以只能放到JSON_ARRAYAGG()
函数里面做排序
但是在JSON_ARRAYAGG()
函数里面竟然不能用row_number()
函数!
这才换了一种句式解决这个问题
ORDER BY
CASE WHEN v_sort_by = 'id' AND v_sort_dir = 'desc' THEN temp_t.id END DESC,
CASE WHEN v_sort_by = 'id' AND v_sort_dir = 'asc' THEN temp_t.id END ASC,
CASE WHEN v_sort_by = 'name' AND v_sort_dir = 'desc' THEN temp_t.name END DESC,
CASE WHEN v_sort_by = 'name' AND v_sort_dir = 'asc' THEN temp_t.name END ASC,
CASE WHEN v_sort_by = 'age' AND v_sort_dir = 'desc' THEN temp_t.age END DESC,
CASE WHEN v_sort_by = 'age' AND v_sort_dir = 'asc' THEN temp_t.age END ASC
注意:
- 记得每个
CASE
句子只有一个WHEN
/THEN
- 记得每个
CASE
句子都要加END
- 记得在
END
后面加上DESC
/ASC
- 记得每个排序字段对应两个
CASE
语句,一个升序,一个降序