本人想写个函数!~自动生成排产单。格式是0707A,如果当月15号后就是0707B,请问怎么写,谢谢!~我这样写对 吗?create or replace function OQC_PAICHANDAN
return varchar2 is r_num varchar2(40);
PRAGMA AUTONOMOUS_TRANSACTION ;
begin
select to_char(sysdate,'yymmdd') from dual
if dd<15 then
r_num=(select to_char(sysdate,'yymm') from dual)+A
else
r_num=(select to_char(sysdate,'yymm') from dual)+B
end if;
RETURN r_num;
end;
return varchar2 is r_num varchar2(40);
PRAGMA AUTONOMOUS_TRANSACTION ;
begin
select to_char(sysdate,'yymmdd') from dual
if dd<15 then
r_num=(select to_char(sysdate,'yymm') from dual)+A
else
r_num=(select to_char(sysdate,'yymm') from dual)+B
end if;
RETURN r_num;
end;
return varchar2 is r_num varchar2(10);
PRAGMA AUTONOMOUS_TRANSACTION;
dd varchar2(2);
ym varchar(6);
begin
select to_char(sysdate,'dd') into dd,to_char(sysdate,'yyyymm') into ym from dual;
if dd<15 then
r_num:= ym || 'A';
else
r_num:= ym || 'B';
end if;
RETURN r_num;
end OQC_PAICHANDAN;
SELECT insert_date,DECODE(TO_CHAR(insert_date,'YYYYMMDD'),TO_CHAR(next_day(TO_DATE(TO_CHAR(TO_DATE(insert_date),'YYYYMM')||'10'),1),'YYYYMMDD'),TO_CHAR(insert_date,'YYMM')||'A',insert_date) FROM 表
decode(insert_date,decode(to_char(last_day(insert_date) - round(extract(day from last_day(insert_date))/2),'D'),
'1',last_day(insert_date) - round(extract(day from last_day(insert_date))/2),
next_day(last_day(insert_date) - round(extract(day from last_day(insert_date))/2),1)),to_char(insert_date,'yymm')||'B',to_char(insert_date,'yymm')||'A') new_insert_date from table_name;