Rownum - sql pseudocolumns, PL-SQL Programming

ROWNUM

The ROWNUM returns a number representing the order in which a row was selected from the table. The first row selected has a ROWNUM of 1; the second row has a ROWNUM of 2, and so on. If the SELECT statement involves an ORDER BY clause, then the ROWNUMs are assigned to the retrieved rows before the sort is complete.

You can use the ROWNUM in an UPDATE statement to assign exclusive values to each row in a table. You can also use ROWNUM in the WHERE clause of a SELECT statement to the limit the number of rows retrieved, as shown:

DECLARE

CURSOR c1 IS SELECT empno, sal FROM emp

WHERE sal > 2000 AND ROWNUM < 10; -- returns 10 rows

The value of ROWNUM increase only whenever a row is retrieved, so the only significant use of ROWNUM in a WHERE clause is

... WHERE ROWNUM < constant;

Posted Date: 10/4/2012 3:43:52 AM | Location : United States







Related Discussions:- Rownum - sql pseudocolumns, Assignment Help, Ask Question on Rownum - sql pseudocolumns, Get Answer, Expert's Help, Rownum - sql pseudocolumns Discussions

Write discussion on Rownum - sql pseudocolumns
Your posts are moderated
Related Questions
Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax:

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

Advantages of Exceptions Using the exceptions for the error handling has many benefits. Without an exception handling, every time you issue a command, you should ensure for th

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

Stored Subprograms Normally, tools (like Oracle Forms) which incorporate the PL/SQL engine can store subprograms locally for later, strictly local execution. Though, to become

Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

Initial thought process: Design a script which was simple and user friendly. Integrate procedures/functions to extract data under the hood. I focused on giving the user the opt

Components of an Object Type: An object type encapsulates the operations and data. Therefore, you can declare the methods and attributes in an object type specification, but no

Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no