可以用存储过程 `.`
create proc prc_1 @var int
as
begin
update table1表 set 字段3=字段3*@var
end
...........................................
create proc prc_1 @var int
as
begin
update table1表 set 字段3=字段3*@var
end
...........................................
解决方案 »
- 急。。osql -U** -P** 怎么写用Windows认证登陆的用户呢?
- 关于时间类型
- 如何根据sql脚本文件恢复数据库,谢谢!
- 救救妹妹!我是新手,请问如何将SQL SERVER2000数据库倒换成ORACLE数据库?高分酬谢!!!
- 字节编码 mysql
- 简单问题!!
- 触发器递归问题,搞定马上结帐
- 有谁知道linux下proc的编译过
- sql 查询问题
- 传入的表格格式数据流(TDS)远程过程调用(RPC)协议流不正确。此 RPC 请求中提供了过多的参数。最多应为 2100。如何解决?
- sql server management studio express和sql 2000的企业管理器共存问题
- sql7.0怎么备份数据?
这个内容和你差不多,你参考参考.
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
delete t from @t t where exists(select 1 from @t where PID=t.ID)
return
end
go--执行查询
select ID from dbo.f_getChild(1)
go--输出结果
/*
4
7
*/--删除测试数据
drop function f_getChild
drop table BOM
create table BOM(ID INT,PID INT,NUM INT)
insert into BOM select 1,0,1
insert into BOM select 2,1,2
insert into BOM select 3,1,3
insert into BOM select 4,1,2
insert into BOM select 5,2,2
insert into BOM select 6,3,1
insert into BOM select 7,6,2
insert into BOM select 8,6,1
insert into BOM select 9,6,3
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),NUM INT,Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,NUM,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,a.NUM*B.NUM,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
delete t from @t t where exists(select 1 from @t where PID=t.ID)
return
end
go--执行查询
select ID,NUM from dbo.f_getChild(1)
go--输出结果
/*
ID NUM
---------- -----------
4 2
5 4
7 6
8 3
9 9
*/--删除测试数据
drop function f_getChild
drop table BOM