DECLARE
v_max_add_time TIMESTAMP;
v_check_time TIMESTAMP := SYSTIMESTAMP; -- 统一检测时间
v_sql VARCHAR2(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(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;
-- 3. 遍历每条记录并动态检测
FOR i IN 1..v_results.COUNT LOOP
DECLARE
v_data_type VARCHAR2(128);
v_conversion_expr VARCHAR2(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(-20002, '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 'VARCHAR2', '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 (* ...
本文介绍了一个使用Shell脚本实现的循环示例，该脚本通过命令行参数接收循环总次数和间隔时间，并输出循环过程中的相关信息。文章展示了如何利用while循环、expr进行算术运算以及如何解析命令行选项。
扫一扫
2031
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
