select * from app_reportfomulalist where len(fomula)>400
declare @fomulaId varchar(36)
declare my_cursor cursor
for
select fomulaId from app_reportfomulalist
open my_cursor
fetch next from my_cursor into @fomulaId
while(@@fetch_status=0)
begin declare @formula varchar(4000)
declare @CellId varchar (50)
select @CellId=fomulaId ,@formula=fomula from app_reportfomulalist where fomulaId=@fomulaId
select @formula = substring(@formula,2,len(@formula)-2)+ '+' declare @str varchar(50)
declare @i int
declare @j int
declare @tempFuhao varchar(5)
declare @tempFuhao_1 varchar(5)
while len(@formula) > 0
begin
set @i=charindex('+',@formula)
set @j=charindex('-',@formula)
if @i>@j and @j>0
begin
set @i=@j
end
set @str=substring(@formula,0,@i)
set @tempFuhao=substring(@formula,@i,1)
set @formula=substring(@formula,@i+1,len(@formula)-@i)
insert into app_temp_formula(formulaid,cellCode,oper) values(@CellId,@str,isnull(@tempFuhao_1,'+'))
set @tempFuhao_1=@tempFuhao
end fetch next from my_cursor into @fomulaId
end
close my_cursor
declare @fomulaId varchar(36)
declare my_cursor cursor
for
select fomulaId from app_reportfomulalist
open my_cursor
fetch next from my_cursor into @fomulaId
while(@@fetch_status=0)
begin declare @formula varchar(4000)
declare @CellId varchar (50)
select @CellId=fomulaId ,@formula=fomula from app_reportfomulalist where fomulaId=@fomulaId
select @formula = substring(@formula,2,len(@formula)-2)+ '+' declare @str varchar(50)
declare @i int
declare @j int
declare @tempFuhao varchar(5)
declare @tempFuhao_1 varchar(5)
while len(@formula) > 0
begin
set @i=charindex('+',@formula)
set @j=charindex('-',@formula)
if @i>@j and @j>0
begin
set @i=@j
end
set @str=substring(@formula,0,@i)
set @tempFuhao=substring(@formula,@i,1)
set @formula=substring(@formula,@i+1,len(@formula)-@i)
insert into app_temp_formula(formulaid,cellCode,oper) values(@CellId,@str,isnull(@tempFuhao_1,'+'))
set @tempFuhao_1=@tempFuhao
end fetch next from my_cursor into @fomulaId
end
close my_cursor
v_fomulaId varchar2(36);
v_formula varchar2(4000);
v_CellId varchar2(50);
v_str varchar2(50);
v_i int;
v_j int ;
v_tempFuhao varchar2(5);
v_tempFuhao_1 varchar2(5);
begin
for rec in (
select fomulaId from app_reportfomulalist )
loop
v_fomulaId:=rec.fomulaId ;
v_CellId:=v_fomulaId ;select fomula into v_formula from app_reportfomulalist where fomulaId=v_fomulaId;v_formula := substr(v_formula,2,length(v_formula)-2)|| '+';while legth(v_formula) > 0
loop
v_i:=instr('+',v_formula);
v_j:=instr('-',v_formula);if v_i>v_j and v_j>0 then
v_i:=v_j;
end if;
v_str:=substr(v_formula,1,v_i);
v_tempFuhao;=substr(v_formula,v_i,1);
v_formula:=substr(v_formula,v_i+1,length(v_formula)-v_i);
insert into app_temp_formula(formulaid,cellCode,oper)
values(v_CellId,v_str,nvl(v_tempFuhao_1,'+'));
v_tempFuhao_1:=v_tempFuhao;
end loop;end loop;end;
变量定义不用@
赋值 :=
一些基础函数略有不同,注意替换就好
上面的没有环境测试
大概得修改了一下
lz还得自己调试