Explain how to use oracle sequence instances and explain oracle sequence instances

Source: Internet
Author: User

Explain how to use oracle sequence instances and explain oracle sequence instances
Oracle sequence creation Sequence

Create sequence seq_test_1 // create sequence name minvalue 1 // minimum value maxvalue 100000 // maximum value start with 15 // sequence start value, increment by default minvalue, the default value of decrease is maxvalueincrement by 1 // the incremental sequence value is n. If n is a positive number, it increases. If n is a negative number, it decreases by 1 nocache by default; // It is allocated and stored in the memory: no nocycle; // No cycle
Returns the next valid value in the sequence, which can be referenced by any user.

SELECT SEQUENCE_NAME.NEXTVAL from dual; eg: select seq_test_1.nextval from dual;

Returns the current value of the sequence.

SELECT SEQUENCE_NAME.CURRVAL from dual; eg: select seq_test_1.CURRVAL from dual;

Use Cases to implement id auto-Increment
# Create table cdpt (id number (6), name varchar2 (30), constraint pk_id primary key (id )); # Create sequence seq_cdptIncrement by 1 Start with 1 Maxvalue 999999 Minvalue 1 notesenocache # implement primary key auto-incrementing insert into cdpt values (seq_cdpt.nextval, 'weifany '); # query validation select * from cdpt;
Modify or delete a sequence

Alter SEQUENCE xxx; Drop SEQUENCE xxx;

Practical application (October xx, not enough)

Mapper

@ MyBatisRepositorypublic interface PmcmoMapper {/* add xx create serial number */void createSeq (@ Param ("seq_orgxx_cuid") String sequence); String getSeq (@ Param ("sequence") String sequence, @ Param ("length") String length, @ Param ("cover") String cover );}

Xml

     
 
  
Create sequence $ {seq_orgxx_cuid} minvalue 1 maxvalue 99999999 start with 1 increment by 1 cache 10
     
     Select lpad ($ {seq_orgxx_cuid}. nextval, $ {length}, $ {cover}) from dual 

Oracle lpad Functions

Syntax format: lpad (string, padded_length, [pad_string]) string to be filled. The length of the string after padded_length is filled, that is, the length of the string returned by the function, if the number is shorter than the length of the original string, the lpad function truncates the string into n characters from left to right. The pad_string string is an optional parameter, this string is to be pasted to the left of the string. If this parameter is not written, the lpad function will paste a space on the left of the string.

Oracle rpad Functions

The rpad function corresponds to the lpad function. The rpad function fills the string with the specified characters on the right. The syntax format is the same as that of the lpad function: rpad (string, padded_length, [pad_string]). the length of the string-filled string padded_length is the number of returned strings. If the number is shorter than the length of the original string, the rpad function truncates the string to n characters from left to right. pad_string is an optional parameter, which must be pasted to the right of the string. If this parameter is not written, the lpad function will paste a space on the right of the string.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.