java - 如何动态排序选择

Question

我在oracle中有这样的函数

FUNCTION myFunc(p_order_col    IN VARCHAR2 default 'city')
RETURN SYS_REFCURSOR IS
    v_result          SYS_REFCURSOR;
begin

    open v_result for  select m.abrv_name AS abrv_name,
               m.full_name AS merchant_name,
               m.city AS city,
               m.phone AS phone
    from myTable m
    where m.full_name=SomeCondition
    order by CASE WHEN p_order_col = 'city' then ff.city end,
               CASE WHEN p_order_col = 'abrv_name' then m.abrv_name end,
               CASE WHEN p_order_col = 'phone' then  m.phone end;

    return v_result;

end myFunc;

我正在从java传递p_order_col参数，但我正在获取有序数据，即使没有传递参数，默认情况下我也不会获取带有顺序city参数的数据

Answer 1

您对可选形式参数的默认值如何工作感到有点困惑。

如果您将函数调用为 myFunc()，且完全不带任何参数，则将应用默认值，并且其行为就像您将其调用为 myFunc('city' )。

但是，如果您将其称为 myFunc(null)(Java 调用正在执行的操作)，则不会使用默认值，并且 p_order_col 实际上为 null。

From the documentation :

A formal parameter with a default value is called an optional parameter, because its corresponding actual parameter is optional in a subprogram invocation.

这意味着声明默认值允许在调用中省略参数；所以我上面显示的两个调用都是合法的。

Omitting an actual parameter does not make the value of the corresponding formal parameter NULL. To make the value of a formal parameter NULL, specify NULL as either the default value or the actual parameter.

和

If a subprogram invocation specifies an actual parameter for the formal parameter, then that invocation does not evaluate the default value.

总而言之，传递 null 与根本不传递参数不同，并且由于传递 null 是提供实际参数，因此不使用默认值。

您需要处理显式传入 null 的可能性；一种方法是将其合并到第一个 order-by 子句中:

order by CASE WHEN COALESCE(p_order_col, 'city') = 'city' then ff.city end,
           CASE WHEN p_order_col = 'abrv_name' then m.abrv_name end,
           CASE WHEN p_order_col = 'phone' then  m.phone end;

或者也许更清楚

order by CASE WHEN p_order_col IS NULL OR p_order_col = 'city' then ff.city end,
           CASE WHEN p_order_col = 'abrv_name' then m.abrv_name end,
           CASE WHEN p_order_col = 'phone' then  m.phone end;

如果您不确定是否确实传递了 null 和/或在传入其他未知值时还想按城市排序，您可以从不同的角度进行处理:

order by CASE WHEN p_order_col is null
  or p_order_col not in ('abrv_name', 'phone') then m.city end,

但是如果稍后添加更多有效选项，您也需要维护该列表。

如果所有列都是相同的数据类型或者可以强制为相同的数据类型，则另一种方法是使用单例表达式:

order by CASE p_order_col WHEN 'abrv_name' then m.abrv_name
  WHEN 'phone' then m.phone ELSE m.city end;

使用 else 处理显式或默认的“city”值、null 或任何意外值。

<小时/>

如果没有要传递的值，您可以更改 Java 代码以调用不带形式参数的函数:

if someVar == null then
{
    callableStmnt = conn.prepareCall("{?=call myFunc()}");
    callableStmnt.registerOutParameter(1, OracleTypes.CURSOR); 
}
else
{
    callableStmnt = conn.prepareCall("{?=call myFunc(?)}");
    callableStmnt.registerOutParameter(1, OracleTypes.CURSOR); 
    callableStmnt.setString(2, someVar);
}
callableStmnt.execute;

但这可能会让事情变得不必要的复杂化，无论如何，函数最好正确处理这种情况。