Subtotal for Oracle and subtotal for oracle

Subtotal for Oracle and subtotal for oracle
1. What is Sequence 1.1?

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 SEQUENCESequence// Create a sequence name [increment by n] // the incremental sequence value is n. If n is a positive number, it increments, if it is a negative number, the default value is 1 [start with n] // The START value, increment: minvalue by default; decrease: maxvalue [{MAXVALUE n | NOMAXVALUE}] // maximum value/no maximum value [{MINVALUE n | NOMINVALUE}] // minimum value/no minimum value [{CYCLE | NOCYCLE }] // loop/non-loop [{CACHE n | NOCACHE}]; // allocate and coexist 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 mySeqincrement by 1start with 1minvalue 1 nomaxvaluenocylceselect mySeq.nextval  from dual; select mySeq.currval  from dual;

1.3 create table auto-increment Application

//1) create tablecreate table cdpt(id number(6),name varchar2(30),constraint pk_id primary key(id)); //2)create sequencecreate sequence seq_cdptIncrement by 1Start with 1Maxvalue 999999Minvalue 1Nocyclenocache//3)create triggercreate 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. 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 4: 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 2: grant the user permissions */grant connect, resource, dba to username;

3. rownum3.1 top n in SQL server

// SQL serverselect top 10 * from tableA // corresponding oracle implementation select * from tableA where rownum <= 10

4. NVCHAR2

4.1 features described directly

 

Happy Every Day!