【Oracle】DML语言

个人主页：Guiat
归属专栏：Oracle

正文
DML（Data Manipulation Language）是Oracle数据库的"魔法师"，专门负责数据的增删改查操作。如果说DDL是建房子的，那DML就是在房子里生活的——添置家具、整理物品、搬家换房。它让静态的数据结构变得生动起来，是我们与数据库数据打交道最频繁的语言！

1. DML概述

1.1 什么是DML？

DML就像是数据库的"生活管家"，负责管理数据库中的实际数据。它不改变数据库的结构，而是专注于数据内容的操作。在Oracle这个数据王国里，DML是让数据"活"起来的关键工具。

1.2 DML的核心功能

Oracle DML的功能体系就像一个完整的数据处理工厂：

2. INSERT语句详解

2.1 基础插入操作

INSERT就像往盒子里放东西，有很多种放法：

-- 最基本的插入
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1001, 'John', 'Doe', 'john.doe@company.com', SYSDATE);

-- 插入所有列（按表结构顺序）
INSERT INTO employees
VALUES (1002, 'Jane', 'Smith', 'jane.smith@company.com', '555-1234', 
        SYSDATE, 'IT_PROG', 5000, NULL, 100, 20);

-- 插入部分列（其他列使用默认值或NULL）
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (1003, 'Bob', 'Johnson', 'bob.johnson@company.com');

-- 使用序列插入
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (emp_seq.NEXTVAL, 'Alice', 'Brown', 'alice.brown@company.com', SYSDATE);

-- 插入计算值
INSERT INTO order_summary (order_id, order_date, total_amount, tax_amount, final_amount)
VALUES (1001, SYSDATE, 1000, 1000 * 0.08, 1000 * 1.08);

-- 插入函数结果
INSERT INTO audit_log (log_id, table_name, action_type, action_date, username)
VALUES (audit_seq.NEXTVAL, 'EMPLOYEES', 'INSERT', SYSTIMESTAMP, USER);

2.2 子查询插入

用子查询插入就像批量搬家，一次性处理大量数据：

-- 从其他表插入数据
INSERT INTO employees_backup
SELECT * FROM employees WHERE department_id = 20;

-- 插入聚合数据
INSERT INTO department_summary (dept_id, dept_name, emp_count, avg_salary, total_salary)
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id),
    AVG(e.salary),
    SUM(e.salary)
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 插入复杂计算结果
INSERT INTO sales_analysis (region, year, month, total_sales, growth_rate)
SELECT 
    region,
    EXTRACT(YEAR FROM sale_date) as year,
    EXTRACT(MONTH FROM sale_date) as month,
    SUM(amount) as total_sales,
    ROUND(
        (SUM(amount) - LAG(SUM(amount)) OVER (
            PARTITION BY region 
            ORDER BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
        )) / LAG(SUM(amount)) OVER (
            PARTITION BY region 
            ORDER BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
        ) * 100, 2
    ) as growth_rate
FROM sales_data
GROUP BY region, EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date);

-- 条件插入
INSERT INTO high_performers (employee_id, performance_score, bonus_eligible)
SELECT 
    employee_id,
    (salary * 0.1 + COALESCE(commission_pct * salary, 0)) as performance_score,
    CASE WHEN salary > 8000 THEN 'Y' ELSE 'N' END
FROM employees
WHERE hire_date < ADD_MONTHS(SYSDATE, -12)
  AND department_id IN (10, 20, 30);

2.3 多表插入

Oracle的多表插入功能就像是"一箭多雕"，一次操作影响多个表：

-- 无条件多表插入
INSERT ALL
    INTO sales_summary (region, total_amount) VALUES (region, amount)
    INTO sales_detail (sale_id, customer_id, amount) VALUES (sale_id, customer_id, amount)
    INTO sales_audit (sale_id, insert_date) VALUES (sale_id, SYSDATE)
SELECT sale_id, region, customer_id, amount
FROM sales_data
WHERE sale_date = TRUNC(SYSDATE);

-- 条件多表插入
INSERT ALL
    WHEN amount > 10000 THEN
        INTO high_value_sales (sale_id, amount, customer_id) 
        VALUES (sale_id, amount, customer_id)
    WHEN amount BETWEEN 1000 AND 10000 THEN
        INTO medium_value_sales (sale_id, amount, customer_id) 
        VALUES (sale_id, amount, customer_id)
    ELSE
        INTO low_value_sales (sale_id, amount, customer_id) 
        VALUES (sale_id, amount, customer_id)
SELECT sale_id, amount, customer_id
FROM sales_data
WHERE sale_date >= TRUNC(SYSDATE);

-- 旋转插入（数据透视）
INSERT ALL
    INTO quarterly_sales (year, quarter, region, amount) 
    VALUES (year, 1, region, q1_sales)
    INTO quarterly_sales (year, quarter, region, amount) 
    VALUES (year, 2, region, q2_sales)
    INTO quarterly_sales (year, quarter, region, amount) 
    VALUES (year, 3, region, q3_sales)
    INTO quarterly_sales (year, quarter, region, amount) 
    VALUES (year, 4, region, q4_sales)
SELECT year, region, q1_sales, q2_sales, q3_sales, q4_sales
FROM annual_sales_pivot;

-- 复杂的条件多表插入
INSERT ALL
    WHEN department_id = 10 THEN
        INTO dept_10_employees (employee_id, name, salary) 
        VALUES (employee_id, first_name || ' ' || last_name, salary)
        INTO dept_10_salaries (employee_id, salary, bonus) 
        VALUES (employee_id, salary, salary * 0.1)
    WHEN department_id = 20 THEN
        INTO dept_20_employees (employee_id, name, salary) 
        VALUES (employee_id, first_name || ' ' || last_name, salary)
    WHEN salary > 5000 THEN
        INTO high_earners (employee_id, salary, department_id) 
        VALUES (employee_id, salary, department_id)
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE status = 'ACTIVE';

2.4 批量插入优化

批量插入就像是用卡车运货，比一件件搬要高效得多：

-- 使用FORALL进行批量插入（PL/SQL）
DECLARE
    TYPE emp_array IS TABLE OF employees%ROWTYPE;
    l_employees emp_array;
BEGIN
    -- 准备数据
    l_employees := emp_array();
    FOR i IN 1..10000 LOOP
        l_employees.EXTEND;
        l_employees(i).employee_id := emp_seq.NEXTVAL;
        l_employees(i).first_name := 'Employee' || i;
        l_employees(i).last_name := 'Test';
        l_employees(i).email := 'emp' || i || '@test.com';
        l_employees(i).hire_date := SYSDATE;
        l_employees(i).job_id := 'IT_PROG';
        l_employees(i).salary := 5000 + MOD(i, 3000);
    END LOOP;
    
    -- 批量插入
    FORALL i IN l_employees.FIRST..l_employees.LAST
        INSERT INTO employees VALUES l_employees(i);
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Inserted ' || l_employees.COUNT || ' employees');
END;
/

-- 使用INSERT /*+ APPEND */ 提高性能
INSERT /*+ APPEND */ INTO employees_archive
SELECT * FROM employees WHERE hire_date < DATE '2020-01-01';

-- 并行插入
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(employees_archive, 4) */ INTO employees_archive
SELECT * FROM employees WHERE department_id IN (10, 20, 30);

-- 使用NOLOGGING减少日志
ALTER TABLE employees_temp NOLOGGING;
INSERT /*+ APPEND NOLOGGING */ INTO employees_temp
SELECT * FROM external_employee_data;
ALTER TABLE employees_temp LOGGING;

3. UPDATE语句详解

3.1 基础更新操作

UPDATE就像是重新装修房间，可以改变现有的数据：

-- 简单更新
UPDATE employees 
SET salary = 6000 
WHERE employee_id = 1001;

-- 更新多个字段
UPDATE employees 
SET salary = salary * 1.1,
    commission_pct = 0.05,
    modified_date = SYSDATE
WHERE department_id = 20;

-- 使用表达式更新
UPDATE employees 
SET salary = CASE 
    WHEN job_id = 'IT_PROG' THEN salary * 1.15
    WHEN job_id = 'SA_REP' THEN salary * 1.10
    WHEN job_id = 'ST_CLERK' THEN salary * 1.05
    ELSE salary * 1.03
END,
email = LOWER(first_name || '.' || last_name || '@company.com')
WHERE hire_date < ADD_MONTHS(SYSDATE, -12);

-- 使用函数更新
UPDATE products 
SET product_name = INITCAP(product_name),
    description = REGEXP_REPLACE(description, '\s+', ' '),
    modified_date = SYSTIMESTAMP
WHERE category_id = 5;

-- 条件更新
UPDATE orders 
SET status = 'SHIPPED',
    shipped_date = SYSDATE,
    tracking_number = 'TRK' || LPAD(order_id, 10, '0')
WHERE status = 'PROCESSING' 
  AND order_date < SYSDATE - 2;

3.2 关联更新

关联更新就像是根据其他房间的情况来调整当前房间：

-- 使用子查询更新
UPDATE employees e
SET salary = (
    SELECT AVG(salary) * 1.1
    FROM employees 
    WHERE department_id = e.department_id
)
WHERE job_id = 'IT_PROG';

-- 多列子查询更新
UPDATE employees e
SET (salary, commission_pct) = (
    SELECT j.max_salary * 0.8, 0.1
    FROM jobs j
    WHERE j.job_id = e.job_id
)
WHERE hire_date > ADD_MONTHS(SYSDATE, -6);

-- 使用EXISTS的关联更新
UPDATE products p
SET discontinued = 'Y'
WHERE NOT EXISTS (
    SELECT 1 
    FROM order_details od 
    WHERE od.product_id = p.product_id 
      AND od.order_date > ADD_MONTHS(SYSDATE, -12)
);

-- 复杂关联更新
UPDATE customers c
SET (credit_limit, customer_status) = (
    SELECT 
        CASE 
            WHEN total_orders > 100 THEN 50000
            WHEN total_orders > 50 THEN 25000
            WHEN total_orders > 10 THEN 10000
            ELSE 5000
        END,
        CASE 
            WHEN total_orders > 50 THEN 'VIP'
            WHEN total_orders > 10 THEN 'REGULAR'
            ELSE 'NEW'
        END
    FROM (
        SELECT 
            customer_id,
            COUNT(*) as total_orders,
            SUM(total_amount) as total_spent
        FROM orders
        WHERE order_date > ADD_MONTHS(SYSDATE, -12)
        GROUP BY customer_id
    ) order_stats
    WHERE order_stats.customer_id = c.customer_id
)
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

3.3 批量更新优化

-- 使用MERGE进行高效更新
MERGE INTO employees e
USING (
    SELECT employee_id, salary * 1.1 as new_salary
    FROM employees
    WHERE performance_rating = 'EXCELLENT'
) perf
ON (e.employee_id = perf.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        salary = perf.new_salary,
        last_raise_date = SYSDATE;

-- 分批更新大表
DECLARE
    v_batch_size NUMBER := 10000;
    v_total_updated NUMBER := 0;
    v_batch_updated NUMBER;
BEGIN
    LOOP
        UPDATE employees 
        SET status = 'INACTIVE'
        WHERE status = 'TEMP' 
          AND hire_date < ADD_MONTHS(SYSDATE, -24)
          AND ROWNUM <= v_batch_size;
        
        v_batch_updated := SQL%ROWCOUNT;
        v_total_updated := v_total_updated + v_batch_updated;
        
        COMMIT;
        
        EXIT WHEN v_batch_updated = 0;
        
        DBMS_OUTPUT.PUT_LINE('Updated ' || v_batch_updated || ' rows');
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Total updated: ' || v_total_updated || ' rows');
END;
/

-- 并行更新
ALTER SESSION ENABLE PARALLEL DML;
UPDATE /*+ PARALLEL(employees, 4) */ employees
SET salary = salary * 1.05
WHERE department_id IN (10, 20, 30, 40);
COMMIT;

4. DELETE语句详解

4.1 基础删除操作

DELETE就像是清理房间，移除不需要的物品：

-- 简单删除
DELETE FROM employees WHERE employee_id = 1001;

-- 条件删除
DELETE FROM employees 
WHERE status = 'TERMINATED' 
  AND termination_date < ADD_MONTHS(SYSDATE, -24);

-- 使用子查询删除
DELETE FROM order_details
WHERE order_id IN (
    SELECT order_id 
    FROM orders 
    WHERE order_date < ADD_MONTHS(SYSDATE, -36)
      AND status = 'CANCELLED'
);

-- 使用EXISTS删除
DELETE FROM products p
WHERE EXISTS (
    SELECT 1 
    FROM discontinued_products dp 
    WHERE dp.product_id = p.product_id
);

-- 复杂条件删除
DELETE FROM sales_data
WHERE sale_date < ADD_MONTHS(SYSDATE, -60)
  AND customer_id NOT IN (
      SELECT DISTINCT customer_id 
      FROM orders 
      WHERE order_date > ADD_MONTHS(SYSDATE, -12)
  )
  AND amount < 100;

4.2 关联删除

-- 删除孤儿记录
DELETE FROM order_details od
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.order_id = od.order_id
);

-- 基于统计信息删除
DELETE FROM customers c
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id 
    FROM orders 
    WHERE order_date > ADD_MONTHS(SYSDATE, -36)
)
AND created_date < ADD_MONTHS(SYSDATE, -48);

-- 删除重复数据（保留最新的）
DELETE FROM employees e1
WHERE e1.rowid > (
    SELECT MIN(e2.rowid)
    FROM employees e2
    WHERE e2.email = e1.email
);

-- 或者使用分析函数删除重复数据
DELETE FROM (
    SELECT rowid, 
           ROW_NUMBER() OVER (
               PARTITION BY email 
               ORDER BY hire_date DESC, employee_id DESC
           ) as rn
    FROM employees
)
WHERE rn > 1;

4.3 批量删除优化

-- 分批删除避免锁表
DECLARE
    v_batch_size NUMBER := 5000;
    v_total_deleted NUMBER := 0;
    v_batch_deleted NUMBER;
BEGIN
    LOOP
        DELETE FROM audit_log 
        WHERE log_date < ADD_MONTHS(SYSDATE, -12)
          AND ROWNUM <= v_batch_size;
        
        v_batch_deleted := SQL%ROWCOUNT;
        v_total_deleted := v_total_deleted + v_batch_deleted;
        
        COMMIT;
        
        EXIT WHEN v_batch_deleted = 0;
        
        -- 给其他会话一些处理时间
        DBMS_LOCK.SLEEP(0.1);
        
        DBMS_OUTPUT.PUT_LINE('Deleted ' || v_batch_deleted || ' rows');
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Total deleted: ' || v_total_deleted || ' rows');
END;
/

-- 使用TRUNCATE快速清空表（注意：不能回滚）
TRUNCATE TABLE temp_calculations;

-- 删除分区数据
ALTER TABLE sales_data DROP PARTITION sales_2020_q1;

-- 并行删除
ALTER SESSION ENABLE PARALLEL DML;
DELETE /*+ PARALLEL(old_transactions, 4) */ FROM old_transactions
WHERE transaction_date < ADD_MONTHS(SYSDATE, -84);
COMMIT;

5. MERGE语句详解

5.1 MERGE基础语法

MERGE就像是"智能搬家"，能够根据情况决定是搬新家具还是更新现有家具：

-- 基本MERGE语法
MERGE INTO employees e
USING employee_updates eu
ON (e.employee_id = eu.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        salary = eu.new_salary,
        department_id = eu.new_department_id,
        modified_date = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, email, hire_date, salary, department_id)
    VALUES (eu.employee_id, eu.first_name, eu.last_name, eu.email, 
            SYSDATE, eu.new_salary, eu.new_department_id);

-- 带条件的MERGE
MERGE INTO products p
USING (
    SELECT product_id, new_price, supplier_id
    FROM product_price_updates
    WHERE effective_date = TRUNC(SYSDATE)
) ppu
ON (p.product_id = ppu.product_id)
WHEN MATCHED THEN
    UPDATE SET 
        unit_price = ppu.new_price,
        modified_date = SYSDATE
    WHERE p.supplier_id = ppu.supplier_id  -- 额外条件
WHEN NOT MATCHED THEN
    INSERT (product_id, unit_price, supplier_id, created_date)
    VALUES (ppu.product_id, ppu.new_price, ppu.supplier_id, SYSDATE)
    WHERE ppu.new_price > 0;  -- 插入条件

5.2 复杂MERGE应用

-- 数据仓库ETL场景
MERGE INTO fact_sales fs
USING (
    SELECT 
        s.sale_id,
        s.product_id,
        s.customer_id,
        s.sale_date,
        s.quantity,
        s.unit_price,
        s.total_amount,
        p.category_id,
        c.region_id,
        EXTRACT(YEAR FROM s.sale_date) as sale_year,
        EXTRACT(MONTH FROM s.sale_date) as sale_month
    FROM staging_sales s
    JOIN products p ON s.product_id = p.product_id
    JOIN customers c ON s.customer_id = c.customer_id
    WHERE s.processed_flag = 'N'
) stage
ON (fs.sale_id = stage.sale_id)
WHEN MATCHED THEN
    UPDATE SET 
        quantity = stage.quantity,
        unit_price = stage.unit_price,
        total_amount = stage.total_amount,
        last_updated = SYSDATE
    WHERE fs.total_amount != stage.total_amount  -- 只更新变化的记录
WHEN NOT MATCHED THEN
    INSERT (
        sale_id, product_id, customer_id, sale_date,
        quantity, unit_price, total_amount,
        category_id, region_id, sale_year, sale_month,
        created_date
    ) VALUES (
        stage.sale_id, stage.product_id, stage.customer_id, stage.sale_date,
        stage.quantity, stage.unit_price, stage.total_amount,
        stage.category_id, stage.region_id, stage.sale_year, stage.sale_month,
        SYSDATE
    );

-- 库存管理MERGE
MERGE INTO inventory i
USING (
    SELECT 
        product_id,
        SUM(CASE WHEN transaction_type = 'IN' THEN quantity ELSE -quantity END) as net_change
    FROM inventory_transactions
    WHERE transaction_date = TRUNC(SYSDATE)
      AND processed_flag = 'N'
    GROUP BY product_id
) it
ON (i.product_id = it.product_id)
WHEN MATCHED THEN
    UPDATE SET 
        quantity_on_hand = quantity_on_hand + it.net_change,
        last_updated = SYSDATE
    WHERE it.net_change != 0
WHEN NOT MATCHED THEN
    INSERT (product_id, quantity_on_hand, last_updated)
    VALUES (it.product_id, it.net_change, SYSDATE)
    WHERE it.net_change > 0;

-- 客户360度视图更新
MERGE INTO customer_360 c360
USING (
    WITH customer_stats AS (
        SELECT 
            customer_id,
            COUNT(*) as total_orders,
            SUM(total_amount) as total_spent,
            AVG(total_amount) as avg_order_value,
            MAX(order_date) as last_order_date,
            MIN(order_date) as first_order_date
        FROM orders
        WHERE order_date >= ADD_MONTHS(SYSDATE, -12)
        GROUP BY customer_id
    ),
    customer_segments AS (
        SELECT 
            customer_id,
            CASE 
                WHEN total_spent > 10000 THEN 'VIP'
                WHEN total_spent > 5000 THEN 'PREMIUM'
                WHEN total_spent > 1000 THEN 'REGULAR'
                ELSE 'BASIC'
            END as segment,
            CASE 
                WHEN last_order_date > SYSDATE - 30 THEN 'ACTIVE'
                WHEN last_order_date > SYSDATE - 90 THEN 'INACTIVE'
                ELSE 'DORMANT'
            END as status
        FROM customer_stats
    )
    SELECT 
        cs.customer_id,
        cs.total_orders,
        cs.total_spent,
        cs.avg_order_value,
        cs.last_order_date,
        cs.first_order_date,
        seg.segment,
        seg.status
    FROM customer_stats cs
    JOIN customer_segments seg ON cs.customer_id = seg.customer_id
) stats
ON (c360.customer_id = stats.customer_id)
WHEN MATCHED THEN
    UPDATE SET 
        total_orders = stats.total_orders,
        total_spent = stats.total_spent,
        avg_order_value = stats.avg_order_value,
        last_order_date = stats.last_order_date,
        customer_segment = stats.segment,
        customer_status = stats.status,
        last_updated = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (
        customer_id, total_orders, total_spent, avg_order_value,
        last_order_date, first_order_date, customer_segment, customer_status,
        created_date, last_updated
    ) VALUES (
        stats.customer_id, stats.total_orders, stats.total_spent, stats.avg_order_value,
        stats.last_order_date, stats.first_order_date, stats.segment, stats.status,
        SYSDATE, SYSDATE
    );

6. 事务控制

6.1 事务基础

事务就像是一套组合拳，要么全部成功，要么全部失败：

-- 基本事务控制
BEGIN
    INSERT INTO orders (order_id, customer_id, order_date, total_amount)
    VALUES (1001, 123, SYSDATE, 1500);
    
    INSERT INTO order_details (order_id, product_id, quantity, unit_price)
    VALUES (1001, 456, 2, 750);
    
    UPDATE inventory 
    SET quantity_on_hand = quantity_on_hand - 2
    WHERE product_id = 456;
    
    COMMIT;  -- 提交所有更改
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- 出错时回滚
        RAISE;
END;
/

-- 使用保存点
DECLARE
    v_error_count NUMBER := 0;
BEGIN
    SAVEPOINT start_processing;
    
    -- 第一批操作
    INSERT INTO batch_log (batch_id, status) VALUES (1, 'STARTED');
    
    SAVEPOINT batch1_complete;
    
    -- 第二批操作
    BEGIN
        UPDATE products SET unit_price = unit_price * 1.1;
        INSERT INTO price_history (product_id, old_price, new_price, change_date)
        SELECT product_id, unit_price / 1.1, unit_price, SYSDATE FROM products;
        
        SAVEPOINT batch2_complete;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK TO batch1_complete;  -- 只回滚第二批操作
            v_error_count := v_error_count + 1;
    END;
    
    -- 第三批操作
    BEGIN
        DELETE FROM temp_calculations WHERE created_date < SYSDATE - 1;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK TO batch2_complete;
            v_error_count := v_error_count + 1;
    END;
    
    IF v_error_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Completed with ' || v_error_count || ' errors');
    END IF;
END;
/

6.2 复杂事务处理

-- 银行转账事务示例
CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_account IN NUMBER,
    p_to_account IN NUMBER,
    p_amount IN NUMBER
)
IS
    v_from_balance NUMBER;
    v_to_balance NUMBER;
    insufficient_funds EXCEPTION;
    account_not_found EXCEPTION;
BEGIN
    SAVEPOINT before_transfer;
    
    -- 锁定源账户并检查余额
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;
    
    IF v_from_balance < p_amount THEN
        RAISE insufficient_funds;
    END IF;
    
    -- 锁定目标账户
    SELECT balance INTO v_to_balance
    FROM accounts
    WHERE account_id = p_to_account
    FOR UPDATE;
    
    -- 执行转账
    UPDATE accounts 
    SET balance = balance - p_amount,
        last_transaction_date = SYSDATE
    WHERE account_id = p_from_account;
    
    UPDATE accounts 
    SET balance = balance + p_amount,
        last_transaction_date = SYSDATE
    WHERE account_id = p_to_account;
    
    -- 记录交易历史
    INSERT INTO transactions (transaction_id, account_id, transaction_type, amount, balance_after)
    VALUES (trans_seq.NEXTVAL, p_from_account, 'DEBIT', -p_amount, v_from_balance - p_amount);
    
    INSERT INTO transactions (transaction_id, account_id, transaction_type, amount, balance_after)
    VALUES (trans_seq.NEXTVAL, p_to_account, 'CREDIT', p_amount, v_to_balance + p_amount);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Transfer completed successfully');
    
EXCEPTION
    WHEN insufficient_funds THEN
        ROLLBACK TO before_transfer;
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
    WHEN NO_DATA_FOUND THEN
        ROLLBACK TO before_transfer;
        RAISE_APPLICATION_ERROR(-20002, 'Account not found');
    WHEN OTHERS THEN
        ROLLBACK TO before_transfer;
        RAISE_APPLICATION_ERROR(-20003, 'Transfer failed: ' || SQLERRM);
END;
/

-- 批量处理事务
CREATE OR REPLACE PROCEDURE process_daily_orders
IS
    CURSOR order_cursor IS
        SELECT order_id, customer_id, total_amount
        FROM pending_orders
        WHERE order_date = TRUNC(SYSDATE)
        FOR UPDATE SKIP LOCKED;  -- 跳过被锁定的行
    
    v_processed_count NUMBER := 0;
    v_error_count NUMBER := 0;
    v_batch_size NUMBER := 100;
    
BEGIN
    FOR order_rec IN order_cursor LOOP
        BEGIN
            SAVEPOINT process_order;
            
            -- 处理订单逻辑
            UPDATE inventory 
            SET quantity_on_hand = quantity_on_hand - (
                SELECT SUM(quantity) 
                FROM order_details 
                WHERE order_id = order_rec.order_id
            )
            WHERE product_id IN (
                SELECT product_id 
                FROM order_details 
                WHERE order_id = order_rec.order_id
            );
            
            -- 更新订单状态
            UPDATE pending_orders 
            SET status = 'PROCESSED',
                processed_date = SYSDATE
            WHERE order_id = order_rec.order_id;
            
            v_processed_count := v_processed_count + 1;
            
            -- 批量提交
            IF MOD(v_processed_count, v_batch_size) = 0 THEN
                COMMIT;
                DBMS_OUTPUT.PUT_LINE('Processed ' || v_processed_count || ' orders');
            END IF;
            
        EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK TO process_order;
                v_error_count := v_error_count + 1;
                
                -- 记录错误
                INSERT INTO error_log (error_date, error_message, order_id)
                VALUES (SYSDATE, SQLERRM, order_rec.order_id);
        END;
    END LOOP;
    
    COMMIT;  -- 提交剩余的更改
    
    DBMS_OUTPUT.PUT_LINE('Processing complete: ' || v_processed_count || 
                        ' processed, ' || v_error_count || ' errors');
END;
/

7. 性能优化技巧

7.1 批量操作优化

-- 高效的批量插入
DECLARE
    TYPE number_array IS TABLE OF NUMBER;
    TYPE varchar_array IS TABLE OF VARCHAR2(100);
    TYPE date_array IS TABLE OF DATE;
    
    l_emp_ids number_array;
    l_names varchar_array;
    l_emails varchar_array;
    l_hire_dates date_array;
    
    v_batch_size NUMBER := 10000;
BEGIN
    -- 准备数据
    SELECT employee_id, first_name || ' ' || last_name, email, hire_date
    BULK COLLECT INTO l_emp_ids, l_names, l_emails, l_hire_dates
    FROM external_employees
    WHERE import_flag = 'Y';
    
    -- 批量插入
    FORALL i IN 1..l_emp_ids.COUNT SAVE EXCEPTIONS
        INSERT INTO employees (employee_id, full_name, email, hire_date)
        VALUES (l_emp_ids(i), l_names(i), l_emails(i), l_hire_dates(i));
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Inserted ' || l_emp_ids.COUNT || ' employees');
    
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -24381 THEN  -- FORALL with SAVE EXCEPTIONS
            FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                DBMS_OUTPUT.PUT_LINE('Error ' || i || ': ' || 
                    SQL%BULK_EXCEPTIONS(i).ERROR_CODE || ' at index ' ||
                    SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
            END LOOP;
        END IF;
        RAISE;
END;
/

-- 优化的大表更新
CREATE OR REPLACE PROCEDURE optimize_large_table_update
IS
    CURSOR update_cursor IS
        SELECT rowid, employee_id, salary
        FROM employees
        WHERE last_review_date < ADD_MONTHS(SYSDATE, -12)
          AND status = 'ACTIVE';
    
    TYPE rowid_array IS TABLE OF ROWID;
    TYPE number_array IS TABLE OF NUMBER;
    
    l_rowids rowid_array;
    l_new_salaries number_array;
    
    v_batch_size NUMBER := 5000;
    v_total_updated NUMBER := 0;
BEGIN
    OPEN update_cursor;
    
    LOOP
        FETCH update_cursor BULK COLLECT INTO l_rowids, l_new_salaries
        LIMIT v_batch_size;
        
        EXIT WHEN l_rowids.COUNT = 0;
        
        -- 计算新薪资
        FOR i IN 1..l_rowids.COUNT LOOP
            l_new_salaries(i) := l_new_salaries(i) * 1.05;
        END LOOP;
        
        -- 批量更新
        FORALL i IN 1..l_rowids.COUNT
            UPDATE employees
            SET salary = l_new_salaries(i),
                last_review_date = SYSDATE
            WHERE rowid = l_rowids(i);
        
        v_total_updated := v_total_updated + l_rowids.COUNT;
        COMMIT;
        
        DBMS_OUTPUT.PUT_LINE('Updated ' || l_rowids.COUNT || ' rows');
    END LOOP;
    
    CLOSE update_cursor;
    
    DBMS_OUTPUT.PUT_LINE('Total updated: ' || v_total_updated || ' rows');
END;
/

7.2 索引和查询优化

-- 使用提示优化DML
-- 强制使用特定索引
UPDATE /*+ INDEX(employees, idx_emp_dept_id) */ employees
SET salary = salary * 1.1
WHERE department_id = 20;

-- 并行DML
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(sales_archive, 4) */ INTO sales_archive
SELECT * FROM sales_data WHERE sale_date < ADD_MONTHS(SYSDATE, -36);

-- 使用APPEND提示减少日志
INSERT /*+ APPEND */ INTO employees_backup
SELECT * FROM employees WHERE status = 'INACTIVE';

-- 优化MERGE性能
MERGE /*+ USE_HASH(target source) */ INTO customer_summary target
USING (
    SELECT /*+ PARALLEL(orders, 2) */
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_spent
    FROM orders
    WHERE order_date >= ADD_MONTHS(SYSDATE, -12)
    GROUP BY customer_id
) source
ON (target.customer_id = source.customer_id)
WHEN MATCHED THEN
    UPDATE SET 
        order_count = source.order_count,
        total_spent = source.total_spent,
        last_updated = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (customer_id, order_count, total_spent, last_updated)
    VALUES (source.customer_id, source.order_count, source.total_spent, SYSDATE);

-- 分区智能操作
-- 只操作相关分区
INSERT INTO sales_data PARTITION (sales_2024_q1)
SELECT * FROM staging_sales 
WHERE sale_date BETWEEN DATE '2024-01-01' AND DATE '2024-03-31';

-- 分区交换（快速数据移动）
ALTER TABLE sales_data EXCHANGE PARTITION sales_2023_q1 
WITH TABLE sales_2023_q1_archive;

结语
感谢您的阅读！期待您的一键三连！欢迎指正！