Question

【急】db2 逗号拆分记录 存储过程错误

DROP PROCEDURE TOOLS.splite@
CREATE PROCEDURE TOOLS.splite
BEGIN
DECLARE @ID SMALLINT;
DECLARE @MLS_NR SMALLINT;
DECLARE @F_SEGMENT INTEGER ;
DECLARE @NODE INTEGER ;
DECLARE @T_SEGMENT INTEGER ;
DECLARE @NODE1 INTEGER ;
DECLARE @T_SEGMENT1 INTEGER ;
DECLARE @NODE2 INTEGER ;
DECLARE @T_SEGMENT2 INTEGER ;
DECLARE @NODE3 INTEGER ;
DECLARE @T_SEGMENT3 INTEGER ;
DECLARE @NODE4 SMALLINT ;
DECLARE @T_SEGMENT4 SMALLINT ;
DECLARE @TEXT1 VARCHAR(57);
DECLARE @TEXT2 SMALLINT;
DECLARE @EXIT_NR SMALLINT;
DECLARE @COLOR VARCHAR(5);
DECLARE @CONTYP INTEGER;
DECLARE @TXTCONT VARCHAR(100);
DECLARE @INFOTYP VARCHAR(4);

DECLARE @tmpstr varchar(57);
DECLARE @tmp varchar(57);

CREATE TABLE rou.Tmptable like rou.signpost;

DECLARE youbiao cursor for
select ID,MLS_NR,F_SEGMENT,NODE,T_SEGMENT,NODE1,T_SEGMENT1,NODE2,T_SEGMENT2,NODE3,T_SEGMENT3,NODE4,T_SEGMENT4,TEXT1,TEXT2,EXIT_NR,COLOR,CONTYP,TXTCONT,INFOTYP from rou.signpost ;
open youbiao ;
fetch next from youbiao into @ID,@MLS_NR,@F_SEGMENT,@NODE,@T_SEGMENT,@NODE1,@T_SEGMENT1,@NODE2,@T_SEGMENT2,@NODE3,@T_SEGMENT3,@NODE4,@T_SEGMENT4,@TEXT1,@TEXT2,@EXIT_NR,@COLOR,@CONTYP,@TXTCONT,@INFOTYP;
while @@FETCH_STATUS = 0
begin
select @tmp = @TEXT1
while charindex(',',@tmp) > 0
begin
select @tmpstr = substring(@tmp,1,charindex(',',@tmp)-1)
insert into rou.Tmptable values(@ID,@MLS_NR,@F_SEGMENT,@NODE,@T_SEGMENT,@NODE1,@T_SEGMENT1,@NODE2,@T_SEGMENT2,@NODE3,@T_SEGMENT3,@NODE4,@T_SEGMENT4,@tmpstr,@TEXT2,@EXIT_NR,@COLOR,@CONTYP,@TXTCONT,@INFOTYP)
select @tmp = substring(@tmp,charindex(',',@tmp)+1,len(@tmp))
end
insert into rou.Tmptable values(@ID,@MLS_NR,@F_SEGMENT,@NODE,@T_SEGMENT,@NODE1,@T_SEGMENT1,@NODE2,@T_SEGMENT2,@NODE3,@T_SEGMENT3,@NODE4,@T_SEGMENT4,@tmp,@TEXT2,@EXIT_NR,@COLOR,@CONTYP,@TXTCONT,@INFOTYP)
fetch next from youbiao into @ID,@MLS_NR,@F_SEGMENT,@NODE,@T_SEGMENT,@NODE1,@T_SEGMENT1,@NODE2,@T_SEGMENT2,@NODE3,@T_SEGMENT3,@NODE4,@T_SEGMENT4,@TEXT1,@TEXT2,@EXIT_NR,@COLOR,@CONTYP,@TXTCONT,@INFOTYP
close youbiao;
end@
---------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "from" was found following "oubiao ; fetch
next". Expected tokens may include: ".". LINE NUMBER=32. SQLSTATE=42601
--------------------------------

PS: 请尽量提出错误并解决 小弟第一次写这些东西
DB2的东西

Answer 1

DROP PROCEDURE TOOLS.splite
@
CREATE PROCEDURE TOOLS.splite
P1: BEGIN
declare at_endc10 int default 0;

p2: begin
DECLARE ID SMALLINT;
DECLARE MLS_NR SMALLINT;
DECLARE F_SEGMENT INTEGER ;
DECLARE NODE INTEGER ;
DECLARE T_SEGMENT INTEGER ;
DECLARE NODE1 INTEGER ;
DECLARE T_SEGMENT1 INTEGER ;
DECLARE NODE2 INTEGER ;
DECLARE T_SEGMENT2 INTEGER ;
DECLARE NODE3 INTEGER ;
DECLARE T_SEGMENT3 INTEGER ;
DECLARE NODE4 SMALLINT ;
DECLARE T_SEGMENT4 SMALLINT ;
DECLARE TEXT1 VARCHAR(57);
DECLARE TEXT2 SMALLINT;
DECLARE EXIT_NR SMALLINT;
DECLARE COLOR VARCHAR(5);
DECLARE CONTYP INTEGER;
DECLARE TXTCONT VARCHAR(100);
DECLARE INFOTYP VARCHAR(4);

DECLARE tmpstr varchar(57);
DECLARE tmp varchar(57);

CREATE TABLE rou.Tmptable like rou.signpost;

DECLARE youbiao cursor with return for
select ID,MLS_NR,F_SEGMENT,NODE,T_SEGMENT,NODE1,T_SEGMENT1,NODE2,T_SEGMENT2,NODE3,T_SEGMENT3,NODE4,T_SEGMENT4,TEXT1,TEXT2,EXIT_NR,COLOR,CONTYP,TXTCONT,INFOTYP from rou.signpost ;
declare continue handler for not_found set at_endc10 = 1;
open youbiao ;
while(at_endc10 < 1) do
fetch youbiao into ID,MLS_NR,F_SEGMENT,NODE,T_SEGMENT,NODE1,T_SEGMENT1,NODE2,T_SEGMENT2,NODE3,T_SEGMENT3,NODE4,T_SEGMENT4,TEXT1,TEXT2,EXIT_NR,COLOR,CONTYP,TXTCONT,INFOTYP;
if (at_endc10 < 1) then
insert into rou.Tmptable values(ID,MLS_NR,F_SEGMENT,NODE,T_SEGMENT,NODE1,T_SEGMENT1,NODE2,T_SEGMENT2,NODE3,T_SEGMENT3,NODE4,T_SEGMENT4,tmp,TEXT2,EXIT_NR,COLOR,CONTYP,TXTCONT,INFOTYP)
fetch next from youbiao into ID,MLS_NR,F_SEGMENT,NODE,T_SEGMENT,NODE1,T_SEGMENT1,NODE2,T_SEGMENT2,NODE3,T_SEGMENT3,NODE4,T_SEGMENT4,TEXT1,TEXT2,EXIT_NR,COLOR,CONTYP,TXTCONT,INFOTYP
end if;
end while;
close youbiao;
end P2;
end P1
@

不一定对，参考一下吧

Answer 2

1、变量前不要@；
2、每一句末尾都写上分号；
3、变量对变量赋值用SET不用SELECT；
4、FETCH_STATUS是MSSQL嘀；
总之这个基本上是按照MSSQL语法写嘀，先把DB2的语法基础东西了解一下再来写，思路可以套用，但是语法不行