using rownum function in pl sql

Question

HELLO everyone please am new to plsql and am confused about ROWNUM function. In fact i have a plsql code that uses 2 tables EMPLOYEE and WORKS_ON and we create an associative array and then stores the first record and second one i will put both the code the results and the tables . my question is why we put rownum <= 1 to get the second record and when i use rownum <= 2 it gives an error : this the picture of the tables and the output

SET SERVEROUTPUT ON;

DECLARE

 --Declare the table
 TYPE EmpSSNarray
 IS TABLE OF employee.ssn%TYPE
 INDEX BY SIMPLE_INTEGER;

 --Declare variables using the table
 ManagementList  EmpSSNarray;
 WorkerList      EmpSSNarray;

BEGIN

 --Retrieve the first Supervisor
 SELECT superssn
 INTO ManagementList(1)
 FROM employee
 WHERE superssn IS NOT NULL
 AND ROWNUM <= 1;

 --Retrieve the second Supervisor
 SELECT superssn
 INTO ManagementList(2)
 FROM employee
 WHERE superssn IS NOT NULL
 AND ROWNUM <= 1
 AND superssn <> ManagementList(1);

 --Retrieve the first worker
 SELECT essn
 INTO WorkerList(1)
 FROM works_on
 WHERE hours IS NOT NULL
 AND ROWNUM <= 1
 AND essn NOT IN (ManagementList(1), ManagementList(2));

 --Retrieve the second worker
 SELECT essn
 INTO WorkerList(2)
 FROM works_on
 WHERE hours IS NOT NULL
 AND ROWNUM <= 1
 AND essn NOT IN (ManagementList(1), ManagementList(2),WorkerList(1));

 --Output the results
 dbms_output.put_line ('Managers are: ' || ManagementList(1) || ', ' || ManagementList(2));
 dbms_output.put_line ('Workers are: ' || WorkerList(1) || ', ' || WorkerList(2));


END;

ROWNUM is not a function, but pseudocolumn that's arbitrarly returned to restrict to one row whenever ROWNUM<=1. There should return one row within a SELECT .. INTO .. statement, whereas ROWNUM<=2 might return more than one row which would cause too_many_rows exception. — Barbaros Özhan
– Barbaros Özhan, Commented Apr 13, 2020 at 23:59
Yeah, I see, but what I didn't understand is why they used rownum<=1 to retrieve the second record I was thinking that rownum<=1 will retrieve the first record not the second PS: this code I got it from a course and test it. The output is totally correct — Amine Ch 99
– Amine Ch 99, Commented Apr 16, 2020 at 10:55

MT0 · Accepted Answer · 2020-04-14 01:40:53Z

Use a collection rather than a PL/SQL associative array:

CREATE TYPE EmpSSNarray IS TABLE OF NUMBER(8,0);

Then you can use BULK COLLECT INTO to store multiple rows into the collection and, if you've defined it in the global SQL scope rather than as a local PL/SQL type, then you can also use the MEMBER OF operator:

DECLARE
  ManagementList  EmpSSNarray;
  WorkerList      EmpSSNarray;
BEGIN
  SELECT superssn
  BULK COLLECT INTO ManagementList
  FROM   employee
  WHERE  superssn IS NOT NULL
  AND    ROWNUM <= 2;

  SELECT essn
  BULK COLLECT INTO WorkerList
  FROM   works_on
  WHERE  hours IS NOT NULL
  AND    ROWNUM <= 2
  AND    essn NOT MEMBER OF management_list;

 --Output the results
 dbms_output.put_line ('Managers are: ' || ManagementList(1) || ', ' || ManagementList(2));
 dbms_output.put_line ('Workers are: ' || WorkerList(1) || ', ' || WorkerList(2));
END;
/

So, if your tables are:

CREATE TABLE employee ( ssn, superssn ) AS
SELECT 1, 3    FROM DUAL UNION ALL
SELECT 2, 4    FROM DUAL UNION ALL
SELECT 3, 5    FROM DUAL UNION ALL
SELECT 4, 5    FROM DUAL UNION ALL
SELECT 5, NULL FROM DUAL;

CREATE TABLE works_on ( essn, hours ) AS
SELECT 1, 10 FROM DUAL UNION ALL
SELECT 5, 20 FROM DUAL UNION ALL
SELECT 2, 30 FROM DUAL;

Then the PL/SQL would output:

Managers are: 4, 3
Workers are: 5, 1

db<>fiddle here

collapsar · Accepted Answer · 2020-04-13 23:55:35Z

0

The queries are selecting into scalar variables, namely the elements of an associative array. As a consequence, the result sets of each of these queries must be a single record from which a single column is projected.

rownum is an ordinal number assigned to records in a result set indicating the order in which they are returned from the database. If n is the size of the result set, records are numbered contiguously with 1..n.

The rownum <= 1 clause in the where conditions thus is a generic means to reduce the number of records in the result set to 1. Obviously, the clause rownum <= [any number k > 1] will in general produce k records triggering an error.

The queries presented might still fail if the queries (without the rownum clause) would produce an empty result set. It is likely that business logic rules out this possibility ( eg. it is guaranteed [and enforced by constraints or triggers] that the employee table always contains at least 2 supervisors ).

At first glance it may seem not to make sense to limit the result set to the 'first record' without sorting. However, if only some record fulfilling the other conditions in the where clause (or no conditions at all) is of interest, the construct may be sensible as it avoids potentially expensive sorting.

answered Apr 13, 2020 at 23:55

collapsar

17.4k5 gold badges40 silver badges66 bronze badges

3 Comments

Amine Ch 99 Over a year ago

Yeah, I see, but what I didn't understand is why they used rownum<=1 to retrieve the second record I was thinking that rownum<=1 will retrieve the first record not the second PS: this code I got it from a course and test it. The output is totally correct

collapsar Over a year ago

The second record is just the first of all other (matching) records. So the task is to filter the first record which was obtained by the preceding query. The condition used is superssn <> ManagementList(1).

Amine Ch 99 Over a year ago

so if i understand we filter the matching records and then we use the rownum so in this case the seconde record is the first of all records that match the superssn value so the second record will have a rownum == 1 is that true ?

psaraj12 · Accepted Answer · 2020-04-14 07:41:10Z

0

IF you want to retrieve the second record you can use something like the below

SELECT superssn
    INTO   Managementlist(2)
    FROM   (SELECT superssn,
                   ROWNUM rw,
                    ssn
            FROM   employee
            WHERE  superssn IS NOT NULL
                   order by ssn)
    WHERE  rw = 2;

answered Apr 14, 2020 at 7:41

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

2 Comments

Amine Ch 99 Over a year ago

yes i know but am wondering why they used rownum<=1 to retrieve the second row .

psaraj12 Over a year ago

there must be some order by condition to decide first or second row but if you use just rownum in the inner query the second row will be random

Collectives™ on Stack Overflow

using rownum function in pl sql

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related