Oracle数据类型再探：plsql能建表时能用的数据类型

在plsql中创建一张表，我可以看到有以下的数据类型

 

 

 把这里边的每个数据类型都作为一个字段，发现一个表中只能插入一个long类型的字段，

 

 

于是分成两个表

-- Create table
create table ADAMTEST
(
  d_binary_double      BINARY_DOUBLE,
  d_binary_float       BINARY_FLOAT,
  d_blob2              BLOB,
  d_clob               CLOB,
  d_char               CHAR(2),
  d_date               DATE,
  d_intervald2s        INTERVAL DAY(2) TO SECOND(6),
  d_intervaly2m        INTERVAL YEAR(2) TO MONTH,
  d_long               LONG,
  d_nlob               NCLOB,
  d_number             NUMBER,
  d_number18           NUMBER(18),
  d_number182          NUMBER(18,2),
  d_nvarchar220        NVARCHAR2(20),
  d_raw50              RAW(50),
  d_timestamp          TIMESTAMP(6),
  d_timestamp6         TIMESTAMP(6),
  d_timestamplocalzone TIMESTAMP(6) WITH LOCAL TIME ZONE,
  d_timestampzone      TIMESTAMP(6) WITH TIME ZONE,
  d_verchar220         VARCHAR2(20)
);

 

-- Create table
create table ADAMTEST2
(
  d_longraw LONG RAW
)
;

胡乱添加了些测试数据后，我用C#去获取Oracle中的表，很自然地将其转为DataTable

但是在这个过程中，Oracle中的数据类型已经转变成了DataTable中的列类型，对照一下，可以得到下表

 

大类型 字段名 Oracle数据类型 DataTable数据类型 备注
文本 D_VERCHAR220 varchar2(20) {Name = "String" FullName = "System.String"}
文本 D_CLOB clob {Name = "String" FullName = "System.String"}
文本 D_CHAR char(2) {Name = "String" FullName = "System.String"}
文本 D_LONG long {Name = "String" FullName = "System.String"}
文本 D_NLOB nclob {Name = "String" FullName = "System.String"}
文本 D_NVARCHAR220 nvarchar2(20) {Name = "String" FullName = "System.String"}
数字 D_INTERVALY2M interval year to month {Name = "Int64" FullName = "System.Int64"} interval 时检差
数字 D_NUMBER18 number(18) {Name = "Int64" FullName = "System.Int64"}
数字 D_NUMBER number {Name = "Decimal" FullName = "System.Decimal"}
数字 D_NUMBER182 number(18,2) {Name = "Decimal" FullName = "System.Decimal"}
数字 D_BINARY_FLOAT binary_float {Name = "Single" FullName = "System.Single"} Single 类似于float
数字 D_BINARY_DOUBLE binary_double {Name = "Double" FullName = "System.Double"}
时间 D_DATE date {Name = "DateTime" FullName = "System.DateTime"}
时间 D_TIMESTAMP timestamp {Name = "DateTime" FullName = "System.DateTime"}
时间 D_TIMESTAMP6 timestamp(6) {Name = "DateTime" FullName = "System.DateTime"}
时间 D_TIMESTAMPLOCALZONE timestamp with local time zone {Name = "DateTime" FullName = "System.DateTime"}
时间 D_TIMESTAMPZONE timestamp with time zone {Name = "DateTime" FullName = "System.DateTime"}
时间间隔 D_INTERVALD2S interval day to second {Name = "TimeSpan" FullName = "System.TimeSpan"} TimeSpan表示一个时间间隔
二进制流 D_BLOB2 blob {Name = "Byte[]" FullName = "System.Byte[]"}
二进制流 D_RAW50 raw(50) {Name = "Byte[]" FullName = "System.Byte[]"}
二进制流 D_LONGRAW long raw {Name = "Byte[]" FullName = "System.Byte[]"}

 然后就可以对数据的类型进行分情况处理了。