How to write dynamic query with % string compare?-CodePudding

I am writing a dynamic query in a pl/sql procedure, in which I want to retrieve records based on a string comparison operation. I have my strings stored in a variable and I want to use it in a query.

x:='A';
select * from table_name where category like 'A%SITE';

I want to somehow convert the above into a dynamic query and substitute variable x for A in 'A%SITE' and I am not sure how to go about it.

CodePudding user response：

Use string concatenation:

DECLARE
  x   VARCHAR2(20);
  cur SYS_REFCURSOR;
BEGIN
  x := 'A';

  OPEN cur FOR
    select *
    from   table_name
    where  category like x || '%SITE';

  -- Do something with the cursor.
END;
/

db<>fiddle here

Regarding you comment:

EXECUTE IMMEDIATE 'select * from table_name where category like x || '%SITE''

You need to escape the quotes by doubling them up and you probably also want to use a bind variable (to prevent SQL injection attacks):

EXECUTE IMMEDIATE
  'select * from table_name where category like :1 || ''%SITE'''
  USING x;

However, there is nothing in that query that requires dynamic SQL and you can do exactly the same without dynamic SQL if you either use a cursor (as per my example above) or use SELECT ... [BULK COLLECT] INTO.