通过在Oracle 11g中指定ROWNUM可以删除行吗?

问题描述

我的一些值如下:

MYSTRING
--------
Dharshan
Ramalingam
Devid

我可以通过下面的查询选择特定的行值

I can select the specific row value through below query

select * from ( select mystring , rownum rn  FROM teststring  )

其中rn = 2;

告诉删除第二行的方法，给出行号，并给我简要的解释.我尝试了如下操作，但不起作用...

tell the way to delete this second row giving the row no and give me the brief explanation . I have tried as below but its not work....

delete   from testring where rownum=2;

推荐答案

select * from ( select mystring , rownum rn  FROM teststring  ) where  rn = 2;

您的查询随机返回没有指定顺序的行. 按堆组织的表是具有以无特定顺序存储的行的表.这是标准Oracle表.

Your query returns rows randomly without a specified order. A heap-organized table is a table with rows stored in no particular order. This is a standard Oracle table.

从Oracle 文档 ，

From Oracle documentation,

delete from testring where rownum=2;

您的上述查询显示您不理解 ROWNUM的方式有效 .

Your above query shows you do not understand how ROWNUM works.

您的查询将永远不会删除任何行.

ROWNUM 值在它通过查询的谓词阶段之后但在查询进行任何排序或聚合之前被分配给一行.另外，仅在分配ROWNUM值后才递增它，这就是以下查询永远不会返回行的原因:

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select * 
  from t 
 where ROWNUM = 2;

因为第一行的ROWNUM = 2不正确，所以ROWNUM不会前进到2.因此，任何ROWNUM值都不会大于1.

Because ROWNUM = 2 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1.

如何正确使用ROWNUM:

当您要基于ROWNUM选择一行时，可以执行分页操作:

As you wanted to select a row based on ROWNUM, you could do something like pagination:

SQL> SELECT empno
  2  FROM
  3    (SELECT empno, sal, ROWNUM AS rnum FROM
  4      ( SELECT empno, sal FROM emp ORDER BY sal
  5      )
  6    )
  7  WHERE rnum =2;

     EMPNO
----------
      7900

它分为三个级别:

• 最里面的子查询首先根据 ORDER BY 子句对行进行排序.
• 在第二级中，子查询分配 ROWNUM
• 最外面的查询根据内部查询给定的ROWNUM过滤行，内部查询不再是伪列，而是子查询结果集.

• Innermost sub-query first sorts the rows based on the ORDER BY clause.
• In second level, the sub-query assigns ROWNUM
• The outermost query filters the rows based on the ROWNUM given by inner query which is no more a pseudo-column but the sub-query resultset.

这篇关于通过在Oracle 11g中指定ROWNUM可以删除行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！