Oracle数据更新 #

-- UPDATE语句基本语法
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

-- 使用子查询更新
UPDATE table_name
SET column = (SELECT value FROM another_table WHERE ...)
WHERE condition;

-- 创建示例表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    salary NUMBER(8,2),
    department_id NUMBER(4),
    manager_id NUMBER(6),
    hire_date DATE
);

-- 插入测试数据
INSERT INTO employees VALUES (1, 'John', 'Doe', 'john@example.com', 5000, 10, 100, SYSDATE);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 'jane@example.com', 6000, 10, 100, SYSDATE);
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 'bob@example.com', 7000, 20, 101, SYSDATE);
INSERT INTO employees VALUES (4, 'Alice', 'Williams', 'alice@example.com', 8000, 20, 101, SYSDATE);
INSERT INTO employees VALUES (5, 'Charlie', 'Brown', 'charlie@example.com', 9000, 30, 102, SYSDATE);

-- 更新单个列
UPDATE employees
SET salary = 5500
WHERE employee_id = 1;

-- 更新多个列
UPDATE employees
SET salary = 6000, department_id = 20
WHERE employee_id = 2;

-- 使用表达式更新
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 3;

-- 使用函数更新
UPDATE employees
SET email = LOWER(first_name || '.' || last_name || '@example.com')
WHERE employee_id = 4;

-- 使用WHERE条件
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10;

-- 使用多个条件
UPDATE employees
SET salary = salary * 1.2
WHERE department_id = 10 AND salary < 6000;

-- 使用IN条件
UPDATE employees
SET department_id = 40
WHERE employee_id IN (1, 2, 3);

-- 使用BETWEEN条件
UPDATE employees
SET salary = salary * 1.05
WHERE salary BETWEEN 5000 AND 7000;

-- 使用LIKE条件
UPDATE employees
SET email = REPLACE(email, '@example.com', '@newcompany.com')
WHERE email LIKE '%@example.com';

-- 使用子查询条件
UPDATE employees
SET salary = salary * 1.1
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- 更新为NULL
UPDATE employees
SET manager_id = NULL
WHERE employee_id = 5;

-- 更新NULL值为默认值
UPDATE employees
SET manager_id = 100
WHERE manager_id IS NULL;

-- 使用NVL处理NULL
UPDATE employees
SET salary = NVL(salary, 0) + 1000;

-- 使用子查询更新单列
UPDATE employees e
SET salary = (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
)
WHERE employee_id = 1;

-- 使用子查询更新多列
UPDATE employees e
SET (salary, department_id) = (
    SELECT salary, department_id
    FROM employees_backup
    WHERE employee_id = e.employee_id
)
WHERE EXISTS (
    SELECT 1
    FROM employees_backup
    WHERE employee_id = e.employee_id
);

-- 相关子查询更新
UPDATE employees e
SET salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
)
WHERE salary < (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

-- 使用EXISTS确保子查询返回值
UPDATE employees e
SET department_name = (
    SELECT department_name
    FROM departments d
    WHERE d.department_id = e.department_id
)
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
);

-- 使用WITH子句（12c+）
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
UPDATE employees e
SET salary = (
    SELECT avg_salary
    FROM dept_avg d
    WHERE d.department_id = e.department_id
)
WHERE department_id = 10;

-- 创建可更新视图
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    e.department_id,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 通过视图更新
UPDATE emp_dept_view
SET salary = salary * 1.1
WHERE department_name = 'IT';

-- 注意：只能更新基础表的列，不能更新连接列

-- 使用内联视图更新
UPDATE (
    SELECT e.salary, e.department_id
    FROM employees e
    WHERE e.department_id = 10
)
SET salary = salary * 1.1;

-- 使用内联视图关联更新
UPDATE (
    SELECT e.salary, d.max_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
)
SET salary = max_salary
WHERE salary < max_salary;

-- MERGE语句：合并插入和更新
MERGE INTO employees e
USING employees_temp t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        e.first_name = t.first_name,
        e.last_name = t.last_name,
        e.salary = t.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary)
    VALUES (t.employee_id, t.first_name, t.last_name, t.salary);

-- MERGE带条件
MERGE INTO employees e
USING employees_temp t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        e.salary = t.salary
    WHERE e.salary != t.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary)
    VALUES (t.employee_id, t.first_name, t.last_name, t.salary);

-- MERGE带删除
MERGE INTO employees e
USING employees_temp t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        e.salary = t.salary
    DELETE WHERE t.active = 'N'
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary)
    VALUES (t.employee_id, t.first_name, t.last_name, t.salary);

-- 使用CASE表达式条件更新
UPDATE employees
SET salary = CASE
    WHEN department_id = 10 THEN salary * 1.1
    WHEN department_id = 20 THEN salary * 1.15
    WHEN department_id = 30 THEN salary * 1.2
    ELSE salary * 1.05
END;

-- 复杂CASE更新
UPDATE employees
SET 
    salary = CASE
        WHEN salary < 5000 THEN salary * 1.2
        WHEN salary BETWEEN 5000 AND 8000 THEN salary * 1.1
        ELSE salary * 1.05
    END,
    department_id = CASE
        WHEN salary < 5000 THEN 10
        WHEN salary BETWEEN 5000 AND 8000 THEN 20
        ELSE 30
    END;

-- 使用DECODE函数更新
UPDATE employees
SET department_id = DECODE(
    department_id,
    10, 20,
    20, 30,
    30, 40,
    department_id
);

-- 使用DECODE更新薪资
UPDATE employees
SET salary = DECODE(
    job_id,
    'IT_PROG', salary * 1.1,
    'SA_REP', salary * 1.15,
    'SA_MAN', salary * 1.2,
    salary
);

-- 返回更新后的值
VARIABLE v_old_salary NUMBER;
VARIABLE v_new_salary NUMBER;

UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 1
RETURNING 
    salary / 1.1,  -- 旧值
    salary         -- 新值
INTO 
    :v_old_salary, 
    :v_new_salary;

PRINT v_old_salary;
PRINT v_new_salary;

-- PL/SQL中使用RETURNING
DECLARE
    v_old_salary NUMBER;
    v_new_salary NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary * 1.1
    WHERE employee_id = 1
    RETURNING salary / 1.1, salary INTO v_old_salary, v_new_salary;
    
    DBMS_OUTPUT.PUT_LINE('Old Salary: ' || v_old_salary);
    DBMS_OUTPUT.PUT_LINE('New Salary: ' || v_new_salary);
END;
/

-- PL/SQL中使用FORALL批量更新
DECLARE
    TYPE id_array IS TABLE OF NUMBER;
    TYPE salary_array IS TABLE OF NUMBER;
    
    v_ids id_array;
    v_salaries salary_array;
BEGIN
    -- 获取需要更新的数据
    SELECT employee_id, salary * 1.1
    BULK COLLECT INTO v_ids, v_salaries
    FROM employees
    WHERE department_id = 10;
    
    -- 批量更新
    FORALL i IN 1..v_ids.COUNT
        UPDATE employees
        SET salary = v_salaries(i)
        WHERE employee_id = v_ids(i);
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows');
END;
/

-- 批量更新示例
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 10
        FOR UPDATE;
    
    TYPE emp_rec_type IS TABLE OF c_emp%ROWTYPE;
    v_emps emp_rec_type;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp BULK COLLECT INTO v_emps LIMIT 100;
        
        FORALL i IN 1..v_emps.COUNT
            UPDATE employees
            SET salary = v_emps(i).salary * 1.1
            WHERE CURRENT OF c_emp;
        
        EXIT WHEN v_emps.COUNT < 100;
    END LOOP;
    CLOSE c_emp;
    
    COMMIT;
END;
/

-- 确保WHERE条件使用索引
-- 创建索引
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 使用索引列更新
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10;  -- 使用索引

-- 查看执行计划
EXPLAIN PLAN FOR
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 1. 禁用索引
ALTER INDEX idx_emp_salary UNUSABLE;

-- 2. 批量更新
UPDATE employees SET salary = salary * 1.1;

-- 3. 重建索引
ALTER INDEX idx_emp_salary REBUILD;

-- 使用并行更新
UPDATE /*+ PARALLEL(employees, 4) */ employees
SET salary = salary * 1.1
WHERE department_id = 10;

-- 使用NOLOGGING减少日志（对直接路径操作有效）
ALTER TABLE employees NOLOGGING;

-- 对于大量更新，考虑使用CTAS重建表
CREATE TABLE employees_new NOLOGGING AS
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    salary * 1.1 AS salary,
    department_id
FROM employees;

-- 替换原表
DROP TABLE employees;
RENAME employees_new TO employees;

-- 重建索引和约束

-- 捕获更新异常
BEGIN
    UPDATE employees
    SET salary = -100
    WHERE employee_id = 1;
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -2290 THEN
            DBMS_OUTPUT.PUT_LINE('Check constraint violated');
        ELSE
            RAISE;
        END IF;
END;
/

-- 检查更新行数
DECLARE
    v_rows_updated NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary * 1.1
    WHERE department_id = 99;
    
    v_rows_updated := SQL%ROWCOUNT;
    
    IF v_rows_updated = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No rows updated');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_rows_updated || ' rows updated');
    END IF;
END;
/

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

-- 使用LOG ERRORS记录错误
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10
LOG ERRORS INTO err_employees REJECT LIMIT UNLIMITED;

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

-- 陷阱1：忘记WHERE条件
-- 危险操作！更新所有行
-- UPDATE employees SET salary = 0;

-- 安全做法：先查询再更新
SELECT COUNT(*) FROM employees WHERE department_id = 10;
-- 确认数量后再执行更新
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

-- 陷阱2：子查询返回多行
-- 错误：子查询返回多行
-- UPDATE employees e
-- SET department_id = (
--     SELECT department_id FROM departments WHERE location_id = 1700
-- );

-- 正确：使用单行子查询或聚合
UPDATE employees e
SET department_id = (
    SELECT MIN(department_id) FROM departments WHERE location_id = 1700
);

-- 陷阱3：更新主键
-- 可能导致外键约束失败
-- UPDATE departments SET department_id = 100 WHERE department_id = 10;

-- 使用SELECT FOR UPDATE锁定行
SELECT * FROM employees
WHERE employee_id = 1
FOR UPDATE;

-- 更新锁定行
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 1;

COMMIT;

-- 使用NOWAIT避免等待
SELECT * FROM employees
WHERE employee_id = 1
FOR UPDATE NOWAIT;

-- 使用SKIP LOCKED跳过锁定行
SELECT * FROM employees
WHERE employee_id = 1
FOR UPDATE SKIP LOCKED;

-- 创建更新触发器
CREATE OR REPLACE TRIGGER trg_employees_update
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased');
    END IF;
    
    :NEW.salary := ROUND(:NEW.salary, 2);
END;
/

-- 测试触发器
UPDATE employees SET salary = 4000 WHERE employee_id = 1;  -- 会报错