MySQL新手,需要用到一个函数,但不会写,
MS SQL的写法如下:CREATE FUNCTION [dbo].[GetXXFunction]
(@ParentID int)
RETURNS @treeinfo table
(MenuID int NOT NULL,
ParentID int NULL,
Name varchar(100) NOT NULL,
Code varchar(100) not null,
LinkAddress varchar(255),
Visible smallint,
[Index] int,
IsSysMenu smallint,
LogTypeID int,
Re varchar(255),
level1 int,
flag varchar(6000)
) AS BEGIN DECLARE
@level AS int,
@menu_id as int,
@path AS varchar(200) SELECT @level = 0, @path = 'NULL'
INSERT INTO @treeinfo
SELECT MenuID,ParentID,Name,Code,LinkAddress,
Visible,[Index],IsSysMenu,LogTypeID,Re,@level,'NULL->'+convert(varchar,MenuID)
FROM sys_menu
WHERE ParentID=@ParentID
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT e.MenuID,e.ParentID,e.Name,e.Code,e.LinkAddress,
e.Visible,e.[Index],e.IsSysMenu,e.LogTypeID,e.Re,@level,t.flag+'->'+convert(varchar,e.MenuID)
FROM sys_menu as e join @treeinfo as t
on e.ParentID=t.MenuID and t.level1=@level-1
END
RETURN END
MS SQL的写法如下:CREATE FUNCTION [dbo].[GetXXFunction]
(@ParentID int)
RETURNS @treeinfo table
(MenuID int NOT NULL,
ParentID int NULL,
Name varchar(100) NOT NULL,
Code varchar(100) not null,
LinkAddress varchar(255),
Visible smallint,
[Index] int,
IsSysMenu smallint,
LogTypeID int,
Re varchar(255),
level1 int,
flag varchar(6000)
) AS BEGIN DECLARE
@level AS int,
@menu_id as int,
@path AS varchar(200) SELECT @level = 0, @path = 'NULL'
INSERT INTO @treeinfo
SELECT MenuID,ParentID,Name,Code,LinkAddress,
Visible,[Index],IsSysMenu,LogTypeID,Re,@level,'NULL->'+convert(varchar,MenuID)
FROM sys_menu
WHERE ParentID=@ParentID
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT e.MenuID,e.ParentID,e.Name,e.Code,e.LinkAddress,
e.Visible,e.[Index],e.IsSysMenu,e.LogTypeID,e.Re,@level,t.flag+'->'+convert(varchar,e.MenuID)
FROM sys_menu as e join @treeinfo as t
on e.ParentID=t.MenuID and t.level1=@level-1
END
RETURN END
MySQL 函数的返回类型不能是TABLE