Oracle默认值设置方法详解

在Oracle数据库中，为列设置默认值是一种常见的需求，可以简化数据插入操作并确保数据一致性。以下是Oracle中设置默认值的各种方法及其详细说明。

1. 创建表时设置默认值

基本语法

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype DEFAULT default_value,
    ...
);

示例

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    status VARCHAR2(20) DEFAULT 'ACTIVE',
    bonus NUMBER(10,2) DEFAULT 0
);

2. 修改表添加默认值

为已有列添加默认值

ALTER TABLE table_name 
MODIFY column_name DEFAULT default_value;

示例

ALTER TABLE employees 
MODIFY bonus DEFAULT 1000;

3. 默认值类型

Oracle支持多种类型的默认值：

• 常量值：DEFAULT 100, DEFAULT 'ACTIVE'
• 系统函数：DEFAULT SYSDATE, DEFAULT USER
• 序列值：DEFAULT sequence_name.NEXTVAL
• SQL表达式：DEFAULT (expression)

4. 特殊默认值设置

使用序列作为默认值

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE employees (
    emp_id NUMBER DEFAULT emp_seq.NEXTVAL,
    emp_name VARCHAR2(100)
);

使用SQL函数表达式

CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE DEFAULT SYSDATE,
    expected_ship_date DATE DEFAULT (SYSDATE + 2),
    created_by VARCHAR2(30) DEFAULT USER
);

5. 默认值与NOT NULL约束

当列设置了NOT NULL约束且没有显式提供值时，默认值会被使用：

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100) NOT NULL,
    stock_quantity NUMBER DEFAULT 0 NOT NULL
);

6. 修改和删除默认值

修改现有默认值

ALTER TABLE employees 
MODIFY hire_date DEFAULT TRUNC(SYSDATE);

删除默认值

将默认值设置为NULL即可删除：

ALTER TABLE employees 
MODIFY bonus DEFAULT NULL;

7. 默认值的行为特点

1. 插入行为：

   • 如果INSERT语句中明确指定了NULL，则使用NULL而非默认值
   • 如果INSERT语句完全省略该列，则使用默认值

2. 默认值在数据字典中的存储：

   • 可以通过USER_TAB_COLUMNS视图查看默认值设置

   SELECT column_name, data_default 
   FROM user_tab_columns 
   WHERE table_name = 'EMPLOYEES';
   

3. 默认值不会影响已有数据，只对新插入的数据有效

8. 高级用法

条件默认值（Oracle 12c及以上）

Oracle 12c引入了ON NULL子句，可以在显式插入NULL时也使用默认值：

CREATE TABLE test_table (
    id NUMBER,
    status VARCHAR2(10) DEFAULT 'PENDING' ON NULL
);

虚拟列默认值

可以为虚拟列指定默认值：

CREATE TABLE sales (
    amount NUMBER,
    tax_rate NUMBER,
    tax_amount NUMBER GENERATED ALWAYS AS (amount * tax_rate) VIRTUAL,
    total_amount NUMBER GENERATED ALWAYS AS (amount * (1 + tax_rate)) VIRTUAL
);

9. 注意事项

1. 默认值表达式不能引用同表的其他列
2. 默认值不能包含子查询
3. 对于LOB和LONG类型列不能设置默认值
4. 默认值在触发器执行前就已经被应用

通过合理使用默认值，可以简化应用程序代码，确保数据一致性，并提高数据库操作的效率。