Shulou(Shulou.com)06/01 Report--

1. The database object of Oracle: sequence a, what is sequence? Sequence (sequence): a database object used to automatically generate unique numbers. It is mainly used to provide primary key values.

B. how to create a sequence? Syntax format for creating a sequence: CREATE SEQUENCE sequence [INCREMENT BY n]-each increment of numeric value (step size). The default value is 1 (n is positive, then self-increment) N is negative, then subtract) [START WITH n]-- from which value (initial value), the default value is 1 [{MAXVALUE n | NOMAXVALUE}]-- default value is NOMAXVALUE [{MINVALUE n | NOMINVALUE}]-- default value is NOMINVALUE [{CYCLE | NOCYCLE}]-- whether to cycle, default value is NOCYCLE [{CACHE n | NOCACHE}]-- whether to cache, default is not cached example: SQL > create sequence deptno_seq start with 50 increment by 10 maxvalue 70 cache 3 The sequence has been created. C. How to use sequences? When using a sequence, the sequence must be referenced through the pseudo columns NEXTVAL and CURRVAL. NEXTVAL is used to return the next sequence value by reference. For example: deptno_seq.nextval. CURRVAL is used to return the current sequence value by reference. For example: deptno_seq.currval. Example 1: when inserting data, use a sequence (note that when inserting a sequence for the first time, only nextval pseudo columns can be used, not currval pseudo columns) the SQL > create table deptnew as select * from dept; table has been created. SQL > insert into deptnew (deptno, dname, loc) values (deptno_seq.nextval, 'test_dname','test_loc'); 1 line has been created. SQL > select * from deptnew; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 test_dname test_loc View current sequence value: SQL > select deptno_seq.currval from dual; CURRVAL 50

-additional note: dual table in Oracle

The dual table is a virtual table that is used to form the syntax rules of select. Oracle ensures that there is always only one record in the dual. We can use it to do many things, as follows:

1. To view the current user, you can execute the following statement in SQLPlus: select user from dual

2. Used to call the system function: select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from dual;-- to get the current system time select SYS_CONTEXT ('USERENV','TERMINAL') from dual;-- to get the hostname select SYS_CONTEXT (' USERENV','language') from dual;-- to get the current localeselect dbms_random.random from dual;-- to get a random number

3. Get the next value or current value of the sequence, and use the following statement: select your_sequence.nextval from dual;-- gets the next value of the sequence your_sequence select your_sequence.currval from dual;-- gets the current value of the sequence your_sequence

4. Can be used as a calculator: select 7: 9 from dual;d, how to query the sequence? Query data dictionary view USER_SEQUENCES to get sequence definition information: SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size, last_number FROM user_sequences;LAST_NUMBER columns have two cases. If NOCACHE is specified, LAST_NUMBER displays the next value in the sequence; if CACHE is specified, LAST_NUMBER displays the next value of the last sequence number in the sequence cache. SQL > SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size, last_number from user_sequences SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C CACHE_SIZE LAST_NUMBER DEPTNO_SEQ 1 70 10 N 3 80e, the command to modify the sequence: ALTER SEQUENCE. ALTER SEQUENCE sequence [INCREMENT BY n]-- the value of each increase [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}]-- whether a loop [{CACHE n | NOCACHE}] is required-- note that the initial value of the sequence cannot be modified. SQL > alter sequence deptno_seq maxvalue 90; sequence has changed. F, how to delete a sequence? The command to delete the sequence: DROP SEQUENCE. The syntax format is: DROP SEQUENCE sequence;SQL > DROP SEQUENCE deptno_seq; sequence deleted. G, sequence Note if you specify a cache value, access efficiency can be improved, but the possibility of hopping will also occur when using cach. (that is, sequence gaps) sequences return sequence gaps (cracks) in the following cases: rollback, system exceptions, and multiple tables using the same sequence at the same time. 2. The database object of Oracle: index a, what is index? An index is a storage structure created to speed up the retrieval of rows of data in a table. B, the index is classified according to the number of index columns: single-column index, compound index. Uniqueness of column values by index: unique index, non-unique index. C. How to create an index? Create an index on one or more columns. Syntax format: CREATE INDEX index ON table (column [, column]...); example of creating a single-column index: SQL > create index idx_ename on emp (ename); the index has been created. Example of creating a composite index: SQL > create index idx_deptno on emp (deptno, job); the index has been created. Create a unique index (the column values of the index cannot be duplicated) SQL > create unique index idx_dname on dept (dname); the index has been created. Create a non-unique index SQL > create index idx_job on emp (job); the index has been created. When will the index be created? An index can be created in the following situations:

1. On the table column frequently referenced by the where clause.

2. In order to improve the performance of multi-table joins, indexes should be established on the join columns.

3. Creating indexes on frequently sorted columns can speed up the speed of data sorting. When an index cannot be created:

1. The watch is very small.

2. Columns do not often appear as join conditions or in WHERE clauses.

3. The table is updated frequently. E. How to query the index? You can use the data dictionary views USER_INDEXES (index information) and USER_IND_COLUMNS (index column information) to view the information of the index. Example: SELECT uic.index_name, uic.column_name, uic.column_position, ui.unqueness FROM user_indexes ui, user_ind_columns uic WHERE uic.index_name = ui.index_name AND ui.table_name = 'EMP'; SQL > SELECT uic.index_name, uic.column_name, uic.column_position, ui.uniqueness FROM user_indexes ui, user_ind_columns uic WHERE uic.index_name = ui.index_name AND ui.table_name =' EMP' INDEX_NAME COLUMN_NAME COLUMN_POSITION UNIQUENES IDX_JOB JOB 1 NONUNIQUE. PK_EMP EMPNO 1 UNIQUEf, how to delete an index? Delete the index using the DROP INDEX command: the DROP INDEX index;SQL > DROP INDEX idx_ename; index has been deleted. 3. The database object of Oracle-synonym a, what are synonyms? A synonym is an alias for a database schema object. Scheme objects: tables, indexes, views, triggers, sequences, synonyms, stored procedures, etc.; non-scheme objects: tablespaces, users, roles, etc. For example, stu is synonymous with studuent_info. B. the function of synonyms

1. Simplify object access.

2. Provide the security of object access: in multi-user collaborative development, the names of objects and their holders can be shielded.

C. The classification of synonyms includes public synonyms and private synonyms. Public synonym: it means that all users in the database can use it; private synonym: it can only be owned by the user who created it, and other users must refer to it with the scheme name. D, create synonyms create public synonyms: CREATE PUBLIC SYNONYM synonym FOR [schema.] object; create private synonyms: CREATE SYNONYM synonym FOR [schema.] object; Note: schema refers to the scheme name. (same as user name); the user needs permission to create synonyms when creating synonyms. Give scott users permission to create synonyms example: (create public and private synonyms) SQL > conn sys/02000059 as sysdba; is connected. SQL > grant create public synonym to scott; authorization succeeded. SQL > grant create synonym to scott; authorization succeeded. Create a common synonym dn for the deptnew table under the scott user: SQL > conn scott/02000059 is connected. The synonym SQL > create public synonym dn for scott.deptnew; has been created. SQL > select * from dn; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 test_dname test_loc create private synonyms for the emp01 table under scott users em example: SQL > create synonym em for scott.emp01; synonyms have been created. SQL > select * from em; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7951 EASON ANALYST 7566 01-December-17 3000 20 7369 G_EASON CLERK 7902 17-December-80 800 20. 7934 MILLER CLERK 7782 23-January-82 1300 10 9 lines have been selected. E. Viewing synonyms when creating synonyms, Oracle stores the information about the synonyms in the data dictionary. By querying the data dictionary view USER_SYNONYMS, you can display the details of all synonyms for the current user. Example: SELECT synonym_name, table_owner, table_name FROM user_synonyms WHERE synonym_name = 'EM';SQL > SELECT synonym_name, table_owner, table_name FROM user_synonyms WHERE synonym_name =' EM' The synonym sequence provided by SYNONYM_NAME TABLE_OWNER TABLE_NAMEEM SCOTT EMP01f and Oracle to the following data dictionary view, data dictionary: USER_SEQUENCES, corresponding synonym: SEQ. Index, data dictionary: USER_INDEXES, corresponding synonym: IND. Synonym: data dictionary: USER_SYNONYMS, corresponding synonym: SYN. Example View Private synonyms: SQL > select * from syn where synonym_name = 'EM';SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINKEM SCOTT EMP01 example View Public synonyms: SQL > SELECT synonym_name, table_owner, table_name FROM all_synonyms WHERE synonym_name =' EM';SQL > select * from syn where synonym_name = 'EM' SYNONYM_NAME TABLE_OWNER TABLE_NAME EM SCOTT EMP01g, delete synonyms delete public synonyms: DROP PUBLIC SYNONYM synonym; delete private synonyms: DROP SYNONYM synonym

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.