Oracle PL/SQL Tutorial/Query Select/rownum
Содержание
Getting the Five Most Expensive Products
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL, 4 Description VARCHAR(1000) NOT NULL, 5 Price NUMBER NULL, 6 ImagePath VARCHAR(50) NULL, 7 soldout NUMBER(1,0) NULL, 8 Promotion NUMBER(1,0) NULL);
Table created. SQL> SQL> CREATE SEQUENCE ProductIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product 3 FOR EACH ROW 4 BEGIN 5 SELECT ProductIDSeq.NEXTVAL 6 INTO :NEW.ProductID FROM DUAL; 7 END; 8 /
Trigger created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler", "Long",14.99, "ruler.jpg", 0, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard", "keyboard",3.75, "keyboard.jpg", 0, 0);
1 row created. SQL> SQL> SELECT Name, Price FROM
2 (SELECT Name, Price 3 FROM Product 4 ORDER BY Price DESC, Name ASC) 5 WHERE ROWNUM<=5;
NAME PRICE
----------
PC 49.99 Ruler 14.99 Mouse 9.99 Desk 5.99 Pen 5.99 5 rows selected. SQL> SQL> drop table product; Table dropped. SQL> drop sequence ProductIDSeq; Sequence dropped.</source>
SELECTs using the ORDER BY clause with rownum
<source lang="sql">
SQL> SQL> SELECT username, default_tablespace FROM dba_users where rownum < 50; USERNAME DEFAULT_TABLESPACE
------------------------------
sqle SYSTEM SYS SYSTEM SYSTEM SYSTEM ANONYMOUS SYSAUX PLSQL USERS MDSYS SYSTEM OUTLN SYSTEM DIP SYSTEM TSMSYS SYSTEM FLOWS_FILES SYSAUX CTXSYS SYSAUX DBSNMP SYSAUX FLOWS_020100 SYSAUX XDB SYSAUX HR USERS 15 rows selected. SQL> SQL> SELECT username, default_tablespace
2 FROM dba_users 3 where rownum < 50 4 ORDER BY username;
USERNAME DEFAULT_TABLESPACE
------------------------------
ANONYMOUS SYSAUX CTXSYS SYSAUX DBSNMP SYSAUX DIP SYSTEM FLOWS_020100 SYSAUX FLOWS_FILES SYSAUX HR USERS sqle SYSTEM MDSYS SYSTEM OUTLN SYSTEM PLSQL USERS SYS SYSTEM SYSTEM SYSTEM TSMSYS SYSTEM XDB SYSAUX 15 rows selected. SQL></source>
Select with rownum
<source lang="sql">
SQL> SQL> CREATE TABLE s_inventory
2 (product_id NUMBER(7), 3 warehouse_id NUMBER(7), 4 amount_in_stock NUMBER(9), 5 reorder_point NUMBER(9), 6 max_in_stock NUMBER(9), 7 out_of_stock_explanation VARCHAR2(255), 8 restock_date DATE);
Table created. SQL> SQL> INSERT INTO s_inventory VALUES (10011, 101, 650, 625, 1100, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10012, 101, 600, 560, 1000, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10013, 101, 400, 400, 700, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10021, 101, 500, 425, 740, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10022, 101, 300, 200, 350, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10023, 101, 400, 300, 525, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20106, 101, 993, 625, 1000, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20108, 101, 700, 700, 1225, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20201, 101, 802, 800, 1400, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20510, 101, 1389, 850, 1400, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20512, 101, 850, 850, 1450, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (30321, 101, 2000, 1500, 2500, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (30326, 101, 2100, 2000, 3500, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (30421, 101, 1822, 1800, 3150, NULL, NULL); 1 row created. SQL> SQL> SQL> SELECT product_id
2 FROM s_inventory 3 WHERE ROWNUM < 5;
PRODUCT_ID
10011 10012 10013 10021
SQL> SQL> SQL> drop table s_inventory; Table dropped. SQL></source>
Use rownum to limit the resultset
<source lang="sql">
SQL> describe user_tables;
Name Null? Type --------------------------------------------------------- TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8) COMPRESSION VARCHAR2(8) DROPPED VARCHAR2(3)
SQL> SQL> select table_name, tablespace_name
2 from user_tables 3 where rownum < 30 4 order by table_name;
TABLE_NAME TABLESPACE_NAME
------------------------------
EMPLOYEE_TAB EMP_DEPT SYSTEM PEOPLE SYSTEM SESS_EVENT TEMP_ALL_OBJECTS TRANSACTION_TAB 6 rows selected. SQL></source>