在存储过程Oracle中使用参数作为SQL查询的一部分

Question

我对存储过程和使用游标对多行运行SELECT有点困惑。我需要在SQL查询中使用LIKE子句。我已经测试过，这实际上可以与我的存储过程一起工作:

SQL> /* THIS WORKS*/
SQL> create or replace procedure search_testimonials(
  2  curRETURN OUT sys_refcursor
  3  )
  4  IS
  5  begin
  6   OPEN curRETURN FOR 
  7   SELECT testimonial
  8   FROM testimonial
  9   WHERE testimonial like '%like%';
 10  END search_testimonials;
 11  /
Procedure created.
SQL> variable buffer refcursor;
SQL> execute Search_Testimonials(:buffer);
PL/SQL procedure successfully completed.
SQL> print buffer;
TESTIMONIAL
--------------------------------------------------------------------------------
I like the way OAG does business. I will be a repeat customer
I like the Cashier named David. I think he is a hottie! Go AOGS

当我尝试使用参数作为搜索词的一部分时，问题出现了:

SQL> /* THIS DOES NOT WORK*/
SQL> create or replace procedure search_testimonials(
  2  search_term IN varchar2,
  3  curRETURN OUT sys_refcursor
  4  )
  5  IS
  6  begin
  7   OPEN curRETURN FOR 
  8   SELECT testimonial
  9   FROM testimonial
 10   WHERE testimonial like search_term||'%%';
 11  END search_testimonials;
 12  /
Procedure created.
SQL> 
SQL> execute Search_Testimonials('like', :buffer);
PL/SQL procedure successfully completed.
SQL> print buffer;
no rows selected

我不知道为什么这不起作用。我还尝试使用@VARIABLE符号来传递适当的术语。我使用SQLPlus来运行代码。谢谢你的建议。

Answer 1

变化

 WHERE testimonial like search_term||'%%'

 WHERE testimonial like '%'|| search_term||'%'