我有下面的Oracle存储过程，运行正常并返回条件 IF (V_KPI_TYPE_ID = 13) 的结果 . 但当我使用条件 IF ((V_KPI_TYPE_ID = 12) OR (V_KPI_TYPE_ID = 18)) 然后它没有返回任何结果 . 我尝试调试我的代码并尝试运行选择查询，我将结果放入变量 V_SYSTEM_SQL_CHECK 并返回值，选择查询工作正常 . 此变量中的值返回值为sql string . 我使用varchar2数据类型来存储此sql字符串 . 所以我认为我使用的数据类型是将sql字符串存储到变量中是不正确但不确定
FUNCTION BUILD_ALERT_EMAIL_BODY
(
IN_ALERT_LOGS_TIMESTAMP IN TIMESTAMP
, IN_ALERT_LOGS_LOG_DESC IN VARCHAR2
, IN_KPI_LOG_ID IN NUMBER
) RETURN VARCHAR2 AS
BODY VARCHAR2(4000) := '';
V_SYSTEM_SQL_CHECK VARCHAR2(3000) := '';
V_SOAP_SERVICE_NAME VARCHAR2(100) := '';
BEGIN
Select KPI_DEF_ID INTO V_KPI_DEF_ID FROM KPI_LOGS WHERE KPI_LOG_ID = IN_KPI_LOG_ID;
Select KT.KPI_TYPE_ID INTO V_KPI_TYPE_ID FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.KPI_TYPE KT ON KD.KPI_TYPE = KT.KPI_TYPE_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;
DBMS_OUTPUT.PUT_LINE('KPI_TYPE_ID:' || V_KPI_TYPE_ID);
Select KT.CHECK_SQL INTO V_SYSTEM_SQL_CHECK FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.SYSTEM_SQL_CHECK KT ON KD.SYSTEM_SQL_CHECK_ID = KT.SYSTEM_SQL_CHECK_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;
DBMS_OUTPUT.PUT_LINE('SYSTEM_SQL_CHECK' || V_SYSTEM_SQL_CHECK);
Select SP.SOAP_SERVICE_NAME INTO V_SOAP_SERVICE_NAME FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.SOAP_SERVICE SP ON KD.SOAP_SERVICE_ID = SP.SOAP_SERVICE_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;
BODY := to_char(IN_ALERT_LOGS_TIMESTAMP,'DD.MM.YYYY HH24:MI') || Chr(13) || Chr(10);
IF ((V_KPI_TYPE_ID = 12) OR (V_KPI_TYPE_ID = 18)) THEN
BODY := BODY || 'SQL USED:' || V_SYSTEM_SQL_CHECK || Chr(13) || Chr(10);
ELSE IF (V_KPI_TYPE_ID = 13) THEN
BODY := BODY || 'SOAP_SERVICE:' || Chr(13) || Chr(10);
BODY := BODY || V_SOAP_SERVICE_NAME || Chr(13) || Chr(10);
END IF;
END IF;
BODY := BODY || 'ALERT_DESCRIPTION:' || to_char(IN_ALERT_LOGS_LOG_DESC);
RETURN BODY;
END BUILD_ALERT_EMAIL_BODY;
当我调试我的代码然后它返回如下结果是正确的：
KPI_TYPE_ID:18
SYSTEM_SQL_CHECK:select CASE WHEN (o.cnt = 0) THEN 0 ELSE round((r.cnt / o.cnt)*100,3) END from (select count(*) as cnt from rate_errors where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS') ||'0000' ) r cross join
(select count(*) as cnt
from SDR_O2
where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS')||'0000'
) o
2 回答
只是用
如果它仍然没有返回结果，请告诉我
修改代码中的以下行
像这样试试 -
还可以修改使用to_char函数（即sql）分配BODY的位置 .