我可以设置“任意”吗？用于 perl 中的绑定参数（Oracle 的 SQL）

发布于 2024-11-27 18:31:31 字数 369 浏览 5 评论 0原文

在 Perl 模块中，我有这样的代码：

 ...
 my $param = 123;
 my $sql = "select id, name from obj where id = ?";
 $sth = $DBH->prepare($sql) || die $DBH->errstr;
 $sth->execute($param) || die $DBH->errstr;
 ...

whern param has value 一切都很好，但是在这个模块中，当您需要从表中选择所有行时，有一些条件（在我的代码中 $param = undef）

有谁知道如何在没有情况下执行此操作更改查询？

谢谢！

原文

In Perl module I have code like this:

 ...
 my $param = 123;
 my $sql = "select id, name from obj where id = ?";
 $sth = $DBH->prepare($sql) || die $DBH->errstr;
 $sth->execute($param) || die $DBH->errstr;
 ...

whern param has value all is well, but in this module there are conditions when you need to select all rows from table (in my code $param = undef)

Does anyone know how to do this without changing the query?

thx!

分享到QQ

分享到微博

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

发布评论

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

暮光沉寂 2024-12-04 18:31:31

如果不更改查询就无法完成此操作，因为您无法使用“=”将某些内容与 NULL 进行比较，并且 undef 为 NULL；您需要使用“is null”或“is not null”。在DBI中搜索“NULL值”，它会解释。

评论后的修订：

如果我现在正确理解了问题，您可能希望根据 Perl 标量是否为 undef 选择所有行或特定行。解决方案是：

my $sql = q/select something from table where ? is null or column = ?/;
$s->prepare($sql);
# here $param is undef for all rows and !undef for specific rows
$s->execute($param, $param);

It cannot be done without changing the query as you cannot compare something with NULL using '=' and undef is NULL; you need to use 'is null' or 'is not null'. Search for "NULL values" in DBI and it will explain.

Revision after comments:

If I understand the problem correctly now you may want to select all rows or specific rows based on whether a Perl scalar is undef or not. A solution would be:

my $sql = q/select something from table where ? is null or column = ?/;
$s->prepare($sql);
# here $param is undef for all rows and !undef for specific rows
$s->execute($param, $param);

回复收藏 0 原文

清风无影 2024-12-04 18:31:31

我通常更喜欢 bohica 提供的解决方案，但 Oracle 可以做一些奇怪的优化，并且对查询进行一些操作可以使某些查询获得更好的性能，或更差的性能。（始终对任何解决方案进行基准测试）

select * from cust where cust_no = nvl(?, cust_no);

这将匹配 cust_no = ?当设置了绑定变量并且 cust_no = cust_no 当绑定变量为 null 时。 Oracle 通常会优化 cust_no = cust_no 并仅返回所有行。

I usually prefer to solution provided by bohica, but Oracle can do some strange optimizations, and playing a bit with the query can for some queries get much better performance, or much worse. (Always benchmark any solution)

select * from cust where cust_no = nvl(?, cust_no);

This will match cust_no = ? when the bind variable is set and cust_no = cust_no when the bind variable is null. Oracle does optimize cust_no = cust_no out usually and just returns all rows.

回复收藏 0 原文

~没有更多了~