The only bit I find confusing is why SvNV returns 0 for the non-number cases

Both sv_2nv/SvNV and sv_2iv/SvIV are coercing to the given type (and Perl does interpret "non-numeric" strings as zero...)

You may want to play with the following to get a better idea of what's going on for sv_2nv and sv_2iv (in particular, observe the flags NOK, IOK, and that both NV and IV are almost always being created):

#!/usr/bin/perl -w

use Inline C => <<'END_C';

void num_test(SV* sv) {

    sv_2nv(sv);
    //sv_2iv(sv);

    do_sv_dump(0, Perl_debug_log, sv, 0, 0, 0, 0);   // like Devel::Pe
+ek
}

END_C

num_test('aa');
num_test('123');
num_test('123aa');
num_test('aa123');
num_test('1.23');
[download]

Output for sv_2nv uncommented:

Argument "aa" isn't numeric in subroutine entry at ./809635.pl line 15
+.
SV = PVNV(0x669cf0) at 0x65ed30
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x665880 "aa"\0
  CUR = 2
  LEN = 8
SV = PVNV(0x7d8540) at 0x65ecb0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 123
  PV = 0x644a50 "123"\0
  CUR = 3
  LEN = 8
Argument "123aa" isn't numeric in subroutine entry at ./809635.pl line
+ 17.
SV = PVNV(0x7d85e0) at 0x65ecd0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 123
  PV = 0x644be0 "123aa"\0
  CUR = 5
  LEN = 8
Argument "aa123" isn't numeric in subroutine entry at ./809635.pl line
+ 18.
SV = PVNV(0x7d8608) at 0x65ed20
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x644cb0 "aa123"\0
  CUR = 5
  LEN = 8
SV = PVNV(0x840a58) at 0x65ed00
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 1.23
  PV = 0x665710 "1.23"\0
  CUR = 4
  LEN = 8
[download]

Output for sv_2iv uncommented:

Argument "aa" isn't numeric in subroutine entry at ./809635.pl line 15
+.
SV = PVNV(0x669cf0) at 0x65ed30
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x665880 "aa"\0
  CUR = 2
  LEN = 8
SV = PVIV(0x63e130) at 0x65ecb0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,POK,READONLY,pIOK,pPOK)
  IV = 123
  PV = 0x644a50 "123"\0
  CUR = 3
  LEN = 8
Argument "123aa" isn't numeric in subroutine entry at ./809635.pl line
+ 17.
SV = PVNV(0x7d8540) at 0x65ecd0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 123
  NV = 123
  PV = 0x644be0 "123aa"\0
  CUR = 5
  LEN = 8
Argument "aa123" isn't numeric in subroutine entry at ./809635.pl line
+ 18.
SV = PVNV(0x7d85e0) at 0x65ed20
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x644cb0 "aa123"\0
  CUR = 5
  LEN = 8
SV = PVNV(0x7d8608) at 0x65ed00
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 1
  NV = 1.23
  PV = 0x665710 "1.23"\0
  CUR = 4
  LEN = 8
[download]

It's only by accident (kind of) that you get the desired result for SQL_INTEGER because you're doing cast_ok = (SvIOK(sv) && !SvNOK(sv)); and - as a special case - NV isn't being created for ints like 123.

Thanks again almut, as your post is very enlightening and helping me to understand what is going on.

To be honest what originally drove this was my requirement to get IVs created (only and not a pv) when you have an integer as JSON::XS (oh how I'm going to regret adding this comment) does something different when an sv has an IV but not a pv. See Support binding of integers so they are returned as IVs and DBD::Oracle, Support binding of integers so they are returnedas IVs. I was not bothered about doubles, numerics and bigints myself but was happy to help introduce a more general solution to DBI. As it turns out, it has been a lot more work than I first thought but still worth it for us since it avoids us having to loop through a database result-set and add 0 to all the values we know are small integers.