Oracle PL/SQL/Table/ROWNUM
Содержание
Including the ROWNUM pseudo-column in the WHERE clause
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- Including the ROWNUM pseudo-column in the WHERE clause
SQL>
SQL> SELECT ROWNUM, City
2 FROM Employee;
ROWNUM CITY
---------- ----------
1 Toronto
2 Vancouver
3 Vancouver
4 Vancouver
5 Vancouver
6 New York
7 New York
8 Vancouver
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Only list the first four rows using ROWNUM
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- Only list the first four rows using ROWNUM
SQL>
SQL> SELECT ROWNUM, ID, City
2 FROM Employee
3 WHERE ROWNUM < 5;
ROWNUM ID CITY
---------- ---- ----------
1 01 Toronto
2 02 Vancouver
3 03 Vancouver
4 04 Vancouver
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Select the Top 5
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6)
10 );
Table created.
SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL>
SQL> select last_name, hire_date
2 from employees
3 order by hire_date;
LAST_NAME HIRE_DATE
-------------------------------------------------- ---------
Kyte 16-JUN-98
Wells 01-JAN-02
Bliss 01-JAN-02
Lawson 01-JAN-02
Viper 13-JUN-08
Beck 13-JUN-08
Oracle 13-JUN-08
Java 13-JUN-08
8 rows selected.
SQL>
SQL> select last_name, hire_date
2 from employees
3 where rownum < 6
4 order by hire_date;
LAST_NAME HIRE_DATE
-------------------------------------------------- ---------
Kyte 16-JUN-98
Wells 01-JAN-02
Lawson 01-JAN-02
Bliss 01-JAN-02
Viper 13-JUN-08
SQL>
SQL> select last_name, hire_date
2 from ( select last_name, hire_date
3 from employees
4 order by hire_date )
5 where rownum <= 5;
LAST_NAME HIRE_DATE
-------------------------------------------------- ---------
Kyte 16-JUN-98
Lawson 01-JAN-02
Wells 01-JAN-02
Bliss 01-JAN-02
Viper 13-JUN-08
SQL>
SQL> drop table employees;
Table dropped.
SQL>
The values for ROWNUM column are generated before the order by was applied
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- the values for ROWNUM column are generated before the order by was applied;
SQL>
SQL> SELECT ROWNUM, ID, First_Name, Last_Name
2 FROM Employee
3 WHERE ROWNUM < 4
4 ORDER BY Salary DESC;
ROWNUM ID FIRST_NAME LAST_NAME
---------- ---- ---------- ----------
2 02 Alison Mathews
3 03 James Smith
1 01 Jason Martin
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
User rownum to get only first 200 records
SQL>
SQL> select owner, table_name
2 from all_tables
3 where rownum < 200
4 order by owner, table_name ;
OWNER TABLE_NAME
------------------------------ ------------------------------
OUTLN OL$
OUTLN OL$HINTS
OUTLN OL$NODES
SYS ACCESS$
SYS ARGUMENT$
SYS ASSOCIATION$
SYS ATTRCOL$
SYS ATTRIBUTE$
SYS AUDIT$
SYS BOOTSTRAP$
SYS CCOL$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS CDC_CHANGE_SETS$
SYS CDC_CHANGE_SOURCES$
SYS CDC_SYSTEM$
SYS CDEF$
SYS CLU$
SYS COL$
SYS COLLECTION$
SYS COLTYPE$
SYS COL_USAGE$
SYS COM$
SYS CON$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS CONTEXT$
SYS DEFROLE$
SYS DEFSUBPART$
SYS DEFSUBPARTLOB$
SYS DEPENDENCY$
SYS DIM$
SYS DIMATTR$
SYS DIMJOINKEY$
SYS DIMLEVEL$
SYS DIMLEVELKEY$
SYS DIR$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS DUAL
SYS DUC$
SYS ERROR$
SYS EXPACT$
SYS EXPDEPACT$
SYS EXPDEPOBJ$
SYS EXPPKGACT$
SYS EXPPKGOBJ$
SYS EXTERNAL_LOCATION$
SYS EXTERNAL_TAB$
SYS FET$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS FILE$
SYS HIER$
SYS HIERLEVEL$
SYS HISTGRM$
SYS HIST_HEAD$
SYS ICOL$
SYS ICOLDEP$
SYS IDL_CHAR$
SYS IDL_SB4$
SYS IDL_UB1$
SYS IDL_UB2$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS ID_GENS$
SYS INCEXP
SYS INCFIL
SYS INCVID
SYS IND$
SYS INDARRAYTYPE$
SYS INDCOMPART$
SYS INDOP$
SYS INDPART$
SYS INDPART_PARAM$
SYS INDSUBPART$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS INDTYPES$
SYS JAVAOBJ$
SYS JAVASNM$
SYS JOB$
SYS KOPM$
SYS LIBRARY$
SYS LINK$
SYS LOB$
SYS LOBCOMPPART$
SYS LOBFRAG$
SYS METAFILTER$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS METANAMETRANS$
SYS METAPATHMAP$
SYS METASCRIPT$
SYS METASCRIPTFILTER$
SYS METASTYLESHEET
SYS METAVIEW$
SYS METAXSL$
SYS METAXSLPARAM$
SYS METHOD$
SYS MIGRATE$
SYS MLOG$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS MLOG_REFCOL$
SYS MON_MODS$
SYS MON_MODS_ALL$
SYS NOEXP$
SYS NTAB$
SYS OBJ$
SYS OBJAUTH$
SYS OBJPRIV$
SYS OID$
SYS OPANCILLARY$
SYS OPARG$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS OPBINDING$
SYS OPERATOR$
SYS OPQTYPE$
SYS PARAMETER$
SYS PARTCOL$
SYS PARTLOB$
SYS PARTOBJ$
SYS PENDING_SESSIONS$
SYS PENDING_SUB_SESSIONS$
SYS PENDING_TRANS$
SYS PROCEDURE$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS PROCEDUREC$
SYS PROCEDUREINFO$
SYS PROCEDUREJAVA$
SYS PROCEDUREPLSQL$
SYS PROFILE$
SYS PROFNAME$
SYS PROPS$
SYS PROXY_DATA$
SYS PROXY_ROLE_DATA$
SYS REFCON$
SYS REG_SNAP$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS RESOURCE_COST$
SYS RESOURCE_MAP
SYS RESULT$
SYS RGCHILD$
SYS RGROUP$
SYS RLS$
SYS RLS_CTX$
SYS RLS_GRP$
SYS RLS_SC$
SYS RULESET$
SYS SECOBJ$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS SEG$
SYS SEQ$
SYS SETTINGS$
SYS SLOG$
SYS SNAP$
SYS SNAP_COLMAP$
SYS SNAP_LOADERTIME$
SYS SNAP_LOGDEP$
SYS SNAP_OBJCOL$
SYS SNAP_REFOP$
SYS SNAP_REFTIME$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS SNAP_SITE$
SYS SOURCE$
SYS SQL$
SYS SQL$TEXT
SYS SQLPROF$
SYS SQLPROF$ATTR
SYS SQLPROF$DESC
SYS SQL_VERSION$
SYS STMT_AUDIT_OPTION_MAP
SYS SUBCOLTYPE$
SYS SUBPARTCOL$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS SUM$
SYS SUMAGG$
SYS SUMDELTA$
SYS SUMDEP$
SYS SUMDETAIL$
SYS SUMINLINE$
SYS SUMJOIN$
SYS SUMKEY$
SYS SUMPARTLOG$
SYS SUMPRED$
SYS SUMQB$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS SUPEROBJ$
SYS SYN$
SYS SYSAUTH$
SYS SYSTEM_PRIVILEGE_MAP
SYS TAB$
SYS TABCOMPART$
SYS TABLE_PRIVILEGE_MAP
SYS TABPART$
SYS TABSUBPART$
SYS TRIGGER$
SYS TRIGGERCOL$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS TRIGGERJAVAC$
SYS TRIGGERJAVAF$
SYS TRIGGERJAVAM$
SYS TRIGGERJAVAS$
SYS TRUSTED_LIST$
SYS TS$
SYS TSQ$
SYS TYPE$
SYS TYPED_VIEW$
SYS TYPEHIERARCHY$
SYS TYPE_MISC$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS UET$
SYS UGROUP$
SYS UNDO$
SYS USER$
SYS USER_ASTATUS_MAP
SYS USER_HISTORY$
SYS USTATS$
SYS VIEW$
SYS VIEWCON$
SYS VIEWTRCOL$
SYS VTABLE$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS _default_auditing_options_
199 rows selected.
SQL>
SQL>
SQL>