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
(*
...
博客聚焦于ORACLE出现PLS - 00103错误的情况，介绍了该错误的现象，并给出了解决方法，为处理ORACLE相关错误提供了信息技术领域的有效参考。
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
