Oracle数据类型 #

Oracle数据类型
├── 数值类型
│   ├── NUMBER
│   ├── INTEGER
│   ├── FLOAT
│   ├── BINARY_FLOAT
│   └── BINARY_DOUBLE
├── 字符类型
│   ├── CHAR
│   ├── VARCHAR2
│   ├── NCHAR
│   ├── NVARCHAR2
│   └── CLOB
├── 日期时间类型
│   ├── DATE
│   ├── TIMESTAMP
│   ├── TIMESTAMP WITH TIME ZONE
│   └── INTERVAL
├── LOB类型
│   ├── CLOB
│   ├── BLOB
│   ├── NCLOB
│   └── BFILE
├── RAW类型
│   ├── RAW
│   └── LONG RAW
└── 其他类型
    ├── ROWID
    ├── UROWID
    ├── XMLTYPE
    └── JSON（21c+）

-- NUMBER语法
-- NUMBER(precision, scale)
-- precision：精度，总位数（1-38）
-- scale：标度，小数位数（-84-127）

-- 整数
CREATE TABLE number_examples (
    id NUMBER,              -- 无限制
    age NUMBER(3),          -- 最大999
    count_num NUMBER(10)    -- 最大10位整数
);

-- 定点数
ALTER TABLE number_examples ADD (
    salary NUMBER(10,2),    -- 最大8位整数，2位小数
    rate NUMBER(5,4)        -- 1位整数，4位小数
);

-- 负标度
ALTER TABLE number_examples ADD (
    large_num NUMBER(10,-2) -- 精确到百位
);

-- 插入数据
INSERT INTO number_examples (id, age, salary, rate, large_num)
VALUES (1, 25, 12345.67, 0.1234, 12345);
-- large_num存储：12300（四舍五入到百位）

-- NUMBER存储范围
-- 正数：1 x 10^-130 到 9.999... x 10^125
-- 负数：-1 x 10^-130 到 -9.999... x 10^125
-- 零：0

-- 测试范围
SELECT 
    CAST('1E-130' AS NUMBER) AS min_positive,
    CAST('1E125' AS NUMBER) AS max_positive
FROM DUAL;

-- 精度溢出会报错
INSERT INTO number_examples (age) VALUES (1000);  -- ORA-01438

-- INTEGER是NUMBER的子类型，存储整数
CREATE TABLE integer_examples (
    id INTEGER,
    count_val INTEGER
);

-- 等价于
CREATE TABLE integer_examples (
    id NUMBER(38),
    count_val NUMBER(38)
);

-- 插入数据
INSERT INTO integer_examples VALUES (1, 100);
INSERT INTO integer_examples VALUES (2, 100.5);  -- 四舍五入为101

-- FLOAT是NUMBER的子类型，浮点数
CREATE TABLE float_examples (
    id NUMBER,
    value FLOAT,
    value_126 FLOAT(126)  -- 126位二进制精度
);

-- FLOAT(n)：n为二进制精度（1-126）
-- FLOAT(126)约等于38位十进制精度

-- 插入数据
INSERT INTO float_examples VALUES (1, 123.456789, 123.456789);

-- BINARY_FLOAT：32位单精度浮点数
-- BINARY_DOUBLE：64位双精度浮点数

CREATE TABLE binary_examples (
    id NUMBER,
    float_val BINARY_FLOAT,
    double_val BINARY_DOUBLE
);

-- 插入数据
INSERT INTO binary_examples VALUES (
    1, 
    123.456f,    -- BINARY_FLOAT后缀f
    123.456d     -- BINARY_DOUBLE后缀d
);

-- 特殊值
INSERT INTO binary_examples VALUES (
    2,
    BINARY_FLOAT_INFINITY,    -- 正无穷
    BINARY_DOUBLE_INFINITY
);

INSERT INTO binary_examples VALUES (
    3,
    BINARY_FLOAT_NAN,         -- 非数字
    BINARY_DOUBLE_NAN
);

-- 性能比较
-- BINARY_FLOAT/BINARY_DOUBLE：计算速度快
-- NUMBER：精确计算，适合金融

-- CHAR：定长字符串
-- CHAR(n)：n为字符数（1-2000）

CREATE TABLE char_examples (
    code CHAR(10),        -- 10个字符
    status CHAR(1),       -- 1个字符
    flag CHAR(5) DEFAULT 'N'
);

-- 插入数据
INSERT INTO char_examples (code, status) VALUES ('ABC', 'A');
-- code存储：'ABC       '（补空格到10位）

-- 查询时注意空格
SELECT * FROM char_examples WHERE code = 'ABC';      -- 可以匹配
SELECT * FROM char_examples WHERE code = 'ABC     '; -- 可以匹配
SELECT * FROM char_examples WHERE TRIM(code) = 'ABC'; -- 去空格匹配

-- VARCHAR2：变长字符串
-- VARCHAR2(n)：n为字符数（1-4000）

CREATE TABLE varchar2_examples (
    name VARCHAR2(100),
    email VARCHAR2(200),
    description VARCHAR2(4000)
);

-- 插入数据
INSERT INTO varchar2_examples (name, email, description)
VALUES ('John', 'john@example.com', 'This is a description');

-- name存储：'John'（4个字符，不补空格）

-- 最大长度
-- 12c R1及之前：4000字节
-- 12c R2及之后：32767字节（需启用EXTENDED）

-- NCHAR：定长Unicode字符串（1-2000字符）
-- NVARCHAR2：变长Unicode字符串（1-4000字符）

CREATE TABLE nchar_examples (
    chinese_name NVARCHAR2(100),
    japanese_name NVARCHAR2(100),
    korean_name NVARCHAR2(100)
);

-- 插入多语言数据
INSERT INTO nchar_examples VALUES (
    '张三',
    '田中',
    '김철수'
);

-- 使用N前缀指定Unicode字符串
INSERT INTO nchar_examples VALUES (
    N'李四',
    N'佐藤',
    N'박영희'
);

-- DATE：日期时间类型
-- 存储：世纪、年、月、日、时、分、秒

CREATE TABLE date_examples (
    id NUMBER,
    birth_date DATE,
    hire_date DATE,
    create_time DATE DEFAULT SYSDATE
);

-- 插入数据
INSERT INTO date_examples (id, birth_date, hire_date)
VALUES (1, TO_DATE('1990-05-15', 'YYYY-MM-DD'), SYSDATE);

-- 使用日期字面量
INSERT INTO date_examples (id, birth_date, hire_date)
VALUES (2, DATE '1990-05-15', DATE '2024-01-15');

-- 日期范围：公元前4712年1月1日 到 公元9999年12月31日

-- 当前日期
SELECT SYSDATE FROM DUAL;
SELECT CURRENT_DATE FROM DUAL;

-- 日期计算
SELECT SYSDATE + 1 FROM DUAL;      -- 加1天
SELECT SYSDATE - 1 FROM DUAL;      -- 减1天
SELECT SYSDATE + 1/24 FROM DUAL;   -- 加1小时
SELECT SYSDATE + 1/1440 FROM DUAL; -- 加1分钟

-- 日期函数
SELECT 
    ADD_MONTHS(SYSDATE, 1) AS next_month,
    LAST_DAY(SYSDATE) AS last_day,
    MONTHS_BETWEEN(SYSDATE, hire_date) AS months,
    NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday
FROM date_examples;

-- 提取日期部分
SELECT 
    EXTRACT(YEAR FROM SYSDATE) AS year,
    EXTRACT(MONTH FROM SYSDATE) AS month,
    EXTRACT(DAY FROM SYSDATE) AS day
FROM DUAL;

-- 日期格式化
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') FROM DUAL;

-- TIMESTAMP：时间戳，包含小数秒

CREATE TABLE timestamp_examples (
    id NUMBER,
    create_time TIMESTAMP,
    update_time TIMESTAMP(6)  -- 6位小数秒
);

-- 插入数据
INSERT INTO timestamp_examples (id, create_time)
VALUES (1, TO_TIMESTAMP('2024-01-15 10:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.FF6'));

-- 使用时间戳字面量
INSERT INTO timestamp_examples (id, create_time)
VALUES (2, TIMESTAMP '2024-01-15 10:30:45.123456');

-- 当前时间戳
SELECT SYSTIMESTAMP FROM DUAL;
SELECT CURRENT_TIMESTAMP FROM DUAL;

-- 时间戳格式化
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM DUAL;

-- TIMESTAMP WITH TIME ZONE：带时区的时间戳

CREATE TABLE timezone_examples (
    id NUMBER,
    event_time TIMESTAMP WITH TIME ZONE,
    local_time TIMESTAMP WITH LOCAL TIME ZONE
);

-- 插入数据
INSERT INTO timezone_examples (id, event_time)
VALUES (1, TIMESTAMP '2024-01-15 10:30:00 +08:00');

-- 查询时区信息
SELECT 
    event_time,
    EXTRACT(TIMEZONE_HOUR FROM event_time) AS tz_hour,
    EXTRACT(TIMEZONE_MINUTE FROM event_time) AS tz_minute
FROM timezone_examples;

-- 时区转换
SELECT 
    event_time AT TIME ZONE 'UTC' AS utc_time,
    event_time AT TIME ZONE 'America/New_York' AS ny_time
FROM timezone_examples;

-- INTERVAL YEAR TO MONTH：年月间隔
-- INTERVAL DAY TO SECOND：日时分秒间隔

CREATE TABLE interval_examples (
    id NUMBER,
    year_month INTERVAL YEAR(2) TO MONTH,
    day_second INTERVAL DAY(2) TO SECOND(6)
);

-- 插入数据
INSERT INTO interval_examples (id, year_month, day_second)
VALUES (
    1,
    INTERVAL '1-6' YEAR TO MONTH,      -- 1年6个月
    INTERVAL '2 12:30:45.123456' DAY TO SECOND  -- 2天12小时30分45秒
);

-- 使用INTERVAL计算
SELECT 
    SYSDATE + INTERVAL '1' YEAR AS next_year,
    SYSDATE + INTERVAL '6' MONTH AS six_months_later,
    SYSDATE + INTERVAL '7' DAY AS next_week
FROM DUAL;

-- 计算两个日期的间隔
SELECT 
    (TO_DATE('2024-12-31', 'YYYY-MM-DD') - SYSDATE) DAY TO SECOND AS remaining
FROM DUAL;

-- CLOB：字符大对象，存储大文本

CREATE TABLE clob_examples (
    id NUMBER PRIMARY KEY,
    title VARCHAR2(200),
    content CLOB
);

-- 插入数据
INSERT INTO clob_examples (id, title, content)
VALUES (1, '文章标题', '这是一篇很长的文章内容...');

-- 使用EMPTY_CLOB()
INSERT INTO clob_examples (id, title, content)
VALUES (2, '文章标题2', EMPTY_CLOB());

-- 更新CLOB
UPDATE clob_examples 
SET content = '更新后的内容'
WHERE id = 2;

-- 读取CLOB
SELECT id, title, DBMS_LOB.SUBSTR(content, 100, 1) AS content_preview
FROM clob_examples;

-- CLOB最大大小：4GB * 数据库块大小

-- BLOB：二进制大对象，存储图片、文件等

CREATE TABLE blob_examples (
    id NUMBER PRIMARY KEY,
    file_name VARCHAR2(100),
    file_data BLOB,
    file_size NUMBER
);

-- 插入空BLOB
INSERT INTO blob_examples (id, file_name, file_data, file_size)
VALUES (1, 'image.png', EMPTY_BLOB(), 0);

-- 使用PL/SQL写入BLOB
DECLARE
    v_blob BLOB;
    v_file BFILE;
BEGIN
    INSERT INTO blob_examples (id, file_name, file_data)
    VALUES (1, 'image.png', EMPTY_BLOB())
    RETURNING file_data INTO v_blob;
    
    v_file := BFILENAME('FILE_DIR', 'image.png');
    DBMS_LOB.FILEOPEN(v_file);
    DBMS_LOB.LOADFROMFILE(v_blob, v_file, DBMS_LOB.GETLENGTH(v_file));
    DBMS_LOB.FILECLOSE(v_file);
    
    COMMIT;
END;
/

-- NCLOB：Unicode字符大对象

CREATE TABLE nclob_examples (
    id NUMBER PRIMARY KEY,
    title NVARCHAR2(200),
    content NCLOB
);

-- 插入多语言大文本
INSERT INTO nclob_examples (id, title, content)
VALUES (1, '标题', '多语言内容...');

-- BFILE：外部文件引用，文件存储在文件系统中

-- 创建目录对象
CREATE DIRECTORY FILE_DIR AS '/data/files';

CREATE TABLE bfile_examples (
    id NUMBER PRIMARY KEY,
    file_name VARCHAR2(100),
    file_path BFILE
);

-- 插入BFILE引用
INSERT INTO bfile_examples (id, file_name, file_path)
VALUES (1, 'document.pdf', BFILENAME('FILE_DIR', 'document.pdf'));

-- 检查文件是否存在
SELECT id, file_name, DBMS_LOB.FILEEXISTS(file_path) AS exists_flag
FROM bfile_examples;

-- RAW：原始二进制数据
-- RAW(n)：n为字节数（1-2000）

CREATE TABLE raw_examples (
    id NUMBER PRIMARY KEY,
    raw_data RAW(100),
    guid RAW(16)
);

-- 插入数据
INSERT INTO raw_examples (id, raw_data, guid)
VALUES (1, '48656C6C6F', SYS_GUID());

-- 生成GUID
SELECT SYS_GUID() FROM DUAL;
-- 结果：类似 6F9619FF8B86D011B42D00C04FC964FF

-- RAW转字符串
SELECT UTL_RAW.CAST_TO_VARCHAR2(raw_data) FROM raw_examples;
-- 结果：Hello

-- 字符串转RAW
SELECT UTL_RAW.CAST_TO_RAW('Hello') FROM DUAL;
-- 结果：48656C6C6F

-- 获取RAW长度
SELECT UTL_RAW.LENGTH(UTL_RAW.CAST_TO_RAW('Hello')) FROM DUAL;

-- RAW拼接
SELECT UTL_RAW.CONCAT(
    UTL_RAW.CAST_TO_RAW('Hello'),
    UTL_RAW.CAST_TO_RAW(' ')
) FROM DUAL;

-- RAW转十六进制字符串
SELECT RAWTOHEX(UTL_RAW.CAST_TO_RAW('Hello')) FROM DUAL;

-- 十六进制字符串转RAW
SELECT HEXTORAW('48656C6C6F') FROM DUAL;

-- ROWID：行的物理地址

CREATE TABLE rowid_examples (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);

-- 查看ROWID
SELECT ROWID, id, name FROM rowid_examples;

-- 通过ROWID查询（最快）
SELECT * FROM rowid_examples WHERE ROWID = 'AAASdqAAEAAAAInAAA';

-- ROWID组成
-- OOOOOO：数据对象号
-- FFF：相对文件号
-- BBBBBB：数据块号
-- RRR：行号

-- 解析ROWID
SELECT 
    DBMS_ROWID.ROWID_OBJECT(ROWID) AS object_id,
    DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS file_no,
    DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS block_no,
    DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS row_no
FROM rowid_examples;

-- UROWID：通用ROWID，支持索引组织表

-- XMLTYPE：存储XML数据

CREATE TABLE xml_examples (
    id NUMBER PRIMARY KEY,
    xml_data XMLTYPE
);

-- 插入XML数据
INSERT INTO xml_examples (id, xml_data)
VALUES (
    1,
    XMLTYPE('<employee><id>1</id><name>John</name></employee>')
);

-- 查询XML
SELECT 
    id,
    XMLTYPE.getclobval(xml_data) AS xml_string
FROM xml_examples;

-- 提取XML节点
SELECT 
    id,
    EXTRACTVALUE(xml_data, '/employee/name') AS name
FROM xml_examples;

-- 使用XMLTable
SELECT 
    x.id AS emp_id,
    x.name AS emp_name
FROM xml_examples e,
XMLTABLE('/employee' PASSING e.xml_data
    COLUMNS 
        id NUMBER PATH 'id',
        name VARCHAR2(50) PATH 'name'
) x;

-- JSON类型：存储JSON数据（Oracle 21c+）

CREATE TABLE json_examples (
    id NUMBER PRIMARY KEY,
    json_data JSON
);

-- 插入JSON数据
INSERT INTO json_examples (id, json_data)
VALUES (
    1,
    '{"name": "John", "age": 30, "skills": ["Oracle", "SQL"]}'
);

-- 查询JSON
SELECT 
    id,
    json_data.name AS name,
    json_data.age AS age
FROM json_examples;

-- JSON函数
SELECT 
    JSON_VALUE(json_data, '$.name') AS name,
    JSON_QUERY(json_data, '$.skills') AS skills
FROM json_examples;

-- JSON条件
SELECT * FROM json_examples
WHERE json_data.age > 25;

-- JSON_EXISTS
SELECT * FROM json_examples
WHERE JSON_EXISTS(json_data, '$.skills');

-- 字符串转数字
SELECT '123' + 10 FROM DUAL;  -- 133

-- 数字转字符串
SELECT 'ID: ' || 123 FROM DUAL;  -- ID: 123

-- 字符串转日期
SELECT * FROM employees 
WHERE hire_date > '2020-01-01';  -- 隐式转换

-- 不推荐依赖隐式转换，可能导致性能问题

-- TO_CHAR：转换为字符串
SELECT 
    TO_CHAR(12345.67, '999,999.99') AS formatted_number,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM DUAL;

-- TO_NUMBER：转换为数字
SELECT TO_NUMBER('12345.67', '99999.99') FROM DUAL;

-- TO_DATE：转换为日期
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;

-- TO_TIMESTAMP：转换为时间戳
SELECT TO_TIMESTAMP('2024-01-15 10:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- CAST：通用转换
SELECT 
    CAST(123 AS VARCHAR2(10)) AS str,
    CAST('123' AS NUMBER) AS num,
    CAST(SYSDATE AS TIMESTAMP) AS ts
FROM DUAL;