Oracle9i-SQL-12 语言 大全.pdf

12 Copyright ? Oracle Corporation, 2001. All rights reserved. Other Database Objects 12-2 Copyright ? Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: ? Create, maintain, and use sequences ? Create and maintain indexes ? Create private and public synonyms 12-3 Copyright ? Oracle Corporation, 2001. All rights reserved. Database Objectst j t Description Basic unit of storage; composed of rows and columns Logically represents subsets of data from one or more tables Generates primary key values Improves the performance of some queries Alternative name for an object Object Table View Sequence Index Synonym 12-4 Copyright ? Oracle Corporation, 2001. All rights reserved. What Is a Sequence? A sequence: ? Automatically generates unique numbers ? Is a sharable object ? Is typically used to create a primary key value ? Replaces application code ? Speeds up the efficiency of accessing sequence values when cached in memory 12-5 Copyright ? Oracle Corporation, 2001. All rights reserved. The CREATE SEQUENCE Statement Syntax Define a sequence to generate sequential numbers automatically: CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; 12-6 Copyright ? Oracle Corporation, 2001. All rights reserved. Creating a Sequence ? Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table. ? Do not use the CYCLE option. CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; Sequence created. CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; Sequence created. 12-7 Copyright ? Oracle Corporation, 2001. All rights reserved. Confirming S