Oracle Series: (24) sequence

What is the sequence "Sequence"

(1) Similar to the Auto_increment automatic growth mechanism in MySQL, but no auto_increment mechanism in Oracle

(2) is a mechanism provided by Oracle to produce a unique numeric value

(3) The main health value of the table is usually used

(4) Sequence can only be guaranteed to be unique and cannot guarantee continuous

Disclaimer: In Oracle, only rownum is always maintained starting at 1 and continues

(5) Sequence value, can be placed in memory, take the faster

Question: Why does Oracle not use RowNum directly?

Rownum=1 This record can't always be the only one that says Smith this user

But the primary key =1 can always uniquely represent the Smith user

The purpose of a primary key is to uniquely identify a record, and RowNum cannot implement a record that uniquely identifies it.

Why use a sequence

(1) Before we set the value for the main health, we need to set the value manually, error-prone

(2) The main health value of each table before, is independent, cannot share

For the Empno field of the EMP table, create the sequence emp_empno_seq,

Create sequence sequence name

Create sequence emp_empno_seq;

Delete sequence Emp_empno_seq,drop sequence sequence name

Drop sequence emp_empno_seq;

Querying the current value of the emp_empno_seq sequence Currval and the next value Nextval, the first time you use a sequence, you must choose: Sequence name. nextval

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

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/87/19/wKioL1fUM5fj24CbAACS43O9YOU257.jpg "title=" 022. JPG "alt=" wkiol1fum5fj24cbaacs43o9you257.jpg "/>

Using sequences, inserting records into the EMP table, empno fields using 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);

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/87/1B/wKiom1fUNCqBHLAAAAChrDkakmA007.jpg "title=" 023. JPG "alt=" wkiom1funcqbhlaaaachrdkakma007.jpg "/>

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

Alter sequence emp_empno_seqincrement by 20;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/87/19/wKioL1fUNM2AKGNAAACTc88ACOA413.jpg "title=" 024. JPG "alt=" wkiol1funm2akgnaaactc88acoa413.jpg "/>

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, OK?

Alter sequence emp_empno_seqstart with 100;

No, I'll get an error.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/87/19/wKioL1fUOuODpxEVAABEYlKiufE815.jpg "title=" 025. JPG "alt=" wkiol1fuouodpxevaabeylkiufe815.jpg "/>

However, you can specify a starting value and a growth value when creating a sequence

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/87/19/wKioL1fUPDWSdQSqAABlgq4nmLY618.jpg "title=" 027. JPG "alt=" wkiol1fupdwsdqsqaablgq4nmly618.jpg "/>

With a sequence, can you set the value manually for the master Jian?

INSERT into EMP (EMPNO) VALUES (9999), insert into EMP (EMPNO) values (7900);

OK

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/87/1B/wKiom1fUPISATyoPAABcOEGP85o548.jpg "title=" 028. JPG "alt=" wkiom1fupisatyopaabcoegp85o548.jpg "/>

(Lecture content) Does deleting a table affect the sequence? You can't do an insert operation Does deleting a sequence affect the table? The table is really dead, the sequence is dead

"Doubt: I did the experiment, and after I completely removed the EMP table, I was able to continue using Emp_empno_seq's Nextval and Currval"

In Hibernate, if you are accessing an Oracle database server, what about the <id> tags in the User.hbm.xml mapping file?

<id name= "id" column= "id" > <generator class= "Increment/identity/uuid/" sequence "/" native ""/></id>

(1) whether the underlying database support is required

The identity requires the underlying database to support auto_increment. In the MySQL database, you need to set the table's primary key field to self-grow.

The increment and UUID do not require the underlying database support and do not need to set the primary key field to self-grow.

(2) Do you support multithreading concurrent operations?

Increment can only be single-threaded access, not multithreading.

Both identity and UUID support concurrent operations.

(3) applicable scenarios

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

If you are unsure of using Oracle, MySQL, SQL Server, you can use native, which is a common variable. Native is generally used.

Hibernate Help Documentation Various Additional Generators All generators implement the interface org.hibernate.id.IdentifierGenerator . This was a very simple interface. Some applications can choose to provide their own specialized implementations, however, Hibernate provides a range of Buil T-in implementations. The shortcut names for the built-in generators is as follows: increment Generates identifiers of type long , or that was unique only if short no other process was int inserting data into the Same table. Do not use the in a cluster. identity Supports identity columns in DB2, MySQL, MS SQL Server, Sybase and Hypersonicsql. The returned identifier long is of type, short or int . sequence Uses a sequence in DB2, PostgreSQL, Oracle, SAP DB, Mckoi or a generator in Interbase. The returned identifier long is of type, short  orint uuid Generates a 128-bit UUID based on a custom algorithm. The value generated is represented as a string of 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 a IETF RFC 4122 compliant (variant 2) 128-bit UUID. The exact "version" (the RFC term) is generated depends on the pluggable "generation strategy" used. Capable of generating values java.util.UUID as, java.lang.String or as a byte array of length ( 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 is save() called. The default strategy if no element is <generator> specified. foreign Uses the identifier of another associated object. It is usually used in conjunction with a <one-to-one> primary key association.

Oracle Series: (24) sequence