slaj/sql/hidd_migration.sql

-- ============================================================================
-- 隐患管理模块 - 数据迁移工具SQL
-- 生成时间: 2026-06-12
-- 说明: 用于从Oracle迁移数据到瀚高(PostgreSQL)
-- ============================================================================

-- ============================================================================
-- 一、数据迁移前检查
-- ============================================================================

-- 检查表是否存在
SELECT 
    table_name,
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'slaj' AND table_name = t.table_name) 
        THEN '已存在' 
        ELSE '不存在' 
    END AS status
FROM (VALUES 
    ('OBJ_HIDD'),
    ('BIS_HIDD_REC_REP'),
    ('BIS_HIDD_RECT_IMPL'),
    ('BIS_HIDD_RECT_ACCE'),
    ('BIS_HIDD_RECT_PROG'),
    ('BIS_HIDD_INVE'),
    ('BIS_HIDD_MAJ_VERI'),
    ('BIS_MAJ_HIDD_SUP'),
    ('BIS_HIDD_ASSESS'),
    ('BIS_HIDD_ASSESS_REC'),
    ('BIS_HIDD_ZERO_ASSESS'),
    ('BIS_HIDD_ZERO_ASSESS_REC'),
    ('BIS_ORG_MON_REP_PERI'),
    ('BIS_ORG_MON_REP_REVO'),
    ('ATT_ORG_BASE'),
    ('ATT_ORG_EXT'),
    ('OBJ_ENG'),
    ('BIS_SINS_SCHE'),
    ('ATT_MED_BASE'),
    ('REL_BIS_MED'),
    ('T_BUSI_HIDD_COUNT'),
    ('T_BUSI_HIDD_COUNT_MON'),
    ('T_BUSI_HIDD_EVA'),
    ('T_BUSI_HIDD_THR_EVA'),
    ('T_DICT_HIDD_ENG')
) AS t(table_name)
ORDER BY table_name;

-- ============================================================================
-- 二、表数据量统计
-- ============================================================================

-- 统计各表数据量
SELECT 
    schemaname,
    relname AS table_name,
    n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE schemaname = 'slaj'
    AND relname IN (
        'OBJ_HIDD', 'BIS_HIDD_REC_REP', 'BIS_HIDD_RECT_IMPL', 
        'BIS_HIDD_RECT_ACCE', 'BIS_HIDD_RECT_PROG', 'BIS_HIDD_INVE',
        'BIS_HIDD_MAJ_VERI', 'BIS_MAJ_HIDD_SUP', 'BIS_HIDD_ASSESS',
        'BIS_HIDD_ASSESS_REC', 'BIS_HIDD_ZERO_ASSESS', 'BIS_HIDD_ZERO_ASSESS_REC',
        'BIS_ORG_MON_REP_PERI', 'BIS_ORG_MON_REP_REVO', 'ATT_ORG_BASE',
        'ATT_ORG_EXT', 'OBJ_ENG', 'BIS_SINS_SCHE', 'ATT_MED_BASE',
        'REL_BIS_MED', 'T_BUSI_HIDD_COUNT', 'T_BUSI_HIDD_COUNT_MON',
        'T_BUSI_HIDD_EVA', 'T_BUSI_HIDD_THR_EVA', 'T_DICT_HIDD_ENG'
    )
ORDER BY n_live_tup DESC;

-- ============================================================================
-- 三、数据迁移脚本（使用DBeaver或Navicat的数据传输功能）
-- ============================================================================

-- 迁移顺序建议（先迁移基础表，再迁移业务表）:
-- 
-- 第1步: 基础表（无外键依赖）
--   1. ATT_ORG_BASE - 机构基础信息
--   2. ATT_ORG_EXT - 机构扩展信息
--   3. OBJ_ENG - 工程信息
--   4. ATT_MED_BASE - 多媒体基础表
--   5. T_DICT_HIDD_ENG - 隐患工程字典
--
-- 第2步: 业务主表
--   6. OBJ_HIDD - 隐患主表
--   7. BIS_SINS_SCHE - 安全检查计划
--
-- 第3步: 业务子表
--   8. BIS_HIDD_REC_REP - 隐患记录上报
--   9. BIS_HIDD_RECT_IMPL - 隐患整改实施
--   10. BIS_HIDD_RECT_ACCE - 隐患整改验收
--   11. BIS_HIDD_RECT_PROG - 隐患整改进度
--   12. BIS_HIDD_INVE - 隐患调查评估
--   13. BIS_HIDD_MAJ_VERI - 重大隐患核实
--   14. BIS_MAJ_HIDD_SUP - 重大隐患挂牌督办
--
-- 第4步: 考核评估表
--   15. BIS_HIDD_ASSESS - 隐患考核
--   16. BIS_HIDD_ASSESS_REC - 隐患考核记录
--   17. BIS_HIDD_ZERO_ASSESS - 隐患零报告考核
--   18. BIS_HIDD_ZERO_ASSESS_REC - 隐患零报告考核记录
--
-- 第5步: 月报相关表
--   19. BIS_ORG_MON_REP_PERI - 月报期间表
--   20. BIS_ORG_MON_REP_REVO - 月报修订表
--
-- 第6步: 关联表
--   21. REL_BIS_MED - 多媒体关联表
--
-- 第7步: 统计汇总表
--   22. T_BUSI_HIDD_COUNT - 隐患统计表
--   23. T_BUSI_HIDD_COUNT_MON - 隐患月度统计
--   24. T_BUSI_HIDD_EVA - 隐患评估统计
--   25. T_BUSI_HIDD_THR_EVA - 隐患威胁评估

-- ============================================================================
-- 四、DBeaver数据传输配置参考
-- ============================================================================

-- 源数据库配置（Oracle）:
--   主机: 39.98.38.2
--   端口: 1521
--   数据库: GW_BASIC
--   用户名: GW_BASIC
--   密码: Gw./16001
--   Schema: SLAJ
--
-- 目标数据库配置（Highgo）:
--   主机: 192.168.0.149
--   端口: 5866
--   数据库: highgo
--   用户名: highgo
--   密码: Gw@141516
--   Schema: slaj

-- ============================================================================
-- 五、数据验证SQL
-- ============================================================================

-- 验证迁移结果 - 比较源和目标表的行数
-- 需要在Oracle中执行以下SQL获取行数:
/*
SELECT 
    table_name,
    num_rows
FROM user_tables
WHERE table_name IN (
    'OBJ_HIDD', 'BIS_HIDD_REC_REP', 'BIS_HIDD_RECT_IMPL', 
    'BIS_HIDD_RECT_ACCE', 'BIS_HIDD_RECT_PROG', 'BIS_HIDD_INVE',
    'BIS_HIDD_MAJ_VERI', 'BIS_MAJ_HIDD_SUP', 'BIS_HIDD_ASSESS',
    'BIS_HIDD_ASSESS_REC', 'BIS_HIDD_ZERO_ASSESS', 'BIS_HIDD_ZERO_ASSESS_REC',
    'BIS_ORG_MON_REP_PERI', 'BIS_ORG_MON_REP_REVO', 'ATT_ORG_BASE',
    'ATT_ORG_EXT', 'OBJ_ENG', 'BIS_SINS_SCHE', 'ATT_MED_BASE',
    'REL_BIS_MED', 'T_BUSI_HIDD_COUNT', 'T_BUSI_HIDD_COUNT_MON',
    'T_BUSI_HIDD_EVA', 'T_BUSI_HIDD_THR_EVA', 'T_DICT_HIDD_ENG'
)
ORDER BY table_name;
*/

-- 在Highgo中执行以下SQL获取行数:
SELECT 
    relname AS table_name,
    n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE schemaname = 'slaj'
    AND relname IN (
        'OBJ_HIDD', 'BIS_HIDD_REC_REP', 'BIS_HIDD_RECT_IMPL', 
        'BIS_HIDD_RECT_ACCE', 'BIS_HIDD_RECT_PROG', 'BIS_HIDD_INVE',
        'BIS_HIDD_MAJ_VERI', 'BIS_MAJ_HIDD_SUP', 'BIS_HIDD_ASSESS',
        'BIS_HIDD_ASSESS_REC', 'BIS_HIDD_ZERO_ASSESS', 'BIS_HIDD_ZERO_ASSESS_REC',
        'BIS_ORG_MON_REP_PERI', 'BIS_ORG_MON_REP_REVO', 'ATT_ORG_BASE',
        'ATT_ORG_EXT', 'OBJ_ENG', 'BIS_SINS_SCHE', 'ATT_MED_BASE',
        'REL_BIS_MED', 'T_BUSI_HIDD_COUNT', 'T_BUSI_HIDD_COUNT_MON',
        'T_BUSI_HIDD_EVA', 'T_BUSI_HIDD_THR_EVA', 'T_DICT_HIDD_ENG'
    )
ORDER BY relname;

-- ============================================================================
-- 六、迁移后处理
-- ============================================================================

-- 1. 更新序列值（如果有自增ID的表）
-- 对于使用NUMBER类型作为主键的表，需要更新序列

-- BIS_HIDD_ASSESS 序列
SELECT setval('SEQ_BIS_HIDD_ASSESS', COALESCE((SELECT MAX(ID) FROM BIS_HIDD_ASSESS), 1));

-- BIS_HIDD_ASSESS_REC 序列
SELECT setval('SEQ_BIS_HIDD_ASSESS_REC', COALESCE((SELECT MAX(ID) FROM BIS_HIDD_ASSESS_REC), 1));

-- BIS_HIDD_ZERO_ASSESS 序列
SELECT setval('SEQ_BIS_HIDD_ZERO_ASSESS', COALESCE((SELECT MAX(ID) FROM BIS_HIDD_ZERO_ASSESS), 1));

-- BIS_HIDD_ZERO_ASSESS_REC 序列
SELECT setval('SEQ_BIS_HIDD_ZERO_ASSESS_REC', COALESCE((SELECT MAX(ID) FROM BIS_HIDD_ZERO_ASSESS_REC), 1));

-- T_BUSI_HIDD_COUNT 序列
SELECT setval('SEQ_T_BUSI_HIDD_COUNT', COALESCE((SELECT MAX(ID) FROM T_BUSI_HIDD_COUNT), 1));

-- T_BUSI_HIDD_COUNT_MON 序列
SELECT setval('SEQ_T_BUSI_HIDD_COUNT_MON', COALESCE((SELECT MAX(ID) FROM T_BUSI_HIDD_COUNT_MON), 1));

-- T_BUSI_HIDD_EVA 序列
SELECT setval('SEQ_T_BUSI_HIDD_EVA', COALESCE((SELECT MAX(ID) FROM T_BUSI_HIDD_EVA), 1));

-- T_BUSI_HIDD_THR_EVA 序列
SELECT setval('SEQ_T_BUSI_HIDD_THR_EVA', COALESCE((SELECT MAX(ID) FROM T_BUSI_HIDD_THR_EVA), 1));

-- T_DICT_HIDD_ENG 序列
SELECT setval('SEQ_T_DICT_HIDD_ENG', COALESCE((SELECT MAX(ID) FROM T_DICT_HIDD_ENG), 1));

-- 2. 重新统计表信息
ANALYZE OBJ_HIDD;
ANALYZE BIS_HIDD_REC_REP;
ANALYZE BIS_HIDD_RECT_IMPL;
ANALYZE BIS_HIDD_RECT_ACCE;
ANALYZE BIS_HIDD_RECT_PROG;
ANALYZE BIS_HIDD_INVE;
ANALYZE BIS_HIDD_MAJ_VERI;
ANALYZE BIS_MAJ_HIDD_SUP;
ANALYZE BIS_HIDD_ASSESS;
ANALYZE BIS_HIDD_ASSESS_REC;
ANALYZE BIS_HIDD_ZERO_ASSESS;
ANALYZE BIS_HIDD_ZERO_ASSESS_REC;
ANALYZE BIS_ORG_MON_REP_PERI;
ANALYZE BIS_ORG_MON_REP_REVO;
ANALYZE ATT_ORG_BASE;
ANALYZE ATT_ORG_EXT;
ANALYZE OBJ_ENG;
ANALYZE BIS_SINS_SCHE;
ANALYZE ATT_MED_BASE;
ANALYZE REL_BIS_MED;
ANALYZE T_BUSI_HIDD_COUNT;
ANALYZE T_BUSI_HIDD_COUNT_MON;
ANALYZE T_BUSI_HIDD_EVA;
ANALYZE T_BUSI_HIDD_THR_EVA;
ANALYZE T_DICT_HIDD_ENG;

-- ============================================================================
-- 完成
-- ============================================================================