### Configuration: Database Link Setup (dmmal.ini)

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

Shows an example configuration file (dmmal.ini) used for setting up database links. This file defines connection parameters for remote database instances, including instance names, host addresses, and ports.

```ini
-- Configure dmmal.ini for database link setup
-- File: dmmal.ini
-- [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
```

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

### SQL: Create and Use Public Database Link

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

Demonstrates the creation of a public database link for connecting to a remote DM database. It also includes examples of inserting, updating, deleting, and querying data on the remote database through this link.

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

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

-- Update remote data via database link
UPDATE TEST@LINK01 SET C2='C' WHERE C1=1;

-- Delete remote data via database link
DELETE FROM TEST@LINK01 WHERE C1=2;
COMMIT;

-- Query remote table via database link
SELECT * FROM TEST@LINK01;
```

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

### SQL: Create List Partitioned Table

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

Illustrates the creation of a list-partitioned table based on a column with discrete values. It defines partitions for specific job IDs and includes a default partition for any other job IDs. The example also shows querying data from a specific partition.

```sql
-- Create list partition by job_id
CREATE TABLE dmhr.lp_job_emp (
   employee_id      INT PRIMARY KEY,
   employee_name    VARCHAR(20),
   job_id           VARCHAR(10) NOT NULL,
   salary           INT,
   department_id    INT
)
PARTITION BY LIST(job_id) (
   PARTITION p1 VALUES ('11','12','21','22'),
   PARTITION p2 VALUES ('31','32','41','42'),
   PARTITION p3 VALUES ('51','52','61','62'),
   PARTITION pmax VALUES (DEFAULT)
) STORAGE (NOBRANCH);

-- Query specific partition
SELECT * FROM dmhr.lp_job_emp PARTITION(P1);
```

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

### SQL: Create Range Partitioned Table

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

Demonstrates the creation of a range-partitioned table based on a date column. It includes defining partitions with specific value ranges and a default partition for values exceeding the defined ranges. The example also shows inserting data and querying partition metadata.

```sql
-- Create range partition by hire_date
CREATE TABLE dmhr.rp_hiredt_emp (
   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,
   CONSTRAINT emp_email_uk1 UNIQUE(email),
   CONSTRAINT emp_dept_fk1 FOREIGN KEY(department_id)
       REFERENCES dmhr.department(department_id),
   CHECK(salary > 0)
)
PARTITION BY RANGE(hire_date) (
   PARTITION p1 VALUES LESS THAN ('2007-1-1'),
   PARTITION p2 VALUES LESS THAN ('2008-1-1'),
   PARTITION p3 VALUES LESS THAN ('2009-1-1'),
   PARTITION pmax VALUES LESS THAN (MAXVALUE)
) STORAGE (NOBRANCH);

-- Insert data into partitioned table
INSERT INTO dmhr.rp_hiredt_emp SELECT * FROM dmhr.employee;
COMMIT;

-- Query partition metadata
SELECT partition_name, high_value, tablespace_name
  FROM dba_tab_partitions
 WHERE table_name='RP_HIREDT_EMP';
```

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

### SQL: Create and Use Database Link to Oracle

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

Illustrates creating a database link to connect to an Oracle database. It includes examples of querying data from a remote Oracle table and performing a join between a local table and a remote table via the database link.

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

-- Query Oracle table via database link
SELECT * FROM remote_table@LINK1 WHERE id > 100;

-- Join local and remote tables
SELECT e.employee_name, d.department_name
  FROM dmhr.employee e, department@LINK01 d
 WHERE e.department_id = d.department_id;
```

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

### Create and Execute Stored Procedures (SQL)

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

Demonstrates the creation and execution of parameterized and non-parameterized stored procedures in SQL. Includes table creation, procedure definition, procedure calls, and querying results. Assumes a SQL environment supporting CREATE OR REPLACE PROCEDURE syntax.

```sql
-- Create test table for stored procedure
CREATE TABLE test_tab (id INT PRIMARY KEY, name VARCHAR(30));

-- Create stored procedure with parameter
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 stored procedure
CALL p_test(3);

-- Query results
SELECT * FROM test_tab;
-- Returns: (1,'p_test1'), (2,'p_test2'), (3,'p_test3')

-- Create 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
CALL p_test2;

SELECT * FROM test_tab;
-- Returns all rows including (10,'p_test2:10') through (13,'p_test2:13')
```

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

### 创建 START WITH NEXT 刷新物化视图 (SQL)

Source: https://github.com/tomllt/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/13-物化视图.md

创建物化视图并配置其刷新计划，从当前时间开始（START WITH SYSDATE），以后每隔一天执行一次刷新（NEXT SYSDATE + INTERVAL '1' DAY）。

```SQL
CREATE MATERIALIZED VIEW mv1 REFRESH COMPLETE WITH PRIMARY KEY START WITH
SYSDATE NEXT SYSDATE + INTERVAL '1' DAY AS SELECT * FROM dmhr.employee;

```

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

### SQL: Create Hash Partitioned Table

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

Shows how to create a hash-partitioned table on an email column. This method distributes data evenly across partitions based on a hash function, suitable for large tables where even distribution is key. The example defines four partitions.

```sql
-- Create hash partition by email
CREATE TABLE dmhr.hp_email_emp (
   employee_id      INT PRIMARY KEY,
   email            VARCHAR(50) NOT NULL,
   salary           INT
)
PARTITION BY HASH(email) (
   PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4
) STORAGE (NOBRANCH);
```

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

### SQL: Drop and View Database Links

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

Provides SQL statements for managing database links. It shows how to drop an existing database link and how to query system views to retrieve metadata about configured database links.

```sql
-- Drop database link
DROP LINK LINK1;

-- View database link metadata
SELECT * FROM USER_DB_LINKS;
SELECT * FROM DBA_DB_LINKS;
```

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

### SQL: Create Partition Indexes

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

Demonstrates the creation of indexes on partitioned tables. This includes creating a standard index on a column within a partitioned table and a unique index that spans multiple columns.

```sql
-- Create partition index
CREATE INDEX ind_sal ON dmhr.lp_job_emp(salary);
CREATE UNIQUE INDEX ind_mail ON dmhr.lp_job_emp(job_id, email);
```

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

### SQL: Create Interval Partitioned Table

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

Illustrates the creation of an interval-partitioned table, which automatically creates new partitions based on a specified interval as data is inserted. This is useful for time-series data where new partitions are frequently needed.

```sql
-- Create interval partition with automatic partition creation
CREATE TABLE dmhr.emp_part (
   EMPLOYEE_ID      INT PRIMARY KEY,
   EMPLOYEE_NAME    VARCHAR(20),
   HIRE_DATE        DATE NOT NULL,
   SALARY           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'))
);
```

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

### SQL: Create Composite Range-Hash Partitioned Table

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

Demonstrates the creation of a composite partitioned table, combining range partitioning on a date column and subpartitioning using hash on an email column. This approach offers finer-grained data management for very large datasets.

```sql
-- Create composite range-hash partition
CREATE TABLE dmhr.rhp_emp (
   employee_id      INT PRIMARY KEY,
   email            VARCHAR(50) NOT NULL,
   hire_date        DATE NOT NULL,
   salary           INT
)
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);
```

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

### SQL: Materialized View Logging and Fast Refresh

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

Covers the setup and usage of materialized view logs to enable fast, incremental refreshes. This includes creating logs with ROWID or SEQUENCE options, altering materialized views to use FAST refresh, and performing manual refresh operations (COMPLETE, FAST, FORCE).

```sql
-- Create materialized view log for fast refresh
CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH PRIMARY KEY;

-- Alter refresh method to FAST
ALTER MATERIALIZED VIEW mv_employees REFRESH FAST;

-- Manual refresh operations
REFRESH MATERIALIZED VIEW mv_employees COMPLETE;  -- Full refresh
REFRESH MATERIALIZED VIEW mv_employees FAST;      -- Incremental refresh
REFRESH MATERIALIZED VIEW mv_employees FORCE;     -- Auto-select method

-- Create join materialized view with fast refresh
DROP MATERIALIZED VIEW LOG ON dmhr.employee;
DROP MATERIALIZED VIEW LOG ON dmhr.department;

CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON dmhr.department WITH ROWID;

CREATE MATERIALIZED VIEW mv_join
REFRESH WITH ROWID FAST AS
SELECT e.employee_id, e.employee_name, d.department_name,
       e.rowid e_rowid, d.rowid d_rowid
  FROM dmhr.employee e, dmhr.department d
 WHERE e.department_id = d.department_id;

-- Create aggregate materialized view with fast refresh
CREATE MATERIALIZED VIEW LOG ON dmhr.employee
WITH ROWID, SEQUENCE(department_id, salary);

CREATE MATERIALIZED VIEW mv_agg
REFRESH WITH ROWID FAST AS
SELECT department_id, SUM(salary), COUNT(salary), COUNT(*)
  FROM dmhr.employee
 GROUP BY department_id;
```

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

### SQL: Create Index on Materialized View

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

Shows how to create an index on a materialized view to further optimize query performance. Indexes can be created on specific columns of a materialized view, similar to regular tables.

```sql
-- Create index on materialized view
CREATE INDEX ind_mv1 ON MV1(employee_id);
```

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

### SQL: Query Materialized View Metadata and Management

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

Provides SQL queries to inspect the metadata of materialized views and commands for managing materialized view logs. This includes querying the `user_mviews` data dictionary view and dropping materialized view logs.

```sql
-- Query materialized view metadata
SELECT * FROM user_mviews WHERE mview_name='MV_EMPLOYEES';

-- Drop materialized view log
DROP MATERIALIZED VIEW LOG ON dmhr.employee;
```

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

### Create and Manage Database Triggers (SQL)

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

Explains the creation and management of various SQL triggers, including BEFORE INSERT, AFTER INSERT, and INSTEAD OF triggers on views. Also covers time-based triggers and trigger management commands like ALTER TRIGGER and DROP TRIGGER. Assumes a SQL environment supporting trigger syntax and DDL operations.

```sql
-- Create BEFORE trigger to increment ID
SET SCHEMA OTHER;
CREATE OR REPLACE TRIGGER TRG_INS_BEFORE
BEFORE INSERT ON OTHER.READER
FOR EACH ROW
BEGIN
    :NEW.READER_ID := :NEW.READER_ID + 1;
END;

-- Test BEFORE trigger
INSERT INTO OTHER.READER(READER_ID, NAME, AGE, GENDER, MAJOR)
VALUES(13, 'test', 20, 'F', 'History');
COMMIT;
--READER_ID will be stored as 14

-- Create AFTER trigger for audit logging
CREATE TABLE T_TEMP(C1 INT, C2 CHAR(20));

CREATE OR REPLACE TRIGGER TRG_INS_AFTER
AFTER INSERT ON OTHER.READER
FOR EACH ROW
BEGIN
    INSERT INTO T_TEMP VALUES(:NEW.READER_ID, 'INSERT ON READER');
END;

-- Test AFTER trigger
INSERT INTO OTHER.READER(READER_ID, NAME, AGE, GENDER, MAJOR)
VALUES(15, 'test1', 22, 'F', 'Computer');
COMMIT;

SELECT * FROM T_TEMP;
-- Shows audit record: (15, 'INSERT ON READER')

-- Create INSTEAD OF trigger on view
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES(10,10), (11,11);
CREATE VIEW v1 AS SELECT * FROM t1;

CREATE OR REPLACE TRIGGER tri1
INSTEAD OF UPDATE ON v1
BEGIN
    INSERT INTO t1 VALUES(111, 111);
END;

UPDATE v1 SET a=100 WHERE a=10;
SELECT * FROM t1;
-- Original rows unchanged, new row (111,111) inserted

-- Create time-based trigger
CREATE OR REPLACE TRIGGER timer2
AFTER TIMER ON DATABASE
FOR EACH 1 DAY FOR EACH 1 MINUTE
BEGIN
    PRINT 'HELLO WORLD';
END;

-- Trigger management
ALTER TRIGGER OTHER.TRG_INS_AFTER DISABLE;
ALTER TRIGGER OTHER.TRG_INS_AFTER ENABLE;
DROP TRIGGER OTHER.TRG_INS_AFTER;

-- View trigger metadata
SELECT * FROM USER_TRIGGERS WHERE trigger_name = 'TRG_INS_AFTER';
```

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

### SQL: Create Materialized Views with Different Refresh Options

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

Demonstrates the creation of materialized views with various refresh mechanisms like PRIMARY KEY, ROWID, ON COMMIT, and scheduled refreshes. These views store query results physically for improved performance. Materialized view logs are essential for enabling fast refreshes.

```sql
-- Create materialized view with primary key
CREATE MATERIALIZED VIEW mv_employees
REFRESH WITH PRIMARY KEY AS
SELECT * FROM dmhr.employee;

-- Create materialized view with ROWID
CREATE MATERIALIZED VIEW mv_employees_rowid
REFRESH WITH ROWID AS
SELECT * FROM dmhr.employee;

-- Create materialized view with ON COMMIT refresh
CREATE MATERIALIZED VIEW mv_employees
REFRESH WITH PRIMARY KEY ON COMMIT AS
SELECT * FROM dmhr.employee;

-- Create materialized view with scheduled refresh
CREATE MATERIALIZED VIEW mv1
REFRESH COMPLETE WITH PRIMARY KEY
START WITH SYSDATE NEXT SYSDATE + INTERVAL '1' DAY AS
SELECT * FROM dmhr.employee;
```

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

### Fast Refresh Materialized View with Aggregate Functions

Source: https://github.com/tomllt/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/13-物化视图.md

This SQL snippet shows how to create a materialized view with aggregate functions (SUM, COUNT) that supports fast refresh. It requires the materialized view log to include specific columns used in the SELECT and GROUP BY clauses (department_id, salary). The example demonstrates creating the log and the materialized view.

```sql
//删除物化视图日志
DROP MATERIALIZED VIEW LOG ON dmhr.employee;
DROP MATERIALIZED VIEW LOG ON dmhr.department;

//创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH ROWID,
SEQUENCE(department_id, salary);

//创建物化视图
CREATE MATERIALIZED VIEW mv_agg REFRESH WITH ROWID FAST AS
SELECT department_id,
         SUM (salary),
         COUNT (salary),
         COUNT (*)
    FROM  dmhr.employee
GROUP BY department_id;
```

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

### 创建时间触发器 - 达梦 SQL

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

创建一个在数据库级别每分钟执行一次，并在屏幕上输出 'HELLO WORLD' 的时间触发器。时间触发器可用于定时任务。

```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/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/12-闪回查询.md

通过 ALTER SYSTEM 命令启用闪回功能。该命令需要管理员权限，设置 'enable_flashback' 参数为 1。修改会同时应用到内存和配置文件，无需重启。

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

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

### SQL: 按次数排序生成序号

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

对不同工资出现的次数进行降序排序，并为每个工资等级生成一个排名序号。此步骤是找到出现次数最多的工资等级的基础。

```sql
SELECT salary,
       DENSE_RANK () OVER (ORDER BY 出现次数 DESC) AS 次数排序
  FROM (  SELECT salary, COUNT (*) AS 出现次数
            FROM dmhr.employee
           WHERE job_id = 11
        GROUP BY salary);
```

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

### Create and Use User-Defined Functions (SQL)

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

Illustrates the creation of custom user-defined functions in SQL, such as determining gender from an ID card. Also shows examples of using built-in SQL functions for string manipulation, numeric operations, date calculations, and null handling. Assumes a SQL environment supporting function creation and standard SQL functions.

```sql
-- Create function to determine gender from ID card
CREATE OR REPLACE FUNCTION GET_SEX(id_card IN VARCHAR(50))
RETURN CHAR(2)
AS
    v_sex CHAR(2);
BEGIN
    IF TO_NUMBER(SUBSTR(id_card,17,1)) % 2 = 1 THEN
        v_sex := '男';
    ELSE
        v_sex := '女';
    END IF;
    RETURN v_sex;
END;

-- Use custom function in query
SELECT identity_card, GET_SEX(identity_card) AS gender
  FROM DMHR.EMPLOYEE;

-- Built-in string function example
SELECT employee_name, LENGTH(employee_name) AS name_length
  FROM DMHR.EMPLOYEE;

-- Numeric functions
SELECT ABS(-15), CEIL(4.3), FLOOR(4.8), MOD(10,3), POWER(2,3)
  FROM DUAL;
-- Returns: 15, 5, 4, 1, 8

-- Date functions
SELECT ADD_MONTHS(SYSDATE, 3), LAST_DAY(SYSDATE),
       MONTHS_BETWEEN(SYSDATE, DATE'2020-01-01')
  FROM DUAL;

-- String manipulation functions
SELECT UPPER('hello'), LOWER('WORLD'), INITCAP('hello world'),
       SUBSTR('database', 1, 4), REPLACE('hello', 'l', 'L')
  FROM DUAL;
-- Returns: 'HELLO', 'world', 'Hello World', 'data', 'heLLo'

-- Null handling functions
SELECT NVL(NULL, 'default'), COALESCE(NULL, NULL, 'value'),
       NULLIF(5, 5), NULLIF(5, 10)
  FROM DUAL;
-- Returns: 'default', 'value', NULL, 5
```

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

### SQL：创建与插入测试数据用于累计差计算

Source: https://github.com/tomllt/dm8/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
INSERT INTO dmhr.detail VALUES(100,'预算费用',1000000);
commit;
```

```sql
INSERT INTO dmhr.detail SELECT employee_id as 编号,'支出' || rownum as 项目,
salary+1000 as 金额 FROM dmhr.employee WHERE job_id=11;
commit;
```

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

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

### SYS_CONNECT_BY_PATH Function for Hierarchical Path Traversal

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

The SYS_CONNECT_BY_PATH function traverses from a starting node in a hierarchy, accumulating node names into a string separated by a specified delimiter. It's useful for visualizing the path from the root to any given node. Dependencies include a properly structured hierarchical table and the use of CONNECT BY and START WITH clauses.

```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;
```

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

### 创建多媒体数据类型字段

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

使用 CREATE TABLE 语句创建表，定义 TEXT、BLOB、CLOB 和 IMAGE 类型的字段。这些类型用于存储大文本、二进制大对象和图像信息，最大长度可达 100G-1。示例展示了如何插入十六进制数字串。

```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;
```

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

### PL/SQL 批量插入数据

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

使用 FORALL 语句和集合类型进行批量插入操作，显著提高 DML 效率，减少上下文切换。该方法通过将多个 DML 操作组合成一个原子操作来提升性能。

```sql
CREATE TABLE student
(
  stu_id NUMBER,
  stu_name VARCHAR(12)
);

DECLARE 
  --定义集合类型和变量
  TYPE STU_ID_T   IS TABLE OF NUMBER  INDEX BY INTEGER;
  TYPE STU_NAME_T IS TABLE OF VARCHAR(12) INDEX BY INTEGER;
  v_stu_id   STU_ID_T;
  v_stu_name STU_NAME_T;
  v_rows NUMBER;
BEGIN
  FOR i IN 1 .. 10 LOOP
    v_stu_id(i)   := i;
    v_stu_name(i) := DBMS_RANDOM.string('x', 3);
  END LOOP; 

  FORALL i IN 1 .. 10
    INSERT INTO student(stu_id, stu_name) VALUES(v_stu_id(i),  v_stu_name(i));
  COMMIT;

  SELECT COUNT(*) INTO v_rows FROM student;

  DBMS_OUTPUT.PUT_LINE('There is ' || v_rows || ' rows in the test table.');
END;
/
```

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

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

Source: https://github.com/tomllt/dm8/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;
```

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

### DM SQL: 使用 LEAD 获取同一分区行间差

Source: https://github.com/tomllt/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/10-范围处理.md

此 SQL 语句使用 `LEAD()` 分析函数，在 `PARTITION BY log_name` 的基础上，按 `log_time` 排序，获取同一用户的下一次登录时间。这为计算登录间隔奠定了基础。

```sql
SELECT log_name AS 登录名,
       log_time AS 登录时间,
       LEAD (log_time) OVER (PARTITION BY log_name ORDER BY log_time) 下一登录时间
  FROM v;
```

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

### SYS_CONNECT_BY_PATH for Hierarchical Aggregation Simulation

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

This example demonstrates using SYS_CONNECT_BY_PATH in conjunction with ROW_NUMBER and CONNECT BY clauses to achieve a similar outcome to WMSYS.WM_CONCAT for ordered hierarchical aggregation, simulating an ordered string concatenation of employee IDs per manager.

```sql
SELECT t.manager_id,
       MAX (SUBSTR (SYS_CONNECT_BY_PATH (t.employee_id, ','), 2)) str
  FROM (SELECT manager_id,
               employee_id,
               ROW_NUMBER ()
               OVER (PARTITION BY manager_id ORDER BY employee_id) rn
          FROM dmhr.emp) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1 AND manager_id = PRIOR manager_id
  GROUP BY t.manager_id
  ORDER BY t.manager_id;
```

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

### DM SQL: 对连续状态累加得到分组依据

Source: https://github.com/tomllt/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/10-范围处理.md

此 SQL 代码创建视图 `x2`，它通过对 `x1` 视图中的 `连续状态` 列进行累加 (`SUM() OVER (ORDER BY 编号)`) 来生成分组依据。这有助于将连续的项目归为同一组。

```sql
CREATE OR REPLACE VIEW x2
AS
   SELECT 编号,
          开始日期,
          结束日期,
          上一工程结束日期,
          连续状态,
          SUM(连续状态) over(ORDER BY 编号) AS 分组依据
     FROM x1;
SELECT * FROM x2;
```

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

### SQL: Partition Maintenance Operations

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

Provides SQL statements for common partition maintenance tasks, including adding a new partition, dropping an existing partition, truncating a partition (removing data but keeping the structure), merging multiple partitions into one, and splitting a partition into two.

```sql
-- Partition maintenance: Add partition
ALTER TABLE dmhr.rp_hiredt_emp
ADD PARTITION p_before_2017 VALUES LESS THAN ('2017-1-1');

-- Drop partition
ALTER TABLE dmhr.rp_hiredt_emp DROP PARTITION pmax;

-- Truncate partition
ALTER TABLE dmhr.rp_hiredt_emp TRUNCATE PARTITION p7;

-- Merge partitions
ALTER TABLE dmhr.rp_hiredt_emp
MERGE PARTITIONS p3, p4 INTO PARTITION p3_4;

-- Split partition
ALTER TABLE dmhr.rp_hiredt_emp
SPLIT PARTITION p3_4 AT ('2009-01-01')
INTO (PARTITION p3, PARTITION p4);
```

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

### DM SQL: 遍历字符串并拆分

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

使用 CONNECT BY 子句和 SUBSTR 函数遍历字符串，并将其拆分成单个字符。此方法适用于需要逐个处理字符串内字符的场景。

```sql
CREATE OR REPLACE VIEW v AS SELECT '天天向上' AS 汉字,'TTXS' AS 首拼 FROM dual;

SELECT v.汉字, v.首拼, LEVEL FROM v CONNECT BY LEVEL <= LENGTH (v.汉字);

SELECT v.汉字,v.首拼,LEVEL,SUBSTR (v.汉字, LEVEL, 1) AS 汉字拆分,
       SUBSTR (v.首拼, LEVEL, 1) AS 首拼拆分,
       'substr(''' || v.汉字 || ''',''' || LEVEL || ',1)' AS study
  FROM v
CONNECT BY LEVEL <= LENGTH (v.汉字);
```

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

### Define and Use Nested Table Type in PL/SQL

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

This snippet demonstrates how to define a schema-level nested table type (EMP_NAME_T) and then use it within a PL/SQL block. It shows extending the nested table, assigning values to its elements, and iterating through them using a FOR loop. The example highlights that nested tables are dynamic and can become sparse after deletions.

```sql
CREATE TYPE EMP_NAME_T IS TABLE OF VARCHAR(12);
/
   
DECLARE 
  emp_names EMP_NAME_T := EMP_NAME_T();
BEGIN
  emp_names.extend(2);
  emp_names(1) := 'lxm'; 
  emp_names(2) := 'zh';
  FOR i IN emp_names.FIRST .. emp_names.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('There is a employee, name is ' || emp_names(i));
  END LOOP;
END;
/
```

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

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

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

查询数据库参数表中与 'FLASHBACK' 相关的参数，用于检查闪回功能是否已启用及其当前状态。此操作不需要特殊依赖，直接在SQL客户端执行。

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

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

### 根据指定条件排序 (SQL)

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

利用 CASE 语句在查询中生成一个临时的排序字段，根据条件（如工资范围）为记录分配优先级（1 或 2），然后以此字段和实际工资进行排序，将满足条件的记录优先显示。

```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;
```

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

### Extract Nth Substring using REGEXP_SUBSTR in SQL

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

This snippet shows how to extract the nth substring from a delimited string using the REGEXP_SUBSTR function. It's useful for parsing comma-separated values or similar formats. The function takes the string, a pattern to match non-comma characters, a starting position, and the occurrence number as input.

```sql
CREATE OR REPLACE VIEW v AS SELECT 'CLARK,KATE,JAMES' AS name;
SELECT REGEXP_SUBSTR (v.name,'[^,]+',1,2) AS 子串 FROM v;
```

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

### SQL：转换取款金额为负数

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

将视图 v 中的取款（PY）金额转换为负数，存款（PR）金额保持不变。此步骤是为了统一处理，方便后续进行余额的累加计算。

```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;
```

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

### DM SQL: 计算用户两次登录时间间隔

Source: https://github.com/tomllt/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/10-范围处理.md

此 SQL 查询计算用户两次登录之间的时间间隔。它首先使用 `LEAD()` 获取下一登录时间，然后在外部查询中计算时间差，并将其转换为分钟。

```sql
SELECT  登录名,
              登录时间,
       (下一登录时间 - 登录时间) * 24 * 60 AS 登录间隔
  FROM (
    SELECT log_name AS 登录名,
           log_time AS 登录时间,
           LEAD (log_time) OVER (PARTITION BY log_name ORDER BY log_time)
           下一登录时间
      FROM v
  );
```

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

### Query Tables Using Custom Collection Types

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

This SQL query identifies columns in tables that are defined using custom collection types. It queries the `all_tab_columns` data dictionary view and filters for columns where the `data_type` starts with 'CLASS%' (a common prefix for user-defined types) and the `table_name` is specifically 'T2'. This helps in finding tables that incorporate complex data structures.

```sql
SELECT table_name, column_name FROM all_tab_columns WHERE data_type LIKE 'CLASS%' AND table_name='T2';
```

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

### 手动全量刷新物化视图 (SQL)

Source: https://github.com/tomllt/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/13-物化视图.md

手动执行物化视图的 COMPLETE 刷新。此操作会重新执行物化视图的定义查询，并将结果全量更新到物化视图中，同时清空物化视图日志。

```SQL
REFRESH MATERIALIZED VIEW mv_employees COMPLETE;

```

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

### Handle Sparse Nested Tables with EXISTS in PL/SQL

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

This PL/SQL example demonstrates handling sparse nested tables after element deletion. It shows extending the table, adding elements, deleting an element by its index, and then iterating through the remaining elements. Crucially, it uses the `EXISTS` method within the loop to safely access elements, preventing errors when dealing with gaps created by deletion.

```sql
DECLARE 
  emp_names EMP_NAME_T := EMP_NAME_T();
BEGIN
  emp_names.extend(2);
  emp_names(1) := 'lxm';
  emp_names(2) := 'zh';
  emp_names.extend(2);
  emp_names(3) := 'wpr';
  emp_names(4) := 'ljl';
  FOR i IN emp_names.FIRST .. emp_names.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('The index is ' || i);
    DBMS_OUTPUT.PUT_LINE('There is a employee, name is ' || emp_names(i));
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('------Delete element of 3.');
  emp_names.delete(3);
  FOR i IN emp_names.FIRST .. emp_names.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('The index is ' || i);
    if ( emp_names.EXISTS(i) ) THEN
      DBMS_OUTPUT.PUT_LINE('There is a employee, name is ' || emp_names(i));
    END IF;
  END LOOP;
END;
/
```

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

### DM8 SQL: Data Manipulation (INSERT, UPDATE, DELETE)

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

Covers Data Manipulation Language (DML) operations in DM8 SQL, including inserting single and multiple rows, creating tables from select statements (CTAS), performing selective column inserts with default values, using MERGE INTO for upsert operations, deleting duplicate records based on ROWID, and updating multiple tables with conditional logic using EXISTS.

```sql
-- Insert single row
INSERT INTO dmhr.employee VALUES
(11146,'Dameng','220103198501166001','whdm@dameng.com','13712346385',
'2020-10-26','52',9500.00,0,11005,1105);

-- Create table as select (CTAS) for testing
CREATE TABLE dmhr.test AS
   SELECT employee_id, employee_name, identity_card, salary, department_id
     FROM dmhr.employee WHERE 1 = 2;

-- Insert multiple rows in one statement
INSERT INTO dmhr.test VALUES
(1109, '程东生', '410107197103252999', 4400, 102),
(1110, '王金玉', '410107197103258999', 4300, 102),
(1111, '程东生', '410107197103252999', 4400, 102);
COMMIT;

-- Selective column insert with defaults
CREATE TABLE dmhr.t1 (
   id         INTEGER PRIMARY KEY,
   name       VARCHAR(12) DEFAULT 'dm2024',
   class_id   INTEGER NOT NULL,
   tp         TIMESTAMP DEFAULT SYSDATE
);
INSERT INTO dmhr.t1 (id, class_id) VALUES (1, 103);

-- MERGE INTO for upsert operations
CREATE TABLE dmhr.dup_emp (
   employee_id     INTEGER PRIMARY KEY,
   employee_name   VARCHAR2(12),
   identity_card   VARCHAR2(18),
   salary          INTEGER,
   department_id   INTEGER
);

CREATE TABLE dmhr.emp_salary (
   employee_id   INTEGER PRIMARY KEY,
   new_salary    INTEGER
);

MERGE INTO dmhr.dup_emp
     USING dmhr.emp_salary
        ON (dmhr.dup_emp.employee_id = dmhr.emp_salary.employee_id)
WHEN MATCHED THEN
   UPDATE SET dmhr.dup_emp.salary = dmhr.emp_salary.new_salary
WHEN NOT MATCHED THEN
   INSERT VALUES (dmhr.emp_salary.employee_id, 'dm2024',
                  410107197103257999, dmhr.emp_salary.new_salary, 102);

-- Delete duplicate records using ROWID
DELETE FROM dmhr.dup_emp
 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM dmhr.dup_emp
                      GROUP BY employee_name);

-- Multi-table update with EXISTS check
UPDATE dmhr.test ot
   SET (salary, department_id) =
          (SELECT nt.salary, nt.department_id
             FROM dmhr.test_new nt
            WHERE ot.employee_id = nt.employee_id)
 WHERE EXISTS (SELECT 1 FROM dmhr.test_new nt
                WHERE ot.employee_id = nt.employee_id);
```

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

### DM SQL: 在字符串中增加引号

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

演示了两种在 SQL 字符串中引入单引号的方法：一是将单个单引号替换为两个单引号，二是使用界定符 Q'...'。这两种方法都可以实现在字符串字面量中包含单引号。

```sql
SELECT 'girl''day' qmarks FROM DUAL UNION ALL SELECT '''' FROM DUAL;

SELECT q'[girl'day]' qmarks FROM DUAL UNION ALL SELECT q'[']' FROM DUAL;
```

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

### DM8 SQL: Query Single Table Operations

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

Demonstrates various single-table query operations in DM8 SQL, including retrieving table structure using metadata or stored procedures, full table scans, filtering rows with WHERE clauses, handling NULL values with NVL, using logical operators for multiple conditions, applying column aliases, implementing conditional logic with CASE statements, and limiting returned rows using ROWNUM and LIMIT clauses.

```sql
-- Query table structure using metadata
SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE','DMHR') FROM dual;

-- Alternative method using stored procedure
SP_TABLEDEF('DMHR','EMPLOYEE');

-- Full table scan with all columns
SELECT * FROM dmhr.employee;

-- Filter rows by hire date
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 function
SELECT employee_name, employee_id, NVL(commission_pct, 0) AS commission_pct
  FROM dmhr.employee;

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

-- Column aliases for readability
SELECT employee_id AS "员工编号", employee_name AS "员工姓名"
  FROM dmhr.employee;

-- Conditional logic in SELECT
SELECT employee_name, salary,
       CASE
         WHEN salary <= 4000  THEN 'low'
         WHEN salary >= 12000 THEN 'high'
         ELSE 'ok'
       END AS salary_status
FROM dmhr.employee;

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

-- Limit rows using LIMIT clause
SELECT * FROM dmhr.employee LIMIT 2;
```

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

### 创建 BIT 类型字段

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

使用 CREATE TABLE 语句创建表，定义 BIT 类型的字段。BIT 类型用于存储整数 1、0 或 NULL，常用于表示布尔值。示例展示了插入 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;
```

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

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

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

使用 SF_GET_PARA_VALUE 函数分别查询 'ENABLE_FLASHBACK' 参数在配置文件（in_file）和内存（in_mem）中的值，用于验证参数设置。

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

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

### DM SQL: 使用 LEAD 定位连续项目值范围

Source: https://github.com/tomllt/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/10-范围处理.md

此 SQL 查询利用 `LEAD()` 分析函数查找连续的项目记录。它通过比较当前记录的结束日期与下一条记录的开始日期来识别连续范围，仅需一次视图扫描。

```sql
SELECT 工程号, 开始日期, 结束日期
  FROM (
    SELECT pro_id AS 工程号,
           pro_start AS 开始日期,
           pro_end AS 结束日期,
           LEAD (pro_start) OVER (ORDER BY pro_id) 下一工程开始日期
      FROM v
  )
 WHERE 下一工程开始日期 = 结束日期
```

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

### 修改物化视图刷新方式为快速刷新 (SQL)

Source: https://github.com/tomllt/dm8/blob/main/workspace/dmdoc/claudedocs/dameng-sql-dev/13-物化视图.md

将已存在的物化视图的刷新方式从默认的 FORCE 或 COMPLETE 修改为 FAST（快速刷新）。前提是已成功创建了物化视图日志。

```SQL
ALTER MATERIALIZED VIEW mv_employees REFRESH FAST;

```

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

### SQL: 查询各部门出现次数最多的工资等级

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

利用 `PARTITION BY job_id` 子句，查询每个部门（job_id）中出现次数最多的工资等级。结果会显示每个部门的 job_id 和对应的出现次数最多的 salary。

```sql
SELECT job_id, salary
  FROM (SELECT job_id,
               salary,
               DENSE_RANK ()
                  OVER (PARTITION BY job_id ORDER BY 出现次数 DESC)
                  AS 次数排序
          FROM (  SELECT salary, job_id, COUNT (*) AS 出现次数
                    FROM dmhr.employee
                GROUP BY job_id, salary) x) y
 WHERE 次数排序 = 1;
```

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

### SQL 聚集函数：计算部门员工薪资汇总

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

使用SQL中的聚集函数（AVG, MIN, MAX, SUM, COUNT）来计算每个部门员工的平均薪资、最小薪资、最大薪资、总工资以及总记录数。该函数适用于对表数据进行汇总分析，需要配合GROUP BY子句使用。

```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;
```