如何在Oracle SQL WHERE子句中正确使用字符串变量

问题场景 #

在Oracle SQL*Plus中使用DEFINE定义字符串变量，通过嵌套CONCAT添加单引号后查询无结果；但直接在SQL中写入带单引号的字符串值，能正常返回数据。去掉CONCAT直接定义变量又会报错“SQL command not properly ended”，变量无法被识别为字符串。

原脚本如下：

define Team_Name = concat(concat('''','100.100.1112'),'''');
define not_tcode_name1 = concat(concat('''','PTO'),'''');
define not_tcode_name2 = concat(concat('''','BRV'),'''');
define start_date = to_date('28/11/2021','dd/mm/yyyy');
define end_date = to_date('11/12/2021','dd/mm/yyyy');

select wd.dept_name
    , wd.tcode_name "Time Code"
    , wd.htype_name "Hour Type"
    , wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = &Team_Name
    and wd.wrkd_work_date between &start_date and &end_date
    and wd.tcode_name <> &not_tcode_name1
    and wd.tcode_name <> &not_tcode_name2
;

错误原因 #

DEFINE是SQL*Plus的文本替换变量，而非执行SQL函数。使用CONCAT会导致替换后的SQL保留函数调用，而非直接生成带单引号的字符串。即使函数逻辑上返回正确值，也可能因隐式类型转换（如CHAR与VARCHAR2字段匹配）导致查询无结果。

正确解决方案 #

1. 直接定义带转义单引号的变量 #

无需使用CONCAT，直接通过连续两个单引号转义来定义带单引号的字符串变量：

-- 定义带单引号的字符串变量：三个单引号包裹值，中间两个转义为一个实际单引号
define Team_Name = '''100.100.1112'''
define not_tcode_name1 = '''PTO'''
define not_tcode_name2 = '''BRV'''
define start_date = to_date('28/11/2021','dd/mm/yyyy')
define end_date = to_date('11/12/2021','dd/mm/yyyy')

select wd.dept_name
    , wd.tcode_name "Time Code"
    , wd.htype_name "Hour Type"
    , wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = &Team_Name
    and wd.wrkd_work_date between &start_date and &end_date
    and wd.tcode_name <> &not_tcode_name1
    and wd.tcode_name <> &not_tcode_name2
;

2. 验证替换效果 #

执行SET VERIFY ON开启替换验证，脚本执行时会显示替换后的完整SQL，确认变量是否被正确替换为'100.100.1112'这类格式：

SET VERIFY ON
-- 执行上述定义和查询脚本

3. 导出结果到Excel/Word #

这种方式完全支持SQL*Plus的导出功能，例如导出为CSV文件（可直接用Excel打开）：

-- 导出到指定路径的CSV文件
SPOOL C:\temp\work_detail.csv
SET COLSEP ','       -- 设置列分隔符为逗号
SET LINESIZE 1000    -- 适配宽表
SET PAGESIZE 0       -- 不显示表头重复和分页信息
SET FEEDBACK OFF     -- 不显示行数统计

-- 执行查询语句
select wd.dept_name
    , wd.tcode_name "Time Code"
    , wd.htype_name "Hour Type"
    , wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = &Team_Name
    and wd.wrkd_work_date between &start_date and &end_date
    and wd.tcode_name <> &not_tcode_name1
    and wd.tcode_name <> &not_tcode_name2
;

SPOOL OFF            -- 结束导出

生成的CSV文件可直接用Excel打开，或复制内容到Word中。

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