Subtotal for Oracle

Subtotal for Oracle

1. Sequence

1.1 What is Sequence?

Sequence is an object provided by Oracle for generating auto-increment primary keys. This is similar to the identity of SQL server.

From a mathematical point of view, it is an arithmetic progression, which is shown in 1.2.

1.2 detailed syntax

Creation Syntax:

Create sequence sequence // CREATE a SEQUENCE name
[Increment by n] // the incremental sequence value is n. If n is a positive number, it increases. If n is a negative number, it decreases. The default value is 1.
[Start with n] // The START value. The default value of increment is minvalue and the value of increment is maxvalue.
[{MAXVALUE n | NOMAXVALUE}] // maximum value/no maximum value
[{MINVALUE n | NOMINVALUE}] // minimum value/no minimum value
[{CYCLE | NOCYCLE}] // cyclic/non-cyclic
[{CACHE n | NOCACHE}]; // allocation and coexistence into memory/no CACHE

Explanation:

1) increment by is used to define the sequence step. If omitted, the default value is 1. If a negative value is displayed, the value of the Oracle sequence decreases according to this step.

2) start with defines the initial value of the sequence (that is, the first value generated). The default value is 1.

3) MAXVALUE defines the maximum value that can be generated by the sequence generator. The "NOMAXVALUE" option is the default option, indicating that no maximum value is defined. In this case, the maximum value generated by the system for an incremental Oracle sequence is the 27 power of 10. For a descending sequence, the maximum value is-1.

4) MINVALUE defines the minimum value generated by the sequence generator. The "NOMAXVALUE" option is the default option, indicating that there is no minimum value defined. What is the minimum value that the system can produce for the descending sequence? 10 to the power of 26; for incremental sequence, the minimum value is 1.

5) CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence generator reaches the limit value. CYCLE indicates loop, and NOCYCLE indicates no loop. If there is a loop, when the ascending sequence reaches the maximum value, it loops to the minimum value. When the descending sequence reaches the minimum value, it loops to the maximum value. If there is no loop, an error occurs when a new value is generated after the limit value is reached.

6) CACHE (buffer) defines the size of the memory block for storing the sequence. The default value is 20. NOCACHE indicates no memory buffer for the sequence. Buffer the sequence memory to improve the sequence performance.

Eg:

Create sequence mySeq
Increment by 1
Start with 1
Minvalue 1 nomaxvalue
Nocylce

Select mySeq. nextval from dual;
Select mySeq. currval from dual;

1.3 create table auto-increment Application

// 1) create table
Create table cdpt (
Id number (6 ),
Name varchar2 (30 ),
Constraint pk_id primary key (id)
);
 
// 2) create sequence
Create sequence seq_cdpt
Increment by 1
Start with 1
Max value 999999
Minvalue 1
Nocycle
Nocache

// 3) create trigger
Create or replace trigger tigger_userinfo
Before insert on userinfo
For each row
Declare
Begin
Select seq_cdpt.nextval into: New. id from dual;
End tigger_userinfo;

1.4 Delete Sequence

Drop sequence name

2. tablesapce

2.1 What is tablespace?

Tablespace is a major innovation in oracle design. It consistently runs through the database.

Du Niang: "The ORACLE database is divided into logical regions as tablespaces-forming the logical structure of the ORACLE database. One ORACLE database can have one or more tablespaces, while one tablespace corresponds to one or more physical database files. The tablespace is the minimum unit for ORACLE database recovery. It contains many database entities, such as tables, views, indexes, clustering, rollback segments, and temporary segments ."

2.2 how to use it?

/* Divided into four steps */
/* Step 2: create a temporary tablespace ref: http://www.cnblogs.com/netsql/articles/1745978.html */
Create temporary tablespace user_temp
Tempfile 'd: \ oracle \ oradata \ Oracle9i \ user_temp.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
 
/* Step 2: create a data table space */
Create tablespace user_data
Logging
Datafile 'd: \ oracle \ oradata \ Oracle9i \ user_data.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
 
/* Step 2: create a user and specify the tablespace */
Create user username identified by password
Default tablespace user_data
Temporary tablespace user_temp;
 
/* Step 4: grant permissions to users */
Grant connect, resource, dba to username;

3. rownum

3.1 top n in SQL server

// SQL server

Select top 10 * from tableA

// Corresponding oracle implementation

Select * from tableA where rownum <= 10

4. NVCHAR2

4.1 features described directly
1. First, it is a data type;
2. N indicates that Unicode can be stored;
3. Varchar indicates that characters with variable length can be stored;
4.2: nvarchar2 stores all dual-byte data. The difference between nvarchar2 and nvarchar is that the English letters of nvarchar are stored in single-byte data.