---------------- 若订单流水号是字符串型的话：(Oracle 10g)  -----------------
CREATE SEQUENCE order_id_seq INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 CYCLE CACHE 10;

SET SERVEROUTPUT ON
DECLARE
  order_id VARCHAR(17); -- 订单流水号
BEGIN
  SELECT to_char(SYSDATE,'YYYYMMDD')||LPAD( to_char(order_id_seq.nextval),7,'0') INTO order_id FROM dual;
  DBMS_OUTPUT.PUT_LINE('当前流水号是：'||order_id);
END;
/

---------------- 若订单流水号是数值型的话：(Oracle 10g)  -----------------
CREATE SEQUENCE order_id_seq INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 CYCLE CACHE 10;

SET SERVEROUTPUT ON
DECLARE
  order_id NUMBER(18,0); -- 订单流水号
BEGIN
  SELECT to_number(to_char(SYSDATE,'YYYYMMDD')||LPAD( to_char(order_id_seq.nextval),7,'0')) INTO order_id FROM dual;
  DBMS_OUTPUT.PUT_LINE('当前流水号是：'||order_id);
END;
/


---------------- 若订单流水号是字符串型的话：(Oracle 11g)  -----------------
CREATE SEQUENCE order_id_seq INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 CYCLE CACHE 10;

SET SERVEROUTPUT ON
DECLARE
  order_id VARCHAR(17); -- 订单流水号
BEGIN
  order_id := to_char(SYSDATE,'YYYYMMDD')||LPAD( to_char(order_id_seq.nextval),7,'0');
  DBMS_OUTPUT.PUT_LINE('当前流水号是：'||order_id);
END;
/

---------------- 若订单流水号是数值型的话：(Oracle 11g)  -----------------
CREATE SEQUENCE order_id_seq INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 CYCLE CACHE 10;

SET SERVEROUTPUT ON
DECLARE
  order_id NUMBER(18,0); -- 订单流水号
BEGIN
  order_id := to_number(to_char(SYSDATE,'YYYYMMDD')||LPAD( to_char(order_id_seq.nextval),7,'0'));
  DBMS_OUTPUT.PUT_LINE('当前流水号是：'||order_id);
END;
/

------解决方案--------------------

使用for update强制串行，每天从1开始，生成唯一流水号
每天10W，性能没问题
SQL code
--创建流水号表
create table T_SERIALNO
(
  CURDAT     VARCHAR2(10) not null,
  SERIALNUM  NUMBER(8) not null
);

--创建函数
CREATE OR REPLACE FUNCTION GetSerialNo RETURN VARCHAR2 IS
  v_DateStr   VARCHAR2(10); --系统日期对应的字符串
  v_SerialNum NUMBER(7); --流水序号
BEGIN
  --取系统日期生成需要的字符串
  v_DateStr := TO_CHAR(SYSDATE, ('YYYYMMDD'));
  --生成产生流水号的序号
  BEGIN
    SELECT Decode(CurDat, v_DateStr, (SerialNum + 1), 1)
      INTO v_SerialNum
      FROM t_SerialNo
       FOR UPDATE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_SerialNum := 1;
      INSERT INTO t_SerialNo (CurDat, SerialNum) VALUES (v_DateStr, v_SerialNum);
  END;

  --修改流水号参数
  UPDATE t_SerialNo SET CurDat = v_DateStr, SerialNum = v_SerialNum;
  COMMIT;
  --得到需要的流水号
  RETURN v_DateStr || Lpad(v_SerialNum, 7, '0');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RETURN NULL;
END;
/

------解决方案--------------------
SQL code
Set Serveroutput on

create or replace procedure CreSeq(AsSql in varchar2) is
  iCurID  Integer;    -- 游标ID 
  iResult Integer;
begin
  iCurID:= Dbms_Sql.Open_Cursor;
  Dbms_Sql.Parse(iCurID, AsSql, Dbms_Sql.V7); 
  iResult:= Dbms_Sql.Execute(iCurID);
  Dbms_Sql.Close_Cursor(iCurID);
end;
/

create or replace function GetSqlValue(AsSql in varchar2) return Integer is
  iCurID  Integer;    -- 游标ID 
  iResult Integer;
  iReturn Integer;
begin
  iCurID:= Dbms_Sql.Open_Cursor;
  Dbms_Sql.Parse(iCurID, AsSql,