Oracle中传入日期参数的动态SQL查询学生表数据实现方法

原代码问题说明 #

初始编写的代码存在3个核心问题，导致无法正常执行：

• 字符串拼接逻辑缺失闭合单引号，生成的SQL存在语法错误
• 直接拼接参数值存在SQL注入风险，且日期格式容易受数据库会话NLS参数影响，出现日期识别失败的问题
• 仅完成了SQL字符串拼接，没有实际执行动态SQL的逻辑，也未遵循Oracle绑定变量传参的规范

推荐实现（绑定变量传参，生产环境可用） #

使用EXECUTE IMMEDIATE搭配USING子句传入绑定参数，完全避免字符串拼接带来的问题，同时Oracle可以复用SQL执行计划，执行效率更高：

set serveroutput on;
declare
  -- 建议直接使用DATE类型存储日期，避免字符串转换的格式问题
  v_effective_date date := to_date('30-APR-2022', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN');
  v_sql_stmt varchar2(10000);
  -- 定义和学生表行结构一致的集合类型，用于接收查询结果
  type t_student_list is table of student%rowtype;
  v_student_result t_student_list;
begin
  -- 动态SQL中用:参数名作为绑定变量占位符，不拼接实际参数值
  v_sql_stmt := 'select * from student where effective_date = :eff_date';
  -- 调试用：打印最终执行的SQL语句
  dbms_output.put_line('待执行SQL：' || v_sql_stmt);
  -- 执行动态SQL，通过USING传入绑定参数，批量接收查询结果
  execute immediate v_sql_stmt bulk collect into v_student_result using v_effective_date;

  -- 后续可自行遍历v_student_result处理查询到的数据，此处示例打印结果条数
  dbms_output.put_line('查询到符合条件的学生记录共' || v_student_result.count || '条');
end;
/

原拼接逻辑的修正版（仅作语法纠错，不推荐生产使用） #

如果一定要用字符串拼接的写法，需要补全末尾转义的单引号，修正后拼接出的SQL语法才合法：

set serveroutput on;
declare
  date_ varchar2(100) := '30-APR-2022';
  sql_stmt varchar2 (10000);
begin
  -- 补全拼接末尾的转义单引号，闭合日期字符串
  sql_stmt := 'select * from student where effective_date = date''' || to_char(date_, 'DD-MON-YYYY') || '''';
  dbms_output.put_line('拼接生成的SQL：' || sql_stmt);
end;
/

注意事项 #

• 所有动态SQL传参优先选择绑定变量方式，从根源上避免SQL注入风险，同时提升SQL执行效率
• 处理日期类型时，明确指定日期格式和对应的NLS语言参数，避免不同会话环境下月份/日期缩写识别失败

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