创建含可选参数动态条件的Oracle存储过程报错求助

原代码及错误信息 #

原存储过程代码 #

create or replace PROCEDURE FETCH_CMP_CONSOLIDATED_REPORT
(
    P_STATENAME IN NVARCHAR2,
    P_CITYNAME IN NVARCHAR2,
    P_FROMDATE IN DATE,
    P_TODATE IN DATE,
    TBLOUT OUT SYS_REFCURSOR
)
AS

V_QUERY_STRING  NVARCHAR2(5000);
V_WHERE_CONDITION NVARCHAR2(5000);

BEGIN

OPEN TBLOUT FOR

V_QUERY_STRING  = 'SELECT a.changerequestid, a.changerequestnumber, a.networktype, a.statename, a.cityname, a.description, a.createdon, a.lastmodifiedon, a.lastmodifiedby,a.band,
b.sap_id, b.site_type, b.cr_category, b.latitude, b.longitude, b.approve_reject
FROM changerequests a
inner join tbl_pre_post_hoto b
on a.changerequestid = b.CHANGEREQUEST_ID';

IF P_STATENAME IS NOT NULL THEN
    V_WHERE_CONDITION = 'WHERE a.statename = P_STATENAME';
ELSIF P_CITYNAME IS NOT NULL THEN
    V_WHERE_CONDITION = 'WHERE a.cityname = P_CITYNAME';
ELSIF P_FROMDATE IS NOT NULL THEN
    V_WHERE_CONDITION = 'WHERE a.createdon = P_FROMDATE';
ELSE P_TODATE IS NOT NULL THEN
    V_WHERE_CONDITION = 'WHERE a.lastmodifiedon = P_TODATE';
END;

END FETCH_CMP_CONSOLIDATED_REPORT;

报错信息 #

Error(27,19): PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "=" to continue.

问题分析及修正方案 #

1. 变量赋值语法错误 #

PL/SQL中变量赋值必须使用:=，而非普通的=。原代码中所有变量赋值语句都用了=，这是触发报错的直接原因。

2. 动态SQL参数绑定错误 #

直接将存储过程参数写入SQL字符串会导致数据库无法识别这些变量，必须使用绑定变量（前缀加:），既可以避免SQL注入，又能正确传递参数值。

3. 条件逻辑缺陷 #

原代码用ELSIF实现的是互斥条件（仅生效第一个非空参数），但实际场景中通常需要支持多个可选参数同时生效（比如同时传入州名和城市名时，同时过滤这两个条件），因此需要逐步拼接WHERE子句。

4. 游标打开顺序错误 #

必须先构建完整的查询字符串，再打开游标，原代码顺序颠倒，会导致游标无法获取正确的SQL语句。

修正后的完整代码 #

create or replace PROCEDURE FETCH_CMP_CONSOLIDATED_REPORT
(
    P_STATENAME IN NVARCHAR2,
    P_CITYNAME IN NVARCHAR2,
    P_FROMDATE IN DATE,
    P_TODATE IN DATE,
    TBLOUT OUT SYS_REFCURSOR
)
AS
    V_QUERY_STRING     NVARCHAR2(5000);
    V_WHERE_CONDITION  NVARCHAR2(5000);
BEGIN
    -- 初始化基础查询语句
    V_QUERY_STRING := 'SELECT a.changerequestid, a.changerequestnumber, a.networktype, a.statename, a.cityname, 
                       a.description, a.createdon, a.lastmodifiedon, a.lastmodifiedby,a.band,
                       b.sap_id, b.site_type, b.cr_category, b.latitude, b.longitude, b.approve_reject
                       FROM changerequests a
                       INNER JOIN tbl_pre_post_hoto b
                       ON a.changerequestid = b.CHANGEREQUEST_ID';

    -- 初始化条件为空
    V_WHERE_CONDITION := '';

    -- 逐个拼接可选条件
    IF P_STATENAME IS NOT NULL THEN
        IF V_WHERE_CONDITION IS NOT NULL AND V_WHERE_CONDITION != '' THEN
            V_WHERE_CONDITION := V_WHERE_CONDITION || ' AND ';
        ELSE
            V_WHERE_CONDITION := ' WHERE ';
        END IF;
        V_WHERE_CONDITION := V_WHERE_CONDITION || 'a.statename = :P_STATENAME';
    END IF;

    IF P_CITYNAME IS NOT NULL THEN
        IF V_WHERE_CONDITION IS NOT NULL AND V_WHERE_CONDITION != '' THEN
            V_WHERE_CONDITION := V_WHERE_CONDITION || ' AND ';
        ELSE
            V_WHERE_CONDITION := ' WHERE ';
        END IF;
        V_WHERE_CONDITION := V_WHERE_CONDITION || 'a.cityname = :P_CITYNAME';
    END IF;

    IF P_FROMDATE IS NOT NULL THEN
        IF V_WHERE_CONDITION IS NOT NULL AND V_WHERE_CONDITION != '' THEN
            V_WHERE_CONDITION := V_WHERE_CONDITION || ' AND ';
        ELSE
            V_WHERE_CONDITION := ' WHERE ';
        END IF;
        -- 注意：如果是日期范围查询，应该用 >= 而非 =，根据实际需求调整
        V_WHERE_CONDITION := V_WHERE_CONDITION || 'a.createdon >= :P_FROMDATE';
    END IF;

    IF P_TODATE IS NOT NULL THEN
        IF V_WHERE_CONDITION IS NOT NULL AND V_WHERE_CONDITION != '' THEN
            V_WHERE_CONDITION := V_WHERE_CONDITION || ' AND ';
        ELSE
            V_WHERE_CONDITION := ' WHERE ';
        END IF;
        -- 注意：如果是日期范围查询，应该用 <= 而非 =，根据实际需求调整
        V_WHERE_CONDITION := V_WHERE_CONDITION || 'a.lastmodifiedon <= :P_TODATE';
    END IF;

    -- 拼接完整SQL语句
    V_QUERY_STRING := V_QUERY_STRING || V_WHERE_CONDITION;

    -- 打开游标并绑定参数
    OPEN TBLOUT FOR V_QUERY_STRING
        USING P_STATENAME, P_CITYNAME, P_FROMDATE, P_TODATE;

END FETCH_CMP_CONSOLIDATED_REPORT;

额外说明 #

• 日期条件部分：原代码用=匹配日期，通常实际需求是范围查询（比如createdon >= P_FROMDATE和lastmodifiedon <= P_TODATE），已在代码中注释提示，可根据业务需求调整。
• 参数绑定：USING子句中的参数顺序要和SQL中绑定变量的顺序严格对应，确保参数正确传递。

内容的提问来源于stack exchange，提问作者Nadeem