Separating PL/SQL Declarations and Assignments

For variables with default or derived values, I'm normally a big fan of declaring and assigning it in one go. In PL/SQL, that looks like:

create or replace procedure foo as
v1 varchar2(16) := 'hello';
n1 integer := 7;
cond1 varchar2(32) := case
when n1 > 3 then 'greater than 3'
when n1 < 3 then 'less than 3'
else 'exactly 3'
end;
begin
...

(That last technique with CASE is quite a powerful one by the way, you can encode a whole function's worth of logic into a single statement.)

However, I've found through the school of hard knocks that in many cases, it's better to declare the variable but not assign it until the code starts after BEGIN.

If the assignment inside a declaration causes an exception, your EXCEPTION block inside the same procedure or function won't catch it. That is, you'll get different behaviour from:

declare
v1 varchar2(8) := function_that_returns_a_long_string();
begin
...
exception
...

and:

declare
v1 varchar2(8);
begin
v1 := function_that_returns_a_long_string();
exception
...

The second example can trap the exception itself. In the first example, the exception could only be trapped by whatever called the procedure.

Another case is when you make an assignment that ends up inside a PL/SQL package. One of the good points of packages is that you can recompile the body, and none of the procedures or functions that calls the package is invalidated. However, if you put a declaration like:

v1 varchar2(32) := 'some string literal';

in the package spec, you can't change the value without recompiling the spec. So if you're writing code in a procedure that might one day evolve into a package, you might as well split up the declaration and the assignment now.