--創建函式
create or replace function dateconvert (flowdate in varchar)
return varchar
is saledate varchar;
begin
str1 varchar(6);
str2 varchar(6);
str3 varchar(6);
lenstr1 number(5);
lenstr2 number(5);
lenstr3 number(5) ;
if instr(flowdate,'-',1,1)>1 then
str1:=substr(flowdate,1,instr(flowdate,'-',1,1)-1);
str2:=replace(substr(flowdate,instr(flowdate,'-',1,1)+1,2),'-','');
str3:=substr(flowdate,instr(flowdate,'-',1,2)+1,2);
lenstr1:=length(str1);
lenstr2:=length(str2);
lenstr3:=length(str3);
case when lenstr3=4 and lenstr2=1 and lenstr1=1
then saledate:= str3 || '0'|| str2 || '0' || str1;
when lenstr3=4 and lenstr2=1 and lenstr1=2
then flowdate:= str3 || '0'|| str2 || str1;
when lenstr3=4 and lenstr2=2 and lenstr1=1
then saledate:= str3 || str2 || '0' || str1;
when lenstr1=4 and lenstr2=1 and lenstr1=1
then flowdate:= str1 || '0'|| str2 || '0' || str3;
when lenstr1=4 and lenstr2=1 and lenstr3=2
then saledate:= str1 || '0'|| str2 || str3;
when lenstr1=4 and lenstr2=2 and lenstr3=1
then saledate:= str3 || str2 || '0' || str3 ;
when lenstr1=2 and lenstr2=2 and lenstr3=1
then saledate:= str3 || str2 || '0' || str3;
when lenstr1=2 and lenstr2=1 and lenstr3=1
then saledate:= str3 || '0' || str2 || '0' || str3;
when lenstr1=2 and lenstr2=1 and lenstr3=2
then saledate:= str3 || '0' || str2 || str3;
else saledate:=flowdate;
end case;
return(saledate);
else
saledate:= substr(flowdate,1,4) || '-' || substr(flowdate,5,2) || '-' substr(flowdate,7,2);
return(saledate);
end if;
end dateconvert;
uj5u.com熱心網友回復：
-- 問題還是很多的，現在給你改了一下
create or replace function dateconvert (p_flowdate in varchar)
return varchar
is
saledate varchar(30);
flowdate varchar(30);
str1 varchar(6);
str2 varchar(6);
str3 varchar(6);
lenstr1 number(5);
lenstr2 number(5);
lenstr3 number(5) ;
begin
if instr(flowdate,'-',1,1)>1 then
str1:=substr(flowdate,1,instr(flowdate,'-',1,1)-1);
str2:=replace(substr(flowdate,instr(flowdate,'-',1,1)+1,2),'-','');
str3:=substr(flowdate,instr(flowdate,'-',1,2)+1,2);
lenstr1:=length(str1);
lenstr2:=length(str2);
lenstr3:=length(str3);
case when lenstr3=4 and lenstr2=1 and lenstr1=1
then saledate:= str3 || '0'|| str2 || '0' || str1;
when lenstr3=4 and lenstr2=1 and lenstr1=2
then flowdate:= str3 || '0'|| str2 || str1;
when lenstr3=4 and lenstr2=2 and lenstr1=1
then saledate:= str3 || str2 || '0' || str1;
when lenstr1=4 and lenstr2=1 and lenstr1=1
then flowdate:= str1 || '0'|| str2 || '0' || str3;
when lenstr1=4 and lenstr2=1 and lenstr3=2
then saledate:= str1 || '0'|| str2 || str3;
when lenstr1=4 and lenstr2=2 and lenstr3=1
then saledate:= str3 || str2 || '0' || str3 ;
when lenstr1=2 and lenstr2=2 and lenstr3=1
then saledate:= str3 || str2 || '0' || str3;
when lenstr1=2 and lenstr2=1 and lenstr3=1
then saledate:= str3 || '0' || str2 || '0' || str3;
when lenstr1=2 and lenstr2=1 and lenstr3=2
then saledate:= str3 || '0' || str2 || str3;
else saledate:=flowdate;
end case;
return(saledate);
else
saledate:= substr(flowdate,1,4) || '-' || substr(flowdate,5,2) || '-' || substr(flowdate,7,2);
return(saledate);
end if;
end dateconvert;
uj5u.com熱心網友回復：
pl/sql developer中，點擊你創建的這個無效物件，它會提示你哪兒錯誤的。沒事，誰愛替你找編譯錯誤啊！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/63547.html
標籤：開發
上一篇：求助plsql欄位的拆分對比