Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
SQL VISUAL QUICKSTART GUIDE- P44:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 15 Generating Sequences Listing 15.4 defines the sequence shown in Figure 15.4. You can use a sequence generator in a few ways. The SQL standard provides the built-in function NEXT VALUE FOR to increment a sequence value as in INSERT INTO shipment part_num desc quantity VALUES NEXT VALUE FOR part_seq motherboard 5 If you re creating a column of unique values you can use the keyword IDENTITY to define a sequence right in the CREATE TABLE statement CREATE TABLE parts part_num INTEGER AS IDENTITY INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 START WITH 1 NO CYCLE desc AS VARCHAR 100 quantity INTEGER This table definition lets you omit NEXT VALUE FOR when you insert a row INSERT INTO shipment desc quantity VALUES motherboard 5 SQL also provides ALTER SEQUENCE and DROP SEQUENCE to change and remove sequence generators. Listing 15.4 Create a sequence generator for the consecutive integers 1 to 10 000. See Figure 15.4 for the result. Listing CREATE SEQUENCE part_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 START WITH 1 NO CYCLE 1 2 3 . 9998 9999 10000 Figure 15.4 The sequence that Listing 15.4 generates. Tip nRIUIC Oracle DB2 and PostgreSQL support CREATE SEQUENCE ALTER SEQUENCE and DROP SEQUENCE. In Oracle use NOCYCLE instead of NO CYCLE. See your DBMS documentation to see how sequences are used in your system. Most DBMSs don t support IDENTITY columns because they have other pre-SQL 2003 ways that define columns with unique values. See Table 3.18 in Unique Identifiers in Chapter 3. PostgreSQL s generate_series function offers a quick way to generate numbered rows. 410 SQL Tricks Listing 15.5 Create a one-column table that contains consecutive integers. See Figure 15.5 for the result. Listing CREATE TABLE temp09 i CHAR l NOT NULL PRIMARY KEY INSERT INTO temp09 VALUES 0 INSERT INTO temp09 VALUES l INSERT INTO temp09 VALUES 2 INSERT INTO temp09 VALUES 3 INSERT INTO temp09 VALUES 4 INSERT INTO temp09 VALUES 5 INSERT INTO temp09 VALUES 6 INSERT INTO temp09 VALUES 7 INSERT
