WITH FUNCTION: a little PL/SQL inside a query

Since 12c, the WITH clause can declare a PL/SQL function that lives only for that one query. Handy when a calculation is repeated or fiddly, and you can't (or don't want to) create a function in the schema.

The test case

The five fastest-scoring batters of 2026 who faced at least 100 balls: runs, balls and strike rate (runs per 100 balls, one decimal).

Expected result

Karthik Raman edges Harry Whitcombe by 0.4. Over 100-odd balls, that's about one extra boundary.

Way 1: WITH FUNCTION

with
  function strike_rate(p_runs number, p_balls number) return number is
  begin
    return round(100 * p_runs / nullif(p_balls, 0), 1);
  end;
faced as (
  select batter_id, count(*) as balls, sum(runs_batter) as runs
  from   cr_balls_v
  where  season = 2026 and (extra_type is null or extra_type <> 'WD')
  group  by batter_id
  having count(*) >= 100
)
select p.player_name, f.runs, f.balls, strike_rate(f.runs, f.balls) as strike_rate
from   faced f
join   cr_players p on p.player_id = f.batter_id
order  by strike_rate desc, p.player_name
fetch  first 5 rows only
/

• Functions come first in the WITH, then the ordinary named subqueries, separated by nothing but the function's own end;.
• NULLIF(p_balls, 0) turns 0 into null so a batter with no balls gives null instead of a divide-by-zero error.
• In SQL Developer and SQLcl, end the statement with / on its own line: the semicolons inside the function would otherwise end it too early.

Way 2: the expression inline

select p.player_name, f.runs, f.balls,
       round(100 * f.runs / nullif(f.balls, 0), 1) as strike_rate
from   (select batter_id, count(*) as balls, sum(runs_batter) as runs
        from   cr_balls_v
        where  season = 2026 and (extra_type is null or extra_type <> 'WD')
        group  by batter_id
        having count(*) >= 100) f
join   cr_players p on p.player_id = f.batter_id
order  by strike_rate desc, p.player_name
fetch  first 5 rows only;

For a one-line formula used once, this is simpler. WITH FUNCTION earns its keep when the logic has IFs and loops, or is used in several places in the same query.

19c and 26ai

The same on both. On 26ai, PL/SQL functions can even return BOOLEAN to SQL (page 12.1).

Gotchas

• Some tools can't run it: older clients choke on the semicolons. SQL Workshop, SQL Developer and SQLcl are fine.
• Inside a view, a WITH FUNCTION query needs the /*+ WITH_PLSQL */ hint in the outer query.
• A schema function is reusable; this isn't. If three reports need strike_rate, create it once.