56,687
社区成员
发帖
与我相关
我的任务
分享
if exists(select 1 from sysobjects where name='GetList')
drop proc GetList;
create proc GetList(@TableName VarChar(50), --表名
@Value VarChar(1000)) --要查找的值
as
declare
@columns varchar(50),
@Temp varchar(8000),
@i int,
@strCount int,
@values varchar(8000),
@SQLString nvarchar(500),
@ParmDefinition nvarchar(500),
@strlen int, --字符串的长度
@postion int,
@markStr varchar(100),
@sublen int, --子串的长度
@TEMPid int;
--使用游标保存表中符合条件的列
Declare getColumns CURSOR FOR
SELECT Sys.Columns.name
FROM Sys.tables,Sys.Types,Sys.Columns
WHERE sys.tables.object_Id = Sys.columns.object_Id
AND Sys.Types.system_type_id =
Sys.columns.system_type_id
AND Sys.Types.Name like '%char'
AND Sys.tables.Name = substring
(@TableName,charIndex('.',@TableName)+1,(LEN(@TableName)-charIndex
('.',@TableName)))
BEGIN
SELECT @Temp = '',@strCount = 0,@strlen=LEN(@Value),@markStr=' ';
if(RIGHT(@Value,1)<>@markStr )
begin
set @Value = @Value + @markStr; --如果字符串末尾没有所需的
标记时,就添加标记
end;
--打开游标得到列
OPEN getColumns;
Fetch next from getColumns into @columns;
while @@FETCH_STATUS = 0
begin
SELECT @postion=1,@TEMPid=0;
WHILE((@postion<=@strlen) and (@postion !=0))
BEGIN
IF(CHARINDEX(@markStr,@Value,@postion)!=0)
BEGIN
SET @sublen=CHARINDEX
(@markStr,@Value,@postion)-@postion;
END;
ELSE
BEGIN
SET @sublen=@strlen-@postion+1;
END;
IF(@postion<=@strlen)
BEGIN
SET @TEMPid=@TEMPid+1;
if(@Temp = '')
set @Temp = @columns + ' Like '+
'''%' +SUBSTRING(@Value,@postion,@sublen)+'%''';
else
begin
set @Temp = @Temp + ' OR ' +
@columns + ' Like ' + '''%' + SUBSTRING(@Value,@postion,@sublen) + '%''';
end;
IF(CHARINDEX(@markStr,@Value,@postion)!=0)
BEGIN
SET @postion=CHARINDEX
(@markStr,@Value,@postion)+1;
END;
ELSE
BEGIN
SET @postion=@postion+1;
END;
END;
END;
Fetch next from getColumns into @columns;
end;
close getColumns;
DEALLOCATE getColumns;
print(@Temp);
if(@Temp != '')
exec('Select * from ' + @TableName + ' Where ' + @Temp);
else
print('The table does not contain the listed characters');
END;
OPEN getColumns;
Fetch next from getColumns into columns;
while done = 0
do
SELECT postion=1,TEMPid=0;
WHILE((postion<=strlen) and (postion !=0))
IF(INSTR(markStr,Value,postion)!=0)
then
SET sublen=instr(markStr,Value,postion)-postion;
ELSE
BEGIN
SET sublen=strlen-postion+1;
END;
IF(postion<=strlen)
BEGIN
SET TEMPid=TEMPid+1;
if(Temp = '')
set Temp = columns + ' Like '+ '''%' +SUBSTRING(Value,postion,sublen)+'%''';
else
begin
set Temp = Temp + ' OR ' + columns + ' Like ' + '''%' + SUBSTRING(Value,postion,sublen) + '%''';
end;
IF(instr(markStr,Value,postion)!=0)
BEGIN
SET postion=instr(markStr,Value,postion)+1;
END;
ELSE
BEGIN
SET postion=postion+1;
END;
END;
END;
Fetch next from getColumns into columns;
end;
close getColumns;
OPEN getColumns;
Fetch next from getColumns into columns;
while @@FETCH_STATUS = 0
begin
SELECT postion=1,TEMPid=0;
WHILE((postion<=strlen) and (postion !=0))
BEGIN
IF(CHARINDEX(markStr,Value,postion)!=0)
BEGIN
SET sublen=CHARINDEX(markStr,Value,postion)-postion;
END;
ELSE
BEGIN
SET sublen=strlen-postion+1;
END;
IF(postion<=strlen)
BEGIN
SET TEMPid=TEMPid+1;
if(Temp = '')
set Temp = columns + ' Like '+ '''%' +SUBSTRING(Value,postion,sublen)+'%''';
else
begin
set Temp = Temp + ' OR ' + columns + ' Like ' + '''%' + SUBSTRING(Value,postion,sublen) + '%''';
end;
IF(CHARINDEX(markStr,Value,postion)!=0)
BEGIN
SET postion=CHARINDEX(markStr,Value,postion)+1;
END;
ELSE
BEGIN
SET postion=postion+1;
END;
END;
END;
Fetch next from getColumns into columns;
end;
close getColumns;
我将它放在了DELIMITER中,请问sql server中的@@FETCH_STATUS = 0在mysql是什么,还有代码的第一个begin报错,不知为何DELIMITER $$
DROP PROCEDURE IF EXISTS GetList $$
create procedure GetList(in TableName VarChar(50),
out Value VarChar(1000))
BEGIN
DECLARE `columns` VARCHAR(50) DEFAULT NULL;
DECLARE Temp VARCHAR(80) DEFAULT NULL;
DECLARE i INT DEFAULT NULL;
DECLARE strCount INT DEFAULT NULL;
DECLARE `VALUES` VARCHAR(8000) DEFAULT NULL;
DECLARE SQLString VARCHAR(500) DEFAULT NULL;
DECLARE ParmDefinition VARCHAR(500) DEFAULT NULL;
DECLARE strlen INT DEFAULT NULL;
DECLARE postion INT DEFAULT NULL;
DECLARE markStr VARCHAR(100) DEFAULT NULL;
DECLARE sublen INT DEFAULT NULL;
DECLARE TEMPid INT DEFAULT NULL;
END$$
DELIMITER ;
Declare getColumns CURSOR FOR
SELECT Sys.Columns.name
FROM Sys.tables,Sys.Types,Sys.Columns
WHERE sys.tables.object_Id = Sys.columns.object_Id
AND Sys.Types.system_type_id = Sys.columns.system_type_id
AND Sys.Types.Name like '%char'
AND Sys.tables.Name = substring(TableName,charIndex('.',TableName)+1,(LEN(TableName)-charIndex('.',TableName)))
BEGIN
SELECT Temp = '',strCount = 0,strlen=LEN(Value),markStr=' ';
if(RIGHT(@Value,1)<>@markStr )
then
set @Value = @Value + @markStr; --如果字符串末尾没有所需的标记时,就添加标记
end if;
显示declare有错误和if有错误,我看mysql中if的用法就是if-then-endif啊,还有declare为什么又会报错呢declare columns varchar(50) default null;
declare Temp varchar(8000) default null;
declare i int default null;
declare strCount int default null;
declare values varchar(8000) default null;
declare SQLString nvarchar(500) default null;
declare ParmDefinition nvarchar(500) default null;
declare strlen int default null;
declare postion int default null;
declare markStr varchar(100) default null;
declare sublen int default null;
declare TEMPid int default null;
按照网上的一些实例改的,不过报错说不需要后面的null,去掉default null又显示要加分号,可是我有分号啊,什么情况?