Declare function in Oracle SQL temporary script

Question

I have a script that I run in the Toad for Oracle (or in the 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;

so it works pretty good and shows me some result like this:

-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

I want to refactor this and extract the first select (in parentheses) into a function something like:

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;

and then use it somehow in the script.

Is it possible? And which syntax have I use? I do not want to save the function into database – only to run it for my own need in Toad or PL/SQL Developer. (We have Oracle version 12.1.0.2.0.)

Answer 1

In Oracle 12c or above you can utilize WITH factoring clause as

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;

Otherwise, in lower versions, no - you can't do that and you'll have to create a stored function (the one that exists in the database).

Answer 2

Why? You can just use analytic functions:

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;

You don't have to worry about NULL values, because the JOIN filters them out.