PL/SQL 查询不获取有限记录

发布于 2024-10-04 07:23:09 字数 512 浏览 5 评论 0原文

我在包中有以下查询

l_sql := 'SELECT  table_name.field1,
                  table_name.field2,
                  table_name.field3
            FROM table_name
            WHERE table_name.status = ''ACTIVE'' AND
                  table_name.replication_date >= SYSDATE - :l'

OPEN zequi_cur FOR l_sql USING l_days_between_start;

理想情况下，它应该只选择那些满足条件SYSTDATE-:l的记录。 l 的值为 730492。但它仍然获取所有记录。而这张表大约有3000万条记录，这使得这个查询永远无法返回。任何想法，它不只获取特定记录的可能原因是什么？

原文

I have following query in a package

l_sql := 'SELECT  table_name.field1,
                  table_name.field2,
                  table_name.field3
            FROM table_name
            WHERE table_name.status = ''ACTIVE'' AND
                  table_name.replication_date >= SYSDATE - :l'

OPEN zequi_cur FOR l_sql USING l_days_between_start;

Ideally, it should select only those records which satisfy the condition SYSTDATE-:l. The value of l is 730492. But its still fetching ALL the records. and this table has about 30 million records, which makes this query forever to return. any ideas, what could be possible reason that its not fetching only particular records?

分享到QQ

分享到微博

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

发布评论

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

高跟鞋的旋律 2024-10-11 07:23:09

SYSDATE-730492 给出了公元 10 年的日期，即 2000 年前。我无法想象这就是你的意思？

如果您的开始日期是 0010 年，您将得到 730492 作为差值：

SQL> select to_date ('22-NOV-2010 04.50.18 PM','DD-MON-YYYY HH.MI.SS AM')
  2         - to_date ('16-NOV-0010 04.59.22 PM','DD-MON-YYYY HH.MI.SS AM') as diff
  3  from dual;

      DIFF
----------
730492.994

SYSDATE-730492 gives a date in the year 10 AD i.e. 2000 years ago. I can't imagine that is what you meant?

If your start date is in the year 0010 you will get 730492 as the difference:

SQL> select to_date ('22-NOV-2010 04.50.18 PM','DD-MON-YYYY HH.MI.SS AM')
  2         - to_date ('16-NOV-0010 04.59.22 PM','DD-MON-YYYY HH.MI.SS AM') as diff
  3  from dual;

      DIFF
----------
730492.994

回复收藏 0 原文

执妄 2024-10-11 07:23:09

为了稍微扩展@Tony 的帖子，当您从 DATE 值中减去一个数字时，该数字的计量单位假定为天。因此，减去730492天，相当于减去约2000年。我怀疑这不是你想做的。

分享并享受。

回复收藏 0 原文

烂柯人 2024-10-11 07:23:09

除了固定日期范围之外，还有什么特殊原因需要使用动态 SQL 吗？也许使用静态游标更好：

declare
  cursor c_cur(b_offset in number) is
    SELECT  table_name.field1,
            table_name.field2,
            table_name.field3
      FROM  table_name
      WHERE table_name.status = ''ACTIVE'' AND
            table_name.replication_date >= SYSDATE - b_offset
  ;
begin
  open c_cur(l_days_between_start);
  ...
end;

Besides fixing your date range, is there any particular reason to use dynamic SQL? Maybe it's better to use a static cursor:

declare
  cursor c_cur(b_offset in number) is
    SELECT  table_name.field1,
            table_name.field2,
            table_name.field3
      FROM  table_name
      WHERE table_name.status = ''ACTIVE'' AND
            table_name.replication_date >= SYSDATE - b_offset
  ;
begin
  open c_cur(l_days_between_start);
  ...
end;

回复收藏 0 原文

~没有更多了~