Oracle-在动态游标的LIKE子句中使用绑定变量 [英] Oracle - using bind variable in LIKE clause of dynamic cursor

问题描述

我正在使用动态光标来获取数据.正在执行的查询类似于:

I am using dynamic cursor for fetching data. Query that is being executed looks similar to:

query := 'SELECT column1, column2 
          FROM my_table 
          WHERE column1 LIKE ''%:bv1%''';

游标本身是这样执行的:

And the cursor itself is executed like this:

OPEN my_cursor FOR query USING my_var1;

我还尝试检查查询并打印它:

I also tried to check the query and print it:

... WHERE column1 LIKE '%:bv1%' ...

因此省略了撇号，但是光标没有获取任何数据.甚至可以在LIKE子句中使用绑定变量，如果可以，我做错了什么?

so apostrophes are escaped, but the cursor fetches no data. Is it even possible to use bind variables in LIKE clause and if yes, what did I do wrong?

推荐答案

这是一个微妙的问题.从静态语句开始，正确处理，然后将其转换为动态SQL，通常很有用.

This is a subtle one. It's often useful to start with a static statement, get that right, then convert it to dynamic SQL.

在非动态SQL中，我们可以这样做:

In non-dynamic SQL we might do it like this:

 SELECT column1, column2 
 FROM my_table 
 WHERE column1 LIKE '%' || local_var || '%'; 

动态等效项是

query := 'SELECT column1, column2 
          FROM my_table 
          WHERE column1 LIKE ''%''||:bv1||''%'' ';

这篇关于Oracle-在动态游标的LIKE子句中使用绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！