-- raiserror ('表名的前两个字母与已有的发生冲突 请修改表名', 16, 1)
-- delete tbrecno
--
oracle版
create or replace procedure getmykeyno(
sign varchar2,
tablename varchar2,
outkey out varchar2
)
is
-- sign getmykey.my_ziguei %type;
-- tablename getmykey.my_tablename %type;
--outkey varchar(20);
lastdate getmykey.my_lastdate%type;
tmpint getmykey.my_lastno %type;
nowdate date;
tmpstr varchar(4);
tmpsign varchar(2);
myyear int;
mymonth int;
myday int;
tmpcount int;
begin
myyear := extract(year from sysdate);
mymonth := extract(month from sysdate);
myday := extract(day from sysdate);
nowdate:=sysdate;
--tablename:='aaaa';
--sign:='ad';
select count(*) into tmpcount from getmykey where my_tablename = tablename;
if tmpcount=0 then
begin
insert into getmyke(my_tablename,my_ziguei,my_lastno,my_lastdate) values (tablename,sign,1,nowdate) ;tmpint := 1;
end;
else
select my_lastdate into lastdate from getmykey where my_tablename=tablename ;
if (myyear>=extract(year from lastdate) and mymonth>=extract(month from lastdate)) then
if(myday=extract(day from lastdate)) then
update getmykey set my_lastno = my_lastno + 1 where my_tablename=tablename;
select my_lastno into tmpint from getmykey where my_tablename=tablename;
else
if (myday > extract(day from lastdate)) then
update getmykey set my_lastno = 1 where my_tablename=tablename ;
update getmykey set my_lastdate = nowdate where my_tablename=tablename;
tmpint := 1 ;
else
dbms_output.put_line('服务器的时间改变，请检查系统!');
end if;
end if;
end if;
end if;
tmpstr:=lpad(to_char(tmpint),4,'0');
-- dbms_output.put_line(tmpstr);
outkey := substr(to_char(myyear),3,2) || lpad(to_char(mymonth),2,'0') || lpad(to_char(myday),2,'0') || tmpstr;
select my_ziguei into tmpsign from getmykey where my_tablename=tablename;
outkey:=tmpsign ||outkey;
--dbms_output.put_line(outkey);
end;
