oracle生成流水号序列,Oracle序列生成订单流水号

一、创建序列：

create sequence 

start with 

increment by 

[maxvalue值]

[minvalue值]

[cycle当到达最大值的时候，将继续从头开始]

[Nocycle  --一直累加，不循环][Cache ]

二、Oracal的lpad函数

lpad( string1, padded_length, [ pad_string ] )

其中string1是需要粘贴字符的字符串

padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length;

pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。

例如：

lpad('tech', 7);

将返回' tech'

lpad('tech', 2);

将返回'te'

lpad('tech', 8, '0');

将返回'0000tech'

lpad('tech on the net', 15, 'z');

将返回 'tech on the net'

lpad('tech on the net', 16, 'z');

将返回 'ztech on the net'

三、Oracal的to_char函数

The following are date examples for the to_char function.

to_char(sysdate, 'yyyy/mm/dd');

would return '2003/07/09'

to_char(sysdate, 'Month DD, YYYY');

would return 'July 09, 2003'

to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'MON DDth, YYYY');

would return 'JUL 09TH, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed.

This can be seen in the examples below.

to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

四、例：

DROP SEQUENCE test_mySeq;

DROP TABLE test;

CREATE SEQUENCE test_mySeq

START WITH 1001

INCREMENT BY 1

MAXVALUE 9999999999

MINVALUE 1

CYCLE

NOCACHE;

CREATE TABLE test

(

ID          VARCHAR2(10) not null,

orderid     VARCHAR2(20) not NULL

);

INSERT INTO test(ID,orderid)VALUES(to_char(SYSDATE,'yyyymmdd'),'HG-'||to_char(SYSDATE,'yyyymmdd')||lpad(test_mySeq.nextval, 8, '0'));

COMMIT;

SELECT * FROM test ORDER BY orderid ASC;