如何将 SQL 查询更改为参数化查询

我不是要回答参数化问题，而是利用这个机会指出您当前查询中的一些问题：

此子查询引用但该表未在其他任何地方提及，因此它应该失败：outerHeight.name

    , ( 
        SELECT outerHeight.name ofchrset
        FROM acr_call_flow false
            , acr_cf_hs hs
            , acr_holiday_set s
            , acr_office_day od
            , acr_office_day_set Os
            , acr_office_hour_set oh
        WHERE od.flow_id = f.id
            AND hs.flow_id(+) = f.id
            AND hs.hs_id = s.id(+)
            AND oh.id = os.ohs.id
            AND os.od_id = od.id
            AND f.id = acf.id
        ) "OFFICE_HOUR_SET"

而且该子查询使用的是古老的连接语法，实际上不应该再使用。(+)

更广泛地说，您正在使用许多“相关子查询”，这些子查询因性能差（缓慢）而臭名昭著：

SELECT *
FROM (
    SELECT acf.ID "call_flow_ID"
        , acf.Name "CALL_FLOW_NAME"
        , acf.SLO "SERVICE_OBJECTIVE"
        , acf.LOG_FILTER "LOG_FILTER"
        , acf.Max_jump
        
        , ( /* this would be more efficient as a joined subquery */
            SELECT LISTAGG(NAME, ',') WITHIN
            GROUP (
                    ORDER BY name
                    )
            FROM ACR_SUBROUTINE acs
            RIGHT JOIN ACR_CALL_FLOW_SUB ON SUB_ID = acs.ID
            WHERE FLOW_ID = acf.ID
                AND TYPE IN ('ANC')
            GROUP BY TYPE
            ) "ANNOUNCEMENT_TREATMENT"
            
        , ( /* this runs the risk of returning more than 1 row which would produce an eror */
            /* you may be able to left join this table to get the same outcome */
            SELECT NAME
            FROM ACR_USER_DOMAIN aud
            WHERE aud.DOMAIN_ID = acf.DOMAIN_ID
            ) "DOMAIN_NAME"
            
        , ( /* this runs the risk of returning more than 1 row which would produce an eror */
            /* you may be able to left join this table to get the same outcome */
            SELECT DOMAIN_ID
            FROM ACR_USER_DOMAIN aud
            WHERE aud.DOMAIN_ID = acf.DOMAIN_ID
            ) "DOMAIN_ID"
            
        , ( /* this would be more efficient as a joined subquery */
            SELECT LISTAGG(name, ',') WITHIN
            GROUP (
                    ORDER BY name
                    )
            FROM ACR_VIRTUAL_QUEUE
            WHERE FLOW_ID = acf.id
            GROUP BY FLOW_ID
            ) "VIRTUAL_QUEUE"
            
        , ( /* unless you run an ancient version of Oracle STOP using ANTIQUE (+) syntax */
            SELECT outerHeight.name ofchrset
            FROM acr_call_flow false
                , acr_cf_hs hs
                , acr_holiday_set s
                , acr_office_day od
                , acr_office_day_set Os
                , acr_office_hour_set oh
            WHERE od.flow_id = f.id
                AND hs.flow_id(+) = f.id
                AND hs.hs_id = s.id(+)
                AND oh.id = os.ohs.id
                AND os.od_id = od.id
                AND f.id = acf.id
            ) "OFFICE_HOUR_SET"
            
    FROM ACR_CALL_FLOW acf
    ) 

我建议重写（但当然未经测试）

SELECT *
FROM (
    SELECT acf.ID "call_flow_ID"
        , acf.Name "CALL_FLOW_NAME"
        , acf.SLO "SERVICE_OBJECTIVE"
        , acf.LOG_FILTER "LOG_FILTER"
        , acf.Max_jump

        , acs.ANNOUNCEMENT_TREATMENT
        , aud.NAME "DOMAIN_NAME"    
        , aud.DOMAIN_ID
        , avq.VIRTUAL_QUEUE
        
        /* ancient syntax sub-query removed as it is incomplete, see under for more */
        --, aod.OFFICE_HOUR_SET
            
    FROM ACR_CALL_FLOW acf
    LEFT JOIN ACR_USER_DOMAIN aud ON aud.DOMAIN_ID = acf.DOMAIN_ID
    LEFT JOIN (
            SELECT FLOW_ID, LISTAGG(NAME, ',') WITHIN GROUP ( ORDER BY name ) "ANNOUNCEMENT_TREATMENT"
            FROM ACR_SUBROUTINE acs
            RIGHT JOIN ACR_CALL_FLOW_SUB ON SUB_ID = acs.ID /* is right join really neccessary */
            WHERE TYPE IN ('ANC')
            GROUP BY FLOW_ID
            ) acs ON acs.FLOW_ID = acf.ID
    LEFT JOIN (
            SELECT FLOW_ID, LISTAGG(name, ',') WITHIN GROUP ( ORDER BY name ) "VIRTUAL_QUEUE"
            FROM ACR_VIRTUAL_QUEUE 
            GROUP BY FLOW_ID
            ) avq ON FLOW_ID = acf.id
    ) 

将“相关子查询”替换为左联接允许返回 NULL（这等效于原始子查询的行为方式）。但是，如果联接中有超过 1 行匹配，那么您将获得比以前更多的行（但要避免原始子查询在返回超过 1 行时会导致的错误）。

最后一个子查询似乎是错误的或不完整的，这是我删除古老语法所能做的最好的事情：

LEFT JOIN (
        SELECT f.id, outerHeight.name "OFFICE_HOUR_SET"
        FROM acr_call_flow f
        INNER JOIN acr_office_day od ON od.flow_id = f.id
        INNER JOIN acr_office_day_set os ON os.od_id = od.id
        INNER JOIN acr_office_hour_set oh ON oh.id = os.ohs.id
        LEFT JOIN acr_cf_hs hs ON hs.flow_id = f.id
        LEFT JOIN acr_holiday_set s ON s.id = hs.hs_id
        ) aod ON aod.id = acf.id

请注意，您很有可能不需要所有这些表（尤其是左连接），但您必须解决问题 - 也许是别名吗？outerHeightoh