DECLARE
v_max_add_time TIMESTAMP;
v_check_time TIMESTAMP := SYSTIMESTAMP; -- 统一检测时间
v_sql
VARCHAR
2(4000
);
v_actual_value
NVARCHAR2(4000
);
v_null_token CONSTANT
NVARCHAR2(20
) := '<<<NULL>>>'; -- 特殊标记用于空值比较
TYPE result_rec IS RECORD
(
order_no
NVARCHAR2(64
),
tb_
name NVARCHAR2(1
28
),
col_
name NVARCHAR2(1
28
),
cf_value
NVARCHAR2(51
2),
add_time TIMESTAMP,
scene_code
NVARCHAR2(3
2),
scene_
name NVARCHAR2(1
28
),
strategy_id NUMBER
(19
),
strategy_
name NVARCHAR2(1
28
),
rule_id NUMBER
(19
),
rule_
name NVARCHAR2(1
28
)
);
TYPE result_tab IS TABLE OF result_rec;
v_results result_tab;
-- 检查表名是否存在的函数
FUNCTION table_exists
(p_table_
name VARCHAR
2) 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 VARCHAR
2, p_column_
name VARCHAR
2) 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 VARCHAR
2,
p_column_
name VARCHAR
2
) RETURN
VARCHAR
2 IS
v_data_type
VARCHAR
2(1
28
);
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;
-- 3. 遍历每条记录并动态检测
FOR i IN 1..v_results.COUNT LOOP
DECLARE
v_data_type
VARCHAR
2(1
28
);
v_conversion_expr
VARCHAR
2(100
);
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
(-
2000
2, 'Column not found: ' || v_results
(i
).col_
name);
END IF;
-- 获取列数据类型并构建转换表达式
v_data_type := get_column_data_type
(v_results
(i
).tb_
name, v_results
(i
).col_
name);
CASE v_data_type
WHEN 'NUMBER' THEN
-- 数字类型：使用TO_CHAR保留格式（包括小于1的前导0）
v_conversion_expr := 'TO_CHAR
(' || v_results
(i
).col_
name || ', ''FM9999999999999999999990D99999999999999999999''
)';
WHEN '
VARCHAR
2', 'CHAR', '
NVARCHAR2', 'NCHAR' THEN
-- 字符类型：直接使用原列
v_conversion_expr := v_results
(i
).col_
name;
ELSE
-- 其他类型：默认转换为字符串
v_conversion_expr := 'TO_CHAR
(' || v_results
(i
).col_
name || '
)';
END CASE;
-- 构建动态SQL（转换为
NVARCHAR2以匹配cf_value类型）
v_sql :=
'SELECT TO_NCHAR
(' || v_conversion_expr || '
) ' ||
'FROM GHANA_RISK.' || v_results
(i
).tb_
name || ' ' ||
'WHERE TQ_ORDER_NO = :1';
-- 执行动态查询
EXECUTE IMMEDIATE v_sql INTO v_actual_value USING v_results
(i
).order_no;
-- 4. 比较值并插入差异
IF NVL
(v_actual_value, v_null_token
) <> NVL
(v_results
(i
).cf_value, v_null_token
) THEN
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;
EXCEPTION
WHEN NO_DATA_FOUND THEN
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,
'RECORD_NOT_FOUND',
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
);
COMMIT;
END;
/报错[65000][6550] ORA-06550: 第 116 行, 第
24 列: PLS-00103: 出现符号 ","在需要下列之一时： * & = - + < / > at in is mod remainder not rem then <an exponent
(* ...
扫一扫
771
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
