Oracle数据插入 #

-- INSERT语句基本语法
INSERT INTO table_name [(column1, column2, ...)]
VALUES (value1, value2, ...);

-- 或使用子查询
INSERT INTO table_name [(column1, column2, ...)]
SELECT column1, column2, ... FROM another_table;

-- 创建示例表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    phone_number VARCHAR2(20),
    hire_date DATE DEFAULT SYSDATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);

-- 创建序列
CREATE SEQUENCE seq_employee_id START WITH 1 INCREMENT BY 1;

-- 插入所有列
INSERT INTO employees VALUES (
    seq_employee_id.NEXTVAL,
    'John',
    'Doe',
    'john.doe@example.com',
    '123-456-7890',
    TO_DATE('2024-01-15', 'YYYY-MM-DD'),
    'IT_PROG',
    8000,
    NULL,
    100,
    10
);

-- 插入指定列
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
) VALUES (
    seq_employee_id.NEXTVAL,
    'Jane',
    'Smith',
    'jane.smith@example.com',
    SYSDATE,
    'SA_REP',
    6000,
    20
);

-- 未指定的列使用默认值或NULL

-- 使用DEFAULT关键字
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
) VALUES (
    seq_employee_id.NEXTVAL,
    'Bob',
    'Johnson',
    'bob.johnson@example.com',
    DEFAULT,  -- 使用默认值SYSDATE
    'IT_PROG',
    7000,
    10
);

-- 显式插入NULL
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
) VALUES (
    seq_employee_id.NEXTVAL,
    'Alice',
    'Williams',
    'alice.williams@example.com',
    NULL,  -- 显式NULL
    SYSDATE,
    'SA_REP',
    5000,
    NULL,
    NULL,
    30
);

-- 省略列（隐式NULL）
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
) VALUES (
    seq_employee_id.NEXTVAL,
    'Charlie',
    'Brown',
    'charlie.brown@example.com',
    SYSDATE,
    'SA_REP',
    5500,
    30
);
-- phone_number, commission_pct, manager_id 为NULL

-- INSERT ALL：无条件多行插入
INSERT ALL
    INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
    VALUES (seq_employee_id.NEXTVAL, 'User1', 'Test1', 'user1@test.com', SYSDATE, 'IT_PROG', 5000, 10)
    INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
    VALUES (seq_employee_id.NEXTVAL, 'User2', 'Test2', 'user2@test.com', SYSDATE, 'IT_PROG', 5500, 10)
    INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
    VALUES (seq_employee_id.NEXTVAL, 'User3', 'Test3', 'user3@test.com', SYSDATE, 'IT_PROG', 6000, 10)
SELECT * FROM DUAL;

-- 注意：seq_employee_id.NEXTVAL在每行都会递增

-- 从另一个表复制数据
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
)
SELECT 
    seq_employee_id.NEXTVAL,
    first_name,
    last_name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
FROM temp_employees;

-- 复制整个表
INSERT INTO employees_backup
SELECT * FROM employees;

-- 条件复制
INSERT INTO high_salary_employees
SELECT * FROM employees WHERE salary > 10000;

-- INSERT FIRST：条件多行插入
-- 满足第一个条件后不再检查后续条件
INSERT FIRST
    WHEN salary > 15000 THEN
        INTO high_salary_employees (employee_id, first_name, last_name, salary)
        VALUES (employee_id, first_name, last_name, salary)
    WHEN salary > 10000 THEN
        INTO medium_salary_employees (employee_id, first_name, last_name, salary)
        VALUES (employee_id, first_name, last_name, salary)
    ELSE
        INTO low_salary_employees (employee_id, first_name, last_name, salary)
        VALUES (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM employees;

-- INSERT ALL：条件多行插入
-- 所有条件都会检查
INSERT ALL
    WHEN salary > 10000 THEN
        INTO high_salary_employees (employee_id, first_name, last_name, salary)
        VALUES (employee_id, first_name, last_name, salary)
    WHEN department_id = 10 THEN
        INTO it_department_employees (employee_id, first_name, last_name, department_id)
        VALUES (employee_id, first_name, last_name, department_id)
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;

-- 多表插入类型
-- INSERT ALL：无条件插入所有目标表
-- INSERT ALL WHEN：条件插入多个表
-- INSERT FIRST WHEN：条件插入第一个满足的表

-- 创建目标表
CREATE TABLE sales_2023 AS SELECT * FROM sales WHERE 1=0;
CREATE TABLE sales_2024 AS SELECT * FROM sales WHERE 1=0;
CREATE TABLE sales_other AS SELECT * FROM sales WHERE 1=0;

-- 按年份分表插入
INSERT FIRST
    WHEN TO_CHAR(sale_date, 'YYYY') = '2023' THEN
        INTO sales_2023 VALUES (sale_id, customer_id, sale_date, amount)
    WHEN TO_CHAR(sale_date, 'YYYY') = '2024' THEN
        INTO sales_2024 VALUES (sale_id, customer_id, sale_date, amount)
    ELSE
        INTO sales_other VALUES (sale_id, customer_id, sale_date, amount)
SELECT sale_id, customer_id, sale_date, amount
FROM sales_temp;

-- 将行数据旋转为列数据插入
-- 源数据
CREATE TABLE sales_by_quarter (
    year NUMBER,
    q1_sales NUMBER,
    q2_sales NUMBER,
    q3_sales NUMBER,
    q4_sales NUMBER
);

-- 目标表
CREATE TABLE sales_monthly (
    year NUMBER,
    month NUMBER,
    sales NUMBER
);

-- 旋转插入
INSERT ALL
    INTO sales_monthly (year, month, sales) VALUES (year, 1, q1_sales)
    INTO sales_monthly (year, month, sales) VALUES (year, 4, q2_sales)
    INTO sales_monthly (year, month, sales) VALUES (year, 7, q3_sales)
    INTO sales_monthly (year, month, sales) VALUES (year, 10, q4_sales)
SELECT year, q1_sales, q2_sales, q3_sales, q4_sales
FROM sales_by_quarter;

-- 创建序列
CREATE SEQUENCE seq_employee_id START WITH 1 INCREMENT BY 1 NOCACHE;

-- 使用序列插入
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (seq_employee_id.NEXTVAL, 'John', 'Doe', 'john.doe@example.com');

-- 批量使用序列
INSERT INTO employees (employee_id, first_name, last_name, email)
SELECT seq_employee_id.NEXTVAL, first_name, last_name, email
FROM temp_employees;

-- 获取刚插入的序列值
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (seq_employee_id.NEXTVAL, 'Jane', 'Smith', 'jane.smith@example.com')
RETURNING employee_id INTO :v_emp_id;

-- 使用日期函数
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date
) VALUES (
    seq_employee_id.NEXTVAL,
    'John',
    'Doe',
    LOWER('John.Doe') || '@example.com',
    TO_DATE('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS')
);

-- 使用字符串函数
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email
) VALUES (
    seq_employee_id.NEXTVAL,
    INITCAP('john'),
    INITCAP('doe'),
    LOWER('JOHN.DOE') || '@example.com'
);

-- 使用SYS_GUID()
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email
) VALUES (
    TO_NUMBER(SUBSTR(SYS_GUID(), 1, 10), 'XXXXXXXXXX'),
    'John',
    'Doe',
    'john.doe@example.com'
);

-- 返回插入的值
VARIABLE v_emp_id NUMBER;
VARIABLE v_hire_date VARCHAR2(30);

INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date
) VALUES (
    seq_employee_id.NEXTVAL,
    'John',
    'Doe',
    'john.doe@example.com',
    SYSDATE
) RETURNING 
    employee_id, 
    TO_CHAR(hire_date, 'YYYY-MM-DD HH24:MI:SS')
INTO 
    :v_emp_id, 
    :v_hire_date;

PRINT v_emp_id;
PRINT v_hire_date;

-- PL/SQL中使用RETURNING
DECLARE
    v_emp_id NUMBER;
    v_hire_date DATE;
BEGIN
    INSERT INTO employees (
        employee_id,
        first_name,
        last_name,
        email,
        hire_date
    ) VALUES (
        seq_employee_id.NEXTVAL,
        'Jane',
        'Smith',
        'jane.smith@example.com',
        SYSDATE
    ) RETURNING employee_id, hire_date INTO v_emp_id, v_hire_date;
    
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);
    DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(v_hire_date, 'YYYY-MM-DD'));
END;
/

-- 直接路径插入（绕过缓冲区缓存）
INSERT /*+ APPEND */ INTO employees_backup
SELECT * FROM employees;

-- 直接路径插入特点
-- 1. 绕过缓冲区缓存，直接写入数据文件
-- 2. 不使用现有空间，在高水位线之上写入
-- 3. 更快的批量插入
-- 4. 插入后需要提交才能查询

-- 使用APPEND_VALUES（11g+）
INSERT /*+ APPEND_VALUES */ INTO employees
VALUES (seq_employee_id.NEXTVAL, 'John', 'Doe', 'john.doe@example.com', SYSDATE, 'IT_PROG', 5000, NULL, NULL, 10);

-- 直接路径插入限制
-- 1. 表不能有触发器
-- 2. 表不能有参照完整性约束
-- 3. 表不能有检查约束
-- 4. 插入后需要提交才能查询

-- 检查表是否适合直接路径插入
SELECT 
    TABLE_NAME,
    TRIGGER_NAME
FROM USER_TRIGGERS
WHERE TABLE_NAME = 'EMPLOYEES';

SELECT 
    TABLE_NAME,
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES';

-- 1. 使用直接路径插入
INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;

-- 2. 禁用索引和约束
ALTER TABLE target_table DISABLE CONSTRAINT pk_target;
ALTER INDEX idx_target UNUSABLE;

INSERT INTO target_table SELECT * FROM source_table;

ALTER INDEX idx_target REBUILD;
ALTER TABLE target_table ENABLE CONSTRAINT pk_target;

-- 3. 使用并行插入
INSERT /*+ PARALLEL(target_table, 4) */ INTO target_table
SELECT * FROM source_table;

-- 4. 使用NOLOGGING减少日志
ALTER TABLE target_table NOLOGGING;
INSERT /*+ APPEND */ INTO target_table SELECT * FROM source_table;
ALTER TABLE target_table LOGGING;

-- SQL*Loader是Oracle提供的高速数据加载工具
-- 控制文件示例 (load.ctl)
/*
LOAD DATA
INFILE 'data.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
    employee_id,
    first_name,
    last_name,
    email,
    hire_date DATE "YYYY-MM-DD",
    job_id,
    salary,
    department_id
)
*/

-- 执行加载
-- sqlldr userid=username/password control=load.ctl direct=true

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

-- 创建外部表
CREATE TABLE ext_employees (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER,
    department_id NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
    )
    LOCATION ('employees.csv')
);

-- 从外部表插入
INSERT INTO employees
SELECT * FROM ext_employees;

-- 创建错误日志表
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('employees', 'err_employees');

-- 使用LOG ERRORS子句
INSERT INTO employees
SELECT * FROM temp_employees
LOG ERRORS INTO err_employees REJECT LIMIT UNLIMITED;

-- 查看错误记录
SELECT * FROM err_employees;

-- 限制错误数量
INSERT INTO employees
SELECT * FROM temp_employees
LOG ERRORS INTO err_employees REJECT LIMIT 100;

-- 捕获唯一约束违规
BEGIN
    INSERT INTO employees (employee_id, email)
    VALUES (1, 'duplicate@example.com');
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Duplicate email');
END;
/

-- 捕获检查约束违规
BEGIN
    INSERT INTO employees (employee_id, salary)
    VALUES (seq_employee_id.NEXTVAL, -100);
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -2290 THEN
            DBMS_OUTPUT.PUT_LINE('Check constraint violated');
        ELSE
            RAISE;
        END IF;
END;
/