oracle Oracle11g for 循环中的数字溢出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5044451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle11g numeric overflow in for loop
提问by canni
I have a for loop in pl/sql function something like:
我在 pl/sql 函数中有一个 for 循环，如下所示：
FOR i IN min..max LOOP
variables i, min, max are declared as NUMERIC
变量 i, min, max 被声明为 NUMERIC
in my case min and max are ofen very large, but range itself is small, ie:
在我的情况下 min 和 max 非常大，但范围本身很小，即：
min = 3232236033
max = 3232236286
as You see range is about ~256, but with this values oracle throws a numeric overflow error and I stuck on how to get it working.
如您所见，范围约为 256，但使用此值，oracle 会引发数字溢出错误，我一直在思考如何使其工作。
How I should iterate over those values?
我应该如何迭代这些值？
EDIT
编辑
OK, I have a working answer, using of loop of max/min diff, but is it really not possible to loop through big values in oracle?
好的，我有一个有效的答案，使用最大/最小差异循环，但是真的不可能在 oracle 中循环遍历大值吗？
EDITThe error I retrieve is:
编辑我检索到的错误是：
SQL Error: ORA-01426: nadmiar numeryczny
ORA-06512: przy "PS.DHCP", linia 88
01426. 00000 - "numeric overflow"
*Cause: Evaluation of an value expression causes an overflow/underflow.
*Action: Reduce the operands.
Line 88 of code is:
代码第 88 行是：
FOR client_ip IN min_host..max_host
min_host, max_host, client_ip is a result of inet_aton(numeric representation of IP)
min_host, max_host, client_ip 是inet_aton（IP 的数字表示）的结果
回答by Harrison
it seems the problem comes from i being cast as too small a number (which seems to a a fault of pl/sql), you can change your loop type:
似乎问题来自我被投射的数字太小（这似乎是 pl/sql 的错误），您可以更改循环类型：
a While loop works fine
While 循环工作正常
set serveroutput on
/
declare
min_val number;
max_val number ;
iterator number ;
begin
min_val := 3232236033 ;
max_val := 3232236286 ;
iterator := min_val;
while iterator<=max_val loop
dbms_output.put_line(iterator);
iterator := iterator + 1;
end loop ;
end;
/
From here: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/controlstatements.htm#BABEFFDC
从这里：http: //download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/controlstatements.htm#BABEFFDC
FOR LOOP Index
The index of a FOR LOOP statement is implicitly declared as a variable of type INTEGER that is local to the loop. The statements in the loop can read the value of the index, but cannot change it. Statements outside the loop cannot reference the index. After the FOR LOOP statement runs, the index is undefined. (A loop index is sometimes called a loop counter.)
In Example 4-17, the FOR LOOP statement tries to change the value of its index, causing an error.
FOR 循环索引
FOR LOOP 语句的索引被隐式声明为循环局部的 INTEGER 类型变量。循环中的语句可以读取索引的值，但不能更改它。循环外的语句不能引用索引。FOR LOOP 语句运行后，索引未定义。（循环索引有时称为循环计数器。）
在示例 4-17 中，FOR LOOP 语句尝试更改其索引的值，从而导致错误。
onwards to this: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/loop_statement.htm
继续：http: //download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/loop_statement.htm
index_name
An undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself; you cannot reference the index outside the loop.
The implicit declaration of index_name overrides any other declaration outside the loop. To refer to another variable with the same name, use a label. See Example 4-22, "Referencing Global Variable with Same Name as Loop Counter".
Inside a loop, the index is treated like a constant: it can appear in expressions, but cannot be assigned a value.
索引名称
一个未声明的标识符，用于命名循环索引（有时称为循环计数器）。它的范围是循环本身；您不能在循环外引用索引。
index_name 的隐式声明会覆盖循环外的任何其他声明。要引用另一个具有相同名称的变量，请使用标签。请参见例 4-22，“引用与循环计数器同名的全局变量”。
在循环内，索引被视为常量：它可以出现在表达式中，但不能赋值。
thus even though you declare the "index" in your declare, it is NOT being used within the loop and instead is using the implicitly created INDEX (which seems to have a precision too small for your needs)
因此，即使您在声明中声明了“索引”，它也不会在循环中使用，而是使用隐式创建的 INDEX（它的精度似乎太小，无法满足您的需要）
回答by Luke Woodward
You could run the loop variable from 0 to the difference between minand max. Here's an example that merely writes the numbers to DBMS_OUTPUT:
你可以运行从0循环变量之间的差异min和max。这是一个仅将数字写入 DBMS_OUTPUT 的示例：
DECLARE
v_min INTEGER := 3232236033;
v_max INTEGER := 3232236286;
v_diff PLS_INTEGER;
BEGIN
v_diff := v_max - v_min;
FOR i IN 0..v_diff
LOOP
-- Use v_min + i where you would have used i.
dbms_output.put_line(v_min + i);
END LOOP;
END;
/
EDIT: sadly you can't use the range operator to iterate through large numbers. The range operator ..is restricted to the range +/- 231. From the PL/SQL documentation:
编辑：遗憾的是，您不能使用范围运算符来遍历大数字。范围运算符..仅限于范围 +/- 2 31。从PL/SQL 文档：
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 -2**31 .. 2**31. If a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment.
在内部，PL/SQL 将边界值分配给临时 PLS_INTEGER 变量，并在必要时将值四舍五入到最接近的整数。PLS_INTEGER 的幅度范围是 -2**31 .. 2**31。如果边界的计算结果超出该范围，则在 PL/SQL 尝试赋值时会出现数字溢出错误。
