位置：文档库 > 数据库 > Oracle中DDL和DML指什么？

Oracle中DDL和DML指什么？

巴斯德上传于 2023-08-30 22:26

在Oracle数据库管理系统中，DDL（Data Definition Language，数据定义语言）和DML（Data Manipulation Language，数据操纵语言）是两类核心的SQL语句，它们分别承担着数据库结构管理和数据操作的不同职责。理解这两类语言的区别与联系，是掌握Oracle数据库开发与管理的基础。

一、DDL：数据库结构的定义者

DDL是用于定义和管理数据库对象结构的SQL语句集合，其主要作用包括创建、修改和删除数据库中的表、视图、索引、存储过程等对象。DDL语句的特点是执行后立即生效，且通常无法回滚（事务中的DDL会隐式提交）。

1.1 核心DDL语句

（1）CREATE：创建数据库对象

创建表是最基础的DDL操作，例如：

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2) CHECK (salary > 0)
);

此语句定义了一个包含主键、非空约束、默认值和检查约束的表结构。

（2）ALTER：修改对象结构

ALTER语句用于修改已有对象的结构，例如添加列：

ALTER TABLE employees
ADD COLUMN department_id NUMBER REFERENCES departments(dept_id);

或修改列数据类型：

ALTER TABLE employees
MODIFY COLUMN emp_name VARCHAR2(150);

（3）DROP：删除对象

删除表的操作需谨慎执行：

DROP TABLE employees CASCADE CONSTRAINTS;

其中CASCADE CONSTRAINTS选项会同时删除与该表相关的约束。

（4）TRUNCATE：清空表数据

与DELETE不同，TRUNCATE是DDL操作：

TRUNCATE TABLE temp_data;

它会快速删除表中所有数据，且不触发DELETE触发器。

1.2 DDL执行特性

1. 隐式提交：DDL语句执行后会自动提交当前事务

2. 不可回滚：执行后的DDL操作无法通过ROLLBACK撤销

3. 权限要求：执行DDL需要相应的对象创建权限（如CREATE TABLE）

4. 依赖管理：Oracle会自动维护对象间的依赖关系，如删除被外键引用的表会失败

二、DML：数据的操作者

DML是用于操作数据库表中数据的SQL语句，主要包括INSERT、UPDATE、DELETE和MERGE（Oracle特有）。与DDL不同，DML操作属于事务控制范围，可以回滚。

2.1 基础DML操作

（1）INSERT：插入数据

基本插入语法：

INSERT INTO employees (emp_id, emp_name, hire_date)
VALUES (101, '张三', TO_DATE('2023-01-15', 'YYYY-MM-DD'));

批量插入示例：

INSERT INTO sales_data
SELECT product_id, sale_date, quantity
FROM temp_sales
WHERE sale_date > SYSDATE-30;

（2）UPDATE：修改数据

条件更新示例：

UPDATE employees
SET salary = salary * 1.1,
    last_updated = SYSDATE
WHERE department_id = 10;

使用子查询更新：

UPDATE products p
SET p.price = (SELECT AVG(price) FROM products WHERE category = p.category)
WHERE p.discontinued = 'N';

（3）DELETE：删除数据

条件删除：

DELETE FROM orders
WHERE order_date

TRUNCATE与DELETE的区别：TRUNCATE是DDL，不记录日志且速度更快；DELETE是DML，可带条件且可回滚。

2.2 Oracle特有DML

（1）MERGE：合并操作（UPSERT）

该语句可实现"存在则更新，不存在则插入"：

MERGE INTO target_table t
USING source_table s
ON (t.key_column = s.key_column)
WHEN MATCHED THEN
    UPDATE SET t.value = s.new_value
WHEN NOT MATCHED THEN
    INSERT (key_column, value) VALUES (s.key_column, s.new_value);

（2）MULTI-TABLE INSERT：多表插入

Oracle支持将单条查询结果插入到多个表中：

INSERT ALL
    INTO sales_2023 VALUES (col1, col2)
    INTO sales_archive VALUES (col1, TO_CHAR(SYSDATE, 'YYYY'))
SELECT * FROM temp_sales;

2.3 DML事务控制

1. 显式事务控制：

BEGIN
    SAVEPOINT start_update;
    UPDATE accounts SET balance = balance - 100 WHERE acct_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE acct_id = 2;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO start_update;
        RAISE;
END;

2. 批量绑定技术：提高大批量DML性能

DECLARE
    TYPE emp_id_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    v_ids emp_id_array;
BEGIN
    -- 填充数组
    FOR i IN 1..1000 LOOP
        v_ids(i) := i;
    END LOOP;
    
    -- 批量更新
    FORALL i IN 1..v_ids.COUNT
        UPDATE employees SET bonus = 500 WHERE emp_id = v_ids(i);
END;

三、DDL与DML的协作实践

在实际数据库应用中，DDL和DML通常需要配合使用。例如在数据迁移场景中：

3.1 表结构调整与数据迁移

-- 1. 创建新表结构（DDL）
CREATE TABLE employees_new (
    emp_id NUMBER PRIMARY KEY,
    full_name VARCHAR2(200),
    effective_date DATE DEFAULT SYSDATE,
    salary NUMBER(12,2)
);

-- 2. 数据转换与迁移（DML）
INSERT INTO employees_new
SELECT 
    e.emp_id,
    e.first_name || ' ' || e.last_name AS full_name,
    TRUNC(SYSDATE) AS effective_date,
    e.base_salary + NVL(e.bonus, 0) AS salary
FROM employees e;

-- 3. 验证数据后重命名表（DDL）
RENAME employees TO employees_old;
RENAME employees_new TO employees;

-- 4. 重建索引等对象（DDL）
CREATE INDEX idx_emp_name ON employees(full_name);

3.2 动态SQL应用

在PL/SQL中结合使用DDL和DML：

DECLARE
    v_sql VARCHAR2(2000);
    v_table_name VARCHAR2(30) := 'TEMP_DATA_' || TO_CHAR(SYSDATE, 'YYYYMMDD');
BEGIN
    -- 动态创建表（DDL）
    v_sql := 'CREATE TABLE ' || v_table_name || ' AS
              SELECT * FROM source_data WHERE create_date > SYSDATE-7';
    EXECUTE IMMEDIATE v_sql;
    
    -- 动态处理数据（DML）
    v_sql := 'UPDATE ' || v_table_name || ' SET status = ''PROCESSED'' 
              WHERE rowid IN (SELECT rowid FROM ' || v_table_name || ' 
              WHERE MOD(id, 10) = 0)';
    EXECUTE IMMEDIATE v_sql;
    
    -- 动态删除表（DDL）
    v_sql := 'DROP TABLE ' || v_table_name;
    -- EXECUTE IMMEDIATE v_sql; -- 实际使用时需谨慎
END;

四、性能优化策略

4.1 DDL优化

1. 在线DDL操作：Oracle 11g后支持部分DDL在线执行

ALTER TABLE large_table ADD COLUMN new_col NUMBER 
ONLINE LOGGING;

2. 分区表维护：使用分区交换提高DDL效率

ALTER TABLE sales EXCHANGE PARTITION sales_q1 
WITH TABLE sales_q1_temp INCLUDING INDEXES;

4.2 DML优化

1. 批量提交：控制事务大小平衡性能与恢复

BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO log_table VALUES (...);
        IF MOD(i, 1000) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    COMMIT;
END;

2. 直接路径加载：使用/*+ APPEND */提示

INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;

五、安全与权限管理

1. DDL权限控制：

GRANT CREATE TABLE, ALTER ANY TABLE TO schema_owner;
REVOKE DROP ANY TABLE FROM junior_dba;

2. DML权限控制：

GRANT SELECT, INSERT ON employees TO app_user;
DENY UPDATE ON employees(salary) TO app_user;

3. 细粒度访问控制（VPD）：

CREATE POLICY emp_policy ON employees
USING (
    CASE WHEN SYS_CONTEXT('USERENV', 'SESSION_USER') = 'HR_ADMIN' 
         THEN '1=1' 
         ELSE 'dept_id = SYS_CONTEXT(''APP_CTX'', ''DEPT_ID'')' 
    END
);

六、常见问题与解决方案

1. DDL锁问题：

现象：执行ALTER TABLE时被其他会话阻塞

解决方案：

-- 查询阻塞会话
SELECT * FROM v$locked_object l, dba_objects o 
WHERE l.object_id = o.object_id;

-- 终止阻塞会话（谨慎使用）
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

2. DML性能下降：

现象：批量UPDATE变慢

解决方案：

-- 检查执行计划
EXPLAIN PLAN FOR
UPDATE large_table SET status = 'NEW' WHERE create_date > SYSDATE-30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 添加适当索引
CREATE INDEX idx_create_date ON large_table(create_date);

3. 对象依赖问题：

现象：删除表时提示"存在依赖对象"

解决方案：

-- 查询依赖关系
SELECT * FROM all_dependencies 
WHERE referenced_name = 'DEPENDENT_TABLE';

-- 使用级联删除
DROP TABLE master_table CASCADE CONSTRAINTS;

七、最佳实践建议

1. DDL操作前备份：

-- 使用数据泵导出
EXPDP schema_name DIRECTORY=dpump_dir DUMPFILE=pre_ddl.dmp 
TABLES=employees,departments

2. DML批量处理：

-- 使用FORALL提高性能
DECLARE
    TYPE id_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    v_ids id_array;
BEGIN
    -- 填充数组...
    FORALL i IN 1..v_ids.COUNT
        UPDATE accounts SET balance = balance + 100 WHERE acct_id = v_ids(i);
END;

3. 版本控制：对DDL脚本进行版本管理

-- 示例版本控制注释
/*
 * 版本: 1.2
 * 作者: dba_team
 * 日期: 2023-11-15
 * 描述: 添加audit_trail列并设置默认值
 */
ALTER TABLE transactions ADD COLUMN audit_trail VARCHAR2(4000) 
DEFAULT 'CREATED BY SYSTEM' NOT NULL;

4. 测试环境验证：所有DDL/DML变更应先在测试环境验证

-- 测试环境验证步骤示例
1. 创建测试表结构
2. 插入测试数据
3. 执行变更脚本
4. 验证数据完整性
5. 执行功能测试

结语

DDL和DML作为Oracle数据库的两大支柱语言，分别承担着结构定义和数据操作的核心职能。理解它们的特性、差异和协作方式，对于数据库设计、开发和运维至关重要。在实际应用中，应遵循"先设计后实现"、"小步变更"、"充分测试"等原则，确保数据库变更的安全性和可靠性。随着Oracle技术的不断发展，新的DDL和DML特性（如12c的多租户DDL、19c的JSON DML等）持续涌现，数据库专业人员需要保持持续学习，以充分利用这些先进功能提升工作效率。

关键词：Oracle数据库、DDL、DML、数据定义语言、数据操纵语言、SQL语句、数据库对象、事务控制、性能优化、权限管理

简介：本文详细阐述了Oracle数据库中DDL（数据定义语言）和DML（数据操纵语言）的核心概念、语法结构、执行特性及协作实践。通过2000余字的系统介绍，结合大量代码示例，全面解析了两类SQL语句在数据库设计、数据操作、事务管理、性能优化等方面的应用技巧和最佳实践，帮助读者深入理解并掌握Oracle数据库开发与管理中的关键技术。

《Oracle中DDL和DML指什么？.doc》

将本文的Word文档下载到电脑，方便收藏和打印

推荐度：

点击下载文档

立即下载

数据库相关