• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1228
  • Last Modified:

PL/SQL query %rowtype

What happens if the next code, the query returns more than just one row??

DECLARE
  v_employees employees%ROWTYPE;
BEGIN
  SELECT *
    INTO v_employees
    FROM employees
    WHERE rownum = 1;
END;

Image that there are more that 2 register where rownum = 1, what would happen? An exception would be raised or what?
0
axtur
Asked:
axtur
  • 4
  • 2
  • 2
3 Solutions
 
sdstuberCommented:
you can't have 2 rows with rownum=1,  

rownum is assigned as the rows are returned, but before they are ordered (if you have an order by)

so, even if you have 1000 identical rows, they will each have their own distinct rownum


0
 
axturAuthor Commented:
So rownum refers to the number of rows of the given result, or is the name of a field in the database??
0
 
sdstuberCommented:
it's a "pseudo-column"  meaning you can think of it as column that is automatically applied to your result set where each row gets a unique value beginning at 1.

it's the same as what you would number rows intuitively.

for example if I have rows like this...

awking00 master
angeliii wizard
sdstuber  sage
sujith80 wizard

the first row, awking00 would be rownum=1
the second row, angeliii would be rownum=2
the third row, sdstuber would be rownum=3
the fourth row, sujith80 would be rownum=4

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I did not know that the people mentioned where employees at the same company :)  

additional info about the rownum:
the value for rownum is generated "after" the order by, so if you do a order by name, you will get this:

angeliii would be rownum=1
awking00 would be rownum=2
sdstuber would be rownum=3
sujith80 would be rownum=4

important: if you plan to use rownum with WHERE, there are some gotchas:
WHERE rownum = 3   => will return 0 rows
WHERE rownum >= 2   => will return 0 rows
WHERE rownum = 1   => will return 1 rows
WHERE rownum <= 3   => will return 3 rows

so, = and >= will not work except for 1, > will not work except for 0

also, if you use WHERE rownum <= 2 ORDER BY something, not that the WHERE rownum will be done BEFORE the order by.

tip: read up about the "new" function ROW_NUMBER(), that is most often an alternative to what you might need.

0
 
sdstuberCommented:
small but important correction...

rownum is generated BEFORE the order by, which, is why the note about "WHERE rownum <= 2 ORDER BY something" works, the rwonum is BEFORE the order by


with mytable as (select
'awking00' name, 'master' rank from dual union all select
'angeliii' ,'wizard'  from dual union all select
'sdstuber' , 'sage'  from dual union all select
'sujith80' ,'wizard'  from dual
)
select rownum, name, rank from mytable order by name


if you want to put numbers AFTER the order by you must do the ordering in a sub query...



with mytable as (select
'awking00' name, 'master' rank from dual union all select
'angeliii' ,'wizard'  from dual union all select
'sdstuber' , 'sage'  from dual union all select
'sujith80' ,'wizard'  from dual
)
select rownum, name, rank from (
select name, rank from mytable order by name
)

0
 
axturAuthor Commented:
excelent answer
0
 
sdstuberCommented:
glad we could help
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry for the typo, it's indeed before the order by. ...
thanks for correcting that, sdstuber.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now