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

What is a sequence [Sequence]

(1) similar to the auto_increment automatic growth mechanism in MySQL, but there is no auto_increment mechanism in Oracle

(2) it is a mechanism provided by oracle to generate unique numerical values.

(3) the main health value commonly used in a table

(4) the sequence can only be unique, not continuous.

Disclaimer: in oracle, only rownum always starts at 1 and continues

(5) the sequence value can be stored in memory and can be taken faster.

Question: why doesn't oracle use rownum as the main key directly?

The record of rownum=1 cannot always uniquely represent the user SMITH.

But the primary key = 1 can always represent SMITH as a unique user.

The purpose of a primary key is to uniquely identify a record, while rownum cannot uniquely identify a record.

Why use sequences?

(1) in the past, when we set the value for the main key, we needed to set the value manually, which was easy to make mistakes.

(2) the main health value of each table before is independent and cannot be shared.

Create a sequence emp_empno_seq for the empno field of the emp table

Create sequence sequence name create sequence emp_empno_seq

Delete sequence emp_empno_seq,drop sequence sequence name

Drop sequence emp_empno_seq

When querying the current value currval and the next value nextval of the emp_empno_seq sequence, the sequence name .nextval must be selected when using the sequence for the first time.

Select emp_empno_seq.nextval from dual;select emp_empno_seq.currval from dual

Use sequences to insert records into the emp table, and empno fields use sequence values

Insert into emp (empno) values (emp_empno_seq.nextval); insert into emp (empno) values (emp_empno_seq.nextval); insert into emp (empno) values (emp_empno_seq.nextval)

Modify the increment by property of the emp_empno_seq sequence to 20, and the default start with is 1 alter sequence sequence name

Alter sequence emp_empno_seqincrement by 20

Modify the increment by property of the modified emp_empno_seq sequence to 5

Alter sequence emp_empno_seqincrement by 5

Modify the start with property of the emp_empno_seq sequence, okay?

Alter sequence emp_empno_seqstart with 100

No, it will make a mistake.

However, when you create a sequence, you can specify the starting and growth values.

After you have the sequence, can you still set the value for the main key manually?

Insert into emp (empno) values (9999); insert into emp (empno) values (7900)

Sure

(lecture content)

Does deleting a table affect the sequence?

You cannot do the insert operation

Does deleting a sequence affect the table?

Real death of watch, death of sequence

[doubt: after I have done an experiment, after completely deleting the emp table, I can continue to use emp_empno_seq 's nextval and currval]

In hibernate, if you are accessing the oracle database server, how do you configure the tags in the User.hbm.xml mapping file?

(1) whether the underlying database support is required

Identity requires the underlying database to support auto_increment. In the MySQL database, you need to set the primary key field of the table to self-growing.

On the other hand, increment and uuid do not need the underlying database support and do not need to set the primary key field to self-growing.

(2) does it support multithreaded concurrent operations?

Increment can only be accessed by single thread, not by multi-thread.

Both identity and uuid support concurrent operations.

(3) applicable scenarios

If you only use a (dedicated) oracle database, you can use sequence,Hibernate to automatically create a sequence in the Oracle database.

If you're not sure about using oracle, mysql, or sqlserver, you can use native, which is a generic variable. Native is generally used.

Hibernate help documentation

Various additional generators

All generators implement the interface org.hibernate.id.IdentifierGenerator. This is a very simple interface. Some applications can choose to provide their own specialized implementations, however, Hibernate provides a range of built-in implementations. The shortcut names for the built-in generators are as follows:

Increment

Generates identifiers of type long, short or int that are unique only when no other process is inserting data into the same table. Do not use in a cluster.

Identity

Supports identity columns in DB2, MySQL, MS SQLServer, Sybase and HypersonicSQL. The returned identifier is of type long, short or int.

Sequence

Uses a sequence in DB2, PostgreSQL, Oracle, SAP DB, McKoi or a generator in Interbase. The returned identifier is of type long, short or int

Uuid

Generates a 128-bit UUID based on a custom algorithm. The value generated is represented as a string of 32 hexidecimal digits. Users can also configure it to use a separator (config parameter "separator") which separates the hexidecimal digits into 8 {sep} 8 {sep} 4 {sep} 8 {sep} 4.

Uuid2

Generates an IETF RFC 4122 compliant (variant 2) 128-bit UUID. The exact "version" (the RFC term) generated depends on the pluggable "generation strategy" used. Capable of generating values as java.util.UUID, java.lang.String or as a byte array of length 16 (byte [16]). The "generation strategy" is defined by the interface org.hibernate.id.UUIDGenerationStrategy.

Guid

Uses a database-generated GUID string on MS SQL Server and MySQL.

Native

Selects identity,sequence or hilo depending upon the capabilities of the underlying database.

Assigned

Lets the application assign an identifier to the object before save () is called. This is the default strategy if no element is specified.

Foreign

Uses the identifier of another associated object. It is usually used in conjunction with a primary key association.

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.