当前位置：首页 > news >正文

DM8 数据库实战：SQL 语法与数据库对象全攻略（表 / 索引 / 视图 / 存储过程 / 触发器）

news 2026/5/11 21:29:08

作为国产关系型数据库的标杆，达梦 DM8 凭借高度兼容 Oracle 的特性、稳定的性能和完善的生态，已成为政务、金融、企业级应用的首选数据库之一。对于开发者而言，掌握 DM8 的 SQL 语法与数据库对象管理，是实现高效开发与运维的核心。本文将从实操角度出发，详细拆解表、索引、视图的创建与管理，深入讲解基础查询、存储过程与触发器的编写技巧，附完整案例与避坑指南，助力快速上手 DM8 开发。

一、数据库对象核心：表的创建与管理（基础中的基础）

表是数据库存储数据的核心载体，DM8 支持标准关系型表结构，同时提供丰富的字段类型和约束机制，确保数据完整性。

1. 表的创建：字段类型与约束设计

DM8 兼容主流数据库字段类型，常用类型包括VARCHAR2、NUMBER、DATE、TIMESTAMP、CLOB等，约束支持主键（PRIMARY KEY）、外键（FOREIGN KEY）、非空（NOT NULL）、唯一（UNIQUE）、检查（CHECK）等。

创建示例：员工信息表（EMP）与部门表（DEPT）

-- 创建部门表（主表）

CREATE TABLE DEPT (

DEPT_ID NUMBER(2) PRIMARY KEY, -- 部门编号（主键，2位数字）

DEPT_NAME VARCHAR2(50) NOT NULL UNIQUE, -- 部门名称（非空+唯一）

LOC VARCHAR2(100) -- 部门地址

);

-- 创建员工表（从表，关联部门表）

CREATE TABLE EMP (

EMP_ID NUMBER(4) PRIMARY KEY, -- 员工编号（主键，4位数字）

EMP_NAME VARCHAR2(50) NOT NULL, -- 员工姓名（非空）

DEPT_ID NUMBER(2), -- 关联部门编号（外键）

SAL NUMBER(10,2) CHECK (SAL > 0), -- 工资（大于0）

HIREDATE DATE DEFAULT SYSDATE, -- 入职日期（默认当前日期）

-- 外键约束：关联部门表的DEPT_ID

CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DEPT_ID) REFERENCES DEPT (DEPT_ID)

ON DELETE SET NULL -- 部门删除时，员工表的DEPT_ID设为NULL

);

关键注意点：

DM8 的VARCHAR2类型长度默认按字符计算（兼容 Oracle），无需额外配置；

外键约束需确保主表字段存在，且数据类型一致；

CHECK约束支持复杂条件（如SAL BETWEEN 3000 AND 50000），区别于 MySQL 的部分版本不支持；

可通过COMMENT为表和字段添加注释，提升可读性：

COMMENT ON TABLE EMP IS '员工信息表';

COMMENT ON COLUMN EMP.EMP_NAME IS '员工姓名';

2. 表的管理：修改、删除与 truncate

实际开发中常需调整表结构，DM8 提供ALTER TABLE语句实现灵活修改：

-- 1. 新增字段（员工邮箱）

ALTER TABLE EMP ADD (EMAIL VARCHAR2(100) UNIQUE);

-- 2. 修改字段类型（调整工资字段长度）

ALTER TABLE EMP MODIFY (SAL NUMBER(12,2));

-- 3. 删除字段（删除部门地址）

ALTER TABLE DEPT DROP COLUMN LOC;

-- 4. 重命名表

ALTER TABLE EMP RENAME TO EMPLOYEE;

-- 5. 清空表数据（保留表结构，不可回滚）

TRUNCATE TABLE EMPLOYEE;

-- 6. 删除表（谨慎使用！）

DROP TABLE EMPLOYEE CASCADE; -- CASCADE：级联删除关联的约束和索引

二、性能优化关键：索引的创建与管理

索引是提升查询效率的核心手段，DM8 支持 B 树索引、位图索引、函数索引等多种类型，合理设计索引可降低查询耗时。

1. 索引的创建：按需选择索引类型

1. 普通 B 树索引（最常用）：适用于等值查询、范围查询，如员工姓名、部门编号查询：

-- 为员工表的EMP_NAME创建普通索引

CREATE INDEX IDX_EMP_NAME ON EMP (EMP_NAME);

-- 为员工表的DEPT_ID创建索引（关联查询优化）

CREATE INDEX IDX_EMP_DEPTID ON EMP (DEPT_ID);

2. 唯一索引：确保字段值唯一，同时提升查询效率（主键默认会自动创建唯一索引）：

-- 为邮箱字段创建唯一索引（避免重复邮箱）

CREATE UNIQUE INDEX IDX_EMP_EMAIL ON EMP (EMAIL);

3. 函数索引：适用于查询条件包含函数的场景（如模糊查询、日期格式化）：

-- 为入职日期的年份创建函数索引（优化按年份查询）

CREATE INDEX IDX_EMP_HIREDATE_YEAR ON EMP (EXTRACT(YEAR FROM HIREDATE));

4. 复合索引：多字段组合索引，适用于多条件查询（注意字段顺序：等值条件在前，范围条件在后）：

-- 优化“部门编号+工资范围”查询

CREATE INDEX IDX_EMP_DEPT_SAL ON EMP (DEPT_ID, SAL);

2. 索引的管理：查询、重建与删除

-- 1. 查询所有索引（查看用户下的索引信息）

SELECT INDEX_NAME, TABLE_NAME, INDEX_TYPE

FROM USER_INDEXES

WHERE TABLE_NAME IN ('EMP', 'DEPT');

-- 2. 重建索引（索引碎片过多时优化）

ALTER INDEX IDX_EMP_NAME REBUILD;

-- 3. 删除无用索引（避免占用空间、影响写入性能）

DROP INDEX IDX_EMP_EMAIL;

索引设计避坑：

避免过度索引：过多索引会导致插入 / 更新 / 删除操作变慢；

小表无需索引：数据量小于 1 万条时，全表扫描可能比索引查询更快；

高频更新字段慎用索引：如订单状态字段，频繁更新会导致索引频繁维护。

三、数据封装与复用：视图的创建与管理

视图是基于查询结果的虚拟表，可简化复杂查询、隐藏敏感字段、实现数据权限控制，DM8 支持普通视图、物化视图（预计算结果存储，提升查询性能）。

1. 普通视图：简化复杂查询

场景：查询员工姓名、部门名称、工资（关联 EMP 和 DEPT 表），创建视图后直接查询视图即可：

-- 创建普通视图

CREATE VIEW VW_EMP_DEPT AS

SELECT

E.EMP_ID,

E.EMP_NAME,

D.DEPT_NAME,

E.SAL,

E.HIREDATE

FROM EMP E

LEFT JOIN DEPT D ON E.DEPT_ID = D.DEPT_ID;

-- 查询视图（与查询表语法一致）

SELECT * FROM VW_EMP_DEPT WHERE DEPT_NAME = '研发部' AND SAL > 10000;

2. 物化视图：优化海量数据查询

场景：统计各部门平均工资，数据量较大时，物化视图会预存储计算结果，避免每次查询重新计算：

-- 创建物化视图（每天凌晨2点刷新）

CREATE MATERIALIZED VIEW MV_DEPT_AVG_SAL

REFRESH COMPLETE ON DEMAND

START WITH SYSDATE NEXT TRUNC(SYSDATE + 1) + 2/24 -- 刷新周期：每天2点

AS

SELECT

DEPT_ID,

DEPT_NAME,

AVG(SAL) AS AVG_SAL,

COUNT(EMP_ID) AS EMP_COUNT

FROM VW_EMP_DEPT

GROUP BY DEPT_ID, DEPT_NAME;

-- 手动刷新物化视图

REFRESH MATERIALIZED VIEW MV_DEPT_AVG_SAL;

3. 视图的管理：修改与删除

-- 1. 修改视图（使用OR REPLACE）

CREATE OR REPLACE VIEW VW_EMP_DEPT AS

SELECT

E.EMP_ID,

E.EMP_NAME,

D.DEPT_NAME,

E.SAL,

E.HIREDATE,

E.EMAIL

FROM EMP E

LEFT JOIN DEPT D ON E.DEPT_ID = D.DEPT_ID;

-- 2. 隐藏敏感字段（如工资）：创建权限控制视图

CREATE VIEW VW_EMP_PUBLIC AS

SELECT EMP_ID, EMP_NAME, DEPT_NAME, HIREDATE FROM VW_EMP_DEPT;

-- 3. 删除视图

DROP VIEW MV_DEPT_AVG_SAL;

四、SQL 查询进阶：从基础到复杂查询

DM8 的 SQL 查询语法高度兼容标准 SQL，同时支持 Oracle 的高级查询特性，以下是开发中常用的查询技巧。

1. 基础查询：过滤、排序与分页

-- 1. 条件过滤（查询研发部2020年后入职的员工）

SELECT * FROM VW_EMP_DEPT

WHERE DEPT_NAME = '研发部'

AND HIREDATE >= TO_DATE('2020-01-01', 'YYYY-MM-DD')

ORDER BY SAL DESC;

-- 2. 分页查询（DM8专用分页：ROWID范围，高效）

-- 查询第11-20条数据（适用于大数据量）

SELECT * FROM (

SELECT ROWID, E.* FROM VW_EMP_DEPT E ORDER BY EMP_ID

) WHERE ROWID BETWEEN 11 AND 20;

-- 兼容Oracle的ROWNUM分页

SELECT * FROM (

SELECT E.*, ROWNUM RN FROM VW_EMP_DEPT E ORDER BY EMP_ID

) WHERE RN BETWEEN 11 AND 20;

2. 复杂查询：聚合、关联与子查询

-- 1. 聚合查询（统计各部门工资总额、最高/最低工资）

SELECT

DEPT_NAME,

SUM(SAL) AS TOTAL_SAL,

MAX(SAL) AS MAX_SAL,

MIN(SAL) AS MIN_SAL,

COUNT(*) AS EMP_NUM

FROM VW_EMP_DEPT

GROUP BY DEPT_NAME

HAVING SUM(SAL) > 50000; -- 过滤总额大于5万的部门

-- 2. 子查询（查询工资高于本部门平均工资的员工）

SELECT E.EMP_NAME, E.SAL, D.DEPT_NAME

FROM EMP E

JOIN DEPT D ON E.DEPT_ID = D.DEPT_ID

WHERE E.SAL > (

SELECT AVG(SAL) FROM EMP WHERE DEPT_ID = E.DEPT_ID

);

五、业务逻辑封装：存储过程与触发器编写

存储过程用于封装复杂业务逻辑（如批量处理、多表操作），触发器用于自动响应表事件（如插入 / 更新 / 删除后触发操作），是 DM8 自动化开发的核心。

1. 存储过程：批量处理与参数传递

场景：编写存储过程，批量插入员工数据，并返回插入成功的条数。

-- 创建存储过程

CREATE OR REPLACE PROCEDURE PROC_BATCH_INSERT_EMP(

P_DEPT_ID IN NUMBER, -- 部门编号（输入参数）

P_EMP_COUNT IN NUMBER, -- 插入员工数量（输入参数）

P_SUCCESS_COUNT OUT NUMBER -- 成功插入条数（输出参数）

)

IS

V_EMP_ID NUMBER := 1000; -- 员工编号起始值（局部变量）

V_EMP_NAME VARCHAR2(50);

V_SAL NUMBER;

BEGIN

P_SUCCESS_COUNT := 0;

-- 查询当前最大员工编号，避免重复

SELECT NVL(MAX(EMP_ID), 1000) INTO V_EMP_ID FROM EMP;

FOR I IN 1..P_EMP_COUNT LOOP

V_EMP_ID := V_EMP_ID + 1;

V_EMP_NAME := '员工' || V_EMP_ID;

V_SAL := ROUND(DBMS_RANDOM.VALUE(5000, 20000), 2); -- 随机工资（5000-20000）

-- 插入数据

INSERT INTO EMP (EMP_ID, EMP_NAME, DEPT_ID, SAL)

VALUES (V_EMP_ID, V_EMP_NAME, P_DEPT_ID, V_SAL);

P_SUCCESS_COUNT := P_SUCCESS_COUNT + 1;

END LOOP;

COMMIT; -- 批量提交

DBMS_OUTPUT.PUT_LINE('批量插入完成，成功条数：' || P_SUCCESS_COUNT);

EXCEPTION

WHEN OTHERS THEN

ROLLBACK; -- 异常回滚

DBMS_OUTPUT.PUT_LINE('插入失败：' || SQLERRM); -- 输出错误信息

RAISE;

END;

/

调用存储过程：

-- 声明变量接收输出参数

DECLARE

V_COUNT NUMBER;

BEGIN

-- 调用存储过程：向部门10插入5条员工数据

PROC_BATCH_INSERT_EMP(P_DEPT_ID => 10, P_EMP_COUNT => 5, P_SUCCESS_COUNT => V_COUNT);

DBMS_OUTPUT.PUT_LINE('实际插入条数：' || V_COUNT);

END;

/

2. 触发器：自动维护数据（如更新时间戳）

场景：创建触发器，当员工表（EMP）发生插入或更新时，自动记录最后修改时间（需先新增LAST_UPDATE_TIME字段）。

-- 1. 新增字段（最后修改时间）

ALTER TABLE EMP ADD (LAST_UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

-- 2. 创建触发器

CREATE OR REPLACE TRIGGER TRG_EMP_UPDATE_TIME

BEFORE INSERT OR UPDATE ON EMP -- 插入或更新前触发

FOR EACH ROW -- 行级触发器（每条记录触发一次）

BEGIN

:NEW.LAST_UPDATE_TIME := CURRENT_TIMESTAMP; -- 更新当前时间戳

END;

/

触发器验证：

-- 插入数据，触发触发器

INSERT INTO EMP (EMP_ID, EMP_NAME, DEPT_ID, SAL)

VALUES (1006, '张三', 10, 8000);

-- 查询结果，LAST_UPDATE_TIME会自动填充当前时间

SELECT EMP_NAME, LAST_UPDATE_TIME FROM EMP WHERE EMP_ID = 1006;

六、实战避坑总结

字段类型兼容：DM8 的NUMBER(p,s)精度严格，插入数据时需避免超出精度（如NUMBER(2)不能插入 100）；

索引失效场景：查询条件使用函数（如UPPER(EMP_NAME) = 'ZHANGSAN'）会导致普通索引失效，需创建函数索引；

存储过程调试：可通过DBMS_OUTPUT.PUT_LINE输出中间变量，或使用 DM 管理工具的调试功能单步执行；

触发器慎用：避免在触发器中执行复杂操作（如批量更新），可能导致性能问题或死锁；

权限控制：创建视图、存储过程时需确保用户有足够权限（如CREATE VIEW、CREATE PROCEDURE）。

结语

DM8 的 SQL 语法与数据库对象管理，核心是 “兼容 + 优化”—— 兼容 Oracle 等主流数据库的使用习惯，同时针对国产硬件和场景进行了性能优化。掌握表、索引、视图的设计技巧，以及存储过程、触发器的封装逻辑，能大幅提升开发效率和系统稳定性。后续可进一步学习 DM8 的分区表、并行查询、高可用集群等高级特性，适配更复杂的业务场景。如果需要具体场景的 SQL 优化或对象设计方案，欢迎在评论区交流！

查看全文

http://www.jsqmd.com/news/110432/