可以定义一个触发器,在触发器中实现学号自动生成: create trigger tri_ol_order before insert on t_order for each row declare v_order varchar2(10); v_date varchar2(6); v_num number(4); begin select odaerno into v_order from (select odaerno from (select odaerno from t_order order by odaerno desc) where rownum<2); --查询上次插入的最后一条记录的序列号 v_date :=to_char(sysdate,'yyyymm'); --当前日期字段 v_num :=substr(v_order,7,10); --后面自增字段 v_order :=v_date||lpad(v_num+1,4,'0'); --要插入的字段 :new.odaerno :=v_order; exception --表为空,第一次插入数据 when no_data_found then :new.odaerno :=to_char(sysdate,'yyyymm')||'0001'; end; 截图:
这样的function应该适用吧:create or replace function f_f return varchar2 is v_OrderNo varchar2(20); v_maxNo varchar2(20); select max(OrderNo) into v_maxNo from ORDER where OrderNo like to_char(sysdate,'yyyymm')||'%'; if v_maxNo is null then v_OrderNo := to_char(sysdate,'yyyymm')||'00001'; else v_OrderNo := to_char(v_maxNo+1); end if; return v_OrderNo; end f_f;
create trigger tri_ol_order
before insert on t_order
for each row
declare
v_order varchar2(10);
v_date varchar2(6);
v_num number(4);
begin
select odaerno into v_order from
(select odaerno from
(select odaerno from t_order order by odaerno desc)
where rownum<2); --查询上次插入的最后一条记录的序列号
v_date :=to_char(sysdate,'yyyymm'); --当前日期字段
v_num :=substr(v_order,7,10); --后面自增字段
v_order :=v_date||lpad(v_num+1,4,'0'); --要插入的字段
:new.odaerno :=v_order;
exception --表为空,第一次插入数据
when no_data_found then
:new.odaerno :=to_char(sysdate,'yyyymm')||'0001';
end;
截图:
往t_order表里insert数据时,orderno给了个值,查询出来却变成另外一个值。。会不会显得太奇怪
v_OrderNo varchar2(20);
v_maxNo varchar2(20);
select max(OrderNo) into v_maxNo from ORDER where OrderNo like to_char(sysdate,'yyyymm')||'%';
if v_maxNo is null then
v_OrderNo := to_char(sysdate,'yyyymm')||'00001';
else
v_OrderNo := to_char(v_maxNo+1);
end if;
return v_OrderNo;
end f_f;