Oracle Apex中LOV添加‘All’选项的代码逻辑解析求助

我已按照步骤实现了在LOV列表开头添加「All」选项的需求，但希望理解其背后的实现逻辑，具体疑问如下：

1. LOV定义的SQL代码解析 #

with temp as                              -- 是否指临时表？ 
(select 1 rn, 'All' state from dual       -- '1 rn'是否指第一行的行号？
union all                                 -- 不理解'All' state from dual的作用
select 2 rn, state from orders              
)
select state as display_value,
state as return_value
from temp
order by rn, state;

我的假设：

• with temp as是创建临时表。
• select 1 rn, 'All' state from dual返回「All」作为第一行，不清楚state from dual的作用。

2. LOV关联的查询过滤SQL代码解析 #

select ...
from order_total_view
where (state = state and :P8_STATE = 'All')
   or (state in (select regexp_substr(:P8_STATE, '[^:]+', 1, level)
                 from dual
                 connect by level <= regexp_count(:P8_STATE, ':') + 1
                )
         and :P8_STATE <> 'All'
      )

我的疑问：

• where (state = state and :P8_STATE = 'All')的作用是什么？
• 是否应该写成where (:P8_STATE = state and :P8_STATE = 'All')？
• and :P8_STATE <> 'All'的作用是什么？是否是在第二个OR分支中判断选择的不是「All」？

内容的提问来源于stack exchange，提问作者ckp7blessed