如何使用％字符串比较编写动态查询？

发布于 2025-01-27 19:15:21 字数 211 浏览 5 评论 0 原文

我在PL/SQL过程中编写动态查询，其中我想根据字符串比较操作检索记录。我的字符串存储在一个变量中，我想在查询中使用它。

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

我想以某种方式将上述内容转换为动态查询，并将变量x替换为“ a％站点”中的x，我不确定如何处理。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

微暖i 2025-02-03 19:15:21

使用字符串串联：

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＆lt;＆gt; fiddle

关于您的评论：

 立即执行'从table_name select * select * section * section from x ||类别中的类别'％地点''

您需要通过将报价加倍来逃避报价，并且您可能还想使用绑定变量（以防止SQL注入攻击）：

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

但是，该查询中没有任何需要动态SQL的问题，并且您无需动态SQL即可完全相同。如果您要么使用光标（按照我的示例）或使用选择... [Bulk Collect] 中。

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.

回复收藏 0 原文

~没有更多了~