[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.