我有一个字段MSGID,格式:SJ+日期+三位流水码:
如20110928这天有两个,
MSGID分别为
SJ20110928001
SJ20110928002怎样可以实现阿不要用触发器,我的oracle帐号没有权限
如20110928这天有两个,
MSGID分别为
SJ20110928001
SJ20110928002怎样可以实现阿不要用触发器,我的oracle帐号没有权限
INSERT INTO table1
(msgid
)
VALUES ((SELECT 'SJ'
|| TO_CHAR (SYSDATE, 'yyyymmdd')
|| LPAD (TO_NUMBER (SUBSTR (MAX (msgid), -3)) + 1, 3,
'0')
FROM table1
WHERE msgid LIKE 'SJ' || TO_CHAR (SYSDATE, 'yyyymmdd') || '___')
);
start with n
increment by m
maxvalue x
minvalue y
......
我怎样让sequence 过了一天之后,又从n开始
如果要每天重置的话sequence不合适
INSERT INTO table1
(msgid
)
VALUES ((SELECT 'SJ'
|| TO_CHAR (SYSDATE, 'yyyymmdd')
|| LPAD (TO_NUMBER (SUBSTR (nvl(MAX (msgid),'000'), -3)) + 1, 3,
'0')
FROM table1
WHERE msgid LIKE 'SJ' || TO_CHAR (SYSDATE, 'yyyymmdd') || '___')
);