日期:2014-05-17 浏览次数:20881 次
---------------- 若订单流水号是字符串型的话:(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,性能没问题
--创建流水号表 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; /
------解决方案--------------------
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,