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.