### Create SQL Partitioned Tables (Range, List, Hash, Composite, Interval)

Source: https://context7.com/tomllt/dmdoc/llms.txt

Provides SQL DDL statements for creating various types of partitioned tables, including range partitioning by date, list partitioning by job ID, hash partitioning by email, composite range-hash partitioning, and interval partitioning for automatic partition creation. It also includes examples of partition maintenance and querying partition information.

```sql
-- Range partitioning by hire_date
CREATE TABLE dmhr.rp_hiredt_emp
(
   employee_id      INT PRIMARY KEY,
   employee_name    VARCHAR(20),
   hire_date        DATE NOT NULL,
   salary           INT,
   department_id    INT,
   CHECK (salary > 0)
)
PARTITION BY RANGE(hire_date)
(
   PARTITION p1 VALUES LESS THAN ('2010-1-1'),
   PARTITION p2 VALUES LESS THAN ('2015-1-1'),
   PARTITION p3 VALUES LESS THAN ('2016-1-1'),
   PARTITION pmax VALUES LESS THAN (MAXVALUE)
)
STORAGE (NOBRANCH);

INSERT INTO dmhr.rp_hiredt_emp SELECT * FROM dmhr.employee;

-- List partitioning by job_id
CREATE TABLE dmhr.lp_job_emp
(
   employee_id      INT PRIMARY KEY,
   job_id           VARCHAR(10) NOT NULL,
   salary           INT
)
PARTITION BY LIST(job_id)
(
   PARTITION p1 VALUES ('11','12','21','22'),
   PARTITION p2 VALUES ('31','32','41','42'),
   PARTITION pmax VALUES (DEFAULT)
)
STORAGE (NOBRANCH);

-- Hash partitioning by email
CREATE TABLE dmhr.hp_email_emp
(
   employee_id      INT PRIMARY KEY,
   email            VARCHAR(50) NOT NULL
)
PARTITION BY HASH(email)
(PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4)
STORAGE (NOBRANCH);

-- Composite partitioning: range-hash
CREATE TABLE dmhr.rhp_emp
(
   employee_id      INT PRIMARY KEY,
   hire_date        DATE NOT NULL,
   email            VARCHAR(50) NOT NULL
)
PARTITION BY RANGE(hire_date)
   SUBPARTITION BY HASH(email)
      SUBPARTITION TEMPLATE (SUBPARTITION p1, SUBPARTITION p2)
(
   PARTITION p1 VALUES LESS THAN ('2010-1-1'),
   PARTITION p2 VALUES LESS THAN ('2013-1-1'),
   PARTITION pmax VALUES LESS THAN (MAXVALUE)
)
STORAGE (NOBRANCH);

-- Interval partitioning: auto-create partitions
CREATE TABLE dmhr.emp_part
(
   EMPLOYEE_ID      INT PRIMARY KEY,
   HIRE_DATE        DATE NOT NULL
)
PARTITION BY RANGE(hire_date)
   INTERVAL(NUMTOYMINTERVAL(1, 'year'))
(
   PARTITION P_BEFORE_2007 VALUES LESS THAN (TO_DATE('2007-01-01', 'yyyy-mm-dd'))
);

-- Partition maintenance
ALTER TABLE dmhr.rp_hiredt_emp DROP PARTITION pmax;
ALTER TABLE dmhr.rp_hiredt_emp ADD PARTITION p_before_2017
  VALUES LESS THAN ('2017-1-1');
ALTER TABLE dmhr.rp_hiredt_emp MERGE PARTITIONS p3, p4 INTO PARTITION p3_4;
ALTER TABLE dmhr.rp_hiredt_emp SPLIT PARTITION p3_4 AT ('2009-01-01')
  INTO (PARTITION p3, PARTITION p4);

-- Query partition info
SELECT partitioned FROM dba_tables WHERE table_name='RP_HIREDT_EMP';
SELECT partition_name, high_value FROM dba_tab_partitions
 WHERE table_name='RP_HIREDT_EMP';
```

--------------------------------

### 基本层次查询：从上到下组织结构 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/19-层次查询.md

使用 START WITH 和 CONNECT BY 关键字列出从指定员工（employee_id = 100）开始的组织结构层级。LEVEL 伪列显示了节点的层级，ORDER SIBLINGS BY employee_id 确保同一层级的员工按 ID 排序。

```sql
SELECT employee_id,
                  employee_name,
                  job_title,
                  manager_id,
                  department_id,
                  LEVEL
             FROM dmhr.emp
       START WITH employee_id = 100
       CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_id;

```

--------------------------------

### SQL SYS_CONNECT_BY_PATH 函数演示

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/19-层次查询.md

使用 SYS_CONNECT_BY_PATH 函数从 START WITH 指定的根节点开始，遍历层次结构，并使用指定的分隔符（'/' 和 '=>'）构建从根节点到当前节点的路径字符串。该函数返回一个字符串，表示节点的遍历路径。

```sql
SELECT employee_id emp_id, employee_name emp_name,LEVEL lvl,
        SYS_CONNECT_BY_PATH(employee_name, '/') format1,
        SYS_CONNECT_BY_PATH(employee_name, '=>') format2
   FROM dmhr.emp
  START WITH employee_id=100
 CONNECT BY NOCYCLE PRIOR employee_id=manager_id;

```

--------------------------------

### Dameng SQL Single Table Queries

Source: https://context7.com/tomllt/dmdoc/llms.txt

Demonstrates basic SELECT operations for querying data from a single table in Dameng Database. Includes examples for viewing table structure, filtering, handling null values, and limiting results using both LIMIT and ROWNUM.

```sql
-- View table structure
SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE','DMHR') FROM dual;
-- Alternative method
SP_TABLEDEF('DMHR','EMPLOYEE');

-- Basic query with filtering
SELECT * FROM dmhr.employee WHERE hire_date > '2015-01-01';

-- Find null values
SELECT * FROM dmhr.employee WHERE commission_pct IS NULL;

-- Handle null values with NVL
SELECT employee_name, employee_id, NVL(commission_pct, 0) AS commission_pct
  FROM dmhr.employee;

-- Multiple conditions
SELECT * FROM dmhr.employee
 WHERE (department_id = 102 OR salary > 20000
        OR (department_id = 105 AND salary > 9000));

-- Limit results with LIMIT
SELECT * FROM dmhr.employee LIMIT 2;

-- Limit with ROWNUM
SELECT * FROM (SELECT ROWNUM AS rn, t.* FROM dmhr.employee t WHERE ROWNUM <= 10)
 WHERE rn = 2;
```

--------------------------------

### Create Interval Partitioned Table (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/18-分区表.md

Creates an interval partitioned table 'emp_part' in the 'dmhr' schema. It partitions employee data by 'hire_date' with a yearly interval, starting with a partition for dates before 2007-01-01. This is useful for managing time-series data.

```sql
CREATE TABLE dmhr.emp_part
(
   EMPLOYEE_ID      INT PRIMARY KEY,
   EMPLOYEE_NAME    VARCHAR (20),
   IDENTITY_CARD    VARCHAR (18),
   EMAIL            VARCHAR (50) NOT NULL,
   PHONE_NUM        VARCHAR (20),
   HIRE_DATE        DATE NOT NULL,
   JOB_ID           VARCHAR (10) NOT NULL,
   SALARY           INT,
   COMMISSION_PCT   INT,
   MANAGER_ID       INT,
   DEPARTMENT_ID    INT
)
PARTITION BY RANGE (hire_date)
   INTERVAL ( NUMTOYMINTERVAL (1, 'year') )
   (
      PARTITION
         P_BEFORE_2007
         VALUES LESS THAN (TO_DATE ('2007-01-01', 'yyyy-mm-dd')))
STORAGE (FILLFACTOR 85, BRANCH(32,32));

```

--------------------------------

### Create and Execute SQL Stored Procedures

Source: https://context7.com/tomllt/dmdoc/llms.txt

Demonstrates the creation of SQL stored procedures, both with and without input parameters. It includes steps to create a test table, define the procedures, execute them, and verify the results by querying the table.

```sql
-- Create test table
CREATE TABLE test_tab (id int primary key, name varchar(30));

-- Stored procedure with parameters
CREATE OR REPLACE PROCEDURE p_test(i in int)
AS j int;
BEGIN
FOR j in 1..i LOOP
INSERT INTO test_tab values(j,'p_test'||j);
END LOOP;
END;

-- Execute procedure
p_test(3);

-- Verify results
SELECT * FROM test_tab;
-- Output:
-- ID  NAME
-- 1   p_test1
-- 2   p_test2
-- 3   p_test3

-- Stored procedure without parameters
CREATE OR REPLACE PROCEDURE p_test2 AS j int;
BEGIN
FOR j in 10..13 LOOP
INSERT INTO test_tab values(j,'p_test2:'||j);
END LOOP;
END;

-- Execute parameterless procedure
p_test2;

SELECT * FROM test_tab;
-- Output now includes:
-- 10  p_test2:10
-- 11  p_test2:11
-- 12  p_test2:12
-- 13  p_test2:13
```

--------------------------------

### SQL: Determine Start and End Dates of Quarters

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/09-日期操作.md

This SQL statement calculates the start and end dates for quarters within a given year. It leverages ADD_MONTHS and TO_DATE functions to perform date arithmetic, useful for quarterly reporting.

```sql
SELECT sn AS 季度,
       (sn - 1) * 3 + 1 AS 开始月份,
       ADD_MONTHS (TO_DATE (年, 'yyyy'), (sn - 1) * 3) AS 开始日期,
       ADD_MONTHS (TO_DATE (年, 'yyyy'), sn * 3) - 1 AS 结束日期
  FROM (SELECT '2020' AS 年, LEVEL AS sn
              FROM DUAL
        CONNECT BY LEVEL <= 4);
```

--------------------------------

### SQL: 插入测试数据到 dmhr.detail 表

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

向 dmhr.detail 表中插入初始预算费用和模拟的支出记录。该插入语句通过 UNION ALL 和子查询生成多条记录，模拟消费流水账。

```sql
INSERT INTO dmhr.detail VALUES(100,'预算费用',1000000);
commit;
INSERT INTO dmhr.detail SELECT employee_id as 编号,'支出' || rownum as 项目,
salary+1000 as 金额 FROM dmhr.employee WHERE job_id=11;
commit;
```

--------------------------------

### 创建时间触发器示例 (DM SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/11-触发器.md

演示如何创建一个时间触发器，该触发器每隔一分钟在屏幕上输出 'HELLO WORLD'。这展示了 DM 数据库在定时任务方面的灵活性。

```sql
CREATE OR REPLACE TRIGGER timer2
AFTER TIMER on database
for each 1 day for each 1 minute
BEGIN
print 'HELLO WORLD';
END;
```

--------------------------------

### SQL - 按多个字段排序

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/03-查询结果排序.md

根据多个字段对查询结果进行排序，可以指定升序（ASC）或降序（DESC）。当第一个排序字段存在重复时，第二个排序字段才生效。

```sql
SELECT employee_name, hire_date, salary
FROM dmhr.employee
WHERE ROWNUM < 5
ORDER BY 2 ASC, 3 DESC;
```

--------------------------------

### SQL: Calculate Departmental Salary Sum and Percentage of Total

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

This SQL query calculates the total salary for each department and then determines that department's total salary as a percentage of the overall total salary across all departments. It uses `GROUP BY` for departmental sums and then applies a window function `SUM() OVER()` to get the grand total.

```sql
SELECT job_id, 工资合计, SUM (工资合计) OVER () AS 总合计
  FROM (
    SELECT job_id, SUM (salary) 工资合计
      FROM dmhr.employee
  GROUP BY job_id
  ) x;
```

--------------------------------

### SQL: 转换支出金额为负数

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

使用 WITH 子句和 CASE 语句，将 dmhr.detail 表中的支出金额（非预算费用）转换为负数。此转换是为计算余额做准备，将支出视为负增长。

```sql
WITH x
     AS (SELECT ROWNUM AS seq, a.*
           FROM (  SELECT 编号, 项目, 金额
                     FROM dmhr.detail
                 ORDER BY 编号) a)
SELECT 编号,项目,金额,
       (CASE WHEN seq = 1 THEN 金额 ELSE -金额 END) AS 转换后的值
  FROM x;
```

--------------------------------

### Extract Nth Substring using REGEXP_SUBSTR in SQL

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/06-如何使用字符串.md

This SQL snippet demonstrates how to extract the Nth substring from a delimited string using REGEXP_SUBSTR. It utilizes a regular expression to split the string by a delimiter (comma in this case) and extracts a specific occurrence. The function requires the string, the pattern, the starting position, and the occurrence number.

```sql
CREATE OR REPLACE VIEW v AS SELECT 'CLARK,KATE,JAMES' AS name;

SELECT REGEXP_SUBSTR (v.name,'[^,]+',1,2) AS 子串 FROM v;
```

--------------------------------

### SQL: 查询 dmhr.detail 表数据

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

查询 dmhr.detail 表中的所有数据，用于展示已插入的预算费用和支出记录，为后续计算余额做准备。

```sql
SELECT * FROM dmhr.detail;
```

--------------------------------

### SQL: Get First and Last Employee by Salary within Job ID

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

This SQL query uses the `LAST_VALUE` window function to find the employee with the highest salary within each job ID, ordered by salary. It also displays the employee's name and salary. Note that `LAST_VALUE` with `ORDER BY salary` within `OVER()` behaves similarly to a cumulative mode, and `FIRST_VALUE` might be needed for the first record.

```sql
SELECT job_id,employee_id,
       LAST_VALUE (employee_name) OVER (PARTITION BY job_id ORDER BY salary)
          AS 工资最高的人,
       employee_name,
       salary
  FROM dmhr.employee
 WHERE job_id = 11
ORDER BY 1, 5;
```

--------------------------------

### 层次查询：CONNECT_BY_ISLEAF 和 CONNECT_BY_ROOT 伪列 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/19-层次查询.md

查询所有 3 级节点员工的上级姓名，并使用 CONNECT_BY_ISLEAF 判断是否为叶子节点，CONNECT_BY_ROOT 显示根节点员工姓名。

```sql
SELECT employee_id emp_id, employee_name emp_name,
       manager_id,LEVEL,
       CONNECT_BY_ISLEAF,
       CONNECT_BY_ROOT employee_name root_emp
  FROM dmhr.emp
 WHERE LEVEL=3
 START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;

```

--------------------------------

### 层次查询：使用 RPAD 和 LPAD 伪列 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/19-层次查询.md

结合 LEVEL 伪列和 Oracle 的 RPAD/LPAD 函数，实现层级缩进显示。RPAD 在左侧填充空格，LPAD 在左侧填充下划线，以可视化展示树形结构。

```sql
SELECT employee_id, employee_name, manager_id, department_id,LEVEL lvl,
       RPAD(' ',LEVEL*3)||employee_name level_with_rpad,
       LPAD(employee_name,length(employee_name)+(level*2)-2,'*') level_with_lpad
  FROM dmhr.emp
 START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;

```

--------------------------------

### 层次查询：无 NOCYCLE 导致的循环错误 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/19-层次查询.md

在存在循环引用的情况下执行层次查询，未加 NOCYCLE 关键字会导致查询报错。

```sql
SELECT employee_id emp_id, employee_name emp_name,job_title,
manager_id, department_id dept_id,LEVEL
  FROM dmhr.emp
 START WITH employee_id=102
CONNECT BY PRIOR employee_id=manager_id;

```

--------------------------------

### Configure and Use Dameng Database Links (DBLINK)

Source: https://context7.com/tomllt/dmdoc/llms.txt

Demonstrates how to configure dmmal.ini for DM-to-DM links and create/manage database links (DBLINK) for distributed queries and operations. Supports DM-to-DM, DM-to-Oracle, and ODBC connections, with notes on limitations.

```sql
-- Configure dmmal.ini for DM-to-DM links
-- [mal_inst1]
-- mal_inst_name  = DMSERVER
-- mal_host       = 127.0.0.1
-- mal_port       = 5282
-- [mal_inst2]
-- mal_inst_name  = DMSERVER2
-- mal_host       = 127.0.0.1
-- mal_port       = 5283

-- Create DM-to-DM database link
CREATE PUBLIC LINK LINK01
  CONNECT WITH SYSDBA IDENTIFIED BY *****
  USING '127.0.0.1/5282';

-- Insert data through link
INSERT INTO TEST@LINK01 VALUES(1,'A');
INSERT INTO TEST@LINK01 VALUES(2,'B');

-- Update through link
UPDATE TEST@LINK01 SET C2='C' WHERE C1=1;

-- Delete through link
DELETE FROM TEST@LINK01 WHERE C1=2;
COMMIT;

-- Query remote table
SELECT * FROM TEST@LINK01;

-- Create DM-to-Oracle link
CREATE LINK LINK1
  CONNECT 'ORACLE' WITH USER01 IDENTIFIED BY USER01PASSWD
  USING '127.0.0.1/orcl';

-- Drop database link
DROP LINK LINK1;

-- Notes:
-- - Supports DM-to-DM, DM-to-Oracle, and ODBC connections
-- - DM-DM links do not support MPP environment
-- - LOB types have limited support in DBLINK operations
-- - INTO statements not supported for DML operations
```

--------------------------------

### SQL: 使用聚集函数汇总员工薪资信息

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

使用 SQL 聚集函数（AVG, MIN, MAX, SUM, COUNT）查询每个部门员工的平均薪资、最小薪资、最大薪资、总工资及总记录数。此查询适用于对员工数据进行分组汇总分析。

```sql
SELECT job_id,
         AVG (salary) AS 平均值,
         MIN (salary) AS 最小值,
         MAX (salary) AS 最大值,
         SUM (salary) AS 工资合计,
         COUNT (*) AS 总行数
    FROM dmhr.employee
GROUP BY job_id;
```

--------------------------------

### SQL: 使用分析函数生成累计工资成本

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

使用 SQL 分析函数 SUM() OVER (ORDER BY ...) 按员工编号对员工工资进行累加，生成累计工资成本。此查询用于展示如何按顺序累加数值，了解用人成本随员工增加的变化。

```sql
SELECT employee_id AS 编号,
       employee_name AS 姓名,
       salary AS 人工成本,
       SUM (salary) OVER (ORDER BY employee_id) AS 成本累计
  FROM dmhr.employee
 WHERE job_id = 11;
```

--------------------------------

### 创建样例层次查询表 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/19-层次查询.md

使用 CREATE TABLE AS SELECT 语句基于现有表创建 emp 表，用于演示层次查询。该语句复制了 employees 和 jobs 表的部分数据，构建了包含员工 ID、姓名、职位、经理 ID 和部门 ID 的样例数据。

```sql
CREATE TABLE dmhr.emp
AS
    SELECT a.employee_id,
           a.first_name || ', ' || a.last_name employee_name,
           b.job_title,
           a.manager_id,
           a.department_id
      FROM dmhr.employees a, dmhr.jobs b
     WHERE a.job_id = b.job_id AND a.employee_id < 120;

```

--------------------------------

### SQL: 创建测试表 dmhr.detail

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

创建名为 dmhr.detail 的测试表，包含编号、项目和金额三个字段，用于演示计算累计差值的场景。该表结构设计用于模拟流水账数据。

```sql
CREATE TABLE dmhr.detail
 (
    编号 int not null,
    项目 varchar(24) null,
    金额 integer null,
    PRIMARY KEY(编号)
  );
```

--------------------------------

### SQL: 包实例化和初始化示例

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/23-包和嵌套子程序.md

演示了达梦数据库中包的实例化和初始化过程。`pkg01` 演示了变量、常量和函数的使用，并在包体初始化部分为 `cur_dt` 赋值。`pkg02` 则展示了如何调用 `pkg01` 中的公共变量。

```sql
CREATE OR REPLACE PACKAGE pkg01 
AS
  cur_dt DATETIME;
  ccc CONSTANT VARCHAR(12) := 'Hello!';
  FUNCTION GetLocalDT() RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg01 
IS

  
FUNCTION GetLocalDT() RETURN NUMBER
IS
BEGIN
  RETURN 90;
END;

BEGIN
  SELECT SYSDATE() INTO cur_dt FROM DUAL;
END;
/


CREATE OR REPLACE PACKAGE pkg02 AS
FUNCTION GetVar() RETURN VARCHAR(120);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg02 IS

FUNCTION GetVar() RETURN VARCHAR(128)
IS
BEGIN
  RETURN 'Public var is ' || to_char(pkg01.cur_dt, 'yyyy-mm-dd hh24:mi:ss') || ',Public constant is ' || pkg01.ccc;
END;

END;
/


--每个不同的会话执行下面的过程，可以到不同的时间，并这个等到的时间在整个会话存在期间保持不变
BEGIN
  PRINT pkg02.GetVar();
END;
/
```

--------------------------------

### 层次查询：使用 NOCYCLE 和 CONNECT_BY_ISCYCLE 避免循环 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/19-层次查询.md

使用 NOCYCLE 关键字避免因循环引用导致的错误，并结合 CONNECT_BY_ISCYCLE 伪列找出可能导致循环的节点。

```sql
SELECT employee_id emp_id, employee_name emp_name,job_title,
manager_id, department_id dept_id,LEVEL, CONNECT_BY_ISCYCLE "Cycle"
  FROM dmhr.emp
 START WITH employee_id=102
CONNECT BY NOCYCLE PRIOR employee_id=manager_id;

```

--------------------------------

### 多媒体数据类型示例 - CREATE, INSERT

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/20-数据类型.md

创建表并定义 TEXT、BLOB、CLOB、IMAGE 数据类型字段，演示插入不同类型多媒体数据的示例。

```sql
CREATE TABLE dmhr.TEXT_TEST(
        c1 TEXT,
        c2 BLOB,
        c3 CLOB,
        c4 IMAGE
);

INSERT INTO dmhr.TEXT_TEST values('dameng','0x123456789','clob','0x987654321');
commit;

SELECT * FROM dmhr.TEXT_TEST;
```

--------------------------------

### SQL: 使用分析函数计算累计余额

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

使用 WITH 子句和分析函数 SUM() OVER (ORDER BY seq)，计算 dmhr.detail 表中每笔交易后的余额。该查询将初始预算费用加上后续的（负数）支出，展示了累计差值的计算方法。

```sql
WITH x
     AS (SELECT ROWNUM AS seq, a.*
           FROM (  SELECT 编号, 项目, 金额
                     FROM dmhr.detail
                 ORDER BY 编号) a)
SELECT 编号,项目,金额,
       SUM (CASE WHEN seq = 1 THEN 金额 ELSE -金额 END)
          OVER (ORDER BY seq)
          AS 余额
  FROM x;
```

--------------------------------

### SQL: 更改取款金额为负数

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

根据交易类型 (trx) 转换 v 视图中的金额 (amt) 为正数（存款）或负数（取款）。此步骤是为了在计算余额时，将取款金额视为负值，简化后续的累加操作。

```sql
SELECT id,
         CASE WHEN trx = 'PY' THEN '取款' ELSE '存款' END 存取类型,
         amt 金额,
         (CASE WHEN trx = 'PY' THEN -amt ELSE amt END) AS 余额
    FROM v
ORDER BY id;
```

--------------------------------

### SQL: 创建或替换测试视图 v

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

创建一个名为 v 的测试视图，包含 id, amt, trx 三个字段，用于模拟存取款交易。该视图包含存款 (PR) 和取款 (PY) 两种交易类型，用于演示余额计算。

```sql
CREATE OR REPLACE VIEW v (id,amt,trx) AS
SELECT 1,100,'PR' FROM dual UNION ALL
SELECT 2,100,'PR' FROM dual UNION ALL
SELECT 3,50,'PY' FROM dual UNION ALL
SELECT 4,100,'PR' FROM dual UNION ALL
SELECT 5,200,'PY' FROM dual UNION ALL
SELECT 6,50,'PY' FROM dual;
```

--------------------------------

### 开启闪回功能 (SQL - ALTER SYSTEM)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/12-闪回查询.md

此SQL语句用于通过ALTER SYSTEM命令开启数据库的闪回功能。参数名'enable_flashback'需要用单引号括起来，'1'表示开启，'both'表示同时修改内存和配置文件。

```sql
alter system set 'enable_flashback'=1 both;

```

--------------------------------

### SQL - 按指定列升序排序

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/03-查询结果排序.md

根据指定的列（例如 hire_date）对查询结果进行升序排序。支持使用列名或列的数字序号进行排序。

```sql
SELECT employee_name, hire_date
FROM dmhr.employee
WHERE ROWNUM < 5
ORDER BY hire_date ASC;
```

```sql
SELECT employee_name, hire_date
FROM dmhr.employee
WHERE ROWNUM < 5
ORDER BY 2 ASC;
```

--------------------------------

### 层次查询：从下到上指定员工 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/19-层次查询.md

查询 employee_id=113 的员工自下而上的组织结构层级。START WITH 指定了查询的起点，CONNECT BY PRIOR manager_id=employee_id 定义了子节点指向父节点的关联，LEVEL 伪列显示层级。

```sql
SELECT employee_id emp_id, employee_name emp_name,job_title,
manager_id, department_id dept_id,LEVEL
  FROM dmhr.emp
 START WITH employee_id=113
CONNECT BY PRIOR manager_id=employee_id;

```

--------------------------------

### SQL: 使用 LISTAGG 模拟累计工资成本计算公式

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

使用 LISTAGG 函数结合分析函数 SUM() OVER (ORDER BY ...) 来模拟计算累计工资成本的过程，展示每一行累计值是如何通过累加前面行的工资构成的。这有助于直观理解分析函数的累加逻辑。

```sql
SELECT employee_id AS 编号,
         employee_name AS 姓名,
         salary AS 人工成本,
         SUM (salary) OVER (ORDER BY employee_id) AS 成本累计,
         (SELECT LISTAGG (salary, '+') WITHIN GROUP (ORDER BY employee_id)
            FROM dmhr.employee b
           WHERE b.job_id = 11 AND b.employee_id <= a.employee_id)  计算公式
    FROM dmhr.employee a
   WHERE job_id = 11
ORDER BY employee_id;
```

--------------------------------

### SQL - 根据指定条件排序

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/03-查询结果排序.md

使用 CASE 语句生成一个新列，并根据该列的值对查询结果进行排序。此示例将特定工资范围内的员工排在前面。

```sql
SELECT job_title AS 职务,
       CASE WHEN min_salary >= 6000 AND min_salary <= 8000 THEN 1 ELSE 2 END
         AS 级别,
       min_salary AS 工资
FROM dmhr.job
WHERE ROWNUM < 5
ORDER BY 2, 3;
```

--------------------------------

### SQL: 创建空表

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

创建一张与 dmhr.employee 表结构相同的空表 dmhr.employee2，用于演示在无数据表上的聚集函数行为。该语句通过 'WHERE 1 = 2' 条件实现复制表结构而不复制数据。

```sql
CREATE TABLE dmhr.employee2 AS SELECT * FROM dmhr.employee WHERE 1 = 2;
```

--------------------------------

### SQL: 使用LEFT JOIN计算部门总工资和奖金

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/04-多表联合检索.md

此SQL语句使用LEFT JOIN，即使奖金数据不包含所有部门，也能返回所有部门的工资总和，以及可用的奖金总和。

```sql
SELECT department_id,
       SUM (salary) AS total_sal,
       SUM (bonus * salary) AS total_bonus
FROM dmhr.employee e
     LEFT JOIN
     (  SELECT employee_id,
               SUM (
                  CASE
                     WHEN TYPE = 1 THEN .1
                     WHEN TYPE = 2 THEN .2
                     ELSE .3
                  END)
                  AS bonus
          FROM dmhr.emp_bonus
      GROUP BY employee_id) eb
        ON e.employee_id = eb.employee_id
GROUP BY e.department_id
ORDER BY 1;
```

--------------------------------

### SQL: 使用分析函数计算累计余额

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

使用 SQL 分析函数 SUM() OVER (ORDER BY id)，累加处理后的金额（存款为正，取款为负），计算每次交易后的余额。该查询展示了如何利用分析函数处理有序序列的累加问题。

```sql
SELECT id,
         CASE WHEN trx = 'PY' THEN '取款' ELSE '存款' END 存取类型,
         amt 金额,
         SUM (CASE WHEN trx = 'PY' THEN -amt ELSE amt END) OVER (ORDER BY id)
            AS 余额
    FROM v
ORDER BY id;
```

--------------------------------

### SQL: 查询测试视图 v

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

查询名为 v 的测试视图，展示模拟的存取款交易数据。该视图包含交易 ID、金额和交易类型（PR: 存款, PY: 取款）。

```sql
SELECT * FROM v;
```

--------------------------------

### 获取闪回功能参数值 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/12-闪回查询.md

此SQL语句使用SF_GET_PARA_VALUE函数分别获取'ENABLE_FLASHBACK'参数在配置文件（scope=2）和内存（scope=1）中的值。in_file和in_mem分别代表这两种情况。

```sql
SELECT SF_GET_PARA_VALUE (1, 'ENABLE_FLASHBACK') in_file,
       SF_GET_PARA_VALUE (2, 'ENABLE_FLASHBACK') in_mem;

```

--------------------------------

### BIT 数据类型示例 - CREATE, INSERT

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/20-数据类型.md

创建表并定义 BIT 类型字段，用于存储布尔值（1-真，0-假），并插入示例数据。

```sql
CREATE TABLE dmhr.BIT_TEST(
        cust_id INT NOT NULL,
        cust_name VARCHAR(10),
        sex BIT
);

INSERT INTO dmhr.BIT_TEST values('10000001','李丽',1);
INSERT INTO dmhr.BIT_TEST values('10000002','王强',0);
COMMIT;

SELECT * FROM dmhr.BIT_TEST;
```

--------------------------------

### DM SQL 内置错误函数和游标属性

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/21-异常处理.md

展示了 DM SQL 中用于错误处理的内置函数 SQLCODE 和 SQLERRM，以及 FORALL 语句中常用的游标属性，包括 SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%BULK_ROWCOUNT, 和 SQL%BULK_EXCEPTIONS。这些属性帮助开发者检查 DML 语句的执行结果和处理批量操作中的异常。

```sql
SQLCODE
-- 返回代码中最后一次抛出的错误代码。如果没有任何错误，则返回 0。

SQLERRM
-- 返回某个错误代码对应的错误信息。如果没有给 SQLERRM 传递错误代码，就会返回 SQLCODE 代码对应的消息，最大长度 512 个字节。

-- FORALL 语句隐含的 SQL 游标属性。
SQL%FOUND 
-- 如果 SQL 语句的最后一次执行修改了一行或者多行，则返回 TRUE
  
SQL%NOTFOUND
-- 如果 DML 语句没有修改任何行则返回 TRUE
  
SQL%ROWCOUNT
-- 返回 SQL 语句全部执行所处理的行数，不仅仅是最后一条语句
  
SQL%BULK_ROWCOUNT 
-- 针对 FORALL 设计的，在语义上属于关联数组。数据库在集合的第N个元素所保存的是 FORALL 的插入、更新、删除  或者合并的第 N 次执行所处理的行数，如果没有处理任何行，则这个集合的第 N 行包含的值是 0。

SQL%BULK_EXCEPTIONS
-- 是带有 SAVE EXCEPTIONS 子句的 FORALL 语句引发的每个异常信息。

```

--------------------------------

### SQL: 创建和修改包的语法

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/23-包和嵌套子程序.md

定义了创建、替换、编译和删除达梦数据库中包的 SQL 语法。包头定义接口，包体实现具体逻辑。包头修改后需要重新编译包体。

```sql
CREATE OR REPLACE PACKAGE <[schema].package_name> IS <declare_section> END [package_name];

CREATE OR REPLACE PACKAGE BODY  <[schema].package_name> IS <declare_section> 
[initialize_section] END [package_name];

ALTER PACKAGE <[schema].package_name> COMPILE PACKAGE;
ALTER PACKAGE <[schema].package_name> COMPILE BODY;
DROP PACKAGE [IF EXISTS] <[schema].package_name>;
```

--------------------------------

### 编译视图 (DM SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/15-视图和同义词.md

重新编译视图以检查其合法性。当基表定义发生变化或权限改变时，可能需要重新编译视图。此示例编译 PURCHASING.VENDOR_EXCELLENT 视图。

```sql
ALTER VIEW PURCHASING.VENDOR_EXCELLENT COMPILE;
```

--------------------------------

### 查询闪回功能状态 (SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/12-闪回查询.md

此SQL语句用于查询数据库中与闪回功能相关的参数设置及其当前状态。它会检索'FLASHBACK%'模式下的参数名称、类型、当前值、系统默认值和文件值。

```sql
SELECT name,
       TYPE,
       VALUE,
       sys_value,
       file_value
  FROM v$parameter
 WHERE name LIKE '%FLASHBACK%';

```

--------------------------------

### SQL: 对流水账排序并生成序号

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

使用 ROWNUM 为 dmhr.detail 表中的记录生成一个序号，并按编号排序。此步骤是为了后续计算累计差值时提供一个处理顺序。

```sql
SELECT ROWNUM AS seq, a.*
  FROM (  SELECT 编号, 项目, 金额
            FROM dmhr.detail
        ORDER BY 编号) a;
```

--------------------------------

### SQL: 先关联再聚合计算员工奖金

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/04-多表联合检索.md

此SQL语句首先将员工表(employee)与员工奖金表(emp_bonus)进行关联，然后计算每个员工的奖金。注意：此方法可能导致工资重复计算。

```sql
SELECT e.employee_id,e.employee_name,e.salary,e.department_id,e.salary
     * CASE WHEN eb.TYPE = 1 THEN .1 WHEN eb.TYPE = 2 THEN .2 ELSE .3 END
       AS bonus
FROM dmhr.employee e, dmhr.emp_bonus eb
WHERE e.employee_id = eb.employee_id
```

--------------------------------

### DM 数据库链接配置 (dmmal.ini)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/14-DBLINK.md

配置 dmmal.ini 文件以建立 DM 数据库之间的同构链接。此文件定义了远程数据库实例的名称、主机 IP 和端口。配置完成后需重启 DM 数据库服务。

```sql
[mal_inst1]
mal_inst_name  = DMSERVER     --A的实例名
mal_host       = 127.0.0.1   --A的ip
mal_port       = 5282
[mal_inst2]
mal_inst_name  = DMSERVER2            --B的实例名
mal_host       = 127.0.0.1  --B的ip
mal_port       = 5283


```

--------------------------------

### SQL - 按子串排序

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/03-查询结果排序.md

使用 SUBSTR 函数提取字符串的子串，并根据该子串对查询结果进行排序。此示例按手机号的后四位进行排序。

```sql
SELECT EMPLOYEE_NAME AS 姓名, SUBSTR (PHONE_NUM, -4) AS 尾号
FROM dmhr.employee
WHERE ROWNUM < 5
ORDER BY 2;
```

--------------------------------

### SQL - TRANSLATE 字符替换

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/03-查询结果排序.md

使用 TRANSLATE 函数替换字符串中的字符。from_string 和 to_string 中的字符按位置一一对应替换。如果 to_string 为空，则替换后的字符为空。

```sql
SELECT TRANSLATE ('ab你好bcadefg', 'abcdefg', '1234567') AS new_str FROM DUAL;
```

```sql
SELECT TRANSLATE('ab你好bcadefg','abcdefg','') AS new_str FROM DUAL;
```

```sql
SELECT TRANSLATE('ab你好bcadefg','1abcdefg','1') AS new_str FROM DUAL;
```

--------------------------------

### DM 数据库异构链接创建 (Oracle)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/14-DBLINK.md

创建指向 Oracle 数据库的异构数据库链接 (LINK1)。此示例展示了如何使用 DM 数据库的 `CREATE LINK` 语句连接到 Oracle 数据库，指定连接用户、密码和 TNS 名称。

```sql
--DM 到 Oracle 的数据库连接创建
CREATE LINK LINK1 CONNECT 'ORACLE' WITH USER01 IDENTIFIED BY USER01PASSWD USING '127.0.0.1/orcl';

```

--------------------------------

### PL/SQL 使用 BULK COLLECT 批量获取学生数据

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/22-记录与集合.md

利用 BULK COLLECT 子句一次性将多行数据从 student 表检索到内存中的集合变量。这种方式比逐行 fetch 效率更高，适用于需要处理大量数据的场景。

```sql
DECLARE 
  TYPE STU_ID_T   IS TABLE OF student.stu_id%TYPE;
  TYPE STU_NAME_T IS TABLE OF student.stu_name%TYPE;
  stu_id_tab STU_ID_T;
  stu_name_tab STU_NAME_T;
BEGIN
  SELECT stu_id, stu_name 
    BULK COLLECT INTO stu_id_tab, stu_name_tab 
    FROM student;

  FOR i IN stu_id_tab.FIRST .. stu_id_tab.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('学生' || stu_name_tab(i) || '的学号是' || stu_id_tab(i));
  END LOOP;
END;
/
```

--------------------------------

### 删除视图 (DM SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/15-视图和同义词.md

演示如何删除一个已经创建的视图。此示例删除了 PURCHASING 模式下的 VENDOR_EXCELLENT 视图。

```sql
DROP VIEW PURCHASING.VENDOR_EXCELLENT;
```

--------------------------------

### SQL: 创建水果表和颜色表

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/04-多表联合检索.md

创建名为'fruits'和'colors'的表，并插入示例数据。这些表将用于演示全外连接操作，以比较不同表之间的差异。

```sql
--创建水果表
CREATE TABLE dmhr.fruits
(
   fruit   VARCHAR (12),
   f_num   NUMBER
);

INSERT INTO dmhr.fruits
VALUES ('APPLE', 1)
,('BANANA',2),('CHERRY',3),
('GRAPE',4),('ORANGE', 5),('STRAWBERRY',1);
COMMIT;

--创建颜色表
CREATE TABLE dmhr.colors
(
   c_num   NUMBER,
   color   VARCHAR (12)
);

INSERT INTO dmhr.colors
VALUES (1, 'RED')
,(2,'YELLOW'),(1,'GREEN'),(5,'ORANGE'),
(6,'WHITE');
COMMIT;
```

--------------------------------

### 查询单表视图 (DM SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/15-视图和同义词.md

查询在 PURCHASING.VENDOR 表上创建的 VENDOR_EXCELLENT 视图，以显示满足特定条件（CREDIT = 1）的供应商信息。

```sql
SELECT * FROM PURCHASING.VENDOR_EXCELLENT;
```

--------------------------------

### SQL: 包内游标定义示例

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/23-包和嵌套子程序.md

展示了如何在达梦数据库的包中声明和定义游标。首先在包头声明游标 `c1`，然后在包体中为其提供具体的查询语句，实现了对查询细节的隐藏。

```sql
CREATE PACKAGE emp_manage AS
  CURSOR c1 RETURN employees%ROWTYPE;  -- Declare cursor
END emp_manage;
/
CREATE PACKAGE BODY emp_manage AS
  CURSOR c1 RETURN employees%ROWTYPE IS
    SELECT * FROM employees WHERE salary > 2500;  -- Define cursor
END emp_manage;
/
```

--------------------------------

### 创建分组视图 (DM SQL)

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/15-视图和同义词.md

在 PRODUCTION 模式下，创建一个名为 VENDOR_STATIS 的视图，用于统计每个供应商的产品数量。该视图使用了 GROUP BY 子句和集函数 COUNT，因此需要显式命名视图的列。

```sql
CREATE VIEW PRODUCTION.VENDOR_STATIS(VENDORID, PRODUCT_COUNT) AS
SELECT VENDORID, COUNT(PRODUCTID)
FROM PRODUCTION.PRODUCT_VENDOR
GROUP BY VENDORID
ORDER BY VENDORID;
```

--------------------------------

### SQL: 无 GROUP BY 的聚集函数在空表上的行为

Source: https://github.com/tomllt/dmdoc/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/07-如何使用数字.md

查询空表 dmhr.employee2，不使用 GROUP BY 子句，计算满足条件的记录总数和工资总和。当表无数据时，COUNT(*) 返回 0，SUM() 返回 NULL。

```sql
SELECT COUNT (*) AS cnt, SUM (salary) AS sum_sal
  FROM dmhr.employee2
 WHERE job_id = 11;
```