Use rownum = 1 and select into : ROWNUM « Select Query « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Select Query » ROWNUM 
Use rownum = 1 and select into
   
SQL>
SQL>
SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(72),
  8      COMM NUMBER(72),
  9      DEPTNO NUMBER(2)
 10  );

Table created.

SQL>
SQL> INSERT INTO EMP VALUES(7369'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7499'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 160030030);

row created.

SQL> INSERT INTO EMP VALUES(7521'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 125050030);

row created.

SQL> INSERT INTO EMP VALUES(7566'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7654'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250140030);

row created.

SQL> INSERT INTO EMP VALUES(7698'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

row created.

SQL> INSERT INTO EMP VALUES(7782'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

row created.

SQL> INSERT INTO EMP VALUES(7788'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7839'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

row created.

SQL> INSERT INTO EMP VALUES(7844'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500030);

row created.

SQL> INSERT INTO EMP VALUES(7876'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7900'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

row created.

SQL> INSERT INTO EMP VALUES(7902'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7934'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

row created.

SQL>
SQL> CREATE TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );

Table created.

SQL>
SQL> INSERT INTO DEPT VALUES (10'ACCOUNTING', 'NEW YORK');

row created.

SQL> INSERT INTO DEPT VALUES (20'RESEARCH', 'DALLAS');

row created.

SQL> INSERT INTO DEPT VALUES (30'SALES', 'CHICAGO');

row created.

SQL> INSERT INTO DEPT VALUES (40'OPERATIONS', 'BOSTON');

row created.

SQL>
SQL> declare
  2      l_ename  emp.ename%type;
  3  begin
  4      for x in (select deptno, dname from dept order by deptno)
  5      loop
  6          begin
  7             select ename into l_ename
  8               from emp
  9              where deptno = x.deptno
 10                and sal = (select max(sal)from emp where deptno = x.deptno)
 11             and rownum = 1;
 12          exception when no_data_found
 13          then
 14              l_ename := '(none)';
 15          end;
 16          dbms_output.put_linex.deptno || ', ' || x.dname || ', ' || l_ename );
 17      end loop;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table dept;

Table dropped.

SQL> drop table emp;

Table dropped.

SQL>

   
    
    
  
Related examples in the same category
1.Use rownum in select clause
2.Use rownum in update set statement
3.Use rownum in where clause to control the row count
4.Use rownum column with order by
5.Use rownum in where clause to limit the row count
6.Limit the query to display only the top 3 highest paid employees.
7.Use rownum to limit the subquery
8.Retrieving the Top Five Students with ROWNUM
9.Getting the Five Most Expensive Products
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.