SQL Sql存储过程while循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16115043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Stored Procedure while loop
提问by user2272865
Example of Data:
数据示例：
ID Name ParentID
1 parent-1 NULL
2 parent-2 NULL
3 sub 1
4 child-1-1 3
5 child-1-2 1
6 child-2-1 2
Now If I search for Name like '%child-1%', I want the following records: Row-1, Row-3, Row-4 and row-5 in above data. What kind if stored procedure can I write that returns me disctinct rows?
现在，如果我搜索 Name like '%child-1%'，我需要以下记录：上述数据中的第 1 行、第 3 行、第 4 行和第 5 行。我可以编写什么样的存储过程来返回不同的行？
My Idea is If I search for a text, it will continue selecting the records from the table until the parentID is null. So If I do a like search for 'child-1', a basic sql query returns Row-4 and Row-5. But my procedure shud check in a loop that Row-4 has parentid which is not null, so it gets a row with ID= parentid of row-4 which is 3. Now it gets a row with ID = parentid of row-3 which is 1 and gets row-1. Now parentd of row-1 is NULL so it stops.
我的想法是如果我搜索一个文本，它将继续从表中选择记录，直到 parentID 为空。因此，如果我对“child-1”进行类似搜索，则基本的 sql 查询将返回 Row-4 和 Row-5。但是我的程序 shud 在循环中检查 Row-4 的 parentid 不是空的，所以它得到一个 ID= row-4 的 parentid 为 3 的行。现在它得到一个 ID = row-3 的 parentid 的行是 1 并获得第 1 行。现在第 1 行的 parentd 是 NULL，所以它停止了。
I am using this stored procedure to implement a search functionality in tree view in which I want to keep the parent-child hierarchy after search.
我正在使用此存储过程在树视图中实现搜索功能，我希望在搜索后保留父子层次结构。
so far I have tried this but I am new to stored procedures:
到目前为止，我已经尝试过这个，但我是存储过程的新手：
USE DBname
Go
DECLARE @ParentID int
Declare @myresultset Cursor
Set @myresultset = CURSOR for Select ParentID from mytable where Name like 'child-1%'
OPEN @myresultset
Fetch NEXT from @myresultset
into @ParentID
While @@Fetch_Status=0
Begin
While @ParentID is not NULL
Begin
Select @ParentID = ParentID from mytable where ID=@ParentID
Select distinct * from mytable where ID=@ParentID
End
Fetch Next from @myresultset
into @ParentID
End
close @myresultset
采纳答案by Michael Harmon
Instead of coding this using procedural code, I recoded this using set oriented SQL. I am using a recursive CTE to find the "parents" of the given children. Here is my stored procedure:
我没有使用过程代码对其进行编码，而是使用面向集合的 SQL 对其进行了重新编码。我正在使用递归 CTE 来查找给定孩子的“父母”。这是我的存储过程：
CREATE PROC find_parents (@childname varchar(20)) as
;WITH heirarchy_cte (ID, Name, ParentID, Level) as
(SELECT e.ID, e.Name, e.ParentID, 0 as Level
FROM mytable as e
where e.Name like @childname
UNION ALL
SELECT e.ID, e.Name, e.ParentID, Level+1
FROM mytable as e
INNER JOIN heirarchy_cte as h
ON h.ParentID=e.ID
)
SELECT DISTINCT ID, Name, ParentID
FROM heirarchy_cte
ORDER BY ID
I then run it with:
然后我运行它：
exec find_parents @childname='child-1%'
If my results are correct, then this solution should scale better for a larger quantity of data. I also coded it as a stored procedure as you had indicated.
如果我的结果是正确的，那么这个解决方案应该可以更好地扩展大量数据。正如您所指出的，我还将其编码为存储过程。
To see the full code, please see the SQL Fiddle at: Find Parents SQL Fiddle Demo
要查看完整代码，请参阅 SQL Fiddle at： Find parents SQL Fiddle Demo
If my solution is correct, please mark it as the answer. Thanks.
如果我的解决方案正确，请将其标记为答案。谢谢。
回答by Dinesh Vaitage
Try this simple example
试试这个简单的例子
CREATE PROCEDURE proc (
IN i INTEGER,
IN j INTEGER)
BEGIN
DECLARE z integer;
set z=i;
while (z<j) do
begin
set z=z+1;
end;
end while;
END;
