今天在用 bitand 时遇到一个错误: ORA-01426: numeric overflow
做了几个测试, 有兴趣的朋友探讨下:
test1 和 test2 表明 pl/sql 中最大处理的数值貌似是 power(2, 31)-1.
test3 证明 pl/sql 中是可以处理大于 power(2, 31)-1 的数值的.
猜测:
test1 和 test2 使用 pl/sql 引擎 编译的
test3 用SQL 引擎 编译的
依据:
1. 在SQL 中 integer 等同于 number(38).
SQL> create table aaa(a integer);
Table created.
SQL> desc aaa;
Name
Null?Type
----------------------------------------- -------- ------------------
A
NUMBER(38)
2. oracle 文档描述函数 BITAND
BITAND(argument1,argument2)
BITAND computes an AND operation on the bits of argument1 and argument2,
both of which must resolve to nonnegative integers, and returns an integer
oracle 说bitand 返回的是 integer, 这个integer 应该就是pl/sql 中的数据类型
----------------------------------------------------------------------
test1:
SQL> set serveroutput on;
SQL> declare
2 v_tmp number;
3begin
4 v_tmp := power(2, 31)-1; -- power(2, 31)-1 是允许的最大值
5 v_tmp := bitand(v_tmp, v_tmp);
6 dbms_output.put_line('v_tmp is ======>'||v_tmp);
7end;
8/
v_tmp is ======>2147483647
PL/SQL procedure successfully completed.
----------------------------------------------------------------------
test2:
SQL> set serveroutput on;
SQL> declare
2 v_tmp number;
3begin
4 v_tmp := power(2, 31);
5 v_tmp := bitand(v_tmp, v_tmp);
6 dbms_output.put_line('v_tmp is ======>'||v_tmp);
7end;
8/
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 5
----------------------------------------------------------------------
test 3:
SQL> set serveroutput on;
SQL> declare
2 v_tmp number;
3begin
4 v_tmp := power(2, 31);
5 select bitand(v_tmp, v_tmp) into v_tmp from dual;
6 dbms_output.put_line('v_tmp is ======>'||v_tmp);
7end;
8/
v_tmp is ======>2147483648
PL/SQL procedure successfully completed.
|
