以下这段代码在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使用符号连接出现1146
- mysql 根据节点名 模糊查询 该节点以上的节点 进行树绑定
- MySQL当表数据量极大时[单张表800万条记录],如何提高查询及更新速度?
- mysqldump 结合批处理备份
- c++调用mysql存储过程问题
- mysql建表: 出现 error 1604(42000)的错误,建表的源代码如正文(是在数据库“test”中建立这个表的)
- Got error 127 from storage engine
- mysql表中建立FULLTEXT(全文检索)字段类型,有什么用处?
- 求教MySQL C++连接时程序崩溃时的异常处理问题
- 求月度平均值
- 报错1064(42000) 郁闷!!!
- 求救:mysql数据库还原问题
你自己参考一下mysql的语法改一下吧,我嫌麻烦,这里不帮你改了, 呵呵;)
原因:有一些语法不一样.
解决:修改为mysql的存储过程语法类型.
[/align]