Shulou(Shulou.com)06/01 Report--

SELECT TO_CHAR (SYSDATE,'YYMMDD') | | LPAD (REFUNDSEQ.NEXTVAL,6,'0') AS RES_ORDER_NO FROM DUAL

The concatenation time of this statement has characters in the first 6 digits of the 'REFUNDSEQ.NEXTVAL value generated by LPAD. If it is less than 6 digits, make up with 0'. In order to prevent duplicate ticket numbers, a maximum of 999999 valid ticket numbers will appear in a day.

DUAL: it is a virtual table of oracle, which is not real. It is also called ORCLE pseudo-table. It is convenient to output result sets.

REFUNDSEQ: this is the name of a "sequence" given by the developer. The sequence is generally used to generate id numbers.

REFUNDSEQ.NEXTVAL: take the next value of the sequence

How to display SELECT TO_CHAR (SYSDATE,'YYMMDD') | | LPAD (REFUNDSEQ.NEXTVAL,6,'0') AS RES_ORDER_NO FROM DUAL in java?

/ / generate transaction pipeline number public String getTicketNo () throws Exception {Map map = jdbcTemplate.queryForMap ("SELECT TO_CHAR (SYSDATE,'YYMMDD') | | LPAD (REFUNDSEQ.NEXTVAL,6,'0') AS TICKET_NO FROM DUAL"); return (String) map.get ("RES_ORDER_NO");}

1. LPAD (), RPAD () function

* it can also be understood from the literal meaning that l is the abbreviation of left and pad is the meaning of filling, so lpad means to fill from the left. This function populates the string from the left with the specified characters.

* Syntax: LPAD (STRING, PADDED_LENGTH, [PAD_STRING])

STRING: the string to be populated

PADDED_LENGTH: the length of the populated string, that is, the length returned by this function. If this number is shorter than the length of the original string, the lpad function will truncate the string into n characters from left to right.

PAD_STRING: fill in the string, which is an optional parameter, which is to be pasted to the left of string. If this parameter is not written, the lpad function will paste a space to the left of string.

Use case:

* SELECT LPAD ('asdfg',10,'x') FROM DUAL; output:' xxxxxasdfg'

* SELECT LPAD ('ASDFG',10,'JK') FROM DUAL; output:' JKJKJASDFG'

* SELECT LPAD ('ASDFG',2) FROM DUAL; output:' AS'

* the RPAD () function populates the string from the right with the specified characters, and the syntax format is the same as the LPAD format:

RPAD ('DEMO', 7); will return' DEMO'

RPAD ('DEMO', 2); will return' DE'

RPAD ('DEMO', 8,' 0'); will return 'tech0000'

RPAD ('DEMO', 4,' M'); will return 'DEMO'

RPAD ('DEMO', 5,' M'); will return 'DEMOM'

Stitching-serial number, time, order number: LPAD (serial number | | time, 6 minutes 0')

Second, NEXTVAL () function

* sequence name .NEXTVAL: calling NEXTVAL will generate the next sequence number in the sequence, indicating the sequence name (the value of the method is 10 for the first call, 11 for the second call, and 12 for the third call, increasing in turn).

* what is a sequence in an Oracle database? What is its use? A SEQUENCE is actually a sequence number generator that automatically generates sequence numbers for rows in a table, producing a set of evenly spaced numeric values (of type numeric). Its main purpose is to generate the primary key value of the table, which can be referenced in the insert statement, check the current value through a query, or increase the sequence to the next value.

Summary

The above is all about the generation of non-repeating ticket numbers by Oracle and the parsing of LPAD,RPAD and NEXTVAL functions. I hope it will be helpful to you. Interested friends can continue to refer to this site: oracle length, lengthb, substr, substrb function usage introduction, oracle string length function length () and hengthb () introduction, if there are deficiencies, please leave a message to point out. Thank you for your support to this site!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.