求大神们帮忙把下面的存储过程转写成mysql 的存储过程 !!create or replace procedure PRO_GEN_SEQ_NUM(vkey in varchar2, seqid out varchar) is
V_SVAL VARCHAR2(8);
V_date VARCHAR2(8);
v_month VARCHAR2(4);
v_year VARCHAR2(4);
v_day VARCHAR2(4);
v_dateformat VARCHAR2(20);
v_randomnums VARCHAR2(20);
begin
select (SVAL + 1),ssty,year,month,day INTO V_SVAL,v_dateformat,v_year,v_month,v_day from xtseq WHERE snam = VKEY;
v_randomnums:=substr(v_dateformat,instr(v_dateformat,'x'));--随机位数xxxx
v_dateformat:=substr(v_dateformat,1,instr(v_dateformat,'x')-1);--日期格式
if v_dateformat not in ('yymm','yyyymm','yyyymmdd','yymmdd','yyyy') then
v_dateformat := 'yyyymm';
end if;
select to_char(sysdate,v_dateformat) into V_date from dual;
if v_dateformat = 'yymm' then
if v_year != to_char(sysdate,'yy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yy'),month=to_char(sysdate,'mm') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yy') and v_month != to_char(sysdate,'mm') then
update xtseq set SVAL='1',month=to_char(sysdate,'mm') where snam = VKEY;
V_SVAL:='1';
end if;
elsif v_dateformat = 'yyyymm' then
if v_year != to_char(sysdate,'yyyy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yyyy'),month=to_char(sysdate,'mm') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yyyy') and v_month != to_char(sysdate,'mm') then
update xtseq set SVAL='1',month=to_char(sysdate,'mm') where snam = VKEY;
V_SVAL:='1';
end if;
elsif v_dateformat = 'yyyymmdd' then
if v_year != to_char(sysdate,'yyyy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yyyy'),month=to_char(sysdate,'mm'),day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yyyy') and v_month != to_char(sysdate,'mm') then
update xtseq set SVAL='1',month=to_char(sysdate,'mm'),day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yyyy') and v_month = to_char(sysdate,'mm') and v_day != to_char(sysdate,'dd') then
update xtseq set SVAL='1',day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
end if;
elsif v_dateformat = 'yymmdd' then
if v_year != to_char(sysdate,'yy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yy'),month=to_char(sysdate,'mm'),day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yy') and v_month != to_char(sysdate,'mm') then
update xtseq set SVAL='1',month=to_char(sysdate,'mm'),day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yy') and v_month = to_char(sysdate,'mm') and v_day != to_char(sysdate,'dd') then
update xtseq set SVAL='1',day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
end if;
elsif v_dateformat = 'yyyy' then
if v_year != to_char(sysdate,'yyyy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yyyy') where snam = VKEY;
V_SVAL:='1';
end if;
end if;
UPDATE xtseq SET SVAL = V_SVAL WHERE snam = VKEY;
seqid:=V_date||trim(to_char(V_SVAL,replace(v_randomnums,'x','0')));
--commit;
seqid:=seqid;
end PRO_GEN_SEQ_NUM;
/
V_SVAL VARCHAR2(8);
V_date VARCHAR2(8);
v_month VARCHAR2(4);
v_year VARCHAR2(4);
v_day VARCHAR2(4);
v_dateformat VARCHAR2(20);
v_randomnums VARCHAR2(20);
begin
select (SVAL + 1),ssty,year,month,day INTO V_SVAL,v_dateformat,v_year,v_month,v_day from xtseq WHERE snam = VKEY;
v_randomnums:=substr(v_dateformat,instr(v_dateformat,'x'));--随机位数xxxx
v_dateformat:=substr(v_dateformat,1,instr(v_dateformat,'x')-1);--日期格式
if v_dateformat not in ('yymm','yyyymm','yyyymmdd','yymmdd','yyyy') then
v_dateformat := 'yyyymm';
end if;
select to_char(sysdate,v_dateformat) into V_date from dual;
if v_dateformat = 'yymm' then
if v_year != to_char(sysdate,'yy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yy'),month=to_char(sysdate,'mm') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yy') and v_month != to_char(sysdate,'mm') then
update xtseq set SVAL='1',month=to_char(sysdate,'mm') where snam = VKEY;
V_SVAL:='1';
end if;
elsif v_dateformat = 'yyyymm' then
if v_year != to_char(sysdate,'yyyy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yyyy'),month=to_char(sysdate,'mm') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yyyy') and v_month != to_char(sysdate,'mm') then
update xtseq set SVAL='1',month=to_char(sysdate,'mm') where snam = VKEY;
V_SVAL:='1';
end if;
elsif v_dateformat = 'yyyymmdd' then
if v_year != to_char(sysdate,'yyyy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yyyy'),month=to_char(sysdate,'mm'),day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yyyy') and v_month != to_char(sysdate,'mm') then
update xtseq set SVAL='1',month=to_char(sysdate,'mm'),day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yyyy') and v_month = to_char(sysdate,'mm') and v_day != to_char(sysdate,'dd') then
update xtseq set SVAL='1',day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
end if;
elsif v_dateformat = 'yymmdd' then
if v_year != to_char(sysdate,'yy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yy'),month=to_char(sysdate,'mm'),day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yy') and v_month != to_char(sysdate,'mm') then
update xtseq set SVAL='1',month=to_char(sysdate,'mm'),day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
elsif v_year = to_char(sysdate,'yy') and v_month = to_char(sysdate,'mm') and v_day != to_char(sysdate,'dd') then
update xtseq set SVAL='1',day=to_char(sysdate,'dd') where snam = VKEY;
V_SVAL:='1';
end if;
elsif v_dateformat = 'yyyy' then
if v_year != to_char(sysdate,'yyyy') then
update xtseq set SVAL='1',year=to_char(sysdate,'yyyy') where snam = VKEY;
V_SVAL:='1';
end if;
end if;
UPDATE xtseq SET SVAL = V_SVAL WHERE snam = VKEY;
seqid:=V_date||trim(to_char(V_SVAL,replace(v_randomnums,'x','0')));
--commit;
seqid:=seqid;
end PRO_GEN_SEQ_NUM;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货