CREATE PROCEDURE [xsd_shjz]
@v_djxh varchar(20),
@v_jzshbz varchar(1) AS set nocount on
declare @a integer
declare @v_xsbm varchar(13)
declare @v_lsh numeric(20)
declare @n_sxsl numeric
declare @n_zxfssl numeric
declare @n_zxzcsl numeric
declare @n_zcsl numeric
declare @n_xjzje numeric
declare @n_yjzje numeric
declare @n_ljjhje numeric
declare @n_ljjhsl numeric
declare xsdj_c1 cursor for select xsbm,sxsl from djmxb where djxh=@v_djxh and flag='2'begin
open xsdj_c1
fetch next from xsdj_c1 into @v_xsbm,@n_sxsl-- if @@FETCH_STATUS <> 0
while @@FETCH_STATUS = 0
begin
select @v_lsh=max(lsh) from djmxb where 1=1
if @v_lsh=null
set @v_lsh=0 select @a=count(*) from djmxb where jzshbz='2' and flag='0' and xsbm=@v_xsbm
if @a=0
begin
update djmxb
set jzshbz=@v_jzshbz,zcsl=-@n_sxsl,jzje=0,hscbje=0
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
end
else
begin
update djmxb
set jzshbz=@v_jzshbz,
zcsl=(select zcsl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2'))-@n_sxsl,
@n_zxfssl=(select fssl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')),
@n_zxzcsl=zcsl
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
-- if @n_zxzcsl>=0
-- begin
if @n_zxzcsl>=0 and @n_zxzcsl<=@n_zxfssl*1/3
begin
update djmxb
set jzje=(select hshjdj from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2'))
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
select @n_yjzje=jzje from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')
end
else
begin
select @n_yjzje=jzje,@n_zcsl=zcsl,@n_ljjhje=ljjhje,@n_ljjhsl=ljjhsl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')
if @n_zcsl=0 or (@n_yjzje/@n_zcsl)<=0
begin
update djmxb
set jzje=@n_sxsl*(@n_ljjhje/@n_ljjhsl)
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
end
update djmxb
set jzje=@n_sxsl*(@n_yjzje/@n_zcsl)
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
end
select @n_xjzje=jzje from djmxb where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm update djmxb
set hscbje=@n_yjzje-@n_xjzje,
jzshbz='2',lsh=@v_lsh+1
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm-- end
end
fetch next from xsdj_c1 into @v_xsbm,@n_sxsl
end
close xsdj_c1
end;
GO
中间主要是一些处理过程,当有多条纪录循环时,在open cusor时就会出错。
@v_djxh varchar(20),
@v_jzshbz varchar(1) AS set nocount on
declare @a integer
declare @v_xsbm varchar(13)
declare @v_lsh numeric(20)
declare @n_sxsl numeric
declare @n_zxfssl numeric
declare @n_zxzcsl numeric
declare @n_zcsl numeric
declare @n_xjzje numeric
declare @n_yjzje numeric
declare @n_ljjhje numeric
declare @n_ljjhsl numeric
declare xsdj_c1 cursor for select xsbm,sxsl from djmxb where djxh=@v_djxh and flag='2'begin
open xsdj_c1
fetch next from xsdj_c1 into @v_xsbm,@n_sxsl-- if @@FETCH_STATUS <> 0
while @@FETCH_STATUS = 0
begin
select @v_lsh=max(lsh) from djmxb where 1=1
if @v_lsh=null
set @v_lsh=0 select @a=count(*) from djmxb where jzshbz='2' and flag='0' and xsbm=@v_xsbm
if @a=0
begin
update djmxb
set jzshbz=@v_jzshbz,zcsl=-@n_sxsl,jzje=0,hscbje=0
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
end
else
begin
update djmxb
set jzshbz=@v_jzshbz,
zcsl=(select zcsl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2'))-@n_sxsl,
@n_zxfssl=(select fssl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')),
@n_zxzcsl=zcsl
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
-- if @n_zxzcsl>=0
-- begin
if @n_zxzcsl>=0 and @n_zxzcsl<=@n_zxfssl*1/3
begin
update djmxb
set jzje=(select hshjdj from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2'))
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
select @n_yjzje=jzje from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')
end
else
begin
select @n_yjzje=jzje,@n_zcsl=zcsl,@n_ljjhje=ljjhje,@n_ljjhsl=ljjhsl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')
if @n_zcsl=0 or (@n_yjzje/@n_zcsl)<=0
begin
update djmxb
set jzje=@n_sxsl*(@n_ljjhje/@n_ljjhsl)
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
end
update djmxb
set jzje=@n_sxsl*(@n_yjzje/@n_zcsl)
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
end
select @n_xjzje=jzje from djmxb where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm update djmxb
set hscbje=@n_yjzje-@n_xjzje,
jzshbz='2',lsh=@v_lsh+1
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm-- end
end
fetch next from xsdj_c1 into @v_xsbm,@n_sxsl
end
close xsdj_c1
end;
GO
中间主要是一些处理过程,当有多条纪录循环时,在open cusor时就会出错。
解决方案 »
- 如何在单表中查询整条数据
- 请教一下,在BOOT.INI里加什么参数可以增大SQL使用更多内存,现在最大使用不能超过2G的内存.
- SQL 2005维护计划向导打不开 ??
- MS SQL 中一个表的多个字段对应另一个表的同一个字段如何效率查询?
- 请问:在SQLSERVER中的STR函数如何处理字符串数据参数?(急!!!)
- 数据库的还原
- 把文本文件导入sql server中的日期处理问题。
- 如何删除此数据库
- 分区视图的排序,请看看这里如何order by?
- sql数据库中的表排序规则为按入库时的先后顺序不变,如何弄?
- 在SQL SERVER7 中如何建立一个序列?(在线等待)
- SQL SERVER与ORACLE之间的联接服务器的存储过程怎么写
open your_cursor
fetch next from your_cursor into ...
while @@fetch_status=0
...
fetch next from your_cursor into...
open xsdj_c1
...
end
这里的begin...end应该可以省掉
CREATE PROCEDURE [xsd_shjz]
@v_djxh varchar(20),
@v_jzshbz varchar(1) ASset nocount on
declare @a integer
declare @v_xsbm varchar(13)
declare @v_lsh numeric(20)
declare @n_sxsl numeric
declare @n_zxfssl numeric
declare @n_zxzcsl numeric
declare @n_zcsl numeric
declare @n_xjzje numeric
declare @n_yjzje numeric
declare @n_ljjhje numeric
declare @n_ljjhsl numeric
BEGIN TRAN
declare xsdj_c1 cursor for select xsbm,sxsl from djmxb where djxh=@v_djxh and flag='2'
--begin
open xsdj_c1
IF @@CURSOR_ROWS = 0 GOTO END_xsdj_c1 -- if @@FETCH_STATUS <> 0 DECLARE @I INT
DECLARE @J INT
SELECT @I=0 while @I<@@CURSOR_ROWS
begin
IF @I=0
fetch FIRST from xsdj_c1 into @v_xsbm,@n_sxsl
ELSE
fetch next from xsdj_c1 into @v_xsbm,@n_sxsl
select @v_lsh=max(lsh) from djmxb where 1=1
if @v_lsh=null
set @v_lsh=0 select @a=count(*) from djmxb where jzshbz='2' and flag='0' and xsbm=@v_xsbm
if @a=0
begin
update djmxb
set jzshbz=@v_jzshbz,zcsl=-@n_sxsl,jzje=0,hscbje=0
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
end
else
begin
update djmxb
set jzshbz=@v_jzshbz,
zcsl=(select zcsl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2'))-@n_sxsl,
@n_zxfssl=(select fssl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')),
@n_zxzcsl=zcsl
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
-- if @n_zxzcsl>=0
-- begin
if @n_zxzcsl>=0 and @n_zxzcsl<=@n_zxfssl*1/3
begin
update djmxb
set jzje=(select hshjdj from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2'))
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
select @n_yjzje=jzje from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')
end
else
begin
select @n_yjzje=jzje,@n_zcsl=zcsl,@n_ljjhje=ljjhje,@n_ljjhsl=ljjhsl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')
if @n_zcsl=0 or (@n_yjzje/@n_zcsl)<=0
begin
update djmxb
set jzje=@n_sxsl*(@n_ljjhje/@n_ljjhsl)
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
end
update djmxb
set jzje=@n_sxsl*(@n_yjzje/@n_zcsl)
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
end
select @n_xjzje=jzje from djmxb where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm update djmxb
set hscbje=@n_yjzje-@n_xjzje,
jzshbz='2',lsh=@v_lsh+1
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
-- end
end
SELECT @I=@I+1
end END_xsdj_c1:
close xsdj_c1
DEALLOCATE xsdj_c1IF @J=0
BEGIN
COMMIT TRAN
RETURN 0
END
ELSE
BEGIN
ROLLBACK TRAN
RETURN -1
END
CREATE PROCEDURE [xsd_shjz]
@v_djxh varchar(20),
@v_jzshbz varchar(1) ASset nocount on
declare @a integer
declare @v_xsbm varchar(13)
declare @v_lsh numeric(20)
declare @n_sxsl numeric
declare @n_zxfssl numeric
declare @n_zxzcsl numeric
declare @n_zcsl numeric
declare @n_xjzje numeric
declare @n_yjzje numeric
declare @n_ljjhje numeric
declare @n_ljjhsl numeric
BEGIN TRAN
declare xsdj_c1 cursor for select xsbm,sxsl from djmxb where djxh=@v_djxh and flag='2'
--begin
open xsdj_c1
IF @@CURSOR_ROWS = 0 GOTO END_xsdj_c1 -- if @@FETCH_STATUS <> 0 DECLARE @I INT
DECLARE @J INT
SELECT @I=0 while @I<@@CURSOR_ROWS
begin
IF @I=0
fetch FIRST from xsdj_c1 into @v_xsbm,@n_sxsl
ELSE
fetch next from xsdj_c1 into @v_xsbm,@n_sxsl
select @v_lsh=max(lsh) from djmxb where 1=1
if @v_lsh=null
set @v_lsh=0 select @a=count(*) from djmxb where jzshbz='2' and flag='0' and xsbm=@v_xsbm
if @a=0
begin
update djmxb
set jzshbz=@v_jzshbz,zcsl=-@n_sxsl,jzje=0,hscbje=0
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
end
else
begin
update djmxb
set jzshbz=@v_jzshbz,
zcsl=(select zcsl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2'))-@n_sxsl,
@n_zxfssl=(select fssl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')),
@n_zxzcsl=zcsl
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
-- if @n_zxzcsl>=0
-- begin
if @n_zxzcsl>=0 and @n_zxzcsl<=@n_zxfssl*1/3
begin
update djmxb
set jzje=(select hshjdj from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2'))
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
select @n_yjzje=jzje from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')
end
else
begin
select @n_yjzje=jzje,@n_zcsl=zcsl,@n_ljjhje=ljjhje,@n_ljjhsl=ljjhsl from djmxb where xsbm=@v_xsbm and flag='0' and lsh=(select max(lsh) from djmxb where xsbm=@v_xsbm and jzshbz='2')
if @n_zcsl=0 or (@n_yjzje/@n_zcsl)<=0
begin
update djmxb
set jzje=@n_sxsl*(@n_ljjhje/@n_ljjhsl)
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
end
update djmxb
set jzje=@n_sxsl*(@n_yjzje/@n_zcsl)
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
end
select @n_xjzje=jzje from djmxb where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm update djmxb
set hscbje=@n_yjzje-@n_xjzje,
jzshbz='2',lsh=@v_lsh+1
from djmxb
where djxh=@v_djxh and flag='2' and xsbm=@v_xsbm
SELECT @J=@@ERROR
IF @J <>0 GOTO END_xsdj_c1
-- end
end
SELECT @I=@I+1
end END_xsdj_c1:
close xsdj_c1
DEALLOCATE xsdj_c1IF @J=0
BEGIN
COMMIT TRAN
RETURN 0
END
ELSE
BEGIN
ROLLBACK TRAN
RETURN -1
END
close xsdj_c1
---------------->>>
close xsdj_c1
DEALLOCATE xsdj_c1