delivery-depot-admin/sql/dm/croe-dm8.sql

CREATE TABLE core_demo01
(
    id               bigint                                 NOT NULL PRIMARY KEY IDENTITY,
    name         	 varchar(64)  DEFAULT ''                NULL,
    age				 int          DEFAULT 0 				NULL,
    creator          varchar(64)  DEFAULT ''                NULL,
    create_time      datetime     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updater          varchar(64)  DEFAULT ''                NULL,
    update_time      datetime     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted          bit          DEFAULT '0'               NOT NULL,
    tenant_id        bigint       DEFAULT 0                 NOT NULL
);

CREATE INDEX idx_core_demo01_01 ON core_demo01 (create_time);

COMMENT ON COLUMN core_demo01.id IS '主键';
COMMENT ON COLUMN core_demo01.name IS '名称';
COMMENT ON COLUMN core_demo01.age IS '年龄';
COMMENT ON COLUMN core_demo01.creator IS '创建者';
COMMENT ON COLUMN core_demo01.create_time IS '创建时间';
COMMENT ON COLUMN core_demo01.updater IS '更新者';
COMMENT ON COLUMN core_demo01.update_time IS '更新时间';
COMMENT ON COLUMN core_demo01.deleted IS '是否删除';
COMMENT ON COLUMN core_demo01.tenant_id IS '租户编号';
COMMENT ON TABLE core_demo01 IS 'demo 代码自动生成';


-- 部门表修改
ALTER TABLE system_dept ADD dept_type tinyint DEFAULT 0 NULL;
ALTER TABLE system_dept ADD warehouse_type tinyint DEFAULT 0 NULL;
ALTER TABLE system_dept ADD longitude varchar(30) NULL;
ALTER TABLE system_dept ADD latitude varchar(30) NULL;
ALTER TABLE system_dept ADD dept_abbr_name varchar(30) NULL;
ALTER TABLE system_dept ADD dept_img varchar(30) NULL;
ALTER TABLE system_dept ADD dept_province varchar(30) NULL;
ALTER TABLE system_dept ADD dept_city varchar(30) NULL;
ALTER TABLE system_dept ADD dept_area varchar(30) NULL;
ALTER TABLE system_dept ADD dept_merge_addr varchar(30) NULL;
ALTER TABLE system_dept ADD dept_address varchar(30) NULL;

COMMENT ON COLUMN system_dept.dept_type IS '部门类型：1级国家局  2级 垂管局  3级仓库';
COMMENT ON COLUMN system_dept.warehouse_type IS '仓库类型：参考字典项  warehouse_type';
COMMENT ON COLUMN system_dept.longitude IS '精度';
COMMENT ON COLUMN system_dept.latitude IS ' 纬度';
COMMENT ON COLUMN system_dept.dept_abbr_name IS '名称缩写';
COMMENT ON COLUMN system_dept.dept_img IS '图片';
COMMENT ON COLUMN system_dept.dept_province IS '省（编码）';
COMMENT ON COLUMN system_dept.dept_city IS '市';
COMMENT ON COLUMN system_dept.dept_area IS '区县';
COMMENT ON COLUMN system_dept.dept_merge_addr IS '省市区';
COMMENT ON COLUMN system_dept.dept_area IS '地址';

-- 项目信息表
CREATE TABLE core_project_info
(
    id                    bigint                                 NOT NULL PRIMARY KEY IDENTITY,
    project_name          varchar(128) DEFAULT ''                NULL,
    project_type          tinyint      DEFAULT 0                 NULL,
    reservoir_id          bigint       DEFAULT 0                 NULL,
    manage_dept_id        bigint       DEFAULT 0                 NULL,
    review_status         tinyint      DEFAULT 0                 NULL,
    confidentiality_level tinyint      DEFAULT 0                 NULL,
    confidentiality_period datetime    DEFAULT NULL              NULL,
    construction_nature   varchar(64)  DEFAULT ''                NULL,
    construction_address  varchar(256) DEFAULT ''                NULL,
    approved_investment   decimal(18, 2) DEFAULT 0.00            NULL,
    approved_content      varchar(512) DEFAULT ''                NULL,
    planned_start_date    datetime     DEFAULT NULL              NULL,
    planned_end_date      datetime     DEFAULT NULL              NULL,
    completion_date       datetime     DEFAULT NULL              NULL,
    legal_entity          varchar(128) DEFAULT ''                NULL,
    project_leader_name   varchar(64)  DEFAULT ''                NULL,
    project_leader_phone  varchar(32)  DEFAULT ''                NULL,
    creator               varchar(64)  DEFAULT ''                NULL,
    create_time           datetime     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updater               varchar(64)  DEFAULT ''                NULL,
    update_time           datetime     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted               bit          DEFAULT '0'               NOT NULL
);

CREATE INDEX idx_core_project_info_01 ON core_project_info (create_time);

COMMENT ON COLUMN core_project_info.id IS '主键';
COMMENT ON COLUMN core_project_info.project_name IS '项目名称';
COMMENT ON COLUMN core_project_info.project_type IS '项目类别';
COMMENT ON COLUMN core_project_info.reservoir_id IS '库区ID';
COMMENT ON COLUMN core_project_info.manage_dept_id IS '垂管局ID';
COMMENT ON COLUMN core_project_info.review_status IS '审核状态';
COMMENT ON COLUMN core_project_info.confidentiality_level IS '保密等级';
COMMENT ON COLUMN core_project_info.confidentiality_period IS '保密期限';
COMMENT ON COLUMN core_project_info.construction_nature IS '建设性质';
COMMENT ON COLUMN core_project_info.construction_address IS '建设详细地址';
COMMENT ON COLUMN core_project_info.approved_investment IS '批复投资概算（万元）';
COMMENT ON COLUMN core_project_info.approved_content IS '批复建设内容';
COMMENT ON COLUMN core_project_info.planned_start_date IS '计划开工时间';
COMMENT ON COLUMN core_project_info.planned_end_date IS '计划结束时间';
COMMENT ON COLUMN core_project_info.completion_date IS '竣工验收时间';
COMMENT ON COLUMN core_project_info.legal_entity IS '项目法人单位';
COMMENT ON COLUMN core_project_info.project_leader_name IS '项目负责人姓名';
COMMENT ON COLUMN core_project_info.project_leader_phone IS '项目负责人电话';
COMMENT ON COLUMN core_project_info.creator IS '创建者';
COMMENT ON COLUMN core_project_info.create_time IS '创建时间';
COMMENT ON COLUMN core_project_info.updater IS '更新者';
COMMENT ON COLUMN core_project_info.update_time IS '更新时间';
COMMENT ON COLUMN core_project_info.deleted IS '是否删除';
COMMENT ON TABLE core_project_info IS '项目信息表';


-- 行政区划（三级）
CREATE TABLE system_area_level_3
(
    id                    varchar(12)  DEFAULT ''				 NOT NULL,
    pid          		  varchar(12)  DEFAULT ''                NOT NULL,
    deep          		  tinyint      DEFAULT 0                 NOT NULL,
    area_name             varchar(100) DEFAULT ''                NOT NULL,
    pinyin_prefix         varchar(10)  DEFAULT ''                NULL,
    pinyin                varchar(100) DEFAULT ''                NULL,
    ext_id          	  varchar(12)  DEFAULT ''                NOT NULL,
    ext_name              varchar(510) DEFAULT ''                NOT NULL,
    creator               varchar(64)  DEFAULT ''                NULL,
    create_time           datetime     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updater               varchar(64)  DEFAULT ''                NULL,
    update_time           datetime     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted               bit          DEFAULT '0'               NOT NULL
);

CREATE INDEX idx_system_area_01 ON system_area_level_3 (pinyin_prefix);
CREATE INDEX idx_system_area_02 ON system_area_level_3 (ext_id);
CREATE INDEX idx_system_area_03 ON system_area_level_3 (ext_name);

COMMENT ON COLUMN system_area_level_3.id IS '地区 id';
COMMENT ON COLUMN system_area_level_3.pid IS '上级地区 id';
COMMENT ON COLUMN system_area_level_3.deep IS '地区级别：0 省 1 市 2 区县';
COMMENT ON COLUMN system_area_level_3.area_name IS '简称';
COMMENT ON COLUMN system_area_level_3.pinyin_prefix IS '拼音首字母';
COMMENT ON COLUMN system_area_level_3.pinyin IS '拼音全拼';
COMMENT ON COLUMN system_area_level_3.ext_id IS '标准地区 id';
COMMENT ON COLUMN system_area_level_3.ext_name IS '标准地区名称';

COMMENT ON TABLE system_area_level_3 IS '行政区划（三级）';


-- 创建存储过程：将字段中以逗号分隔的单词首字母存到另一个字段 bei jing -> bj
CREATE OR REPLACE PROCEDURE get_initial_letters IS
  CURSOR cur IS SELECT id, pinyin FROM system_area_level_3;
v_pinyin VARCHAR2(255);
  v_initials VARCHAR2(255);
  v_word VARCHAR2(255);
  v_pos PLS_INTEGER;
BEGIN
FOR rec IN cur LOOP
    v_pinyin := rec.pinyin;
    v_initials := '';
    v_pos := 1;

    LOOP
      -- 获取每个单词的首字母
v_word := SUBSTR(v_pinyin, v_pos, INSTR(v_pinyin || ' ', ' ', v_pos) - v_pos);
      IF v_word IS NULL THEN EXIT; END IF;
      v_initials := v_initials || SUBSTR(v_word, 1, 1);
      v_pos := INSTR(v_pinyin, ' ', v_pos) + 1;
      IF v_pos = 1 THEN EXIT; END IF;  -- 没有更多的单词
END LOOP;

    -- 更新表格中的首字母字段（假设有一个字段用来存储首字母）
UPDATE system_area_level_3 SET pinyin_prefix = UPPER(v_initials) WHERE id = rec.id;
END LOOP;
END;
/

-- 执行存储过程
BEGIN
  get_initial_letters;
END;