ORACLE中生成流水号 创建一个序列: create sequence EXAMPLE_SEQ increment by 1 start with 1 minvalue 1 cache 20; 在EXAMPLE表上创建一个触发器 CREATE OR REPLACE TRIGGER "PORTAL".TRI_EXAMPLE_TABLE BEFORE INSERT ON EXAMPLE_TABLE FOR EACH ROW begin select to_char(sysdate,'YYYYMMDD')||to_char(EXAMPLE_SEQ.nextval,'0000') into :new.EXAMPLE_ID from dual; end;
--写一个函数 --建立表结构 create table tb(id varchar(30));--写一个函数 create or replace function GETID return varchar2 is Result varchar2(100); istr varchar2(100); i int; begin
select max(id) into istr from tb where substr(id,1,8)=to_char(sysdate,'yyyymmdd');
if istr is null then select to_char(sysdate,'yyyymmdd')||'0001' into Result from dual; else Result:=substr(istr,1,8);
------> 包头 CREATE OR REPLACE PACKAGE MY_SEQUENCE AS V_SEQ_VALUE INTEGER := 0; PROCEDURE NEW_SEQ_VALUE(SEQ_VALUE OUT INTEGER); END MY_SEQUENCE; / ------> 包体 CREATE OR REPLACE PACKAGE BODY MY_SEQUENCE AS PROCEDURE NEW_SEQ_VALUE(SEQ_VALUE OUT INTEGER) IS BEGIN IF MY_SEQUENCE.V_SEQ_VALUE = 0 OR SUBSTR(TO_CHAR(MY_SEQUENCE.V_SEQ_VALUE), 1, 8) < TO_CHAR(SYSDATE, 'YYYYMMDD') THEN MY_SEQUENCE.V_SEQ_VALUE := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD') || '0001'); ELSE MY_SEQUENCE.V_SEQ_VALUE := MY_SEQUENCE.V_SEQ_VALUE + 1; END IF; SEQ_VALUE := MY_SEQUENCE.V_SEQ_VALUE; END NEW_SEQ_VALUE;END MY_SEQUENCE; ----- > 测试 DECLARE V_VALUE INTEGER; BEGIN FOR V_I IN 1 .. 10 LOOP MY_SEQUENCE.NEW_SEQ_VALUE(V_VALUE); DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_VALUE)); END LOOP; END;
create sequence EXAMPLE_SEQ
increment by 1
start with 1
minvalue 1
cache 20;
在EXAMPLE表上创建一个触发器
CREATE OR REPLACE TRIGGER "PORTAL".TRI_EXAMPLE_TABLE BEFORE INSERT ON EXAMPLE_TABLE FOR EACH ROW
begin
select to_char(sysdate,'YYYYMMDD')||to_char(EXAMPLE_SEQ.nextval,'0000') into :new.EXAMPLE_ID from dual;
end;
--写一个函数
--建立表结构
create table tb(id varchar(30));--写一个函数
create or replace function GETID
return varchar2
is
Result varchar2(100);
istr varchar2(100);
i int;
begin
select max(id) into istr from tb where substr(id,1,8)=to_char(sysdate,'yyyymmdd');
if istr is null then
select to_char(sysdate,'yyyymmdd')||'0001' into Result from dual;
else
Result:=substr(istr,1,8);
i:=substr(istr,9,4);
i:=i+1;
istr:='00000'||to_char(i);
istr:=substr(istr,length(istr)-4,length(istr));
Result:=Result||istr;
end if;
return(Result);
end GETID;--查询函数返回值
select getid as id from dual;
请问怎么实现呢? 我虽用ORACELE多年仍菜鸟,不要笑我啊。
帮忙说得清楚些啊,
我是WIN2003SERVER,以前想让ORACEL定时做某事,是写个批处理,它让执行一个SQL文件,再把它加在计划任务中,不知ORACEL本身的JOB怎么用。另外,因是政府机关的服务器,有时会关机,比如周六、日。周一早上再开 “每天夜里0:0:0要把那个序列重置 ” ,那样的话周一的序列号是不是就不符合要求了?
CREATE OR REPLACE PACKAGE MY_SEQUENCE AS
V_SEQ_VALUE INTEGER := 0;
PROCEDURE NEW_SEQ_VALUE(SEQ_VALUE OUT INTEGER);
END MY_SEQUENCE;
/
------> 包体
CREATE OR REPLACE PACKAGE BODY MY_SEQUENCE AS
PROCEDURE NEW_SEQ_VALUE(SEQ_VALUE OUT INTEGER) IS
BEGIN
IF MY_SEQUENCE.V_SEQ_VALUE = 0
OR SUBSTR(TO_CHAR(MY_SEQUENCE.V_SEQ_VALUE),
1,
8) < TO_CHAR(SYSDATE,
'YYYYMMDD')
THEN
MY_SEQUENCE.V_SEQ_VALUE := TO_NUMBER(TO_CHAR(SYSDATE,
'YYYYMMDD') || '0001');
ELSE
MY_SEQUENCE.V_SEQ_VALUE := MY_SEQUENCE.V_SEQ_VALUE + 1;
END IF;
SEQ_VALUE := MY_SEQUENCE.V_SEQ_VALUE;
END NEW_SEQ_VALUE;END MY_SEQUENCE;
----- > 测试
DECLARE
V_VALUE INTEGER;
BEGIN
FOR V_I IN 1 .. 10
LOOP
MY_SEQUENCE.NEW_SEQ_VALUE(V_VALUE);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_VALUE));
END LOOP;
END;
可以先drop掉sequence,在重建sequence
v_type varchar2,
V_number_col VARCHAR2,
V_Table_Name VARCHAR2)
return varchar2
IS
/*****************************************************************************
* *
* PROGRAM-ID : fn_no_make *
* *
* DESCRIPTION : make asset,purchase no *
* *
* *
* PARAMETER *
* *
* INPUT : NULL *
* *
* OUTPUT : NULL *
* *
* LOG : NULL *
******************************************************************************
* *
* *
******************************************************************************/
V_Maked_No varchar2(50);
v_sql VARCHAR2(4000);
begin
v_sql := 'select no_init || max_no ';
v_sql := v_sql || 'from (select '''|| v_type ||''' || substr(TO_CHAR(SYSDATE,''yyyymmdd''), 1, 8) no_init, ';
v_sql := v_sql || 'case when SUBSTR(decode(MAX('||V_number_col||'),null,''19000101''),3,8) <> substr(TO_CHAR(SYSDATE,''yyyymmdd''), 1, 8) then ''001'' ';
v_sql := v_sql || 'ELSE lpad((to_number(SUBSTR(max('||V_number_col||'), 11, 3))+1),3,0) end max_no from '||V_Table_Name||') A'
;
execute immediate v_sql INTO V_Maked_No;
return(V_Maked_No);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
end fn_no_make;调用Asset_No = cf.Fn_No_Make("AT", "ASM_ASSET_NO", "TB_ASSET_MASTER");结果AT20090616001
拼动态sql的地方没有看清楚,好像是从max拿数据出来再处理的吧 这样并发的情况下,会有同号的情况出现吧