 [计算机技术]存储过程中不能使用数组的问题 |
| 存储过程不能处理数组形式的变量，具体解决办法是将数组的各个项连接起来用特殊字符隔开（比如‘|’‘#’等）以便在存储过程中再分解--向数据库中添加新闻(用数组的方式)CREATE PROCEDURE tb_addart( @art_title char(100), @art_writer char(20), @art_nr Text, @art_time SmallDateTime, @art_source char(20), @art_pic int, @art_fj int, @art_fb int, @art_p int, @art_q int, @art_lmname char(150), @art_parentid char(100), @art_tzname char(150), @art_tzpid char(100), @art_flag int output) AS--台帐变量declare @tz_ksid intdeclare @art_tzid intdeclare @tz_name char(20)declare @tz_parentid int--栏目变量declare @art_id intdeclare @lm_ksid intdeclare @art_lmid intdeclare @lm_name char(20)declare @lm_parentid int--公用变量declare @pointercurr1 int --指向一个数组项的头declare @pointerprev1 int --指向一个数组项的尾declare @pointercurr2 intdeclare @pointerprev2 intdeclare @len1 intdeclare @len2 intdeclare @leng1 intdeclare @leng2 intdeclare @file_name1 char(200)declare @file_name2 char(200)if exists (select * from tb_art where art_writer=@art_writer and art_title=@art_title and art_source=@art_source) select @art_flag=0else begin insert into tb_art (art_writer,art_time,art_title,art_nr,art_count,art_source,art_shh,art_pic,art_fj,art_fb) values (@art_writer,@art_time,@art_title,@art_nr,0,@art_source,0,@art_pic,@art_fj,@art_fb) select @art_flag=1 select @art_id=art_id from tb_art where art_writer=@art_writer and art_title=@art_title and art_source=@art_source set @pointerprev1=1 set @pointercurr1=1 set @pointerprev2=1 set @pointercurr2=1 set @leng1=len(@art_lmname) set @leng2=len(@art_parentid) while @art_p>0 begin if @pointerprev1<@leng1 begin set @pointercurr1=charindex('|',@art_lmname,@pointerprev1) set @len1=@pointercurr1-@pointerprev1 set @lm_name=Substring(@art_lmname,@pointerprev1,@len1) set @pointerprev1=@pointercurr1+1 if @pointerprev2<@leng2 begin set @pointercurr2=charindex('|',@art_parentid,@pointerprev2) set @len2=@pointercurr2-@pointerprev2 set @lm_parentid=Substring(@art_parentid,@pointerprev2,@len2) set @pointerprev2=@pointercurr2+1 select @lm_ksid=lm_ksid from tb_lm where lm_name=@lm_name and lm_parentid=@lm_parentid if @lm_ksid=0 begin update tb_art set art_fb=1 where art_writer=@art_writer and art_title=@art_title and art_source=@art_source end select @art_lmid=lm_id from tb_lm where lm_name=@lm_name and lm_parentid=@lm_parentid insert into tb_art_lm(art_id,art_lmid)values(@art_id,@art_lmid) end end set @art_p=@art_p-1 end--台帐处理 set @pointerprev1=1 set @pointercurr1=1 set @pointerprev2=1 set @pointercurr2=1 set @leng1=len(@art_tzname) set @leng2=len(@art_tzpid) while @art_q>0 begin if @pointerprev1<@leng1 begin set @pointercurr1=charindex('|',@art_tzname,@pointerprev1) set @len1=@pointercurr1-@pointerprev1 set @tz_name=Substring(@art_tzname,@pointerprev1,@len1) set @pointerprev1=@pointercurr1+1 if @pointerprev2<@leng2 begin set @pointercurr2=charindex('|',@art_tzpid,@pointerprev2) set @len2=@pointercurr2-@pointerprev2 set @tz_parentid=Substring(@art_tzpid,@pointerprev2,@len2) set @pointerprev2=@pointercurr2+1 select @tz_ksid=tz_ksid from tb_tz where tz_name=@tz_name and tz_parentid=@tz_parentid if @tz_ksid=0 begin update tb_art set art_fb=1 where art_writer=@art_writer and art_title=@art_title and art_source=@art_source --要变动数据库 end select @art_tzid=tz_id from tb_tz where tz_name=@tz_name and tz_parentid=@tz_parentid insert into tb_art_tz(art_id,art_tzid)values(@art_id,@art_tzid) end end set @art_q=@art_q-1 end endGO | |
|
|
---------------------------------------------------------------------------------------------------------- |
