請問各位版大,ORACLE有什麼方式可以達到以下需求,請指教,謝謝
主鍵為當天日期+流水號...隔日...流水號從0001開始
例如:201308190001,201308190002,201308190003......當天的流水號往下加
隔天201308200001,201308200002......
主鍵為當天日期+流水號...隔日...流水號從0001開始
例如:201308190001,201308190002,201308190003......當天的流水號往下加
隔天201308200001,201308200002......
这个跟之前论坛上讨论的一个题目极其相似,我把当时的比较不错的答案直接复制过来了--create table
drop table t_SerialNo;
create table t_SerialNo(
LogDate Date,
t_Current Number(10)
);--procedure
/**
思路:每天第一次插入初始化记录,然后以后每次插入的时候对比时间的前8位,若一致,则update,将流水号更新+1,转换成字符表示。
若不一致,流水号从1开始插入
**/
create or replace procedure p_Get_SerialNo(vreturn out varchar2) as
v_serialNo t_SerialNo%rowtype;
begin
--根据yyyymmdd查找记录
select * into v_serialNo from t_SerialNo where to_char(LogDate,'yyyymmdd')=to_char(sysdate,'yyyymmdd');
--如果找到当天记录,更新
if v_serialNo.LogDate is not null then
--也可以每次不更新日期,后面不需要加条件
update t_SerialNo set LogDate=sysdate,t_Current=v_serialNo.t_Current+1 where to_char(LogDate,'yyyymmdd')=to_char(sysdate,'yyyymmdd');
commit;
vreturn:=to_char(v_serialNo.LogDate,'yyyymmdd')||to_char((v_serialNo.t_Current+1),'fm00000000');
end if;
exception
when no_data_found then
insert into t_SerialNo values(sysdate,1);
commit;
vreturn:=to_char(sysdate,'yyyymmdd')||'00000001';
when others then
dbms_output.put_line(sqlcode);
end p_Get_SerialNo;
/-- test
declare
vreturn varchar2(16);
begin
p_Get_SerialNo(vreturn);
dbms_output.put_line(vreturn);
end;
/
DROP TABLE business_seqno;
CREATE TABLE business_seqno(
last_modified_date DATE,
seq_no NUMBER(10)
);
DECLARE
l_sysno VARCHAR2 (32);
l_seq_no VARCHAR2 (32);
l_sysdate DATE;
l_lastdate DATE;
BEGIN
SELECT SYSDATE
INTO l_sysdate
FROM DUAL; SELECT last_modified_date, seq_no
INTO l_lastdate, l_seq_no
FROM business_seqno; IF (TO_CHAR (l_lastdate, 'YYYYMMDD') = TO_CHAR (l_sysdate, 'YYYYMMDD'))
THEN
SELECT TO_CHAR (l_lastdate, 'YYYYMMDD')
|| LPAD (TO_CHAR (l_seq_no), 4, '0')
INTO l_sysno
FROM DUAL;
ELSE
UPDATE business_seqno
SET seq_no = 1,
last_modified_date = SYSDATE; SELECT TO_CHAR (SYSDATE, 'YYYYMMDD') || LPAD (TO_CHAR (1), 4, '0')
INTO l_sysno
FROM DUAL;
END IF; UPDATE business_seqno
SET seq_no = seq_no + 1,
last_modified_date = SYSDATE; DBMS_OUTPUT.put_line (l_sysno);
COMMIT;
END;刚写的,可直接得到结果,记得先配置表business_seqno 中的数据。
再贴一次,格式不好看,影响心情。DROP TABLE business_seqno;
CREATE TABLE business_seqno(
last_modified_date DATE,
seq_no NUMBER(10)
);
DECLARE
l_sysno VARCHAR2 (32);
l_seq_no VARCHAR2 (32);
l_sysdate DATE;
l_lastdate DATE;
BEGIN
SELECT SYSDATE
INTO l_sysdate
FROM DUAL; SELECT last_modified_date, seq_no
INTO l_lastdate, l_seq_no
FROM business_seqno; IF (TO_CHAR (l_lastdate, 'YYYYMMDD') = TO_CHAR (l_sysdate, 'YYYYMMDD'))
THEN
SELECT TO_CHAR (l_lastdate, 'YYYYMMDD')
|| LPAD (TO_CHAR (l_seq_no), 4, '0')
INTO l_sysno
FROM DUAL;
ELSE
UPDATE business_seqno
SET seq_no = 1,
last_modified_date = SYSDATE; SELECT TO_CHAR (SYSDATE, 'YYYYMMDD') || LPAD (TO_CHAR (1), 4, '0')
INTO l_sysno
FROM DUAL;
END IF; UPDATE business_seqno
SET seq_no = seq_no + 1,
last_modified_date = SYSDATE; DBMS_OUTPUT.put_line (l_sysno);
COMMIT;
END;刚写的,可直接得到结果,记得先配置表business_seqno 中的数据。