在Oracle SQL临时脚本中声明函数

Question

我有一个在Toad for Oracle（或PL/SQL Developer）中运行的脚本：

select distinct AC.STATUS, S.DESCRIPTION, count(*) || ' / ' || (
    select count(*)
    from ACC AC
    where AC.STATUS is not null
) "Count"
from ACC AC
join ACC_STATUS S
    on AC.STATUS = S.STATUS
group by AC.STATUS, S.DESCRIPTION
order by AC.STATUS;

所以它的表现相当不错，向我展示了如下结果：

-7  Status 1    30 / 174
-6  Status 2    124 / 174
-3  Status 3    6 / 174
-2  Status 4    4 / 174
-1  Status 5    10 / 174

我想重构这段代码，并将第一个选择器（括号内的）提取到一个函数中，类似于：

declare function zz
    return number
is
    declare res number;
begin
    select count(*) into res
    from ACC AC
    where AC.STATUS is not null;
    return res;
end zz;

然后在脚本中以某种方式使用它。

这可行吗？我需要使用哪种语法？我不想将函数保存到数据库中 - 只想在Toad或PL/SQL Developer中为自己的需求运行它。(我们有Oracle版本12.1.0.2.0。)

- Roman Tarasiuk

Answer 1

在Oracle 12c或更高版本中，您可以利用WITH子句进行事实分解，如下所示：

with 
  function f_test return number is
    retval number;
  begin
    select count(*) 
      into retval
      from acc
      where status is not null;
    return retval;
  end;
select distinct
  ac.status, 
  s.description, 
  count(*) || ' / ' || f_test
from acc ac join acc_status s on ac.status = s.status
group by ac.status, s.description
order by ac.status;

否则，在低版本中，不行 - 您不能这样做，您必须创建一个存储函数（存在于数据库中的函数）。

- Littlefoot

Answer 2

为什么呢？你可以直接使用分析函数：

select AC.STATUS, S.DESCRIPTION,
       count(*) || ' / ' || sum(count(*)) over () as "count"
from ACC AC join
     ACC_STATUS S
     on AC.STATUS = S.STATUS
group by AC.STATUS, S.DESCRIPTION
order by AC.STATUS;

您不必担心 NULL 值，因为 JOIN 会过滤它们。

- Gordon Linoff

---

非常感谢你的回答！你的版本更简洁，并且教会了我新的语法。但是我想知道，我能否将其提取出来并用作一个函数？ - undefined