达梦数据库DM8：SQL语法篇（DQL、DML、DDL等汇总速查表）

一、DM8 SQL基础语法与数据类型

1.1 DM8 SQL语句分类

达梦数据库DM8的SQL语句主要分为以下几类：

1. 数据查询语言(DQL)：SELECT语句及其子句
2. 数据操纵语言(DML)：INSERT、UPDATE、DELETE、MERGE
3. 数据定义语言(DDL)：CREATE、ALTER、DROP、TRUNCATE等
4. 数据控制语言(DCL)：GRANT、REVOKE、DENY
5. 事务控制语言(TCL)：COMMIT、ROLLBACK、SAVEPOINT

-- 示例：各类SQL语句的基本使用
-- DQL示例
SELECT * FROM employees WHERE department_id = 10;

-- DML示例
INSERT INTO employees(employee_id, name, salary) VALUES(1001, '张三', 8000);

-- DDL示例
CREATE TABLE departments (
    dept_id NUMBER(4) PRIMARY KEY,
    dept_name VARCHAR2(30) NOT NULL
);

-- DCL示例
GRANT SELECT ON employees TO user1;

-- TCL示例
COMMIT;

1.2 DM8数据类型详解

DM8支持丰富的数据类型，以下是主要类型及其特性：

-- 数据类型使用示例
CREATE TABLE data_type_demo (
    id NUMBER(10) PRIMARY KEY,          -- 主键ID
    name VARCHAR2(50) NOT NULL,         -- 姓名，非空
    age INTEGER,                        -- 年龄
    salary NUMBER(12,2),                -- 薪资，精确到小数点后两位
    birth_date DATE,                    -- 出生日期
    is_manager BOOLEAN DEFAULT FALSE,   -- 是否管理者，默认否
    photo BLOB,                         -- 照片二进制数据
    resume TEXT,                        -- 简历大文本
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 创建时间，默认当前时间
);

-- 插入数据示例
INSERT INTO data_type_demo 
VALUES(1, '李四', 30, 15000.50, TO_DATE('1990-05-15', 'YYYY-MM-DD'), TRUE, NULL, '资深工程师', SYSTIMESTAMP);

1.3 数据类型转换

DM8支持显式和隐式数据类型转换：

-- 显式转换函数
SELECT 
    CAST('123' AS INTEGER) AS str_to_int,          -- 字符串转整数
    CAST(123 AS VARCHAR(10)) AS int_to_str,        -- 整数转字符串
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS date_to_str,  -- 日期转字符串
    TO_DATE('2023-01-15', 'YYYY-MM-DD') AS str_to_date,        -- 字符串转日期
    TO_NUMBER('1234.56') AS str_to_num             -- 字符串转数字
FROM DUAL;

-- 隐式转换示例(不推荐，可能影响性能)
SELECT '100' + 200 FROM DUAL;  -- 字符串'100'隐式转换为数字

二、DM8数据查询语言(DQL)

2.1 基本SELECT查询

SELECT语句是SQL中最常用的语句，用于从数据库中检索数据。

-- 基本SELECT语法结构
/*
SELECT [ALL|DISTINCT] <column_list>
FROM <table_name>
[WHERE <condition>]
[GROUP BY <group_by_expression>]
[HAVING <group_condition>]
[ORDER BY <order_by_list> [ASC|DESC]]
[LIMIT <row_count>];
*/

-- 实际示例
SELECT 
    employee_id AS "员工编号",  -- 列别名使用双引号可以包含空格或保留字
    name "员工姓名",           -- AS关键字可选
    salary * 12 "年薪",        -- 可以使用表达式
    department_id
FROM 
    employees
WHERE 
    department_id IN (10, 20, 30)  -- 部门ID在10,20,30中
    AND salary > 5000             -- 且薪资大于5000
ORDER BY 
    salary DESC,                  -- 首先按薪资降序
    name ASC                      -- 然后按姓名升序
LIMIT 10;                         -- 只返回前10条记录

2.2 条件查询与运算符

DM8支持丰富的条件运算符：

-- 条件查询综合示例
SELECT 
    product_id,
    product_name,
    category,
    price,
    stock_quantity
FROM 
    products
WHERE 
    (category = '电子产品' OR category = '办公用品')  -- 类别为电子产品或办公用品
    AND price BETWEEN 100 AND 1000                -- 价格在100到1000之间
    AND product_name LIKE '%联想%'                -- 产品名称包含"联想"
    AND stock_quantity > 0                       -- 库存大于0
    AND discontinued_flag IS NOT NULL            -- 停产标志不为空
ORDER BY 
    price DESC;

2.3 分组与聚合函数

DM8提供丰富的聚合函数进行数据统计：

-- 分组聚合示例
SELECT 
    department_id AS "部门ID",
    COUNT(*) AS "员工人数",
    ROUND(AVG(salary), 2) AS "平均薪资",
    MAX(salary) AS "最高薪资",
    MIN(salary) AS "最低薪资",
    SUM(salary) AS "薪资总额",
    LISTAGG(name, '; ') WITHIN GROUP (ORDER BY salary DESC) AS "员工列表"
FROM 
    employees
WHERE 
    hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD')  -- 2020年后入职
GROUP BY 
    department_id
HAVING 
    COUNT(*) > 5  -- 只显示员工数大于5的部门
ORDER BY 
    "平均薪资" DESC;

2.4 多表连接查询

DM8支持多种表连接方式：

-- 内连接(INNER JOIN)：只返回两表中匹配的行
SELECT 
    e.employee_id,
    e.name,
    d.department_name,
    j.job_title
FROM 
    employees e
INNER JOIN 
    departments d ON e.department_id = d.department_id
INNER JOIN 
    jobs j ON e.job_id = j.job_id
WHERE 
    d.location_id = 1700;

-- 左外连接(LEFT JOIN)：返回左表所有行，右表不匹配则为NULL
SELECT 
    d.department_name,
    e.name
FROM 
    departments d
LEFT JOIN 
    employees e ON d.department_id = e.department_id
ORDER BY 
    d.department_name;

-- 右外连接(RIGHT JOIN)：返回右表所有行，左表不匹配则为NULL
SELECT 
    e.name,
    d.department_name
FROM 
    employees e
RIGHT JOIN 
    departments d ON e.department_id = d.department_id;

-- 全外连接(FULL JOIN)：返回两表所有行，不匹配则为NULL
SELECT 
    e.name,
    d.department_name
FROM 
    employees e
FULL JOIN 
    departments d ON e.department_id = d.department_id;

-- 交叉连接(CROSS JOIN)：笛卡尔积
SELECT 
    e.name,
    d.department_name
FROM 
    employees e
CROSS JOIN 
    departments d;

-- 自连接：表与自身连接
SELECT 
    e1.name AS "员工姓名",
    e2.name AS "上级姓名"
FROM 
    employees e1
LEFT JOIN 
    employees e2 ON e1.manager_id = e2.employee_id;

三、DM8数据操纵语言(DML)

3.1 INSERT语句详解

INSERT语句用于向表中插入新记录。

-- 基本INSERT语法
/*
INSERT INTO table_name [(column1, column2, ..., columnN)] 
VALUES (value1, value2, ..., valueN);
*/

-- 完整列名列表的INSERT
INSERT INTO employees (
    employee_id,
    name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
) VALUES (
    1001,
    '张三',
    'zhangsan@example.com',
    TO_DATE('2023-01-15', 'YYYY-MM-DD'),
    'IT_PROG',
    8000,
    90
);

-- 省略列名列表(必须为所有列提供值)
INSERT INTO departments 
VALUES (300, '研发部', 100, 1700);

-- 多行INSERT(批量插入)
INSERT ALL
    INTO employees VALUES (1002, '李四', 'lisi@example.com', SYSDATE, 'SA_REP', 6000, NULL, 80)
    INTO employees VALUES (1003, '王五', 'wangwu@example.com', SYSDATE, 'IT_PROG', 7500, NULL, 90)
    INTO employees VALUES (1004, '赵六', 'zhaoliu@example.com', SYSDATE, 'HR_REP', 6500, NULL, 70)
SELECT 1 FROM DUAL;

-- 从其他表插入数据
INSERT INTO employee_history
SELECT 
    employee_id,
    name,
    salary,
    department_id,
    SYSDATE
FROM 
    employees
WHERE 
    hire_date > TO_DATE('2022-01-01', 'YYYY-MM-DD');

3.2 UPDATE语句详解

UPDATE语句用于修改表中的现有记录。

-- 基本UPDATE语法
/*
UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
[WHERE condition];
*/

-- 简单UPDATE示例
UPDATE employees
SET 
    salary = salary * 1.1,  -- 薪资增长10%
    last_raise_date = SYSDATE
WHERE 
    department_id = 80
    AND hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');

-- 使用子查询更新
UPDATE employees e
SET 
    salary = (
        SELECT AVG(salary)
        FROM employees
        WHERE department_id = e.department_id
    ) * 1.2,
    manager_id = (
        SELECT manager_id
        FROM departments
        WHERE department_id = e.department_id
    )
WHERE 
    department_id IN (
        SELECT department_id 
        FROM departments 
        WHERE location_id = 1700
    );

-- 使用JOIN更新(达梦特有语法)
UPDATE 
    (SELECT e.salary, d.avg_sal
     FROM employees e
     JOIN (SELECT department_id, AVG(salary) avg_sal 
           FROM employees GROUP BY department_id) d
     ON e.department_id = d.department_id)
SET 
    salary = avg_sal
WHERE 
    salary < avg_sal;

3.3 DELETE语句详解

DELETE语句用于从表中删除记录。

-- 基本DELETE语法
/*
DELETE FROM table_name
[WHERE condition];
*/

-- 简单DELETE示例
DELETE FROM employees
WHERE 
    employee_id = 1001;

-- 使用子查询删除
DELETE FROM employees
WHERE 
    department_id IN (
        SELECT department_id
        FROM departments
        WHERE location_id = 1700
    );

-- 删除所有记录(慎用)
DELETE FROM temp_table;

-- 使用JOIN删除(达梦特有语法)
DELETE FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

3.4 MERGE语句详解

MERGE语句可以同时执行INSERT和UPDATE操作，非常适合数据同步场景。

-- MERGE语法结构
/*
MERGE INTO target_table t
USING source_table s
ON (t.key = s.key)
WHEN MATCHED THEN 
    UPDATE SET t.column1 = s.column1, t.column2 = s.column2, ...
WHEN NOT MATCHED THEN 
    INSERT (t.column1, t.column2, ...) 
    VALUES (s.column1, s.column2, ...);
*/

-- MERGE实际示例
MERGE INTO employee_target t
USING employee_source s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        t.name = s.name,
        t.salary = s.salary,
        t.department_id = s.department_id,
        t.last_update = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (
        employee_id,
        name,
        salary,
        department_id,
        create_date,
        last_update
    ) VALUES (
        s.employee_id,
        s.name,
        s.salary,
        s.department_id,
        SYSDATE,
        SYSDATE
    );

-- 带条件的MERGE
MERGE INTO products p
USING new_products np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
    UPDATE SET 
        p.price = np.price,
        p.stock = p.stock + np.stock
    WHERE 
        np.price > 0
WHEN NOT MATCHED THEN
    INSERT (
        product_id,
        product_name,
        price,
        stock
    ) VALUES (
        np.product_id,
        np.product_name,
        np.price,
        np.stock
    )
    WHERE 
        np.price > 0;

四、DM8数据定义语言(DDL)

4.1 表管理

4.1.1 创建表

-- 基本CREATE TABLE语法
/*
CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
) [TABLESPACE tablespace_name];
*/

-- 创建表示例
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,  -- 主键约束
    name VARCHAR2(50) NOT NULL,         -- 非空约束
    email VARCHAR2(100) UNIQUE,         -- 唯一约束
    hire_date DATE DEFAULT SYSDATE,      -- 默认值
    salary NUMBER(8,2) CHECK (salary > 0), -- 检查约束
    department_id NUMBER(4),
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)  -- 外键约束
        REFERENCES departments(department_id)
) TABLESPACE users;

-- 创建表时添加注释
COMMENT ON TABLE employees IS '公司员工信息表';
COMMENT ON COLUMN employees.employee_id IS '员工编号，主键';
COMMENT ON COLUMN employees.name IS '员工姓名';

-- 基于查询创建表
CREATE TABLE emp_dept_10
AS
SELECT 
    e.employee_id,
    e.name,
    e.salary,
    d.department_name
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id
WHERE 
    e.department_id = 10;

4.1.2 修改表

-- 添加列
ALTER TABLE employees
ADD (middle_name VARCHAR2(30),
     nickname VARCHAR2(20) DEFAULT '无名');

-- 修改列定义
ALTER TABLE employees
MODIFY (email VARCHAR2(150),
        salary NUMBER(10,2));

-- 删除列
ALTER TABLE employees
DROP COLUMN nickname;

-- 重命名列
ALTER TABLE employees
RENAME COLUMN name TO full_name;

-- 添加约束
ALTER TABLE employees
ADD CONSTRAINT emp_salary_min CHECK (salary >= 3000);

-- 禁用/启用约束
ALTER TABLE employees
DISABLE CONSTRAINT emp_salary_min;

ALTER TABLE employees
ENABLE CONSTRAINT emp_salary_min;

-- 删除约束
ALTER TABLE employees
DROP CONSTRAINT emp_salary_min;

-- 重命名表
ALTER TABLE employees
RENAME TO staff;

4.1.3 删除表

-- 基本DROP TABLE语法
DROP TABLE employees;

-- 带约束的删除
DROP TABLE departments CASCADE CONSTRAINTS;  -- 级联删除依赖的约束

-- 清空表数据但保留结构
TRUNCATE TABLE temp_employees;

4.2 索引管理

4.2.1 创建索引

-- 创建普通索引
CREATE INDEX idx_emp_name ON employees(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- 创建复合索引
CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary DESC);

-- 创建函数索引
CREATE INDEX idx_emp_name_upper ON employees(UPPER(name));

-- 位图索引(适合低基数列)
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- 分区索引
CREATE INDEX idx_emp_hire_date ON employees(hire_date)
GLOBAL PARTITION BY RANGE (hire_date) (
    PARTITION p_2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
    PARTITION p_2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

4.2.2 管理索引

-- 重建索引
ALTER INDEX idx_emp_name REBUILD;

-- 重命名索引
ALTER INDEX idx_emp_name RENAME TO idx_employee_name;

-- 监控索引使用
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 查看索引使用情况
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_NAME';

-- 删除索引
DROP INDEX idx_emp_name;

4.3 视图管理

4.3.1 创建视图

-- 基本视图
CREATE VIEW v_emp_dept AS
SELECT 
    e.employee_id,
    e.name,
    e.salary,
    d.department_name
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id;

-- 带检查选项的视图(确保通过视图的修改满足视图定义)
CREATE VIEW v_emp_10 AS
SELECT * FROM employees WHERE department_id = 10
WITH CHECK OPTION;

-- 只读视图
CREATE VIEW v_emp_sal_readonly AS
SELECT 
    department_id,
    AVG(salary) avg_sal,
    MAX(salary) max_sal,
    MIN(salary) min_sal
FROM 
    employees
GROUP BY 
    department_id
WITH READ ONLY;

-- 物化视图(预计算并存储结果)
CREATE MATERIALIZED VIEW mv_emp_sal_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT 
    department_id,
    COUNT(*) emp_count,
    SUM(salary) total_sal,
    AVG(salary) avg_sal
FROM 
    employees
GROUP BY 
    department_id;

4.3.2 管理视图

-- 修改视图
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT 
    e.employee_id,
    e.name,
    e.salary,
    d.department_name,
    l.city
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id
JOIN 
    locations l ON d.location_id = l.location_id;

-- 重编译视图
ALTER VIEW v_emp_dept COMPILE;

-- 删除视图
DROP VIEW v_emp_dept;

-- 刷新物化视图
BEGIN
    DBMS_MVIEW.REFRESH('mv_emp_sal_summary', 'C');
END;
/

五、DM8高级SQL特性

5.1 子查询与派生表

5.1.1 标量子查询

-- 标量子查询(返回单个值)
SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) avg_sal,
    salary - (SELECT AVG(salary) FROM employees) diff_from_avg
FROM 
    employees
WHERE 
    department_id = 10;

-- 在UPDATE中使用标量子查询
UPDATE employees e
SET salary = (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
) * 1.1
WHERE department_id = 20;

5.1.2 多行子查询

-- IN子查询
SELECT 
    name,
    salary
FROM 
    employees
WHERE 
    department_id IN (
        SELECT department_id
        FROM departments
        WHERE location_id = 1700
    );

-- ANY/SOME子查询(满足任一条件)
SELECT 
    name,
    salary
FROM 
    employees
WHERE 
    salary > ANY (
        SELECT salary
        FROM employees
        WHERE department_id = 90
    );

-- ALL子查询(满足所有条件)
SELECT 
    name,
    salary
FROM 
    employees
WHERE 
    salary > ALL (
        SELECT salary
        FROM employees
        WHERE department_id = 90
    );

5.1.3 派生表(FROM子句中的子查询)

-- 派生表示例
SELECT 
    d.department_name,
    emp_stats.emp_count,
    emp_stats.avg_sal
FROM 
    departments d
JOIN (
    SELECT 
        department_id,
        COUNT(*) emp_count,
        AVG(salary) avg_sal
    FROM 
        employees
    GROUP BY 
        department_id
) emp_stats ON d.department_id = emp_stats.department_id
ORDER BY 
    emp_stats.avg_sal DESC;

-- 带列别名的派生表
SELECT 
    dept_info.dept_name,
    dept_info.avg_salary
FROM (
    SELECT 
        d.department_name dept_name,
        AVG(e.salary) avg_salary
    FROM 
        departments d
    JOIN 
        employees e ON d.department_id = e.department_id
    GROUP BY 
        d.department_name
) dept_info
WHERE 
    dept_info.avg_salary > 8000;

5.2 公用表表达式(CTE)

CTE(Common Table Expression)可以显著提高复杂查询的可读性。

-- 基本CTE语法
/*
WITH cte_name AS (
    subquery
)
SELECT ... FROM cte_name ...;
*/

-- 简单CTE示例
WITH dept_salaries AS (
    SELECT 
        department_id,
        AVG(salary) avg_salary
    FROM 
        employees
    GROUP BY 
        department_id
)
SELECT 
    d.department_name,
    ds.avg_salary
FROM 
    departments d
JOIN 
    dept_salaries ds ON d.department_id = ds.department_id
ORDER BY 
    ds.avg_salary DESC;

-- 多CTE示例
WITH 
emp_count AS (
    SELECT 
        department_id,
        COUNT(*) emp_count
    FROM 
        employees
    GROUP BY 
        department_id
),
dept_sal AS (
    SELECT 
        department_id,
        SUM(salary) total_sal
    FROM 
        employees
    GROUP BY 
        department_id
)
SELECT 
    d.department_name,
    ec.emp_count,
    ds.total_sal,
    ds.total_sal / ec.emp_count avg_sal
FROM 
    departments d
JOIN 
    emp_count ec ON d.department_id = ec.department_id
JOIN 
    dept_sal ds ON d.department_id = ds.department_id
ORDER BY 
    avg_sal DESC;

-- 递归CTE(处理层次结构数据)
WITH RECURSIVE emp_hierarchy AS (
    -- 基础查询(顶级管理者)
    SELECT 
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM 
        employees
    WHERE 
        manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询(下属员工)
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM 
        employees e
    JOIN 
        emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    LPAD(' ', (level-1)*4, ' ') || name AS org_chart,
    level
FROM 
    emp_hierarchy
ORDER BY 
    level, name;

5.3 窗口函数

窗口函数允许在行集上进行计算，同时保留行的原始数据。

-- 基本窗口函数语法
/*
function_name([arguments]) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC|DESC], ...]
    [frame_clause]
)
*/

-- 排名函数示例
SELECT 
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) dept_sal_rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) dept_sal_dense_rank,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) dept_sal_row_num
FROM 
    employees;

-- 聚合窗口函数示例
SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) dept_avg_sal,
    salary - AVG(salary) OVER (PARTITION BY department_id) diff_from_avg,
    SUM(salary) OVER (PARTITION BY department_id) dept_total_sal,
    salary / SUM(salary) OVER (PARTITION BY department_id) * 100 sal_percent
FROM 
    employees;

-- 窗口帧示例(计算移动平均)
SELECT 
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_day_avg,
    SUM(daily_sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM 
    sales_data;

-- 偏移函数示例
SELECT 
    date,
    sales,
    LAG(sales, 1) OVER (ORDER BY date) prev_day_sales,
    LEAD(sales, 1) OVER (ORDER BY date) next_day_sales,
    sales - LAG(sales, 1) OVER (ORDER BY date) day_over_day_change
FROM 
    daily_sales;

-- 分布函数示例
SELECT 
    name,
    salary,
    CUME_DIST() OVER (ORDER BY salary) AS salary_cume_dist,
    PERCENT_RANK() OVER (ORDER BY salary) AS salary_percent_rank,
    NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM 
    employees;

5.4 高级分析函数

DM8提供强大的分析函数支持复杂分析需求。

-- FIRST/LAST函数(获取分组内极值对应的行)
SELECT 
    department_id,
    MAX(name) KEEP (DENSE_RANK FIRST ORDER BY salary) lowest_paid_emp,
    MIN(salary) min_salary,
    MAX(name) KEEP (DENSE_RANK LAST ORDER BY salary) highest_paid_emp,
    MAX(salary) max_salary
FROM 
    employees
GROUP BY 
    department_id;

-- ROLLUP/CUBE/GROUPING SETS(多维度聚合)
-- ROLLUP(生成层次化小计)
SELECT 
    department_id,
    job_id,
    COUNT(*) emp_count,
    SUM(salary) total_sal
FROM 
    employees
GROUP BY 
    ROLLUP(department_id, job_id);

-- CUBE(生成所有可能的组合)
SELECT 
    department_id,
    job_id,
    COUNT(*) emp_count,
    SUM(salary) total_sal
FROM 
    employees
GROUP BY 
    CUBE(department_id, job_id);

-- GROUPING SETS(指定需要的分组组合)
SELECT 
    department_id,
    job_id,
    EXTRACT(YEAR FROM hire_date) hire_year,
    COUNT(*) emp_count
FROM 
    employees
GROUP BY 
    GROUPING SETS (
        (department_id, job_id),
        (job_id, hire_year),
        (department_id),
        ()
    );

-- PIVOT/UNPIVOT(行列转换)
-- PIVOT(行转列)
SELECT *
FROM (
    SELECT 
        department_id,
        job_id,
        salary
    FROM 
        employees
)
PIVOT (
    AVG(salary) FOR job_id IN (
        'IT_PROG' AS it_prog,
        'SA_REP' AS sa_rep,
        'HR_REP' AS hr_rep
    )
)
ORDER BY department_id;

-- UNPIVOT(列转行)
SELECT *
FROM monthly_sales
UNPIVOT (
    sales_amount FOR month IN (
        jan AS 'January',
        feb AS 'February',
        mar AS 'March'
    )
);

六、DM8事务控制与并发

6.1 事务基础

事务是数据库操作的逻辑单元，具有ACID特性(原子性、一致性、隔离性、持久性)。

-- 基本事务控制
BEGIN TRANSACTION;  -- 开始事务(达梦也支持直接使用DML语句开始隐式事务)

INSERT INTO accounts (account_id, balance) VALUES (1001, 5000);
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1001;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 1002;

-- 根据业务逻辑决定提交或回滚
IF some_condition THEN
    COMMIT;  -- 提交事务
    DBMS_OUTPUT.PUT_LINE('事务已提交');
ELSE
    ROLLBACK;  -- 回滚事务
    DBMS_OUTPUT.PUT_LINE('事务已回滚');
END IF;

-- 保存点(SAVEPOINT)使用
BEGIN
    INSERT INTO orders (order_id, customer_id) VALUES (5001, 101);
    
    SAVEPOINT after_order_insert;
    
    INSERT INTO order_items (order_id, product_id, quantity) 
    VALUES (5001, 2001, 2);
    
    -- 假设这里发生某些验证
    IF validation_failed THEN
        ROLLBACK TO SAVEPOINT after_order_insert;  -- 回滚到保存点
        DBMS_OUTPUT.PUT_LINE('回滚到保存点');
    END IF;
    
    COMMIT;
END;
/

6.2 事务隔离级别

DM8支持标准的事务隔离级别：

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 默认级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;    -- 最高隔离级别

-- 查询当前会话隔离级别
SELECT 
    CASE isolation_level
        WHEN 0 THEN 'READ UNCOMMITTED'
        WHEN 1 THEN 'READ COMMITTED'
        WHEN 2 THEN 'REPEATABLE READ'
        WHEN 3 THEN 'SERIALIZABLE'
    END AS isolation_level
FROM 
    v$transaction
WHERE 
    sess_id = (SELECT sid FROM v$mystat WHERE rownum = 1);

6.3 锁机制

DM8提供多种锁机制保证数据一致性：

-- 显式锁定示例
-- 行级锁
SELECT * FROM accounts WHERE account_id = 1001 FOR UPDATE;  -- 获取行排他锁

-- 表级锁
LOCK TABLE accounts IN EXCLUSIVE MODE;  -- 获取表排他锁

-- 查看锁信息
SELECT 
    l.session_id,
    s.username,
    o.object_name,
    l.locked_mode,
    CASE l.locked_mode
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Null'
        WHEN 2 THEN 'Row-S (SS)'
        WHEN 3 THEN 'Row-X (SX)'
        WHEN 4 THEN 'Share'
        WHEN 5 THEN 'S/Row-X (SSX)'
        WHEN 6 THEN 'Exclusive'
    END AS lock_mode_desc,
    l.oracle_username
FROM 
    v$locked_object l
JOIN 
    dba_objects o ON l.object_id = o.object_id
JOIN 
    v$session s ON l.session_id = s.sid;

6.4 死锁处理

-- 查询死锁信息
SELECT 
    dl.session_id,
    s.username,
    s.status,
    s.machine,
    s.program,
    dl.waiting_for,
    dl.hold_object
FROM 
    v$deadlock dl
JOIN 
    v$session s ON dl.session_id = s.sid;

-- 杀死死锁会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 避免死锁的最佳实践
/*
1. 以固定顺序访问表和行
2. 保持事务简短
3. 使用适当的隔离级别
4. 在应用中实现重试逻辑
5. 避免用户交互在事务中
*/

七、DM8存储过程与函数

7.1 PL/SQL基础

DM8的PL/SQL与Oracle高度兼容，是一种块结构的语言。

-- PL/SQL基本块结构
/*
[DECLARE
    -- 声明部分(变量、常量、游标等)
BEGIN
    -- 执行部分
[EXCEPTION
    -- 异常处理部分]
END;
*/

-- 简单PL/SQL块示例
DECLARE
    v_emp_count NUMBER;
    v_dept_id NUMBER := 10;
    v_avg_sal NUMBER(10,2);
BEGIN
    -- 查询部门员工数
    SELECT COUNT(*) INTO v_emp_count
    FROM employees
    WHERE department_id = v_dept_id;
    
    -- 查询部门平均薪资
    SELECT AVG(salary) INTO v_avg_sal
    FROM employees
    WHERE department_id = v_dept_id;
    
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('部门 ' || v_dept_id || ' 有 ' || v_emp_count || ' 名员工');
    DBMS_OUTPUT.PUT_LINE('平均薪资: ' || v_avg_sal);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('没有找到部门 ' || v_dept_id);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/

7.2 存储过程

存储过程是存储在数据库中的命名PL/SQL块。

-- 创建存储过程
CREATE OR REPLACE PROCEDURE update_employee_salary (
    p_emp_id IN NUMBER,
    p_percent IN NUMBER,
    p_updated OUT NUMBER
) AS
    v_old_sal NUMBER;
    v_new_sal NUMBER;
BEGIN
    -- 查询当前薪资
    SELECT salary INTO v_old_sal
    FROM employees
    WHERE employee_id = p_emp_id
    FOR UPDATE;  -- 加锁防止并发修改
    
    -- 计算新薪资
    v_new_sal := v_old_sal * (1 + p_percent/100);
    
    -- 更新薪资
    UPDATE employees
    SET salary = v_new_sal
    WHERE employee_id = p_emp_id;
    
    -- 设置输出参数
    p_updated := 1;
    
    -- 记录日志
    INSERT INTO salary_change_log (
        emp_id,
        old_salary,
        new_salary,
        change_date,
        change_by
    ) VALUES (
        p_emp_id,
        v_old_sal,
        v_new_sal,
        SYSDATE,
        USER
    );
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('员工 ' || p_emp_id || ' 薪资从 ' || v_old_sal || 
                         ' 调整为 ' || v_new_sal);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_updated := 0;
        DBMS_OUTPUT.PUT_LINE('员工ID ' || p_emp_id || ' 不存在');
    WHEN OTHERS THEN
        p_updated := 0;
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END update_employee_salary;
/

-- 调用存储过程
DECLARE
    v_result NUMBER;
BEGIN
    update_employee_salary(1001, 10, v_result);
    DBMS_OUTPUT.PUT_LINE('更新结果: ' || v_result);
END;
/

-- 查看存储过程源码
SELECT text FROM user_source WHERE name = 'UPDATE_EMPLOYEE_SALARY' ORDER BY line;

-- 删除存储过程
DROP PROCEDURE update_employee_salary;

7.3 函数

函数与存储过程类似，但必须返回一个值。

-- 创建函数
CREATE OR REPLACE FUNCTION calculate_tax (
    p_salary IN NUMBER,
    p_tax_rate IN NUMBER DEFAULT 0.1
) RETURN NUMBER AS
    v_tax NUMBER;
BEGIN
    -- 计算税额
    v_tax := p_salary * p_tax_rate;
    
    -- 确保税额不为负
    IF v_tax < 0 THEN
        v_tax := 0;
    END IF;
    
    RETURN ROUND(v_tax, 2);
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END calculate_tax;
/

-- 调用函数
DECLARE
    v_salary NUMBER := 8000;
    v_tax NUMBER;
BEGIN
    v_tax := calculate_tax(v_salary);
    DBMS_OUTPUT.PUT_LINE('薪资: ' || v_salary || ', 税额: ' || v_tax);
END;
/

-- SQL中调用函数
SELECT 
    employee_id,
    name,
    salary,
    calculate_tax(salary) AS tax,
    salary - calculate_tax(salary) AS net_salary
FROM 
    employees
WHERE 
    department_id = 10;

-- 查看函数状态
SELECT object_name, status FROM user_objects WHERE object_type = 'FUNCTION';

-- 删除函数
DROP FUNCTION calculate_tax;

7.4 包(Package)

包是一种将相关的过程、函数、变量等组织在一起的数据库对象。

-- 创建包规范(接口)
CREATE OR REPLACE PACKAGE emp_pkg AS
    -- 公共常量
    g_max_salary CONSTANT NUMBER := 100000;
    
    -- 公共异常
    e_invalid_dept EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_invalid_dept, -20001);
    
    -- 公共函数和过程声明
    FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER;
    
    PROCEDURE raise_salary(
        p_emp_id IN NUMBER,
        p_percent IN NUMBER,
        p_updated OUT NUMBER
    );
    
    PROCEDURE transfer_dept(
        p_emp_id IN NUMBER,
        p_new_dept_id IN NUMBER
    );
END emp_pkg;
/

-- 创建包体(实现)
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
    -- 私有变量(只在包内可见)
    v_last_raise_date DATE := SYSDATE;
    
    -- 私有函数(只在包内可见)
    FUNCTION is_valid_dept(p_dept_id NUMBER) RETURN BOOLEAN AS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count
        FROM departments
        WHERE department_id = p_dept_id;
        
        RETURN v_count > 0;
    END is_valid_dept;
    
    -- 公共函数实现
    FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER AS
        v_count NUMBER;
    BEGIN
        IF NOT is_valid_dept(p_dept_id) THEN
            RAISE e_invalid_dept;
        END IF;
        
        SELECT COUNT(*) INTO v_count
        FROM employees
        WHERE department_id = p_dept_id;
        
        RETURN v_count;
    END get_emp_count;
    
    -- 公共过程实现
    PROCEDURE raise_salary(
        p_emp_id IN NUMBER,
        p_percent IN NUMBER,
        p_updated OUT NUMBER
    ) AS
        v_old_sal NUMBER;
        v_new_sal NUMBER;
    BEGIN
        -- 查询当前薪资
        SELECT salary INTO v_old_sal
        FROM employees
        WHERE employee_id = p_emp_id
        FOR UPDATE;
        
        -- 计算新薪资
        v_new_sal := v_old_sal * (1 + p_percent/100);
        
        -- 检查是否超过最大薪资
        IF v_new_sal > g_max_salary THEN
            v_new_sal := g_max_salary;
        END IF;
        
        -- 更新薪资
        UPDATE employees
        SET salary = v_new_sal
        WHERE employee_id = p_emp_id;
        
        -- 更新最后加薪日期
        v_last_raise_date := SYSDATE;
        
        -- 设置输出参数
        p_updated := 1;
        
        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            p_updated := 0;
            DBMS_OUTPUT.PUT_LINE('员工ID ' || p_emp_id || ' 不存在');
        WHEN OTHERS THEN
            p_updated := 0;
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
    END raise_salary;
    
    PROCEDURE transfer_dept(
        p_emp_id IN NUMBER,
        p_new_dept_id IN NUMBER
    ) AS
    BEGIN
        IF NOT is_valid_dept(p_new_dept_id) THEN
            RAISE e_invalid_dept;
        END IF;
        
        UPDATE employees
        SET department_id = p_new_dept_id
        WHERE employee_id = p_emp_id;
        
        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('员工ID ' || p_emp_id || ' 不存在');
        WHEN e_invalid_dept THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('部门ID ' || p_new_dept_id || ' 无效');
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
    END transfer_dept;
END emp_pkg;
/

-- 使用包中的过程和函数
DECLARE
    v_count NUMBER;
    v_result NUMBER;
BEGIN
    -- 调用包函数
    v_count := emp_pkg.get_emp_count(10);
    DBMS_OUTPUT.PUT_LINE('部门10有 ' || v_count || ' 名员工');
    
    -- 调用包过程
    emp_pkg.raise_salary(1001, 5, v_result);
    DBMS_OUTPUT.PUT_LINE('加薪结果: ' || v_result);
    
    -- 调用包过程(会触发异常)
    emp_pkg.transfer_dept(1001, 999);
EXCEPTION
    WHEN emp_pkg.e_invalid_dept THEN
        DBMS_OUTPUT.PUT_LINE('自定义异常: 无效部门');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/

-- 查看包信息
SELECT object_name, object_type, status 
FROM user_objects 
WHERE object_name = 'EMP_PKG';

-- 删除包
DROP PACKAGE emp_pkg;

八、DM8触发器

触发器是在特定数据库事件发生时自动执行的PL/SQL块。

8.1 DML触发器

-- 创建BEFORE语句触发器
CREATE OR REPLACE TRIGGER trg_before_emp_update
BEFORE UPDATE ON employees
BEGIN
    DBMS_OUTPUT.PUT_LINE('即将更新employees表，时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

-- 创建BEFORE行触发器
CREATE OR REPLACE TRIGGER trg_before_emp_sal_update
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 1.5)
BEGIN
    DBMS_OUTPUT.PUT_LINE('员工 ' || :OLD.name || ' 薪资变化超过50%');
    DBMS_OUTPUT.PUT_LINE('旧薪资: ' || :OLD.salary || ', 新薪资: ' || :NEW.salary);
    
    -- 可以修改:NEW值
    IF :NEW.salary > 100000 THEN
        :NEW.salary := 100000;
        DBMS_OUTPUT.PUT_LINE('薪资超过上限，已调整为100000');
    END IF;
END;
/

-- 创建AFTER行触发器
CREATE OR REPLACE TRIGGER trg_after_emp_update
AFTER UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF UPDATING THEN
        INSERT INTO emp_audit (
            emp_id,
            change_type,
            old_salary,
            new_salary,
            change_date,
            changed_by
        ) VALUES (
            :OLD.employee_id,
            'UPDATE',
            :OLD.salary,
            :NEW.salary,
            SYSDATE,
            USER
        );
    ELSIF DELETING THEN
        INSERT INTO emp_audit (
            emp_id,
            change_type,
            old_salary,
            new_salary,
            change_date,
            changed_by
        ) VALUES (
            :OLD.employee_id,
            'DELETE',
            :OLD.salary,
            NULL,
            SYSDATE,
            USER
        );
    END IF;
END;
/

-- 测试触发器
UPDATE employees
SET salary = salary * 1.6
WHERE employee_id = 1001;

-- 禁用/启用触发器
ALTER TRIGGER trg_before_emp_sal_update DISABLE;
ALTER TRIGGER trg_before_emp_sal_update ENABLE;

-- 查看触发器信息
SELECT trigger_name, trigger_type, triggering_event, status 
FROM user_triggers 
WHERE table_name = 'EMPLOYEES';

-- 删除触发器
DROP TRIGGER trg_before_emp_update;

8.2 INSTEAD OF触发器

主要用于视图上的DML操作。

-- 创建复杂视图
CREATE VIEW v_emp_dept AS
SELECT 
    e.employee_id,
    e.name,
    e.salary,
    d.department_name,
    d.location_id
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id;

-- 创建INSTEAD OF触发器
CREATE OR REPLACE TRIGGER trg_instead_of_emp_dept
INSTEAD OF INSERT OR UPDATE OR DELETE ON v_emp_dept
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        -- 首先检查部门是否存在
        DECLARE
            v_dept_id NUMBER;
        BEGIN
            SELECT department_id INTO v_dept_id
            FROM departments
            WHERE department_name = :NEW.department_name
            AND ROWNUM = 1;
            
            -- 插入员工表
            INSERT INTO employees (
                employee_id,
                name,
                salary,
                department_id
            ) VALUES (
                :NEW.employee_id,
                :NEW.name,
                :NEW.salary,
                v_dept_id
            );
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RAISE_APPLICATION_ERROR(-20001, '部门 ' || :NEW.department_name || ' 不存在');
        END;
    ELSIF UPDATING THEN
        -- 更新员工表
        UPDATE employees
        SET 
            name = :NEW.name,
            salary = :NEW.salary
        WHERE 
            employee_id = :OLD.employee_id;
            
        -- 如果部门名变更，需要更新部门关联
        IF :NEW.department_name != :OLD.department_name THEN
            DECLARE
                v_dept_id NUMBER;
            BEGIN
                SELECT department_id INTO v_dept_id
                FROM departments
                WHERE department_name = :NEW.department_name
                AND ROWNUM = 1;
                
                UPDATE employees
                SET department_id = v_dept_id
                WHERE employee_id = :OLD.employee_id;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    RAISE_APPLICATION_ERROR(-20001, '部门 ' || :NEW.department_name || ' 不存在');
            END;
        END IF;
    ELSIF DELETING THEN
        -- 删除员工
        DELETE FROM employees
        WHERE employee_id = :OLD.employee_id;
    END IF;
END;
/

-- 测试INSTEAD OF触发器
-- 插入测试(会触发触发器)
INSERT INTO v_emp_dept (employee_id, name, salary, department_name)
VALUES (2001, '测试员工', 5000, 'IT部门');

-- 查看触发器状态
SELECT trigger_name, trigger_type, status FROM user_triggers 
WHERE table_name = 'V_EMP_DEPT';

8.3 系统事件触发器

-- 创建登录触发器
CREATE OR REPLACE TRIGGER trg_after_logon
AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO session_audit (
        username,
        login_time,
        host,
        program
    ) VALUES (
        USER,
        SYSDATE,
        SYS_CONTEXT('USERENV', 'HOST'),
        SYS_CONTEXT('USERENV', 'MODULE')
    );
    
    -- 设置会话级参数
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''';
END;
/

-- 创建DDL触发器
CREATE OR REPLACE TRIGGER trg_before_ddl
BEFORE DDL ON DATABASE
BEGIN
    INSERT INTO ddl_audit (
        username,
        event_date,
        object_type,
        object_name,
        ddl_command
    ) VALUES (
        ORA_LOGIN_USER,
        SYSDATE,
        ORA_DICT_OBJ_TYPE,
        ORA_DICT_OBJ_NAME,
        ORA_SYSEVENT
    );
END;
/

-- 查看系统触发器
SELECT trigger_name, trigger_type, triggering_event, status
FROM all_triggers
WHERE owner = 'SYS' AND table_name = 'DATABASE';

-- 删除系统触发器
DROP TRIGGER trg_after_logon;

九、DM8性能优化

9.1 SQL执行计划

-- 解释计划
EXPLAIN PLAN FOR
SELECT e.name, d.department_name, j.job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id
WHERE e.salary > 10000;

-- 查看解释计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 自动跟踪
SET AUTOTRACE ON;

-- 执行查询
SELECT * FROM employees WHERE department_id = 10;

-- 关闭自动跟踪
SET AUTOTRACE OFF;

-- 查询执行计划历史
SELECT * FROM v$sql_plan WHERE sql_id = 'your_sql_id';

-- 使用SQL调优顾问
DECLARE
    v_task_name VARCHAR2(30);
    v_sql_id VARCHAR2(13) := 'your_sql_id';
BEGIN
    v_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id => v_sql_id,
        scope => 'COMPREHENSIVE',
        time_limit => 60,
        description => 'Tuning task for SQL ID ' || v_sql_id
    );
    
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => v_task_name);
END;
/

-- 查看调优建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('your_task_name') FROM dual;

9.2 索引优化

-- 创建函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));

-- 创建位图索引(适合低基数列)
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- 创建压缩索引
CREATE INDEX idx_emp_dept_comp ON employees(department_id) COMPRESS;

-- 监控索引使用
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 查看索引使用情况
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_NAME';

-- 重建索引
ALTER INDEX idx_emp_name REBUILD ONLINE;

-- 并行创建索引
CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary) PARALLEL 4;

-- 查看索引统计信息
SELECT 
    index_name,
    blevel,
    leaf_blocks,
    distinct_keys,
    clustering_factor
FROM 
    user_indexes
WHERE 
    table_name = 'EMPLOYEES';

9.3 SQL优化技巧

-- 使用绑定变量
-- 不好的写法(硬解析)
BEGIN
    FOR i IN 1..100 LOOP
        EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE employee_id = ' || i;
    END LOOP;
END;
/

-- 好的写法(软解析)
BEGIN
    FOR i IN 1..100 LOOP
        EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE employee_id = :id' USING i;
    END LOOP;
END;
/

-- 避免全表扫描
-- 不好的写法
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';

-- 好的写法(如果有函数索引)
SELECT * FROM employees WHERE name = 'Smith';

-- 使用适当的JOIN方法
-- 嵌套循环连接(适合小表驱动大表)
SELECT /*+ ORDERED USE_NL(e) */ d.department_name, e.name
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND d.department_id = 10;

-- 哈希连接(适合大表连接)
SELECT /*+ USE_HASH(e d) */ d.department_name, e.name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 合并排序连接(适合已排序数据)
SELECT /*+ USE_MERGE(e d) */ d.department_name, e.name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 使用物化视图优化聚合查询
CREATE MATERIALIZED VIEW mv_emp_sal_summary
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT 
    department_id,
    COUNT(*) emp_count,
    SUM(salary) total_sal,
    AVG(salary) avg_sal
FROM 
    employees
GROUP BY 
    department_id;

-- 查询重写
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
-- 实际会从物化视图获取数据

9.4 统计信息收集

-- 手动收集表统计信息
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'HR',
        tabname => 'EMPLOYEES',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO',
        degree => DBMS_STATS.AUTO_DEGREE,
        cascade => TRUE
    );
END;
/

-- 收集索引统计信息
BEGIN
    DBMS_STATS.GATHER_INDEX_STATS(
        ownname => 'HR',
        indname => 'IDX_EMP_NAME'
    );
END;
/

-- 收集系统统计信息
BEGIN
    DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');
END;
/

-- 锁定统计信息(防止被覆盖)
BEGIN
    DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

-- 查看统计信息
SELECT 
    table_name,
    num_rows,
    blocks,
    avg_row_len,
    last_analyzed
FROM 
    user_tables
WHERE 
    table_name = 'EMPLOYEES';

SELECT 
    column_name,
    num_distinct,
    density,
    num_nulls,
    histogram
FROM 
    user_tab_columns
WHERE 
    table_name = 'EMPLOYEES';

十、DM8安全管理

10.1 用户管理

-- 创建用户
CREATE USER dev_user 
IDENTIFIED BY "StrongPassword123!"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
PASSWORD EXPIRE;

-- 修改用户
ALTER USER dev_user 
QUOTA 200M ON users
IDENTIFIED BY "NewPassword456!";

-- 锁定/解锁用户
ALTER USER dev_user ACCOUNT LOCK;
ALTER USER dev_user ACCOUNT UNLOCK;

-- 删除用户
DROP USER dev_user CASCADE;  -- CASCADE会删除用户的所有对象

-- 查看用户信息
SELECT username, account_status, created, default_tablespace
FROM dba_users
WHERE username = 'DEV_USER';

-- 查看用户权限
SELECT * FROM dba_sys_privs WHERE grantee = 'DEV_USER';
SELECT * FROM dba_role_privs WHERE grantee = 'DEV_USER';
SELECT * FROM dba_tab_privs WHERE grantee = 'DEV_USER';

10.2 权限管理

-- 系统权限授予
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO dev_user;

-- 对象权限授予
GRANT SELECT, INSERT, UPDATE ON hr.employees TO dev_user;
GRANT ALL ON hr.departments TO dev_user;

-- 列级权限授予
GRANT UPDATE (name, email) ON hr.employees TO dev_user;

-- 权限回收
REVOKE CREATE TABLE FROM dev_user;
REVOKE UPDATE ON hr.employees FROM dev_user;

-- 查看权限信息
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;
SELECT * FROM user_col_privs;

10.3 角色管理

-- 创建角色
CREATE ROLE emp_read_only;
CREATE ROLE emp_read_write;

-- 为角色授权
GRANT SELECT ON hr.employees TO emp_read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO emp_read_write;
GRANT CREATE SESSION TO emp_read_only, emp_read_write;

-- 将角色授予用户
GRANT emp_read_only TO dev_user;
GRANT emp_read_write TO manager_user;

-- 启用/禁用角色
SET ROLE emp_read_only;
SET ROLE ALL EXCEPT emp_read_write;

-- 修改角色
ALTER ROLE emp_read_only IDENTIFIED BY "RolePassword123";

-- 删除角色
DROP ROLE emp_read_only;

-- 查看角色信息
SELECT * FROM dba_roles;
SELECT * FROM dba_role_privs WHERE grantee = 'DEV_USER';
SELECT * FROM role_tab_privs WHERE role = 'EMP_READ_ONLY';

10.4 审计

-- 启用数据库审计
ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;

-- 重启数据库使审计生效
SHUTDOWN IMMEDIATE;
STARTUP;

-- 审计对象操作
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees BY ACCESS;

-- 审计系统权限
AUDIT CREATE TABLE, CREATE VIEW BY dev_user;

-- 审计角色
AUDIT emp_read_write BY ACCESS;

-- 查看审计记录
SELECT username, action_name, obj_name, timestamp
FROM dba_audit_trail
ORDER BY timestamp DESC;

-- 细粒度审计(FGA)
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema => 'HR',
        object_name => 'EMPLOYEES',
        policy_name => 'EMP_SALARY_ACCESS',
        audit_condition => 'salary > 10000',
        audit_column => 'SALARY',
        handler_schema => NULL,
        handler_module => NULL,
        enable => TRUE,
        statement_types => 'SELECT'
    );
END;
/

-- 查看FGA审计记录
SELECT db_user, object_name, sql_text, timestamp
FROM dba_fga_audit_trail
ORDER BY timestamp DESC;

-- 停止审计
NOAUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees;

十一、DM8备份与恢复

11.1 逻辑备份与恢复

-- 使用dexp工具进行导出(命令行执行)
dexp USERID=sysdba/SYSDBA@localhost:5236 FILE=full_db.dmp LOG=full_db.log FULL=Y

-- 按用户导出
dexp USERID=hr/hr_password@localhost:5236 FILE=hr_schema.dmp LOG=hr_schema.log OWNER=hr

-- 按表导出
dexp USERID=hr/hr_password@localhost:5236 FILE=emp_dept.dmp LOG=emp_dept.log TABLES=(employees,departments)

-- 使用dimp工具进行导入
dimp USERID=sysdba/SYSDBA@localhost:5236 FILE=full_db.dmp LOG=imp_full_db.log FULL=Y

-- 导入特定表
dimp USERID=hr/hr_password@localhost:5236 FILE=emp_dept.dmp LOG=imp_emp_dept.log TABLES=(employees,departments)

-- 导入时跳过已存在对象
dimp USERID=hr/hr_password@localhost:5236 FILE=hr_schema.dmp LOG=imp_hr_schema.log OWNER=hr IGNORE=Y

11.2 表空间备份与恢复

-- 查看表空间信息
SELECT tablespace_name, status, contents, extent_management 
FROM dba_tablespaces;

-- 将表空间置于备份模式
ALTER TABLESPACE users BEGIN BACKUP;

-- 使用操作系统工具复制数据文件
-- (在操作系统命令行执行)
-- cp /dmdata/DAMENG/users01.dbf /backup/users01.dbf.bak

-- 结束表空间备份模式
ALTER TABLESPACE users END BACKUP;

-- 恢复表空间
-- 1. 将表空间离线
ALTER TABLESPACE users OFFLINE IMMEDIATE;

-- 2. 使用备份文件替换损坏的文件
-- (在操作系统命令行执行)
-- cp /backup/users01.dbf.bak /dmdata/DAMENG/users01.dbf

-- 3. 恢复表空间
RECOVER TABLESPACE users;

-- 4. 将表空间在线
ALTER TABLESPACE users ONLINE;

11.3 RMAN备份与恢复

-- 连接到RMAN(命令行执行)
rman TARGET sysdba/SYSDBA@localhost:5236

-- 完整数据库备份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- 增量备份
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- 备份表空间
RMAN> BACKUP TABLESPACE users;

-- 备份归档日志
RMAN> BACKUP ARCHIVELOG ALL;

-- 查看备份信息
RMAN> LIST BACKUP;

-- 数据库恢复
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

-- 表空间恢复
RMAN> SQL 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

-- 数据文件恢复
RMAN> SQL 'ALTER DATABASE DATAFILE 5 OFFLINE';
RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;
RMAN> SQL 'ALTER DATABASE DATAFILE 5 ONLINE';

11.4 闪回技术

-- 启用闪回(需要先配置闪回区)
ALTER DATABASE FLASHBACK ON;

-- 查看闪回状态
SELECT flashback_on FROM v$database;

-- 闪回查询(查看过去某个时间点的数据)
SELECT * FROM employees 
AS OF TIMESTAMP TO_TIMESTAMP('2023-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE employee_id = 1001;

-- 闪回表(将表恢复到过去某个时间点)
FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2023-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- 闪回删除(恢复被DROP的表)
FLASHBACK TABLE employees TO BEFORE DROP;

-- 闪回数据库(将整个数据库回退到过去某个时间点)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2023-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS');
ALTER DATABASE OPEN RESETLOGS;

-- 查看回收站中的对象(闪回删除用)
SELECT object_name, original_name, type, droptime FROM user_recyclebin;

十二、DM8高可用与分布式

12.1 数据守护(DMDataWatch)

-- 在主库上配置
-- 1. 修改dm.ini参数
ALTER SYSTEM SET 'DW_ENABLE'=1 SCOPE=SPFILE;
ALTER SYSTEM SET 'DW_PORT'=52141 SCOPE=SPFILE;

-- 2. 配置dmwatcher.ini
/*
[GRP1]
DW_TYPE = LOCAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453331
INST_INI = /dmdata/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dmserver /dmdata/DAMENG/dm.ini
*/

-- 3. 启动守护进程
dmwatcher /dmdata/DAMENG/dmwatcher.ini

-- 在备库上执行类似配置

-- 查看数据守护状态
SELECT * FROM v$dmwatcher;
SELECT * FROM v$dataguard_stats;

12.2 读写分离

-- 配置dm_svc.conf文件
/*
[DM_READWRITE]
    SERVICE=(192.168.1.101:5236,192.168.1.102:5236,192.168.1.103:5236)
    RW_SEPARATE=1
    RW_PERCENT=25
    SWITCH_TIME=3
    SWITCH_TYPE=1
*/

-- 应用连接时使用服务名
-- jdbc:dm://DM_READWRITE

-- 在应用层实现读写分离
-- 使用不同的数据源配置读写和只读连接

12.3 分布式数据库

-- 创建分布式数据库链接
CREATE DATABASE LINK remote_db 
CONNECT TO remote_user IDENTIFIED BY "password"
USING '192.168.1.200:5236';

-- 分布式查询
SELECT e.name, d.department_name
FROM employees e
JOIN departments@remote_db d ON e.department_id = d.department_id;

-- 分布式事务
BEGIN
    -- 本地操作
    UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1001;
    
    -- 远程操作
    UPDATE accounts@remote_db SET balance = balance + 1000 WHERE account_id = 2001;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;
/

-- 创建分布式分区表
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    customer_id NUMBER,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')) 
        STORAGE ON remote_db,
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- 查看分布式事务状态
SELECT * FROM v$global_transaction;

12.4 水平分片

-- 创建水平分片表组
CREATE SHARD GROUP sg_east DATABASE LINK east_db;
CREATE SHARD GROUP sg_west DATABASE LINK west_db;

-- 创建水平分片表
CREATE SHARD TABLE customers (
    customer_id NUMBER,
    name VARCHAR2(50),
    region VARCHAR2(10),
    create_date DATE
) 
PARTITION BY LIST (region) (
    PARTITION p_east VALUES ('EAST') STORAGE ON sg_east,
    PARTITION p_west VALUES ('WEST') STORAGE ON sg_west
);

-- 插入数据(自动路由到正确分片)
INSERT INTO customers VALUES (1001, '张三', 'EAST', SYSDATE);
INSERT INTO customers VALUES (1002, '李四', 'WEST', SYSDATE);

-- 分片表查询(自动合并结果)
SELECT * FROM customers WHERE region = 'EAST';
SELECT * FROM customers ORDER BY customer_id;

十三、DM8开发接口

13.1 JDBC连接

// Java JDBC连接示例
import java.sql.*;

public class DM8JdbcDemo {
    public static void main(String[] args) {
        String url = "jdbc:dm://localhost:5236/DAMENG";
        String user = "SYSDBA";
        String password = "SYSDBA";
        
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("连接达梦数据库成功!");
            
            // 执行查询
            String sql = "SELECT employee_id, name, salary FROM employees WHERE department_id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, 10);
                
                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {
                    int empId = rs.getInt("employee_id");
                    String name = rs.getString("name");
                    double salary = rs.getDouble("salary");
                    
                    System.out.printf("ID: %d, Name: %s, Salary: %.2f%n", empId, name, salary);
                }
            }
            
            // 执行更新
            String updateSql = "UPDATE employees SET salary = salary * 1.1 WHERE employee_id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
                pstmt.setInt(1, 1001);
                int rows = pstmt.executeUpdate();
                System.out.println("更新了 " + rows + " 行记录");
                
                conn.commit();
            }
        } catch (SQLException e) {
            System.err.println("数据库错误:");
            e.printStackTrace();
        }
    }
}

13.2 ODBC连接

# Python ODBC连接示例
import pyodbc

# 配置ODBC数据源后连接
conn = pyodbc.connect('DSN=DM8_ODBC;UID=SYSDBA;PWD=SYSDBA')

cursor = conn.cursor()

# 执行查询
cursor.execute("SELECT name, salary FROM employees WHERE department_id = ?", 10)
for row in cursor:
    print(f"Name: {row.name}, Salary: {row.salary}")

# 执行插入
cursor.execute("""
    INSERT INTO employees (employee_id, name, hire_date, salary)
    VALUES (?, ?, ?, ?)
""", (2001, '王五', '2023-01-15', 8500))

conn.commit()
conn.close()

13.3 DPI编程接口

// C语言DPI示例
#include <stdio.h>
#include <dmapi.h>

int main() {
    dhcon handle;
    dhstmt stmt;
    int ret;
    
    // 连接数据库
    ret = dh_connect(&handle, "SYSDBA", "SYSDBA", "localhost:5236");
    if (ret != DH_SUCCESS) {
        printf("连接失败: %d\n", ret);
        return -1;
    }
    
    // 准备语句
    ret = dh_alloc_stmt(handle, &stmt);
    if (ret != DH_SUCCESS) {
        printf("分配语句句柄失败\n");
        dh_disconnect(handle);
        return -1;
    }
    
    // 执行查询
    ret = dh_exec_direct(stmt, "SELECT name, salary FROM employees WHERE department_id = 10");
    if (ret != DH_SUCCESS) {
        printf("执行查询失败\n");
        dh_free_stmt(stmt);
        dh_disconnect(handle);
        return -1;
    }
    
    // 绑定列
    char name[51];
    double salary;
    dh_bind_col(stmt, 1, DH_SQL_VARCHAR, name, sizeof(name), NULL);
    dh_bind_col(stmt, 2, DH_SQL_DOUBLE, &salary, 0, NULL);
    
    // 获取结果
    while ((ret = dh_fetch(stmt)) == DH_SUCCESS) {
        printf("Name: %s, Salary: %.2f\n", name, salary);
    }
    
    // 释放资源
    dh_free_stmt(stmt);
    dh_disconnect(handle);
    
    return 0;
}

13.4 嵌入式SQL

// C语言嵌入式SQL示例
#include <stdio.h>
EXEC SQL INCLUDE SQLCA;

int main() {
    EXEC SQL BEGIN DECLARE SECTION;
        char username[20] = "SYSDBA";
        char password[20] = "SYSDBA";
        char connstr[50] = "localhost:5236";
        int dept_id = 10;
        char emp_name[50];
        double salary;
    EXEC SQL END DECLARE SECTION;
    
    // 连接数据库
    EXEC SQL CONNECT :username IDENTIFIED BY :password USING :connstr;
    if (sqlca.sqlcode != 0) {
        printf("连接失败: %s\n", sqlca.sqlerrm.sqlerrmc);
        return -1;
    }
    
    // 声明游标
    EXEC SQL DECLARE emp_cursor CURSOR FOR
        SELECT name, salary FROM employees WHERE department_id = :dept_id;
    
    // 打开游标
    EXEC SQL OPEN emp_cursor;
    if (sqlca.sqlcode != 0) {
        printf("打开游标失败: %s\n", sqlca.sqlerrm.sqlerrmc);
        EXEC SQL DISCONNECT;
        return -1;
    }
    
    // 获取数据
    printf("部门 %d 的员工:\n", dept_id);
    while (1) {
        EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
        if (sqlca.sqlcode != 0) break;
        
        printf("Name: %s, Salary: %.2f\n", emp_name, salary);
    }
    
    // 关闭游标
    EXEC SQL CLOSE emp_cursor;
    
    // 断开连接
    EXEC SQL DISCONNECT;
    
    return 0;
}

十四、DM8 SQL最佳实践

14.1 编写高效SQL

1. 使用绑定变量：避免硬解析，提高共享池利用率
2. 合理使用索引：为高频查询条件创建适当索引
3. 避免全表扫描：确保查询能使用索引
4. 减少网络传输：只查询需要的列
5. 使用批量操作：减少往返次数
6. 优化JOIN操作：小表驱动大表
7. 合理使用子查询：避免嵌套过深

-- 好的实践示例
-- 使用绑定变量
PREPARE stmt FROM 'SELECT * FROM employees WHERE department_id = ?';
EXECUTE stmt USING 10;

-- 只查询需要的列
SELECT employee_id, name FROM employees;

-- 使用批量插入
INSERT ALL
    INTO employees VALUES (2001, '张三', ...)
    INTO employees VALUES (2002, '李四', ...)
    INTO employees VALUES (2003, '王五', ...)
SELECT 1 FROM DUAL;

-- 使用EXISTS代替IN(对于大表)
SELECT d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e 
    WHERE e.department_id = d.department_id
    AND e.salary > 10000
);

14.2 SQL反模式

1. **SELECT ***：查询不需要的列
2. 过度使用游标：尽量用集合操作代替
3. 在索引列上使用函数：导致索引失效
4. 过度使用DISTINCT：可能表明设计问题
5. 嵌套过深的子查询：难以维护和优化
6. 忽略NULL处理：导致意外结果
7. 隐式类型转换：影响性能

-- 反模式示例
-- 反模式1: SELECT *
SELECT * FROM employees WHERE department_id = 10;

-- 反模式2: 索引列上使用函数
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';

-- 反模式3: 过度使用DISTINCT
SELECT DISTINCT d.department_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id;

-- 反模式4: 忽略NULL处理
SELECT name, salary + commission_pct FROM employees;

-- 反模式5: 隐式类型转换
SELECT * FROM employees WHERE employee_id = '1001';

14.3 性能监控SQL

-- 查询高负载SQL
SELECT 
    sql_id,
    sql_text,
    executions,
    round(elapsed_time/1000000, 2) elapsed_sec,
    round(elapsed_time/decode(executions, 0, 1, executions)/1000000, 4) avg_sec,
    buffer_gets,
    disk_reads
FROM 
    v$sqlarea
WHERE 
    executions > 0
ORDER BY 
    elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

-- 查询锁等待
SELECT 
    l.session_id,
    s.username,
    o.object_name,
    l.oracle_username,
    l.locked_mode,
    s.status,
    s.machine,
    s.program
FROM 
    v$locked_object l
JOIN 
    dba_objects o ON l.object_id = o.object_id
JOIN 
    v$session s ON l.session_id = s.sid
ORDER BY 
    l.session_id;

-- 查询会话资源使用
SELECT 
    s.sid,
    s.username,
    s.status,
    s.machine,
    s.program,
    t.used_ublk,
    t.used_urec,
    t.start_time
FROM 
    v$session s
JOIN 
    v$transaction t ON s.taddr = t.addr
ORDER BY 
    t.used_ublk DESC;

-- 查询表空间使用
SELECT 
    t.tablespace_name,
    round(t.total_mb, 2) total_mb,
    round(t.total_mb - f.free_mb, 2) used_mb,
    round((t.total_mb - f.free_mb) / t.total_mb * 100, 2) pct_used
FROM 
    (SELECT 
        tablespace_name,
        sum(bytes)/1024/1024 total_mb
     FROM 
        dba_data_files
     GROUP BY 
        tablespace_name) t,
    (SELECT 
        tablespace_name,
        sum(bytes)/1024/1024 free_mb
     FROM 
        dba_free_space
     GROUP BY 
        tablespace_name) f
WHERE 
    t.tablespace_name = f.tablespace_name
ORDER BY 
    pct_used DESC;

14.4 数据库设计建议

1. 规范化设计：遵循3NF，避免冗余
2. 适当反规范化：对性能关键表考虑反规范化
3. 合理数据类型：使用最合适的数据类型
4. 主键设计：使用无意义自增主键
5. 索引策略：基于查询模式创建索引
6. 分区设计：对大表考虑分区
7. 命名规范：使用一致的命名约定

-- 好的设计示例
-- 规范化设计
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER REFERENCES customers(customer_id),
    order_date DATE,
    total_amount NUMBER(12,2)
);

CREATE TABLE order_items (
    item_id NUMBER,
    order_id NUMBER REFERENCES orders(order_id),
    product_id NUMBER REFERENCES products(product_id),
    quantity NUMBER,
    unit_price NUMBER(10,2),
    PRIMARY KEY (order_id, item_id)
);

-- 适当反规范化(减少JOIN)
CREATE TABLE order_summary (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    customer_name VARCHAR2(100),  -- 反规范化字段
    order_date DATE,
    total_amount NUMBER(12,2),
    item_count NUMBER
);

-- 分区设计
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    customer_id NUMBER,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

十五、DM8新特性与未来发展

15.1 DM8新特性

1. 增强的SQL兼容性：更好兼容Oracle、MySQL等语法
2. 性能优化：查询优化器改进，并行处理增强
3. JSON支持：原生JSON数据类型和操作函数
4. 分区表增强：更多分区策略，分区交换
5. 内存优化：列存储引擎，内存表
6. 安全增强：透明数据加密，数据脱敏
7. 高可用改进：自动故障转移，读写分离优化

-- JSON支持示例
CREATE TABLE products (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    attributes JSON  -- JSON数据类型
);

INSERT INTO products VALUES (
    1,
    '智能手机',
    '{"color":"黑色","memory":"128GB","brand":"华为"}'
);

-- JSON查询
SELECT 
    id,
    name,
    json_value(attributes, '$.color') color,
    json_value(attributes, '$.memory') memory
FROM 
    products
WHERE 
    json_exists(attributes, '$.brand');

-- 内存表示例
CREATE MEMORY TABLE session_data (
    session_id VARCHAR2(100) PRIMARY KEY,
    user_id NUMBER,
    login_time TIMESTAMP,
    data JSON
);

-- 列存储表示例
CREATE COLUMN TABLE sales_fact (
    sale_id NUMBER,
    product_id NUMBER,
    sale_date DATE,
    quantity NUMBER,
    amount NUMBER(10,2)
);

15.2 未来发展方向

1. 云原生支持：更好的Kubernetes集成，微服务架构
2. 多模型数据库：支持图数据、时序数据等
3. AI集成：内置机器学习算法，智能优化
4. 分布式增强：全局事务，跨库查询优化
5. HTAP能力：混合事务分析处理
6. 开发者体验：更好工具链支持，简化开发

15.3 升级与迁移

-- 兼容性检查
SELECT * FROM v$compatibility_level;

-- 迁移工具使用
-- 使用DTS工具迁移Oracle到DM8
/*
1. 安装DTS工具
2. 创建迁移项目
3. 配置源库(Oracle)和目标库(DM8)连接
4. 选择迁移对象
5. 转换数据类型和SQL语法
6. 执行迁移
7. 验证数据一致性
*/

-- 迁移后验证
-- 1. 检查对象数量
SELECT object_type, COUNT(*) 
FROM dba_objects 
WHERE owner = 'HR'
GROUP BY object_type;

-- 2. 检查数据量
SELECT table_name, num_rows 
FROM dba_tables 
WHERE owner = 'HR'
ORDER BY table_name;

-- 3. 抽样验证数据
SELECT COUNT(*) FROM hr.employees;
SELECT * FROM hr.employees FETCH FIRST 10 ROWS ONLY;

十六、常见问题与解决方案

16.1 连接问题

-- 问题1: 连接被拒绝
-- 解决方案:
-- 1. 检查DM8服务是否启动
-- 2. 检查端口是否正确(默认5236)
-- 3. 检查防火墙设置
-- 4. 检查用户权限

-- 问题2: 用户被锁定
-- 解决方案:
ALTER USER username ACCOUNT UNLOCK;

-- 问题3: 密码过期
-- 解决方案:
ALTER USER username IDENTIFIED BY new_password;

16.2 性能问题

-- 问题1: 查询慢
-- 解决方案:
-- 1. 检查执行计划
EXPLAIN PLAN FOR your_sql;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 2. 添加适当索引
CREATE INDEX idx_name ON table_name(column_name);

-- 3. 优化SQL语句

-- 问题2: 高CPU使用率
-- 解决方案:
-- 1. 识别高负载SQL
SELECT * FROM v$sqlarea ORDER BY cpu_time DESC;

-- 2. 优化或终止问题会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 问题3: 内存不足
-- 解决方案:
-- 1. 调整内存参数
ALTER SYSTEM SET memory_target=4G SCOPE=SPFILE;

-- 2. 重启数据库
SHUTDOWN IMMEDIATE;
STARTUP;

16.3 数据一致性问题

-- 问题1: 外键约束违反
-- 解决方案:
-- 1. 检查违反的数据
SELECT * FROM child_table WHERE foreign_key_column NOT IN (
    SELECT primary_key_column FROM parent_table
);

-- 2. 修复数据或约束

-- 问题2: 唯一约束违反
-- 解决方案:
-- 1. 查找重复值
SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name 
HAVING COUNT(*) > 1;

-- 2. 处理重复数据

-- 问题3: 事务阻塞
-- 解决方案:
-- 1. 识别阻塞会话
SELECT * FROM v$lock WHERE block = 1;

-- 2. 终止阻塞会话或提交事务

16.4 备份恢复问题

-- 问题1: 备份失败
-- 解决方案:
-- 1. 检查磁盘空间
-- 2. 检查权限
-- 3. 检查归档日志模式

-- 问题2: 恢复失败
-- 解决方案:
-- 1. 确保使用兼容的备份
-- 2. 检查恢复日志
-- 3. 考虑不完全恢复

-- 问题3: 数据文件损坏
-- 解决方案:
-- 1. 使用备份恢复
-- 2. 使用DBMS_REPAIR包修复
BEGIN
    DBMS_REPAIR.ADMIN_TABLES(
        table_name => 'REPAIR_TABLE',
        table_type => DBMS_REPAIR.REPAIR_TABLE,
        action => DBMS_REPAIR.CREATE_ACTION
    );
END;
/

十七、总结与资源

17.1 达梦DM8 SQL核心要点总结

1. 兼容性：DM8高度兼容SQL标准，同时兼容Oracle、MySQL等常用语法
2. 性能：提供丰富的优化手段，包括索引、分区、物化视图等
3. 可扩展性：支持存储过程、函数、包等编程扩展
4. 安全性：完善的用户权限体系和数据保护机制
5. 高可用：数据守护、读写分离、分布式等企业级特性
6. 管理性：全面的监控和调优工具

17.2 学习资源推荐

1. 官方文档：达梦数据库官方文档最权威
2. 培训课程：达梦认证培训课程
3. 社区论坛：达梦技术社区和开发者论坛
4. 书籍：《达梦数据库管理与优化》、《达梦数据库SQL指南》
5. 在线实验：达梦官方提供的在线实验环境

17.3 实践建议

1. 从小开始：从简单查询开始，逐步掌握复杂特性
2. 实验环境：搭建测试环境进行实践
3. 性能测试：对比不同写法的性能差异
4. 参与社区：加入用户组，交流学习经验
5. 持续学习：关注新版本特性更新

-- 最后的祝福
BEGIN
    DBMS_OUTPUT.PUT_LINE('祝贺您完成达梦DM8 SQL学习之旅!');
    DBMS_OUTPUT.PUT_LINE('愿您在数据库开发和管理中取得优异成绩!');
    DBMS_OUTPUT.PUT_LINE('记住: 实践是最好的老师，不断尝试和优化是关键。');
END;
/