以下这段代码在sqlserver分析器里面执行肯定成功,
但是在mysql里面执行会出现错误,是不是以下的语法在mysql无法认可呢
以下语句在mysql里面怎么写呢,麻烦高手指点,把代码写出来。谢谢
Create procedure Full_Search(@string varchar(100))
as
begin
declare @tbname varchar(100)
declare tbroy cursor for select name from sysobjects
where xtype='u' --第一个游标遍历所有的表open tbroy
fetch next from tbroy into @tbname
while @@fetch_status=0
begin
declare @colname varchar(100)
declare colroy cursor for select name from syscolumns
where id=object_id(@tbname) and xtype in (
select xtype from systypes
where name in ('varchar','nvarchar','char','nchar') --数据类型为字符型的字段
) --第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段 open colroy
fetch next from colroy into @colname
while @@fetch_status=0
begin
declare @sql nvarchar(4000),@j int
select @sql='select @i=count(1) from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
exec sp_executesql @sql,N'@i int output',@i=@j output --输出满足条件表的记录数
if @j>0
begin
/*
declare @v varchar(8000)
set @v='select distinct '+quotename(@colname)+' from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
exec(@v)
*/
declare @t table(Id int identity,ColValue varchar(200),ColName varchar(200),Tabname varchar(200))
insert into @t select @string,@colname,@tbname end
fetch next from colroy into @colname
end
close colroy
deallocate colroyfetch next from tbroy into @tbname
end
close tbroy
deallocate tbroyselect * from @t
endexec Full_Search '市场'
但是在mysql里面执行会出现错误,是不是以下的语法在mysql无法认可呢
以下语句在mysql里面怎么写呢,麻烦高手指点,把代码写出来。谢谢
Create procedure Full_Search(@string varchar(100))
as
begin
declare @tbname varchar(100)
declare tbroy cursor for select name from sysobjects
where xtype='u' --第一个游标遍历所有的表open tbroy
fetch next from tbroy into @tbname
while @@fetch_status=0
begin
declare @colname varchar(100)
declare colroy cursor for select name from syscolumns
where id=object_id(@tbname) and xtype in (
select xtype from systypes
where name in ('varchar','nvarchar','char','nchar') --数据类型为字符型的字段
) --第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段 open colroy
fetch next from colroy into @colname
while @@fetch_status=0
begin
declare @sql nvarchar(4000),@j int
select @sql='select @i=count(1) from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
exec sp_executesql @sql,N'@i int output',@i=@j output --输出满足条件表的记录数
if @j>0
begin
/*
declare @v varchar(8000)
set @v='select distinct '+quotename(@colname)+' from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
exec(@v)
*/
declare @t table(Id int identity,ColValue varchar(200),ColName varchar(200),Tabname varchar(200))
insert into @t select @string,@colname,@tbname end
fetch next from colroy into @colname
end
close colroy
deallocate colroyfetch next from tbroy into @tbname
end
close tbroy
deallocate tbroyselect * from @t
endexec Full_Search '市场'
你自己参考一下mysql的语法改一下吧,我嫌麻烦,这里不帮你改了, 呵呵;)
原因:有一些语法不一样.
解决:修改为mysql的存储过程语法类型.
[/align]