【Oracle】DML语言

article/2025/7/15 19:48:49

在这里插入图片描述

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

在这里插入图片描述

文章目录

  • 1. DML概述
    • 1.1 什么是DML?
    • 1.2 DML的核心功能
  • 2. INSERT语句详解
    • 2.1 基础插入操作
    • 2.2 子查询插入
    • 2.3 多表插入
    • 2.4 批量插入优化
  • 3. UPDATE语句详解
    • 3.1 基础更新操作
    • 3.2 关联更新
    • 3.3 批量更新优化
  • 4. DELETE语句详解
    • 4.1 基础删除操作
    • 4.2 关联删除
    • 4.3 批量删除优化
  • 5. MERGE语句详解
    • 5.1 MERGE基础语法
    • 5.2 复杂MERGE应用
  • 6. 事务控制
    • 6.1 事务基础
    • 6.2 复杂事务处理
  • 7. 性能优化技巧
    • 7.1 批量操作优化
    • 7.2 索引和查询优化

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

1. DML概述

1.1 什么是DML?

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

DML数据操作语言
INSERT插入
UPDATE更新
DELETE删除
MERGE合并
SELECT查询
单行插入
批量插入
条件更新
批量更新
条件删除
批量删除
插入或更新
数据同步
简单查询
复杂查询

1.2 DML的核心功能

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

Oracle DML功能体系
数据插入
数据更新
数据删除
数据合并
事务控制
性能优化
VALUES插入
子查询插入
多表插入
批量插入
简单更新
关联更新
条件更新
批量更新
条件删除
关联删除
批量删除
级联删除
MERGE语句
UPSERT操作
数据同步
COMMIT提交
ROLLBACK回滚
SAVEPOINT保存点
批量操作
并行处理
索引优化

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 ALLINTO 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 ALLWHEN amount > 10000 THENINTO high_value_sales (sale_id, amount, customer_id) VALUES (sale_id, amount, customer_id)WHEN amount BETWEEN 1000 AND 10000 THENINTO medium_value_sales (sale_id, amount, customer_id) VALUES (sale_id, amount, customer_id)ELSEINTO 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 ALLINTO 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 ALLWHEN department_id = 10 THENINTO 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 THENINTO dept_20_employees (employee_id, name, salary) VALUES (employee_id, first_name || ' ' || last_name, salary)WHEN salary > 5000 THENINTO 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)
DECLARETYPE emp_array IS TABLE OF employees%ROWTYPE;l_employees emp_array;
BEGIN-- 准备数据l_employees := emp_array();FOR i IN 1..10000 LOOPl_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.LASTINSERT 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.15WHEN job_id = 'SA_REP' THEN salary * 1.10WHEN job_id = 'ST_CLERK' THEN salary * 1.05ELSE 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.1FROM 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.1FROM jobs jWHERE 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 50000WHEN total_orders > 50 THEN 25000WHEN total_orders > 10 THEN 10000ELSE 5000END,CASE WHEN total_orders > 50 THEN 'VIP'WHEN total_orders > 10 THEN 'REGULAR'ELSE 'NEW'ENDFROM (SELECT customer_id,COUNT(*) as total_orders,SUM(total_amount) as total_spentFROM ordersWHERE order_date > ADD_MONTHS(SYSDATE, -12)GROUP BY customer_id) order_statsWHERE 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_salaryFROM employeesWHERE performance_rating = 'EXCELLENT'
) perf
ON (e.employee_id = perf.employee_id)
WHEN MATCHED THENUPDATE SET salary = perf.new_salary,last_raise_date = SYSDATE;-- 分批更新大表
DECLAREv_batch_size NUMBER := 10000;v_total_updated NUMBER := 0;v_batch_updated NUMBER;
BEGINLOOPUPDATE 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 e2WHERE e2.email = e1.email
);-- 或者使用分析函数删除重复数据
DELETE FROM (SELECT rowid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY hire_date DESC, employee_id DESC) as rnFROM employees
)
WHERE rn > 1;

4.3 批量删除优化

-- 分批删除避免锁表
DECLAREv_batch_size NUMBER := 5000;v_total_deleted NUMBER := 0;v_batch_deleted NUMBER;
BEGINLOOPDELETE 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 THENUPDATE SET salary = eu.new_salary,department_id = eu.new_department_id,modified_date = SYSDATE
WHEN NOT MATCHED THENINSERT (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_idFROM product_price_updatesWHERE effective_date = TRUNC(SYSDATE)
) ppu
ON (p.product_id = ppu.product_id)
WHEN MATCHED THENUPDATE SET unit_price = ppu.new_price,modified_date = SYSDATEWHERE p.supplier_id = ppu.supplier_id  -- 额外条件
WHEN NOT MATCHED THENINSERT (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_monthFROM staging_sales sJOIN products p ON s.product_id = p.product_idJOIN customers c ON s.customer_id = c.customer_idWHERE s.processed_flag = 'N'
) stage
ON (fs.sale_id = stage.sale_id)
WHEN MATCHED THENUPDATE SET quantity = stage.quantity,unit_price = stage.unit_price,total_amount = stage.total_amount,last_updated = SYSDATEWHERE fs.total_amount != stage.total_amount  -- 只更新变化的记录
WHEN NOT MATCHED THENINSERT (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_changeFROM inventory_transactionsWHERE transaction_date = TRUNC(SYSDATE)AND processed_flag = 'N'GROUP BY product_id
) it
ON (i.product_id = it.product_id)
WHEN MATCHED THENUPDATE SET quantity_on_hand = quantity_on_hand + it.net_change,last_updated = SYSDATEWHERE it.net_change != 0
WHEN NOT MATCHED THENINSERT (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_dateFROM ordersWHERE 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 statusFROM 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.statusFROM customer_stats csJOIN customer_segments seg ON cs.customer_id = seg.customer_id
) stats
ON (c360.customer_id = stats.customer_id)
WHEN MATCHED THENUPDATE 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 THENINSERT (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 事务基础

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

Oracle事务控制
COMMIT提交
ROLLBACK回滚
SAVEPOINT保存点
事务隔离级别
永久保存更改
释放锁资源
撤销所有更改
回到事务开始
设置回滚点
部分回滚
READ COMMITTED
SERIALIZABLE
-- 基本事务控制
BEGININSERT 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 - 2WHERE product_id = 456;COMMIT;  -- 提交所有更改
EXCEPTIONWHEN OTHERS THENROLLBACK;  -- 出错时回滚RAISE;
END;
/-- 使用保存点
DECLAREv_error_count NUMBER := 0;
BEGINSAVEPOINT start_processing;-- 第一批操作INSERT INTO batch_log (batch_id, status) VALUES (1, 'STARTED');SAVEPOINT batch1_complete;-- 第二批操作BEGINUPDATE 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;EXCEPTIONWHEN OTHERS THENROLLBACK TO batch1_complete;  -- 只回滚第二批操作v_error_count := v_error_count + 1;END;-- 第三批操作BEGINDELETE FROM temp_calculations WHERE created_date < SYSDATE - 1;COMMIT;EXCEPTIONWHEN OTHERS THENROLLBACK TO batch2_complete;v_error_count := v_error_count + 1;END;IF v_error_count > 0 THENDBMS_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
)
ISv_from_balance NUMBER;v_to_balance NUMBER;insufficient_funds EXCEPTION;account_not_found EXCEPTION;
BEGINSAVEPOINT before_transfer;-- 锁定源账户并检查余额SELECT balance INTO v_from_balanceFROM accountsWHERE account_id = p_from_accountFOR UPDATE;IF v_from_balance < p_amount THENRAISE insufficient_funds;END IF;-- 锁定目标账户SELECT balance INTO v_to_balanceFROM accountsWHERE account_id = p_to_accountFOR UPDATE;-- 执行转账UPDATE accounts SET balance = balance - p_amount,last_transaction_date = SYSDATEWHERE account_id = p_from_account;UPDATE accounts SET balance = balance + p_amount,last_transaction_date = SYSDATEWHERE 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');EXCEPTIONWHEN insufficient_funds THENROLLBACK TO before_transfer;RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');WHEN NO_DATA_FOUND THENROLLBACK TO before_transfer;RAISE_APPLICATION_ERROR(-20002, 'Account not found');WHEN OTHERS THENROLLBACK TO before_transfer;RAISE_APPLICATION_ERROR(-20003, 'Transfer failed: ' || SQLERRM);
END;
/-- 批量处理事务
CREATE OR REPLACE PROCEDURE process_daily_orders
ISCURSOR order_cursor ISSELECT order_id, customer_id, total_amountFROM pending_ordersWHERE order_date = TRUNC(SYSDATE)FOR UPDATE SKIP LOCKED;  -- 跳过被锁定的行v_processed_count NUMBER := 0;v_error_count NUMBER := 0;v_batch_size NUMBER := 100;BEGINFOR order_rec IN order_cursor LOOPBEGINSAVEPOINT 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 = SYSDATEWHERE order_id = order_rec.order_id;v_processed_count := v_processed_count + 1;-- 批量提交IF MOD(v_processed_count, v_batch_size) = 0 THENCOMMIT;DBMS_OUTPUT.PUT_LINE('Processed ' || v_processed_count || ' orders');END IF;EXCEPTIONWHEN OTHERS THENROLLBACK 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 批量操作优化

DML性能优化策略
批量操作
索引优化
并行处理
分区策略
FORALL批量DML
BULK COLLECT
MERGE语句
合适的索引
避免索引失效
函数索引
并行DML
并行查询
分区并行
分区消除
分区智能连接
分区索引
-- 高效的批量插入
DECLARETYPE 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_dateBULK COLLECT INTO l_emp_ids, l_names, l_emails, l_hire_datesFROM external_employeesWHERE import_flag = 'Y';-- 批量插入FORALL i IN 1..l_emp_ids.COUNT SAVE EXCEPTIONSINSERT 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');EXCEPTIONWHEN OTHERS THENIF SQLCODE = -24381 THEN  -- FORALL with SAVE EXCEPTIONSFOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOPDBMS_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
ISCURSOR update_cursor ISSELECT rowid, employee_id, salaryFROM employeesWHERE 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;
BEGINOPEN update_cursor;LOOPFETCH update_cursor BULK COLLECT INTO l_rowids, l_new_salariesLIMIT v_batch_size;EXIT WHEN l_rowids.COUNT = 0;-- 计算新薪资FOR i IN 1..l_rowids.COUNT LOOPl_new_salaries(i) := l_new_salaries(i) * 1.05;END LOOP;-- 批量更新FORALL i IN 1..l_rowids.COUNTUPDATE employeesSET salary = l_new_salaries(i),last_review_date = SYSDATEWHERE 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_spentFROM ordersWHERE order_date >= ADD_MONTHS(SYSDATE, -12)GROUP BY customer_id
) source
ON (target.customer_id = source.customer_id)
WHEN MATCHED THENUPDATE SET order_count = source.order_count,total_spent = source.total_spent,last_updated = SYSDATE
WHEN NOT MATCHED THENINSERT (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;

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

在这里插入图片描述


http://www.hkcw.cn/article/LMQuVjpLbr.shtml

相关文章

安装启动Mosquitto以及问题error: cjson/cJSON.h: No such file or directory解决

安装Mosquitto 在官方下载地址&#xff1a;https://mosquitto.org/files/source/ 选择版本下载 安装环境是linux centos7&#xff0c;上传 mosquitto-2.0.18.tar.gz 文件到 /mqtt 文件夹下 tar -xvf mosquitto-2.0.18.tar.gz #解压 cd mosquitto-2.0.18/ #切换到解压目录下…

附件上传唯一性校验

1. Overridepublic String uploadFile(MultipartFile file, String id, String funNo, String ctType) {//TODO 附件重复判断// 计算文件哈希值// 将MultipartFile转换为临时File对象String fileHash "";try {File tempFile convertMultipartFileToFile(file);// …

正点原子AU15开发板!板载40G QSFP、PCIe3.0x8和FMC LPC等接口,性能强悍!

正点原子AU15开发板&#xff01;板载40G QSFP、PCIe3.0x8和FMC LPC等接口&#xff0c;性能强悍&#xff01; 正点原子AU15开发板搭载Xilinx Artix UltraScale 系列FPGA&#xff0c;核心板主控芯片的型号是XCAU15P-FFVB676-2I。开发板由核心板&#xff0b;底板组成&#xff0c;外…

Attention-> flashAttention材料参考

1、 一文看懂 Attention&#xff08;本质原理3大优点5大类型&#xff09;_attention结构-CSDN博客2​​​​​​​2https://blog.csdn.net/haima1998/article/details/107845549 2、 一文看懂 NLP 里的模型框架 Encoder-Decoder 和 Seq2Seq (easyai.tech) 3、 详解深度学习…

MySQL高可用集群

https://dev.mysql.com/doc/mysql-shell/8.4/en/mysql-innodb-cluster.html 1 什么是MySQL高可用集群 MySQL高可用集群&#xff1a;MySQL InnoDB ClusterInnoDB Cluster是MySQL官方实现高可用读写分离的架构方案&#xff0c;包含以下组件 MySQL Group Replication&#xff1a;简…

山洪灾害声光电监测预警解决方案

一、方案背景 我国是一个多山的国家&#xff0c;山丘区面积约占国土面积的三分之二。每年汛期&#xff0c;受暴雨等因素影响&#xff0c;极易引发山洪和泥石流。山洪、泥石流地质灾害具有突发性、流速快、流量大、物质容量大和破坏力强等特点&#xff0c;一旦发生&#xff0c;将…

2025年最新工程项目管理系统应该具备哪些模块?

随着数字化转型浪潮席卷工程行业&#xff0c;工程项目管理系统的作用愈发凸显。2025年&#xff0c;工程项目管理系统的核心目标不仅是提升项目效率&#xff0c;更在于通过智能化、集成化技术实现全生命周期的精细化管理。基于行业趋势和企业实际需求&#xff0c;结合金众诚工程…

unity入门:同一文本不同颜色显示

unity入门&#xff1a;同一文本不同颜色显示 同一文本不同颜色显示#RRGGBBAA&#xff08;带透明度&#xff09;用法 同一文本不同颜色显示 在Unity中&#xff0c;如果想让文本中的某一部分显示不同的颜色&#xff0c;可以使用富文本(Rich Text)标记&#xff0c;在字符串中插入…

128、STM32H723ZGT6实现串口IAP

Bootloader程序通过串口接收*.bin文件数据&#xff0c;写入到内部flash区域&#xff0c;然后跳转APP应用程序 flash读写数据参考我的博客&#xff1a;127、stm32h743XI内部flash 注意&#xff1a;H723系列flash必须32字节写入&#xff0c;并且擦除时别重启|断电&#xff0c;不然…

【Netty系列】Reactor 模式 2

目录 流程图说明 关键流程 以下是 Reactor 模式流程图&#xff0c;结合 Netty 的主从多线程模型&#xff0c;帮助你直观理解事件驱动和线程分工&#xff1a; 流程图说明 Clients&#xff08;客户端&#xff09; 多个客户端&#xff08;Client 1~N&#xff09;向服务端发起连…

接口自动化测试用例的编写方法

&#x1f345; 点击文末小卡片&#xff0c;免费获取软件测试全套资料&#xff0c;资料在手&#xff0c;涨薪更快 phpunit 接口自动化测试系列 Post接口自动化测试用例 Post方式的接口是上传接口&#xff0c;需要对接口头部进行封装&#xff0c;所以没有办法在浏览器下直接调…

2025030给荣品PRO-RK3566开发板单独升级Android13的boot.img

./build.sh init ./build.sh -K ./build.sh kernel 【导入配置文件】 Z:\Android13.0\rockdev\Image-rk3566_t\config.cfg 【更新的内核】 Z:\Android13.0\rockdev\Image-rk3566_t\boot.img 【导入分区表&#xff0c;使用原始的config.cfg会出错的^_】 Z:\Android13.0\rockdev\…

伊拉克军方打死6名“伊斯兰国”武装分子

△伊拉克联合行动指挥部发布视频截图当地时间5月30日,伊拉克联合行动指挥部下属安全媒体中心发表声明称,29日晚至30日早间,伊军方出动战机对位于该国北部萨拉赫丁省沙伊谷地的极端组织“伊斯兰国”武装分子藏匿点发动空袭,打死了6名武装分子,并摧毁其藏匿点。(总台记者 米…

Python打卡训练营Day40

DAY 40 训练和测试的规范写法 知识点回顾&#xff1a; 彩色和灰度图片测试和训练的规范写法&#xff1a;封装在函数中展平操作&#xff1a;除第一个维度batchsize外全部展平dropout操作&#xff1a;训练阶段随机丢弃神经元&#xff0c;测试阶段eval模式关闭dropout 作业&#x…

Haproxy搭建web群集

目录 一&#xff1a;Haproxy 1.Haproxy常见的调度算法 二&#xff1a;环境案例 1.配置web主机 2.配置haproxy主机 3.Haproxy日志 一&#xff1a;Haproxy Haproxy 是目前比较流行的一种群集调度工具&#xff0c;同类群集调度工具有很多,如 LVS 和 Nginx。相比较而言&#…

ansible自动化playbook简单实践

方法一&#xff1a;部分使用ansible 基于现有的nginx配置文件&#xff0c;定制部署nginx软件&#xff0c;将我们的知识进行整合 定制要求&#xff1a; 启动用户&#xff1a;nginx-test&#xff0c;uid是82&#xff0c;系统用户&#xff0c;不能登录 启动端口82 web项目根目录/…

一句话开发Chrome摸鱼插件

本文所使用的 CodeBuddy 免费下载链接&#xff1a;腾讯云代码助手 CodeBuddy - AI 时代的智能编程伙伴&#xfeff;。 CodeBuddy 一、CodeBuddy新功能特色 Craft智能体&#xff1a;自然语言驱动的全栈开发引擎Craft开发智能体的核心突破在于实现需求理解-任务拆解-代码生成的…

2024PLM系统实施案例:天水天轲零部件

一、行业背景与中小企业的现实挑战 汽车零部件行业竞争激烈&#xff0c;中小企业普遍面临研发周期长、数据管理混乱、供应链协同效率低等问题。天水天轲零部件作为一家年产值约700万元的小型制造企业&#xff0c;其痛点具有行业典型性&#xff1a; 研发数据分散&#xff1a…

Linux(8)——进程(控制篇——上)

目录 ​编辑 一、进程创建 1.fork函数的回顾 2.fork的返回值 3.写时拷贝 4.fork的常规用法 5.fork调用失败的原因 二、进程终止 1.进程退出的场景 2.进程常见的退出方法 3.进程退出码 4.进程正常退出 1&#xff09;_exit函数 2&#xff09;exit函数 3&#xff…

万亿小吃市场背后:冷链配送如何破解连锁门店“到店难题”?

在餐饮行业连锁化率突破23%、小吃快餐品类占据连锁门店半壁江山的今天&#xff0c;冷链配送已成为支撑品牌扩张的隐形基础设施。从田间到餐桌&#xff0c;从中央厨房到终端门店&#xff0c;冷链物流的每一次温度波动都直接关联着消费者的味蕾体验与品牌口碑。本文将解析冷链配送…