DECLARE
v_max_add_time TIMESTAMP;
v_check_time TIMESTAMP := SYSTIMESTAMP; -- 统一检测时间
v_sql VARCHAR2(4000);
v_actual_value NVARCHAR2(2000);
v_null_token CONSTANT NVARCHAR2(20) := '<<<NULL>>>'; -- 特殊标记用于空值比较
TYPE result_rec IS RECORD (
order_no NVARCHAR2(64),
tb_name NVARCHAR2(128),
col_name NVARCHAR2(128),
cf_value NVARCHAR2(512),
add_time TIMESTAMP,
scene_code NVARCHAR2(32),
scene_name NVARCHAR2(128),
strategy_id NUMBER(19),
strategy_name NVARCHAR2(128),
rule_id NUMBER(19),
rule_name NVARCHAR2(128)
);
TYPE result_tab IS TABLE OF result_rec;
v_results result_tab;
-- 检查表名是否存在的函数
FUNCTION table_exists(p_table_name VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM all_tables
WHERE table_name = UPPER(p_table_name)
AND owner = 'GHANA_RISK'; -- 替换为实际schema名
RETURN (v_count > 0);
EXCEPTION
WHEN OTHERS THEN RETURN FALSE;
END;
-- 检查列名是否存在的函数
FUNCTION column_exists(p_table_name VARCHAR2, p_column_name VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM all_tab_columns
WHERE table_name = UPPER(p_table_name)
AND column_name = UPPER(p_column_name)
AND owner = 'GHANA_RISK'; -- 替换为实际schema名
RETURN (v_count > 0);
EXCEPTION
WHEN OTHERS THEN RETURN FALSE;
END;
-- 获取列数据类型函数
FUNCTION get_column_data_type(
p_table_name VARCHAR2,
p_column_name VARCHAR2
) RETURN VARCHAR2 IS
v_data_type VARCHAR2(128);
BEGIN
SELECT data_type
INTO v_data_type
FROM all_tab_columns
WHERE owner = 'GHANA_RISK'
AND table_name = UPPER(p_table_name)
AND column_name = UPPER(p_column_name);
RETURN v_data_type;
EXCEPTION
WHEN OTHERS THEN RETURN 'UNKNOWN';
END;
BEGIN
-- 1. 获取最新检测时间范围
SELECT MAX(ADD_TIME) INTO v_max_add_time FROM GHANA_RISK.TQ_RULE_ITEM_RESULT_7;
-- 2. 获取半小时内需检测的数据
SELECT
TQ_ORDER_NO,
TB_NAME,
COL_NAME,
CF_VALUE,
ADD_TIME,
SCENE_CODE,
SCENE_NAME,
STRATEGY_ID,
STRATEGY_NAME,
RULE_ID,
RULE_NAME
BULK COLLECT INTO v_results
FROM GHANA_RISK.TQ_RULE_ITEM_RESULT_7
WHERE ADD_TIME BETWEEN v_max_add_time - INTERVAL '10' MINUTE AND v_max_add_time
and TB_NAME not in('tq_user','tq_user_device_info','tq_user_identity','tq_user_info');
-- 3. 遍历每条记录并动态检测
FOR i IN 1..v_results.COUNT LOOP
DECLARE
v_data_type VARCHAR2(128);
v_actual_raw_value NVARCHAR2(4000);
v_cf_converted_value NUMBER;
v_cf_converted_bdouble BINARY_DOUBLE;
v_comparison_result BOOLEAN := FALSE;
BEGIN
-- 验证表名和列名是否存在
IF NOT table_exists(v_results(i).tb_name) THEN
RAISE_APPLICATION_ERROR(-20001, 'Table not found: ' || v_results(i).tb_name);
END IF;
IF NOT column_exists(v_results(i).tb_name, v_results(i).col_name) THEN
RAISE_APPLICATION_ERROR(-20002, 'Column not found: ' || v_results(i).tb_name || '.' || v_results(i).col_name);
END IF;
-- 获取列数据类型
v_data_type := get_column_data_type(v_results(i).tb_name, v_results(i).col_name);
-- 构建动态SQL获取原始值
v_sql := 'SELECT ' || v_results(i).col_name || ' FROM GHANA_RISK.' || v_results(i).tb_name ||
' WHERE TQ_ORDER_NO = :1';
-- 执行动态查询获取原始值
BEGIN
EXECUTE IMMEDIATE v_sql INTO v_actual_raw_value USING v_results(i).order_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_actual_raw_value := NULL; -- 明确设置为NULL
END;
-- 处理NULL值比较逻辑
IF v_actual_raw_value IS NULL AND v_results(i).cf_value IS NULL THEN
-- 双方均为NULL，视为相等
v_comparison_result := TRUE;
ELSIF v_actual_raw_value IS NULL OR v_results(i).cf_value IS NULL THEN
-- 仅一方为NULL，视为不相等
v_comparison_result := FALSE;
ELSE
-- 双方均非NULL，按数据类型比较
CASE v_data_type
WHEN 'NUMBER' THEN
-- 对于数字类型，尝试将cf_value转换为数字进行比较
BEGIN
v_cf_converted_value := TO_NUMBER(v_results(i).cf_value);
v_comparison_result := (v_actual_raw_value = v_cf_converted_value);
EXCEPTION
WHEN VALUE_ERROR OR INVALID_NUMBER THEN
-- 如果转换失败，则使用字符串比较
v_comparison_result := (v_actual_raw_value = v_results(i).cf_value);
END;
WHEN 'BINARY_DOUBLE' THEN
BEGIN
v_cf_converted_bdouble := TO_BINARY_DOUBLE(v_results(i).cf_value);
v_comparison_result := (v_actual_raw_value = v_cf_converted_bdouble);
EXCEPTION
WHEN VALUE_ERROR OR INVALID_NUMBER THEN
-- 如果转换失败，则使用字符串比较
v_comparison_result := (TO_CHAR(v_actual_raw_value) = v_results(i).cf_value);
END;
ELSE
-- 对于其他类型（字符串等），直接比较
v_comparison_result := (v_actual_raw_value = v_results(i).cf_value);
END CASE;
END IF;
-- 4. 比较值并插入差异（仅当不相等时）
IF NOT v_comparison_result THEN
-- 将实际值转换为可读字符串
BEGIN
v_actual_value :=
CASE
WHEN v_actual_raw_value IS NULL THEN v_null_token -- NULL值使用特殊标记
ELSE TO_CHAR(v_actual_raw_value)
END;
EXCEPTION
WHEN OTHERS THEN
v_actual_value := 'CONVERSION_ERROR: ' || SQLERRM;
END;
-- 插入差异记录
INSERT INTO FK_ADS.ADS_MONITORING_RESULT_DI (
TQ_ORDER_NO, TB_NAME, COL_NAME,
CF_VALUE, ACTUAL_VALUE, CHECK_TIME,
ADD_TIME, SCENE_CODE, SCENE_NAME,
STRATEGY_ID, STRATEGY_NAME, RULE_ID, RULE_NAME
) VALUES (
v_results(i).order_no,
v_results(i).tb_name,
v_results(i).col_name,
v_results(i).cf_value,
v_actual_value,
v_check_time,
v_results(i).add_time,
v_results(i).scene_code,
v_results(i).scene_name,
v_results(i).strategy_id,
v_results(i).strategy_name,
v_results(i).rule_id,
v_results(i).rule_name
);
END IF;
END;
END LOOP;
COMMIT;
END;如果匹配字段需要用tq_order_no关联loan_borrow_info的RISK_SERIAL_NO，然后用关联到的数据中的mer_user_id去关联tb_name对应的其他表该怎么改？
最新发布
本文探讨了java.sql.SQLException中executeQueryForObject返回多个结果的问题，并分析了可能的原因及解决方案，建议检查SQL语句或使用executeQueryForList方法。
扫一扫
241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
