要實(shí)現(xiàn)生成一個(gè)6位數(shù)的數(shù)字第一位是年份后面5位是遞增數(shù)字從00001開始，怎么辦？如今年是03年，生成第一個(gè)為300001,第二個(gè)為30002....
先創(chuàng)sequence:
create sequence seq_ticket_code increment by 1 start with 100001 maxvalue 99999999 cycle;
再創(chuàng)函數(shù):
create or replace function sf_GetTicketCode return number
is
ticket_code number(10);
--ticket_seq number(10);
--ticket_year number(1);
begin
select to_number(to_char(sysdate,'y'))||substr(to_char(seq_ticket_code.nextval),2,5)
into ticket_code from dual;
return ticket_code;
exception
when others then
return null;
end;
////
SQL> select sf_GetTicketCode from dual;
SF_GETTICKETCODE
----------------
300010