oracle里的int類型
在 ANSI SQL 標準 中，INTEGER 和 SMALLINT 是定義好的精確數值類型，但它們的 “長度”或“大小”并不是通過 (N) 括號來指定的（如 INT(4)），這一點與 MySQL 等數據庫的非標準擴展完全不同。
- SMALLINT
語義：短整型整數
實際實現：通常是 16 位有符號整數，2個字節
取值范圍：-32,768 ≤ SMALLINT ≤ 32,767 - 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
| ANSI SQL Data Type | Oracle Data Type |
|---|---|
?CHARACTER(n)?CHAR(n)? | ?CHAR(n)? |
?CHARACTER VARYING(n)?CHAR VARYING(n)? | ?VARCHAR2(n)? |
?NATIONAL CHARACTER(n)?NATIONAL CHAR(n)?NCHAR(n)? | ?NCHAR(n)? |
?NATIONAL CHARACTER VARYING(n)?NATIONAL CHAR VARYING(n)?NCHAR VARYING(n)? | ?NVARCHAR2(n)? |
?NUMERIC[(p,s)]?DECIMAL[(p,s)] (Note 1) | ?NUMBER(p,s)? |
?INTEGER?INT?SMALLINT? | ?NUMBER(38)? |
?FLOAT (Note 2)DOUBLE PRECISION (Note 3)REAL (Note 4) | ?FLOAT(126)?FLOAT(126)?FLOAT(63)? |
?
以下測試版本：oracle 19.18.0.0.0
SQL> create table t1(c1 int,c2 smallint);Table created.SQL> desc t1Name 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 parenthesisSQL> 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 t1Name Null? Type----------------------------------------------------------------- -------- --------------------------------------------C1 NUMBER(38)SQL> insert into t1 values (99999999999999999999999999999999999999); --38個91 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 columnSQL> select c1,to_char(c1),length(c1) from t1;C1 TO_CHAR(C1) LENGTH(C1)
---------- -------------------------------------------------------------------------------- ----------
1.0000E+38 99999999999999999999999999999999999999 38SQL> create table t3(c1 int);Table created.
SQL> desc t3Name Null? Type----------------------------------------------------------------- -------- --------------------------------------------C1 NUMBER(38)SQL> insert into t3 values (99999999999999999999999999999999999999); --38個91 row created.SQL> insert into t3 values (999999999999999999999999999999999999999); --39個91 row created.SQL> insert into t3 values (99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999); --125個91 row created.
SQL> insert into t3 values (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999); --126個9
insert into t3 values (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)*
ERROR at line 1:
ORA-01426: numeric overflowSQL> 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 t2Name Null? Type----------------------------------------------------------------- -------- --------------------------------------------C1 NUMBERSQL> 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> DECLAREv1 INT(1);
BEGINv1 := 1;DBMS_OUTPUT.PUT_LINE('v1=' || v1);
END;
/ 2 3 4 5 6 7
v1=1PL/SQL procedure successfully completed.SQL> DECLAREv1 INT(1);
BEGINv1 := 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> DECLAREv1 INT;
BEGINv1 := 99999999999999999999999999999999999999; --38個9DBMS_OUTPUT.PUT_LINE('v1=' || v1);
END;
/ 2 3 4 5 6 7
v1=99999999999999999999999999999999999999PL/SQL procedure successfully completed.SQL> DECLAREv1 INT;
BEGINv1 := 999999999999999999999999999999999999999; --39個9DBMS_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類型是定長存儲，一般性能會更好
?
)
)
