我的sqlserver的自定义函数是实现一个属性结构,现在要把它移植到mysql里面,各位大侠帮我改一下,谢了。。下面的是我sqlserver的函数:请改成可以再mysql里面运行的。==============...
我的sqlserver的自定义函数是实现一个属性结构,现在要把它移植到mysql里面,各位大侠帮我改一下,谢了。。
下面的是我sqlserver的函数:请改成可以再mysql里面运行的。
================================================
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION GetSubtreeInfo ( @org_id int,@flag bit)
RETURNS @treeinfo TABLE
(
level int,
orgid int,
orgpid int,
name nvarchar(50)
)
AS
BEGIN
DECLARE @temptable TABLE(
level int,
orgid int,
orgpid int,
orgidString varchar(8000),
name nvarchar(50)
)
DECLARE @level AS int
SELECT @level=1
/*
@flag 如果为1则代表查询全部组织架构
@flag 如果为0则按照传入的@org_id值来查询全部组织架构
*/
IF @flag=1
BEGIN
SET @org_id = (SELECT MIN(orgid)+1 FROM org)
END
INSERT @temptable
SELECT @level,orgid, orgpid,cast(orgid as varchar),
[name] AS name
FROM org
WHERE orgid=@org_id
WHILE @@ROWCOUNT >0
BEGIN
SET @level = @level+1
INSERT INTO @temptable
SELECT @level,O.orgid,O.orgpid,t.orgidString+','+cast(O.orgid as varchar),O.name AS name
FROM org AS O JOIN @temptable AS T
ON O.orgpid = T.orgid AND T.level = @level-1
END
INSERT INTO @treeinfo
SELECT [level] AS level,orgid,orgpid,[name] AS name FROM @temptable ORDER BY orgidString
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
========================================================
这个sqlserver的函数是返回一个临时表,但我知道mysql的函数没办法返回一个表,所以在mysql里面只能用存储过程实现,各位大侠帮帮忙吧
展开