达梦数据库

johnrui

412人浏览 · 2026-01-07 16:36:03

johnrui · 2026-01-07 16:36:03 发布

版本说明

深度mysql

DBeaver 生成的DDL没有字段注释

select    concat('comment on column ',table_name,'.',column_name,' is ',CONCAT('"',comments,'";')) from user_col_comments where table_name = '表名' AND owner='库名'

达梦关键字处理 COMMENT

CREATE TABLE "evo_attendance".ATTENDANCE_ADJUST (
	ID BIGINT NOT NULL AUTO_INCREMENT,
	LIST_NO VARCHAR(32) NULL,
	PERSON_ID BIGINT NULL,
	CLASSIFY VARCHAR(10) NULL,
	"TYPE" BIGINT NULL,
	START_TIME TIMESTAMP NULL,
	END_TIME TIMESTAMP NULL,
	"`COMMENT`" VARCHAR(512) NULL,
	CREATE_TIME TIMESTAMP NULL,
	UPDATE_TIME TIMESTAMP NULL,
	UPDATE_BY VARCHAR(64) NULL,
	DAILY_ID BIGINT NULL,
	VALID_FLAG VARCHAR(10) NULL,
	CONSTRAINT ATTENDANCE_ADJUST_PK PRIMARY KEY (ID)
);
CREATE INDEX ATTENDANCE_ADJUST_TYPE_IDX ON "evo_attendance".ATTENDANCE_ADJUST ("TYPE");
CREATE UNIQUE INDEX INDEX33573955 ON "evo_attendance".ATTENDANCE_ADJUST (ID);

分表sql, 注意主键

DROP SEQUENCE IF EXISTS "EVO_EVENT"."SEQ_LOG_ALARM_ID_PICTURE";
CREATE SEQUENCE "EVO_EVENT"."SEQ_LOG_ALARM_ID_PICTURE" INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE ORDER;
DROP TABLE IF EXISTS "EVO_EVENT"."EVENT_LOG_ALARM_PICTURE";
CREATE TABLE "EVO_EVENT"."EVENT_LOG_ALARM_PICTURE"
(
    "ID"          BIGINT DEFAULT "EVO_EVENT"."SEQ_LOG_ALARM_ID_PICTURE".NEXTVAL,
    "ALARM_CODE"  VARCHAR(50)  NOT NULL,
    "PICTURE_URL" VARCHAR(256) NOT NULL,
    "UPDATE_TIME" DATETIME     NOT NULL,
    PRIMARY KEY ("ID", "UPDATE_TIME")
) PARTITION BY RANGE ("UPDATE_TIME")(PARTITION "p202001" VALUES LESS THAN ('2020-02-01'));


ALTER TABLE "EVO_EVENT"."EVENT_LOG_ALARM_PICTURE" ADD CONSTRAINT "EVENT_LOG_ALARM_PICTURE_IDX_ALARM_PICTURE" UNIQUE ("ALARM_CODE", "PICTURE_URL", "UPDATE_TIME");

COMMENT ON COLUMN "EVO_EVENT"."EVENT_LOG_ALARM_PICTURE"."ALARM_CODE" IS '报警编号';
COMMENT ON COLUMN "EVO_EVENT"."EVENT_LOG_ALARM_PICTURE"."PICTURE_URL" IS '抓图';
COMMENT ON TABLE "EVO_EVENT"."EVENT_LOG_ALARM_PICTURE" IS '联动抓图';

存储过程样例

   -- "evo_attendance".create_inspect_card_record_table definition
  CREATE OR REPLACE PROCEDURE evo_attendance.create_inspect_card_record_table()
AS
DECLARE
   tableExists NUMBER := 0;
    tmpDate DATE;
    nextMonth VARCHAR(6);
    newTableName VARCHAR(100);
    creatSql VARCHAR(5000);
	creatSqlPk VARCHAR(100);
    creatSqlIndx VARCHAR(5000);
BEGIN
    -- 计算明天的日期
    tmpDate := SYSDATE + 1;
    -- 提取年月部分
    nextMonth := TO_CHAR(tmpDate, 'YYYYMM');
    -- 生成新的表名
    newTableName := 'inspect_card_record_' || nextMonth;
	creatSql := '(ID BIGINT NOT NULL AUTO_INCREMENT,PERSON_ID BIGINT DEFAULT NULL NULL,PERSON_CODE VARCHAR(64) DEFAULT NULL NULL,PERSON_NAME VARCHAR(64) DEFAULT NULL NULL,DEPARTMENT_ID BIGINT DEFAULT NULL NULL,DEPARTMENT_NAME VARCHAR(64) DEFAULT NULL NULL,CARD_NUMBER VARCHAR(50) DEFAULT NULL NULL,SITE_ID BIGINT DEFAULT NULL NULL,SITE_NAME VARCHAR(500) DEFAULT NULL NULL,ATTENDANCE_TYPE INT DEFAULT NULL NULL, SWIPE_TIME TIMESTAMP NULL, SWIPE_REPORT_TIME TIMESTAMP NULL, DATA_SOURCE INT NULL,SUPPLEMENT_FORM_ID BIGINT NULL,SUBSYSTEM VARCHAR(90) NULL,OUTSIDE_FORM_ID BIGINT NULL,CONSTRAINT ';
    creatSqlIndx := 'CREATE INDEX ' || newTableName ||'_SWIPE_TIME_PERSON_ID_IDX ON evo_attendance.'|| newTableName || '(SWIPE_TIME,PERSON_ID);';
	-- 检查表是否存在
SELECT COUNT(*) INTO tableExists
FROM DBA_TABLES
WHERE TABLE_NAME = newTableName
  AND OWNER = 'evo_attendance';
-- 如果表不存在，则创建新表
IF tableExists = 0 THEN
        -- 动态生成创建表的SQL语句
        EXECUTE IMMEDIATE 'CREATE TABLE evo_attendance.' || newTableName || creatSql || newTableName || '_PK PRIMARY KEY (ID));';
        EXECUTE IMMEDIATE creatSqlIndx;
END IF;
END create_inspect_card_record_table;

复杂存储过程

-- "evo_attendance".inspect_card_record_build definition
CREATE OR REPLACE PROCEDURE evo_attendance.inspect_card_record_build()
AS
DECLARE
v_i NUMBER := 0;
    tableExists NUMBER := 0;
    tmpDate DATE;
    lastMonth VARCHAR(6);
    lastTableName VARCHAR(100);
	creatSql VARCHAR(1000);
    creatSqlIndx VARCHAR(1000);
BEGIN
    -- 循环6次，分别处理过去6个月的数据
    WHILE v_i < 6 LOOP
        -- 初始化表存在标志
        tableExists := 0;
        -- 计算当前循环的日期
        tmpDate := ADD_MONTHS(SYSDATE, -v_i);
        -- 提取年月部分
        lastMonth := TO_CHAR(tmpDate, 'YYYYMM');
        -- 生成表名
        lastTableName := 'inspect_card_record_' || lastMonth;
	    creatSql := '(ID BIGINT NOT NULL AUTO_INCREMENT,PERSON_ID BIGINT DEFAULT NULL,PERSON_CODE VARCHAR(64) DEFAULT NULL,PERSON_NAME VARCHAR(64) DEFAULT NULL,DEPARTMENT_ID BIGINT DEFAULT NULL,DEPARTMENT_NAME VARCHAR(64) DEFAULT NULL,CARD_NUMBER VARCHAR(50) DEFAULT NULL,SITE_ID BIGINT DEFAULT NULL,SITE_NAME VARCHAR(500) DEFAULT NULL,ATTENDANCE_TYPE INT DEFAULT NULL, SWIPE_TIME TIMESTAMP NULL, SWIPE_REPORT_TIME TIMESTAMP NULL, DATA_SOURCE INT NULL,SUPPLEMENT_FORM_ID BIGINT NULL,SUBSYSTEM VARCHAR(90) NULL,OUTSIDE_FORM_ID BIGINT NULL,CONSTRAINT ';
        creatSqlIndx := 'CREATE INDEX ' || lastTableName ||'_SWIPE_TIME_PERSON_ID_IDX ON evo_attendance.'|| lastTableName || '(SWIPE_TIME,PERSON_ID);';
        -- 检查表是否存在
SELECT COUNT(*) INTO tableExists
FROM DBA_TABLES
WHERE TABLE_NAME = lastTableName
  AND OWNER = 'evo_attendance';
-- 如果表不存在，则创建新表
IF tableExists = 0 THEN
            -- 动态生成创建表的SQL语句
             EXECUTE IMMEDIATE 'CREATE TABLE evo_attendance.' || lastTableName || creatSql || lastTableName || '_PK PRIMARY KEY (ID));';
EXECUTE IMMEDIATE creatSqlIndx;
END IF;
        -- 循环变量递增
        v_i := v_i + 1;
END LOOP;

END inspect_card_record_build;

表分区

-- "evo_attendance"."inspect_overtime_no_transferred" definition
DROP SEQUENCE IF EXISTS "evo_attendance"."SEQ_inspect_overtime_no_transferred";
CREATE SEQUENCE "evo_attendance"."SEQ_inspect_overtime_no_transferred" INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE ORDER;
DROP TABLE IF EXISTS "evo_attendance"."inspect_overtime_no_transferred";
CREATE TABLE "evo_attendance"."inspect_overtime_no_transferred" (
	ID BIGINT DEFAULT "evo_attendance"."SEQ_inspect_overtime_no_transferred".NEXTVAL,
	PERSON_ID BIGINT NULL,
	PERSON_CODE VARCHAR(64) NULL,
	PERSON_NAME VARCHAR(64) NULL,
	DEPARTMENT_ID BIGINT NULL,
	DEPARTMENT_NAME VARCHAR(64) NULL,
	OVERTIME_DURATION DECIMAL(20,1) NULL,
	OVERTIME_DATE DATE NOT NULL,
	OVERTIME_TYPE TINYINT DEFAULT NULL NULL,
	OVERTIME_IDS VARCHAR(300) DEFAULT NULL NULL,
	ORIGIN TINYINT DEFAULT NULL NULL,
	OVERTIME_EXT_IDS VARCHAR(300) DEFAULT NULL NULL,
	ERROR_INFO VARCHAR(100) DEFAULT NULL NULL,
	CREATE_TIME TIMESTAMP DEFAULT NULL NULL,
	UPDATE_TIME TIMESTAMP DEFAULT NULL NULL,
	CONSTRAINT "inspect_overtime_no_transferred_PK" PRIMARY KEY (ID,OVERTIME_DATE)
)PARTITION BY RANGE ("OVERTIME_DATE")(PARTITION "p20251201" VALUES LESS THAN ('2026-01-01'));
CREATE INDEX "inspect_overtime_no_transferred_OVERTIME_DATE_PERSON_ID_IDX" ON "evo_attendance"."inspect_overtime_no_transferred" (OVERTIME_DATE,PERSON_ID);
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.PERSON_ID IS '人员ID';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.PERSON_CODE IS '人员编号';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.PERSON_NAME IS '人员姓名';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.DEPARTMENT_ID IS '部门ID';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.DEPARTMENT_NAME IS '部门名称';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.OVERTIME_DURATION IS '加班总时长';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.OVERTIME_DATE IS '加班日期, 加班日期 yyyy-mm-dd';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.OVERTIME_TYPE IS '加班类型, 1：平常加班2：休息日加班3：法定节假日加班';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.OVERTIME_IDS IS '加班单ID集合, 关联inspect_overtime_no_transferred';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.ORIGIN IS '数据来源, 1：考勤规则 2：加班单转化 3：管理员导入';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.OVERTIME_EXT_IDS IS '加班单扩展ids集合';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.ERROR_INFO IS '重新计算手提加班单的错误信息';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN "evo_attendance".inspect_overtime_no_transferred.UPDATE_TIME IS '更新时间';
COMMENT ON TABLE "evo_attendance".inspect_overtime_no_transferred IS '加班未转调休表';

查看库表表分区

--- 查看表分区
         SELECT SUBSTR(PARTITION_NAME, -8) 
        FROM DBA_TAB_PARTITIONS
        WHERE TABLE_NAME = 'inspect_attendance_result'
        ORDER BY PARTITION_NAME DESC
        FETCH FIRST 1 ROWS ONLY;
 --- 添加表分区
  ALTER TABLE "evo_attendance".inspect_attendance_result ADD PARTITION "p20251201" VALUES LESS THAN ('2026-01-01');
 --- 删除表分区
 ALTER TABLE "evo_attendance".inspect_attendance_result DROP PARTITION "p20251201";

数据库函数

 SELECT DATEADD(DAY, -1, DATEADD(YEAR, 5, SYSDATE)) FROM DUAL;

数据库系统sql

---查询数据库库表
 SELECT  LOWER(TABLE_NAME) FROM SYS.ALL_TABLES WHERE OWNER = 'EVO_ELEVATOR'
 ---查询数据库sql
      SELECT DISTINCT
        table_name tableName
        FROM
        SYS.ALL_TABLES
        WHERE
       OWNER = 'EVO_ELEVATOR'
       AND REGEXP_LIKE(table_name, '^' || 'elevator_realtime_state' || '_[0-9]+$');
 ---日期格式化
        SELECT table_name
        FROM SYS.ALL_TABLES
        WHERE OWNER = 'evo_elevator'
        AND TABLE_NAME LIKE 'elevator_realtime_state_%'
       AND SUBSTRING(table_name, -8, 8) < TO_CHAR((CURDATE() - 3),'YYYYMMDD');

AI编程社区

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

码道 · 寻艺——从零构建“非遗交互式对话平台“的完整实践

AI编程社区

码道：用 Python + FastAPI 快速构建一个学生信息管理接口：从零到 Swagger 在线文档

在日常开发和教学中，我们经常需要一套简单、可控、能快速上手的后端接口来演示"前后端分离"、练习 Restful API 设计，或者作为前端联调的 Mock 服务。传统做法往往是搭一套完整的 Web 工程：配置数据库、编写 DAO、引入 ORM、做请求分发……一套流程下来，光环境搭建就要花去不少时间，对于想快速验证想法、或者刚入门 API 开发的同学来说，显得过于沉重。

AI编程社区

码道：从零构建学生信息管理系统 API：FastAPI 内存版 CRUD 项目全解析

FastAPI 的强大，很大程度上来自 Pydantic 的校验能力。我们写下类型声明，FastAPI 在运行时自动把它变成边界检查器。name：字符串，长度限制 1 到 50；age：整数，限制在 6 到 120 之间（ge大于等于、le小于等于）；gender：字符串，用正则限定只能是"男"“女”“其他”；email：邮箱格式字符串；grade与major：可选字段，代表班级和专业方向。