Oracle 11G中的LIMIT/OFFSET [英] LIMIT / OFFSET in Oracle 11G

问题描述

我正在尝试更新Oracle中的表，但遇到了一些困难.我正在从MySQL移植我的代码，而Oracle不支持MySQL允许的某些命令.

这是MySQL代码:

 update table1 t1 set c5 = (select ContractID from table2 t2 where t1.assetid = 
 t2.assetid and t1.lastdate >= t2.lastdate and t1.firstdate= t2.firstdate 
 order by lastdate asc limit 1 offset 4);

子查询返回一个ContractIDS列表，按lastdate排序，我只需要一个特定的ID，因此使用了limit 1 offset X命令.

问题如下. Oracle不支持限制"或偏移"命令.使用rownum和嵌套查询可以解决限制问题，但是Oracle 11G解析器在UPDATE命令中不喜欢它们.

在需要更新命令中的限制(而不是偏移量)之前，我遇到了类似的问题.它在这里解决: MySQL到Oracle语法错误(限制/偏移/更新)

弗洛林·吉塔(Florin Ghita)使用解析函数找到了一种解决方法.

 update table1 alf
  set nextcontractid = 
      (SELECT min(contractid) keep (dense_rank first order by lasttradedate asc) 
      FROM table1copy alf2
      WHERE alf2.assetid     = alf.assetid
      AND alf2.lasttradedate > alf.lasttradedate
      )
  where alf.complete = 0

此解决方法使我可以获取顶部或底部条目(通过在density_rank命令中使用asc或desc)，但是如果需要第二行或第三行，则无法找到offset命令的代理. /p>

我尝试过的另一种解决方案是使用嵌套查询.第一个使用rownum命令获得前5行，并以相反的方式对它们进行排序，将最后四行减掉MINUS.该解决方案失败了，因为Oracle解析器无法理解其中一个嵌套查询中引用的最外层命令中对表的引用.

(与我以前遇到的问题相同: MySQL到Oracle语法错误(限制/偏移/更新))

挑战不只是在oracle中运行带有限制和偏移量的select语句，因为我已经可以通过嵌套查询做到这一点.挑战在于使select语句在update语句中工作，因为即使该语句在语法上是正确的，Oracle解析器也无法对它们进行解码.到目前为止，嵌套查询(和Google)使我失败了.

还有其他人有类似的问题吗?

解决方案

已删除原始答案，不可行

我认为这应该在单个SQL语句中可行，但是到目前为止，对相关子查询的需求与对某种分析功能的需求的结合使我尝试过的一切都失败了.

以下是我认为可以执行您想要的操作的一种方法:

DECLARE
  CURSOR t IS
  SELECT LEAD(contractid,4) OVER (PARTITION BY assetid ORDER BY lasttradedate ASC) lead_contractid
    FROM table1
    FOR UPDATE;
BEGIN
  FOR r IN t LOOP
     UPDATE table1 SET nextcontractid = r.lead_contractid
       WHERE CURRENT OF t;
  END LOOP;
END;

I'm trying to update a table in Oracle and I'm running into some difficulty. I'm porting over my code from MySQL and some of the commands that MySQL allows are not supported in Oracle.

Here is the MySQL code:

 update table1 t1 set c5 = (select ContractID from table2 t2 where t1.assetid = 
 t2.assetid and t1.lastdate >= t2.lastdate and t1.firstdate= t2.firstdate 
 order by lastdate asc limit 1 offset 4);

The subquery returns a list of ContractIDS, sorted by lastdate and I only want a particular one, hence the limit 1 offset X command.

The problem is as follows. Oracle does not support the "limit" or "offset" commands. There are workarounds to the limit problem using rownum and nested queries, but the Oracle 11G parser doesn't like them in an UPDATE command.

I had a similar problem before where I needed a limit within an update command, but not an offset. It was solved here: MySQL to Oracle Syntax Error (Limit / Offset / Update)

There is a workaround that Florin Ghita found using analytical functions.

 update table1 alf
  set nextcontractid = 
      (SELECT min(contractid) keep (dense_rank first order by lasttradedate asc) 
      FROM table1copy alf2
      WHERE alf2.assetid     = alf.assetid
      AND alf2.lasttradedate > alf.lasttradedate
      )
  where alf.complete = 0

This workaround allows me to get the top or bottom entry (by using asc or desc in the dense_rank command), but I am not able to find a proxy for the offset command, if I wanted the second or third row.

Another solution I've tried used a nested query. The first got the first 5 rows using the rownum command, ordered them in the opposite way, the MINUS-ed off the last four rows. This solution failed because the Oracle parser didn't understand the reference to a table in the outermost command referenced inside one of the nested queries.

(Same problem as I was having before: MySQL to Oracle Syntax Error (Limit / Offset / Update))

The challenge isn't simply to run a select statement in oracle with a limit and offset, as I can already do that through nested queries. The challenge is to get the select statement to work within an update statement, because even though the statement is syntactically correct, the Oracle parser fails to decode them. So far, nested queries (and Google) have failed me.

Has anyone else been having similar problems?

解决方案

Deleted original answer, not viable

I feel this should be doable in a single SQL statement, but so far the combination of the need for a correlated subquery and the need for some sort of analytic function has made everything I tried fail.

Here's a procedural method that I think will do what you want:

DECLARE
  CURSOR t IS
  SELECT LEAD(contractid,4) OVER (PARTITION BY assetid ORDER BY lasttradedate ASC) lead_contractid
    FROM table1
    FOR UPDATE;
BEGIN
  FOR r IN t LOOP
     UPDATE table1 SET nextcontractid = r.lead_contractid
       WHERE CURRENT OF t;
  END LOOP;
END;

这篇关于Oracle 11G中的LIMIT/OFFSET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！