database Oracle 命令 NULL LAST 默认

Oracle order NULL LAST by default

提问by rascio

Is there a way to set the defaultorder used by Oracle to be NULL LAST(or NULL FIRST), without have to put it in each query?

有没有办法将Oracle 使用的默认顺序设置为NULL LAST（或NULL FIRST），而不必将其放在每个查询中？

回答by Jon Heller

No, there is no way to change the default behavior of NULLS FIRSTand NULLS LAST:

不，无法更改and的默认行为NULLS FIRSTNULLS LAST：

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

NULLS LAST 是升序的默认值，NULLS FIRST 是降序的默认值。

---

---

I can't prove it's impossible to change, but I cannot find such a feature in the places it is most likely to be implemented.

我无法证明无法更改，但我无法在最有可能实现的地方找到这样的功能。

SQL OptionThe manualdoes not mention anything.

SQL选项的手册中没有提到任何东西。

ParameterNone of the nls parameters in V$PARAMETERcontrol it: select * from v$parameter where name like '%nls%';

参数没有V$PARAMETER控制它的 nls 参数：select * from v$parameter where name like '%nls%';

Hidden ParameterThere's no hidden parameter. I tried searching for anything like %null% or %sort%, none of them appear relevant.

隐藏参数没有隐藏参数。我尝试搜索 %null% 或 %sort% 之类的内容，但它们都没有相关性。

Locale BuilderOracle allows you to create your own custom sorting. It has a lot of options, but none of them allow you to define how NULLs are sorted. Setting the Major Sort and Minor Sort numbers really high or low does not change it (I was hoping that a NULL was implemented as a hard-coded small or large value). You can set the sort order for 0x0000, which is "NULL", but that's a different type of NULL.

Locale BuilderOracle 允许您创建自己的自定义排序。它有很多选项，但没有一个选项允许您定义 NULL 的排序方式。将主要排序和次要排序数字设置为高或低不会改变它（我希望将 NULL 实现为硬编码的小值或大值）。您可以为 0x0000 设置排序顺序，即“NULL”，但这是一种不同类型的 NULL。

回答by Nick Krasnov

No, there is no way to enable default ordering without using order byclause, which allows you put NULLs last or first. Here is an example:

不，没有 usingorder by子句就无法启用默认排序，这允许您将NULLs放在最后或最前面。下面是一个例子：

1. Ascending ordering

   SQL> with t1(col) as(
     2    select 1    from dual union all
     3    select 2    from dual union all
     4    select null from dual union all
     5    select 3    from dual
     6  )
     7  select *
     8    from t1
     9  order by col asc nulls last
     10  ;
   

   Result:

     COL
     ------
      1
      2
      3
      null
   

2. Descending ordering

     SQL> with t1(col) as(
      2    select 1    from dual union all
      3    select 2    from dual union all
      4    select null from dual union all
      5    select 3    from dual
      6  )
      7  select *
      8    from t1
      9  order by col desc nulls last
     10  ;
   

   Result:

     COL
     ----------
      3
      2
      1
      null
   

1. 升序

   SQL> with t1(col) as(
     2    select 1    from dual union all
     3    select 2    from dual union all
     4    select null from dual union all
     5    select 3    from dual
     6  )
     7  select *
     8    from t1
     9  order by col asc nulls last
     10  ;
   

   结果：

     COL
     ------
      1
      2
      3
      null
   

2. 降序

     SQL> with t1(col) as(
      2    select 1    from dual union all
      3    select 2    from dual union all
      4    select null from dual union all
      5    select 3    from dual
      6  )
      7  select *
      8    from t1
      9  order by col desc nulls last
     10  ;
   

   结果：

     COL
     ----------
      3
      2
      1
      null
   

回答by Tav

In ascending order, NULL values will always be sorted last

按照升序，NULL 值总是排在最后