-- 初始化测试数据 -- 目标数据库用户: ZJRS_YWXT -- 清理旧数据，防止主键冲突 -- 删除 FW_OPERATOR 时，同时根据 OPERID 和 LOGINID 删除，防止 UK_FW_OPERATOR_LOGINID 冲突 DELETE FROM ZJRS_YWXT.FW_OPERATOR WHERE OPERID IN ('admin', 'test', 'admin_123456') OR LOGINID IN ('admin', 'test', 'admin_123456'); -- 删除关联权限时，需要先找出可能存在的 OPERID（如果是通过 LOGINID 查找到的旧数据） -- 这里简单起见，还是根据 OPERID 删除，但在生产环境中应该更严谨 DELETE FROM ZJRS_YWXT.FW_OPERATOR2RIGHT WHERE OPERID IN ('admin', 'test', 'admin_123456'); -- 同时删除即将插入的 ID 1, 2, 3，防止主键冲突 DELETE FROM ZJRS_YWXT.FW_OPERATOR2RIGHT WHERE ID IN (1, 2, 3); DELETE FROM ZJRS_YWXT.FW_RIGHT WHERE RIGHTID IN ('01', '0101', '0102'); -- 删除旧的字典数据，避免违反唯一约束 UK_AAA100_ITEMCODE -- 同时删除即将插入的主键 ID，防止 PK_AA10 冲突 DELETE FROM ZJRS_YWXT.AA10 WHERE AAZ093 IN (1001, 1002, 1003, 2001, 2002, 2003, 2004); DELETE FROM ZJRS_YWXT.AA10 WHERE AAA100 = 'OPERTYPE' AND ITEMCODE IN ('1', '2', '3'); DELETE FROM ZJRS_YWXT.AA10 WHERE AAA100 = 'RIGHTTYPE' AND ITEMCODE IN ('1', '2'); DELETE FROM ZJRS_YWXT.AA10 WHERE AAA100 = 'AUTHTYPE' AND ITEMCODE IN ('1', '2'); DELETE FROM ZJRS_YWXT.FW_OPERATEUNIT WHERE OPERUNITID = 1001; DELETE FROM ZJRS_YWXT.FW_SYSORG WHERE BAE001 = '440800'; -- 清理异常日志表，防止 ID 冲突 (ORA-00001: PK_FW_LOG4EXCEPTION_ID) DELETE FROM ZJRS_YWXT.FW_LOG4EXCEPTION; -- 清理登录日志表 (可选，保持环境干净) DELETE FROM ZJRS_YWXT.FW_LOG4LOGIN; -- 解决 UNSAFE_VALIDATEFORM 配置问题 -- 如果数据库中有导致空指针的配置，将其更新为安全值 UPDATE AA01 SET AAA005 = 'NEVER_MATCH_THIS_TOKEN' WHERE AAA001 = 'UNSAFE_VALIDATEFORM' AND (AAA005 IS NULL OR AAA005 = ''); COMMIT; -- 1. 插入经办单位 (Organization Unit) -- OPERUNITID: 1001 -- OPERUNITNAME: 湛江市人社局 INSERT INTO ZJRS_YWXT.FW_OPERATEUNIT (BAE001, OPERUNITID, OPERUNITNAME, OPERUNITTYPE, AAE100, BAE006, BAE003) VALUES ('440800', 1001, '湛江市人社局', '1', '1', 'system', 20231027000000); -- 2. 插入字典数据 (AA10) - 操作员类型 -- OPERTYPE: 1-超级管理员, 2-系统管理员, 3-普通操作员 INSERT INTO ZJRS_YWXT.AA10 (AAZ093, ITEMCODE, AAA102, AAA100, AAE100, SORTNO) VALUES (1001, '1', '超级管理员', 'OPERTYPE', '1', '1'); INSERT INTO ZJRS_YWXT.AA10 (AAZ093, ITEMCODE, AAA102, AAA100, AAE100, SORTNO) VALUES (1002, '2', '系统管理员', 'OPERTYPE', '1', '2'); INSERT INTO ZJRS_YWXT.AA10 (AAZ093, ITEMCODE, AAA102, AAA100, AAE100, SORTNO) VALUES (1003, '3', '普通操作员', 'OPERTYPE', '1', '3'); -- 3. 插入操作员 (User) -- 用户1: admin / 密码: admin (明文) -- PWENCRYPT: 0 (表示未加密) -- OPERTYPE: 2 (系统管理员) INSERT INTO ZJRS_YWXT.FW_OPERATOR ( BAE001, OPERUNITID, OPERID, OPERNAME, OPERTYPE, LOGINID, PASSWORD, PWENCRYPT, AAE100, BAE006, BAE003, OPERTYPE2 ) VALUES ( '440800', 1001, 'admin', '系统管理员', '2', 'admin', 'admin', '0', '1', 'system', 20231027000000, '0' ); -- 用户2: test / 密码: test (明文) -- PWENCRYPT: 0 (表示未加密，如果系统支持明文登录) INSERT INTO ZJRS_YWXT.FW_OPERATOR ( BAE001, OPERUNITID, OPERID, OPERNAME, OPERTYPE, LOGINID, PASSWORD, PWENCRYPT, AAE100, BAE006, BAE003, OPERTYPE2 ) VALUES ( '440800', 1001, 'test', '测试用户', '3', 'test', 'test', '0', '1', 'system', 20231027000000, '0' ); -- 用户3: admin_123456 / 密码: 123456 (明文) INSERT INTO ZJRS_YWXT.FW_OPERATOR ( BAE001, OPERUNITID, OPERID, OPERNAME, OPERTYPE, LOGINID, PASSWORD, PWENCRYPT, AAE100, BAE006, BAE003, OPERTYPE2 ) VALUES ( '440800', 1001, 'admin_123456', '管理员123456', '2', 'admin_123456', '123456', '0', '1', 'system', 20231027000000, '0' ); COMMIT; -- ================================================================= -- 修复 ORA-00904: "LOCATE" / "F_LENGTH" 无效标识符问题 -- 原因：代码中使用了 MySQL/自定义函数，Oracle 中不存在 -- 解决方案：在数据库中创建兼容函数 -- ================================================================= -- 1. 创建 LOCATE 兼容函数 (模拟 MySQL LOCATE) CREATE OR REPLACE FUNCTION LOCATE( substr IN VARCHAR2, str IN VARCHAR2 ) RETURN NUMBER IS BEGIN -- 调用 Oracle 内置的 INSTR，注意参数互换位置 RETURN INSTR(str, substr); END LOCATE; / -- 2. 创建 f_length 兼容函数 (模拟 LENGTH) CREATE OR REPLACE FUNCTION f_length( str IN VARCHAR2 ) RETURN NUMBER IS BEGIN RETURN LENGTH(str); END f_length; / /* ================================================================================ 需从正式环境提取数据的表清单 (System Configuration & Framework Tables) 请使用数据导出工具 (如 PL/SQL Developer, DataPump) 导出以下表的数据并在测试环境导入。 ================================================================================ 1. 系统参数与字典 (System Parameters & Dictionaries) ---------------------------------------------------- - AA01 (系统参数表 - System Parameters) - AA10 (数据字典表 - Data Dictionary) - FW_SYSMESSAGE (系统消息提示信息 - System Messages) - FW_HELP (系统帮助信息 - System Help) - FW_BIGDICT_MAINTAIN (大字典维护表 - Big Dictionary) 2. 权限与组织架构 (Rights, Roles & Organization) ---------------------------------------------------- - FW_SYSORG (系统机构表 - System Organization) - FW_OPERATEUNIT (经办单位表 - Operating Units) - FW_OPERATOR (操作员表 - Operators) - FW_RIGHT (菜单与权限表 - Menus & Rights) - FW_RIGHT2RESOURCE (权限资源关联表 - Rights to Resources) - FW_ROLE (角色表 - Roles) - FW_ROLE2RIGHT (角色权限关联表 - Roles to Rights) - FW_OPERATOR2RIGHT (用户权限关联表 - Users to Rights) - FW_OPERATOR2ROLE (用户角色关联表 - Users to Roles) 3. 界面配置 (UI Configuration) ---------------------------------------------------- - FW_GENLIST_CONF (通用列表配置表 - General List Config) - FW_GENTBL_CONF (通用表单配置表 - General Table Config) - FW_QUERYSQL (通用查询SQL表 - General Query SQL) 4. 报表打印 (Reporting & Printing) ---------------------------------------------------- - FW_PRINT_CONF (打印配置表 - Print Configuration) - FW_PRINT_CLIENTCONFIG (客户端打印配置 - Client Print Config) - FW_REPORT_CONF (报表配置表 - Report Configuration) 5. 工作流 (Workflow - If applicable) ---------------------------------------------------- - WF_ACTION_DEF (工作流动作定义 - Workflow Actions) - WF_ACTION_DEF2TASK (工作流任务关联 - Workflow Tasks) - FW_WF_DESIGNER (工作流设计器 - Workflow Designer) 6. 其他 (Others) ---------------------------------------------------- - FW_YXZLINFO_CONF (影像资料配置 - Image Config) - FW_LOG4BUSS (业务日志 - Business Logs, optional structure) 导出命令参考 (Oracle Exp): exp userid=ZJRS_YWXT/PASSWORD tables=(AA01,AA10,FW_SYSMESSAGE,FW_HELP,FW_BIGDICT_MAINTAIN,FW_SYSORG,FW_OPERATEUNIT,FW_OPERATOR,FW_RIGHT,FW_RIGHT2RESOURCE,FW_ROLE,FW_ROLE2RIGHT,FW_OPERATOR2RIGHT,FW_OPERATOR2ROLE,FW_GENLIST_CONF,FW_GENTBL_CONF,FW_QUERYSQL,FW_PRINT_CONF,FW_PRINT_CLIENTCONFIG,FW_REPORT_CONF,WF_ACTION_DEF,WF_ACTION_DEF2TASK,FW_WF_DESIGNER,FW_YXZLINFO_CONF) file=init_data.dmp */