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;