for 循环可能会遇到ORA-01426: numeric overflow

for 循环可能会遇到ORA-01426: numeric overflow

declare
  v_start binary_double;
  v_end binary_double;
begin
 v_start:=1;
 v_end:=2147483647;
 for i in v_start..v_end loop
   exit;
end loop;
end;
/

在FOR循环中可能会遇到ORA-01426,主要是由于起始值或者结束值超过了2147483647,默认情况下
FOR 循环的数值需要 在-2147483648 到 2147483647之间，否则就会报错。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL> declare
  2    v_start binary_double;
  3    v_end binary_double;
  4  begin
  5   v_start:=1;
  6   v_end:=2147483648;
  7   for i in v_start..v_end loop
  8     exit;
  9  end loop;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 7

SQL> declare
  2    v_start binary_double;
  3    v_end binary_double;
  4  begin
  5   v_start:=1;
  6   v_end:=2147483647;
  7   for i in v_start..v_end loop
  8     exit;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> declare
  2    v_start binary_double;
  3    v_end binary_double;
  4  begin
  5   v_start:=-2147483649;
  6   v_end:=2147483647;
  7   for i in v_start..v_end loop
  8     exit;
  9  end loop;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 7

SQL> declare
  2    v_start binary_double;
  3    v_end binary_double;
  4  begin
  5   v_start:=-2147483648;
  6   v_end:=2147483647;
  7   for i in v_start..v_end loop
  8     exit;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

即使在11G中也是如此：

SQL> declare
  2    v_start binary_double;
  3    v_end binary_double;
  4  begin
  5   v_start:=1;
  6   v_end:=2147483647;
  7   for i in v_start..v_end loop
  8     exit;
  9  end loop;
 10  end;
 11  /

PL/SQL 过程已成功完成。

以下摘自ORACLE官方联机文档：

The bounds of a loop range can be literals, variables, or expressions but must evaluate to numbers. Otherwise, PL/SQL raises the predefined exception VALUE_ERROR. The lower bound need not be 1, but the loop counter increment or decrement must be 1.

j IN -5..5
k IN REVERSE first..last
step IN 0..TRUNC(high/low) * 2

Internally, PL/SQL assigns the values of the bounds to temporary PLS_INTEGER variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a PLS_INTEGER is -2147483648 to 2147483647, represented in 32 bits. If a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment. See "PLS_INTEGER Datatype".