第一次写函数,用到递归,不知道能不能执行,这样写有没有错,请高手们看看:
要实现在功能是要删除树中的节点
CREATE FUNCTION dbo.deltree(@pid integer)
RETURNS int AS
BEGIN
DECLARE @tid integer
DECLARE mycur CURSOR FOR
SELECT i_id
FROM bjxt_tree
WHERE i_pid = @pid
ORDER BY i_idOPEN mycurFETCH NEXT FROM mycur
INTO @tidWHILE @@FETCH_STATUS = 0
BEGIN
--select i_id into @tp from bjxt_tree where i_pid = @tid
return dbo.deltree(@tid)//这边是不是返回了,那下面的语句就不执行了是吗
FETCH NEXT FROM mycur //这边的语句如何执行。?
INTO @tid
END
close mycur
return @tid
END
要实现在功能是要删除树中的节点
CREATE FUNCTION dbo.deltree(@pid integer)
RETURNS int AS
BEGIN
DECLARE @tid integer
DECLARE mycur CURSOR FOR
SELECT i_id
FROM bjxt_tree
WHERE i_pid = @pid
ORDER BY i_idOPEN mycurFETCH NEXT FROM mycur
INTO @tidWHILE @@FETCH_STATUS = 0
BEGIN
--select i_id into @tp from bjxt_tree where i_pid = @tid
return dbo.deltree(@tid)//这边是不是返回了,那下面的语句就不执行了是吗
FETCH NEXT FROM mycur //这边的语句如何执行。?
INTO @tid
END
close mycur
return @tid
END
CREATE FUNCTION dbo.deltree(@pid integer)
RETURNS @tb TABLE(id int)
AS
BEGIN
DECLARE @tid integer
DECLARE mycur CURSOR FOR
SELECT i_id
FROM bjxt_tree
WHERE i_pid = @pid
ORDER BY i_id OPEN mycur FETCH NEXT FROM mycur
INTO @tid WHILE @@FETCH_STATUS = 0
BEGIN
--select i_id into @tp from bjxt_tree where i_pid = @tid
INSERT @tb SELECT * FROM dbo.deltree(@tid)
FETCH NEXT FROM mycur
INTO @tid
END
close mycur
RETURN
END
GO
FROM bjxt_tree A, dbo.deltree(1) B -- 1 是要删除的ID
WHERE A.i_id = B.id
CREATE FUNCTION dbo.deltree(@pid integer)
RETURNS @tb TABLE(id int)
AS
BEGIN
DECLARE @tid integer
DECLARE mycur CURSOR FOR
SELECT i_id
FROM bjxt_tree
WHERE i_pid = @pid
ORDER BY i_idOPEN mycurFETCH NEXT FROM mycur
INTO @tidWHILE @@FETCH_STATUS = 0
BEGINDELETE @tb FROM bjxt_tree , dbo.deltree(@tid) -- 1 是要删除的ID
WHERE bjxt_tree.i_id = @tidFETCH NEXT FROM mycur
INTO @tid
END
close mycur
RETURN
END
-----------------
实在是真的搞不清楚,可以帮我改一下上帮的那个函数吗?