Oracle PL/SQL Tutorial/Query Select/rownum

Материал из SQL эксперт
Перейти к: навигация, поиск

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>