[20180927]ora-01426.txt

[20180927]ora-01426.txt

--//連結:http://www.itpub.net/thread-2105458-1-1.html
1.環境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

set serveroutput on

DECLARE
  L_NUMBER number;
BEGIN
  L_NUMBER := 1024 * 1024 * 1024 * 1024;
  DBMS_OUTPUT.PUT_LINE(L_NUMBER);
END;
/

DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4

--//1024*1024 = 1048576
--//1048576*1048576 = 1099511627776

d:\blog>oerr ora 1426
01426, 00000, "numeric overflow"
// *Cause: Evaluation of an value expression causes an overflow/underflow.
// *Action: Reduce the operands.


DECLARE
  L_NUMBER number;
BEGIN
  L_NUMBER := 1024 * 1024 * 1024 * 1024.0;
  DBMS_OUTPUT.PUT_LINE(L_NUMBER);
END;
/

1099511627776
PL/SQL procedure successfully completed.
--//使用小數點,等於浮點運算,出現型別轉換.
--//寫成如下,一樣透過,也證明沒有溢位:

DECLARE
  L_NUMBER number;
BEGIN
  L_NUMBER := 1024 * 1024 * 1024;
  L_NUMBER :=L_NUMBER *1024 *1024*1024;
  DBMS_OUTPUT.PUT_LINE(L_NUMBER);
END;
/

1152921504606846976
PL/SQL procedure successfully completed.

SCOTT@test01p> set serverout on
SCOTT@test01p> DECLARE
  2    L_NUMBER number;
  3  BEGIN
  4      L_NUMBER := 2147483647-1+1;
  5      L_NUMBER := 2147483647+1-1;
  6       DBMS_OUTPUT.PUT_LINE(L_NUMBER);
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 5

--//很明顯oracle在一個算式裡面達到2^31 就報錯.改成如下也不會報錯

DECLARE
  L_NUMBER number;
BEGIN
   -- L_NUMBER := 2147483647-1+1;
    L_NUMBER := 2147483647;
    L_NUMBER := L_NUMBER+1-1;
     DBMS_OUTPUT.PUT_LINE(L_NUMBER);
END;
/

2147483647
PL/SQL procedure successfully completed.