oracle里的int类型

在 ANSI SQL 标准 中，INTEGER 和 SMALLINT 是定义好的精确数值类型，但它们的 “长度”或“大小”并不是通过 (N) 括号来指定的（如 INT(4)），这一点与 MySQL 等数据库的非标准扩展完全不同。

1. SMALLINT
   语义：短整型整数
   实际实现：通常是 16 位有符号整数，2个字节
   取值范围：-32,768 ≤ SMALLINT ≤ 32,767
2. INTEGER 或 INT
   语义：标准整数
   实际实现：通常是 32 位有符号整数，4个字节
   取值范围：-2,147,483,648 ≤ INTEGER ≤ 2,147,483,647

oracle没有内置int类型，但是兼容int类型，在oracle里int和smallint认为是number(38)，元信息是这样的，但数据受约束不一样，number(38)受38位有效数字限制，而int只受number类型的最大限制限制，即NUMBER [ (p [, s]) ]，Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes.
所以在oracle中的int类型约束等同于number类型，但元信息为number(38)，其实际存储数据的限制会远大于sql标准中的int和smallint

Data Types

‍

以下测试版本：oracle 19.18.0.0.0
SQL> create table t1(c1 int,c2 smallint);

Table created.

SQL> desc t1
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 C1                                                                         NUMBER(38)
 C2                                                                         NUMBER(38) 

--但是建表时，不支持int(4)这种限制类型长度，也不支持int4这种写法
SQL> create table t2(id int(4));
create table t2(id int(4))
                      *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL> create table t2(id int4);
create table t2(id int4)
                   *
ERROR at line 1:
ORA-00902: invalid datatype

--int类型与number(38)长度的不同
SQL> create table t1(c1 number(38));

Table created.
SQL> desc t1
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 C1                                                                         NUMBER(38)

SQL> insert into t1 values (99999999999999999999999999999999999999);  --38个9

1 row created.

SQL> insert into t1 values (999999999999999999999999999999999999999);  --39个9
insert into t1 values (999999999999999999999999999999999999999)
                       *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> select c1,to_char(c1),length(c1) from t1;

        C1 TO_CHAR(C1)                                                                      LENGTH(C1)
---------- -------------------------------------------------------------------------------- ----------
1.0000E+38 99999999999999999999999999999999999999                                                   38

SQL> create table t3(c1 int);

Table created.
SQL> desc t3
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 C1                                                                         NUMBER(38)

SQL> insert into t3 values (99999999999999999999999999999999999999);  --38个9

1 row created.

SQL> insert into t3 values (999999999999999999999999999999999999999);  --39个9

1 row created.

SQL> insert into t3 values (99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);  --125个9

1 row created.
SQL> insert into t3 values (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);   --126个9
insert into t3 values (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)
                       *
ERROR at line 1:
ORA-01426: numeric overflow

SQL>  select c1,to_char(c1),length(c1) from t3;

        C1 TO_CHAR(C1)                                                                      LENGTH(C1)
---------- -------------------------------------------------------------------------------- ----------
1.0000E+38 99999999999999999999999999999999999999                                                   38
1.0000E+39 999999999999999999999999999999999999999                                                  39
1.000E+125 1.000000000000000000000000000000000E+125                                                 40

--没有长度的number类型
SQL> create table t2(c1 number);

Table created.

SQL> desc t2
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 C1                                                                         NUMBER

SQL> insert into t2 values (99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);

1 row created.

SQL> insert into t2 values (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
insert into t2 values (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)
                       *
ERROR at line 1:
ORA-01426: numeric overflow

‍

但是oracle在plsql对int类型的用法与在sql中有所区别，在plsql中可以使用int或者int(1) 写法，且要做精度检查，其中int等于number(38),int(1)等于number(1)

SQL> -- 开启输出
SET SERVEROUTPUT ON;SQL>

--int(1)
SQL> DECLARE
    v1  INT(1);
BEGIN
    v1 := 1;
    DBMS_OUTPUT.PUT_LINE('v1=' || v1);
END;
/  2    3    4    5    6    7
v1=1

PL/SQL procedure successfully completed.

SQL> DECLARE
    v1  INT(1);
BEGIN
    v1 := 11;
    DBMS_OUTPUT.PUT_LINE('v1=' || v1);
END;
/  2    3    4    5    6    7
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

----int
SQL> DECLARE
    v1  INT;
BEGIN
    v1 := 99999999999999999999999999999999999999;  --38个9
    DBMS_OUTPUT.PUT_LINE('v1=' || v1);
END;
/  2    3    4    5    6    7
v1=99999999999999999999999999999999999999

PL/SQL procedure successfully completed.

SQL> DECLARE
    v1  INT;
BEGIN
    v1 := 999999999999999999999999999999999999999;  --39个9
    DBMS_OUTPUT.PUT_LINE('v1=' || v1);
END;
/
  2    3    4    5    6    7  DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

‍

所以，在oracle里，在sql里int类型不做精度检查，存储限制上等同于number，元信息上为number(38),在plsql里，要做精度检查，int完全等同于number(38),int(1)等同于number(1)
因此在oracle开发规范中，还是尽量使用number类型，避免使用int类型，不过如果是其他支持标准 int类型的数据库中，如果数据符合int类型特点，还是建议用int类型而非numeric类型，因为int类型是定长存储，一般性能会更好

‍