I all Bovenchin hand code, give a concern or praise it! Blogger: Zhang qq:1445696451 Welcome to add Q to discuss technology at any time.
I. Sequence of Oracle
• A sequence is a database object used to generate a unique contiguous integer. A sequence can usually be used to automatically generate a value for a primary key or a unique key, or an ascending or descending order based on a number.
• For example, to create a student information registration form for a class, you can automatically generate the study number using a sequence. Class number is 10000~10060 this range, each registration of a student, the sequence of automatic growth of one.
1. command syntax for creating sequences
Create sequence Seq_name [start with Interger] [increment by interger] [MaxValue interger] [minvalue interger] [cycle |nocycle] [Cache Interger|nocache];
Comments:
Create sequence Seq_name: Creating a sequence
Start with: From ... Start generation sequence
Increment by: value per increment
MaxValue: Maximum sequence value, default unrestricted maximum value
MinValue: Minimum sequence value, default not limiting minimum value
Cycle: Translated to Chinese is a circle meaning, here is actually refers to the loop, when the sequence reaches the maximum/minimum value, starting from the initial value to continue to generate. The default is Nocycle, and no loop generation is made.
Cache: A pre-assigned sequence that is stored in the cache and can be used to quickly access the serial number. Default is NoCache, that is, no cache is allocated
Example:
Create sequence Seq_1
Start with 10000
Increment by 1
MaxValue 10060
nocycle;
: Or the above example, create a seq_1 sequence that starts at 10000, grows at 1 each time, and has a maximum value of 10060, without cyclic generation.
2. We set up a sequence that naturally requires the sequence to be applied to the tables (table) we create. The value of the sequence can be accessed by nextval, Currval pseudo-columns.
Nextval: This pseudo-column returns the initial value of the sequence when it is used for the first time after the sequence is created. When used later, the value of the increment by clause is used to increase the sequence value
currval: Returns the value returned when the last reference nextval of the sequence is returned.
Example:
CREATE TABLE STUDENT_CLASS25 (stu_id number not null,stu_name varchar2 (20),
Stu_age number);
Create a STUDENT_CLASS25 table
INSERT into STUDENT_CLASS25 values (seq_1.nextval, ' Zhangsan ', ' 13 ');
INSERT into STUDENT_CLASS25 values (seq_1.nextval, ' Lisi ', ' 13 ');
...
Insert data into the STUDENT_CLASS25 table, and in the corresponding stu_id column we refer to the sequence seq_1.
select Seq_1.currval from dual;
Query sequence Current value
# #如果在你指定缓存的环境下, execute startup force to restart the database, and the serial number of the data to be written begins with the initial value + cached value. For example, with an initial value of 10 and a cache value of 30, the serial number is generated from 40 when you execute startup force.
3. Maintenance sequence
1) View sequence (using dictionary user_sequences)
SelectFrom User_sequences;
2) Delete sequence
Drop sequence seq_name;
3) Change sequence
Alter sequence seq_name [...];
Ii. Synonyms for Oracle
• As the name implies, synonyms are words of the same meaning, and you can be seen as an alias for an object, just like an alias for a command in the Liunx operating system. But the Linux command alias only provides the benefit of easy memory.
1. In Oracle, synonyms can be used to:
1) Simplify the SQL statements entered in Oracle, and by creating synonyms for objects, we can simplify complex SQL statements for easy memory and manipulation
2) Hide the name and owner of the object, create synonyms for the object, such as the EMP table in Scott Mode (SCOTT,EMP), we create a synonym called SE. So when we look at this table, we don't have to worry about the location of the exposed table, providing some security.
3) provides public access to the database and can be viewed by other users by establishing a public synonym for the remote connection.
4) as well as providing location transparency for remote objects in a distributed database, we create synonyms for the database link (as an object) so that users can view the data table without hindrance, and the location is transparent to the user.
2. Synonym classification (two kinds)
1) Private synonym: Private for the current mode of the user, other users cannot use
2) public synonym: contrary to private synonym
3. Create synonyms
create [or replace] [public] synonym sy_name for object_name;
Comments:
Create ... for: give ... Create key synonyms
[or replace]: the function of the option is to overwrite (if there is a synonym with the same name, it will be overwritten)
[Public]: plus this option means creating a common synonym
Sy_name: synonym Name
Object_name: Object name, such as Scott.emp (the EMP table under the Scott user)
4. Example
Create synonym sy_name for scott.emp;
Create a private synonym for the scott.emp table
Create public synonym sy_name for scott.emp;
Create a public synonym
# #创建好的公有同义词不代表用户就可以使用, we also need to authorize users who need to manipulate the table
5. Complex application
1) command to establish Oracle connection
Example:
①conn System/[email protected];
②create Database link Link_1 Connect system identified by pwd123 using ORCL;
③create synonym sy_t for [email protected]_1;
Create a database link to the remote database with the above command, and create a synonym for the scott.emp table of the database in the link.
6. Maintain synonyms
1) View synonyms
SelectFrom User_synonyms;
2) Delete public/private synonyms
drop [public] synonym Sy_name;
Third, database links
1. A database link is an object that defines a database path to another database, and you can query remote tables and execute remote programs through database links. In a distributed environment, Oracle database links are required. One point, the Oracle database link is one-way, and a link to the B database from a database can not be linked to a database from the B database.
2. Database Link classification
Private: Belongs to the user who created the link, and only that user can use the
Public: belongs to public, allowing all users in the local database to have access to the database to use
Global: The Complete Collection, which is available to database users in the network
3. Create a database link
CREATE DATABASE link connect to the username identified by user
passwd using
' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = ipaddress) (PORT = 1521))
)
(Connect_data =
(service_name = dbtest)
)
)’;
Iv. partition Table
· Oracle can divide the rows of a table into multiple parts (stored in different tablespaces), which are partitioned tables, which are called Oracle partitions. The application of partition table in general, the size of the table is large enough to reflect the role of the partition table, too small table is not recommended to use.
1, the partition table has a great effect:
1) Improve the query performance of the underlying, and achieve separate management
2) Easy backup and recovery, if part of the data is lost, only need to recover the corresponding partition separately
3) store data separately for increased security
4) The partition is also transparent to the user
2. Create a partitioned table
CREATE TABLE T_name
(stu_id number,stu_name varchar2 (), stu_age number)
Partition by range (STU_ID)
(
Partition P1 values less than (),
Partition P2 values less than (),
Parti ...
);
Oracle object (sequence, synonym, partition table, database link)
