Mmm, π

Young rz.He over on Stack Overflow has a question about why SQL*Plus doesn't parse every line of a PL/SQL block in case it's a comment in order to avoid unnecessary prompts for substitution variables. Here's his mcve:

SQL> begin
  2      null; -- &commented_out
  3  end;
  4  /
Enter value for commented_out: wtf

PL/SQL procedure successfully completed.

Oh, wait - it's not that. It's actually this:

CREATE OR REPLACE PROCEDURE cal_circle AS
-- DECLARE

  pi              CONSTANT NUMBER := 3.1415926;
  radius          NUMBER := 3;

  -- to make it more dynamic I can set 
  -- radius NUMBER := &enter_value;

  circumference   DECIMAL(4,2) := radius * pi * 2;
  area            DECIMAL(4,2) := pi * radius ** 2;

BEGIN

  -- DBMS_OUTPUT.PUT_LINE('Enter a valur of radius: '|| radius);
  dbms_output.put_line('For a circle with radius '
   || radius
   || ',the circumference is '
   || circumference
   || ' and the area is '
   || area
   || '.');
END;
/

But anyway.

An excellent answer explains all about clients and servers and procedure parameters and why it's really not SQL*Plus's job to pre-parse everything before sending it to the database and it wouldn't be such a great idea if it did. I voted up. But what really baked my noodle was the opening comment (my emphasis):

You can use a parameter instead of a substitution variable to allow different users to call the procedure with different values of pi.

...with a fixed version of the code posted as an example...

CREATE OR REPLACE PROCEDURE CAL_CIRCLE
  ( P_RADIUS IN NUMBER, P_PI IN NUMBER )
AS
  CIRCUMFERENCE DECIMAL(4, 2) := P_RADIUS * P_PI * 2;
  AREA          DECIMAL(4, 2) := P_PI * P_RADIUS ** 2;

BEGIN
  DBMS_OUTPUT.put_line('For a circle with radius '
                       || P_RADIUS
                       || ', the circumference is '
                       || CIRCUMFERENCE
                       || ' and the area is '
                       || AREA
                       || '. ' || 'Calculated with Pi = ' || P_PI);
END;

You will agree that is much more flexible. Now we can call it for a conventional π, like this:

SQL> call cal_circle(3, 3.1416);
For a circle with radius 3, the circumference is 18.85 and the area is 28.27. Calculated with Pi = 3.1416

Or like this:

SQL> call cal_circle(3, acos(-1));
For a circle with radius 3, the circumference is 18.85 and the area is 28.27. Calculated with Pi = 3.1415926535897932384626433832795028842

But what if we need to switch back to the imperial π following our exit from the EU, or the chancellor reduces the UK's π in order to stimulate economic growth, or we want to use the biblical π for religious reasons, or if we simply want to use the same procedure in an alternative universe with a fundamentally different geometry than our own? No problem:

SQL> call cal_circle(3, 2.71828);
For a circle with radius 3, the circumference is 16.31 and the area is 24.46. Calculated with Pi = 2.71828

Now that is reusability. (The radius can't be more than 9.99, but come on, you can't have everything.)

As usual, of course, the easy way is no fun:

create or replace function circumference
    ( p_radius in number )
    return number
    deterministic
as
begin
    return p_radius * 6.2831853071795864769252867666;
end circumference;

(with something similar for area - at which point you might consider putting them both into a circle package). Or, slightly more fun:

create or replace type circle as object
( radius         number
, diameter       number
, circumference  number
, area           number
, constructor function circle(p_radius number) return self as result );
/

create or replace type body circle
as
    constructor function circle
        ( p_radius number )
        return self as result
    is
    begin
        self.radius := p_radius;
        self.diameter := self.radius * 2;
        self.circumference := self.radius * 6.2831853071795864769252867666;
        self.area := 3.1415926535897932384626433833 * radius ** 2;

        return;
    end;
end;
/

SQL> select circle(3) as mycircle from dual;

MYCIRCLE(RADIUS, DIAMETER, CIRCUMFERENCE, AREA)
----------------------------------------------------
CIRCLE(3, 6, 18.8495559, 28.2743339)

1 row selected.