Oracle PL/SQL Tutorial/Query Select/rownum
Версия от 13:45, 26 мая 2010; (обсуждение)
Содержание
Getting the Five Most Expensive Products
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.
SELECTs using the ORDER BY clause with rownum
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>
Select with rownum
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>
Use rownum to limit the resultset
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>