--创建函数
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;
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;
解决方案 »
- 关于ORA-01779: cannot modify a column which maps to a non key-preserved table
- oracle 9i 两个时间相减的问题...急!
- 如果写一个bat文件,希望可以在windows里自动执行一个oracle的sql语句
- REDHAT9下安装ORACLE9i,内存为256机子的可以一试么?
- 很急的问题,帮我及时解决的给500分
- Asp使用ODBC连接Oracle数据库的问题!!
- 很难理解的一个参数
- 急求解决方法,急!急!!急!!!
- 高手请进!在线等待
- 准备从SQLserver转向Oracle,所以连菜鸟都不是,入门前高分请教初级问题
- 求教一个Ibatis后台报错的问题,数据库为oracle
- 跪求大神帮忙 用Pro*c 遇到问题 回复就给C币
-- 问题还是很多的,现在给你改了一下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) ;
beginif instr(flowdate,'-',1,1)>1 thenstr1:=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;
没事,谁爱替你找编译错误啊!