MYSQL高手进,结果集给变量赋值

[isoftk]

背景:
最近要把一套SQL SERVER的系统移植到MYSQL上,要求尽量在数据库里做改动,不要改动页面程序.

问题:
一个SQL SERVER里常见的语句:
select @icnt=count(sid) from #Temp_sys_User_Del_sid where uid='delgid'
把搜索的记数结果集赋值给一个整型变量.]
其中:@icnt是整型变量, #Temp_sys_User_Del_sid是临时表

而改为MYSQL时:
SET icnt=count(sid) from Temp_sys_User_Del_sid where uid='delgid';
SET icnt=select count(sid) from Temp_sys_User_Del_sid where uid='delgid';
这两种语句都不能通过.

请问如何把表中某个列的count()值赋给一个整型变量.



附上整个存储过程,如果有其他方法能实现同样效果也可以.
===================================================================
SQL SERVER SP:

CREATE PROC sp_UserManager_Delete_SID
@SID As Nvarchar(4000),
@GID As Nvarchar(4000),
@Operator_Userid varchar(20),
@sSql Nvarchar(4000) = '',
@sSql2 Nvarchar(4000) = '',--备用执行SQL2
@sSql3 Nvarchar(4000) = '',--备用执行SQL3
@sTemp1 varchar(50) = '',
@sTemp2 varchar(50) = '',
@sTemp3 varchar(50) = '',
@sTemp4 varchar(50) = '',
@sTemp5 varchar(50) = '',
@sTemp6 varchar(500) = ''
AS

declare @i int
DECLARE @Sql Nvarchar(4000)
declare @SourceSql Nvarchar(4000)

declare @IntReturn int--返回值

--判断--选择层内如果没有将用户/组全部删除,剩余用户中不存在管理员时，删除操作停止
if @GID is not null and @GID<>''
begin
begin tran--开始事务

set @SourceSql=@GID --'11,22,33,'
set @SID=substring(@SID,1,len(@SID)-1)--去掉字符串最后面的，号
set @gID=substring(@gID,1,len(@gID)-1)--去掉字符串最后面的，号
--set @Gid='1330,1409,1413,1415'

--创建临时表，存放用户的SID,UID,OperatorID
create table #Temp_sys_User_Del_sid (SID Int,uid varchar(20))
create table #Temp_sys_User_Del_gid (sID Int,uid varchar(20))


select @Sql='insert into #Temp_sys_User_Del_sid (sid,uid) (select sid,uid from RecordQuery.dbo.T_sys_user a where a.sid in ('+ @SID +'))'
--print @Sql
EXECUTE sp_executesql @Sql output
select @Sql='insert into #Temp_sys_User_Del_gid (sid,uid) (select sid,uid from RecordQuery.dbo.T_sys_user a where a.sid in ('+ @gID +'))'
--print @Sql
EXECUTE sp_executesql @Sql output

--查询选择组下存在用户、但组内不存在管理员记录
declare @icnt int
--IF EXISTS(SELECT SID FROM T_sys_user WHERE 1=1)
--begin
-- set @IntReturn=-88--剩余用户中不存在管理员时
-- goto rol--查询文件出错
--end


insert into #Temp_sys_User_Del_sid (sid,uid)(
select T.gid,'delgid' from T_sys_User U,(
select a.gid,isnull(a.c1,0) as c1,isnull(b.c2,0) as c2 from (
SELECT gid,count(sid) c1 FROM T_sys_User where 1=1 and gid in (select sid from #Temp_sys_User_Del_gid) and sid not in (select sid from #Temp_sys_User_Del_sid) group by gid ) a left join (
SELECT gid,count(sid) c2 FROM T_sys_User where 1=1 and gid in (select sid from #Temp_sys_User_Del_gid) and sid not in (select sid from #Temp_sys_User_Del_sid) and groupadmin='T'
group by gid) b on a.gid=b.gid
) T where U.sid=T.gid and t.c1>0 and c2=0)

select @icnt=count(sid) from #Temp_sys_User_Del_sid where uid='delgid'
if @icnt>0
begin
set @IntReturn=-3--剩余用户中不存在管理员时
goto rol--查询文件出错
end

--begin
-- set @IntReturn=-4
-- goto rol--删除用户出错
--end

set @i=charindex(',',@SourceSql)
while @i>=1
begin
print substring(@SourceSql,0,@i)
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(',',@SourceSql)
end

--为统一管理删除用户，需要把主系统用户表中的此用户权限取消
if (@sTemp6='wilcom')
begin
--select left(qx,1)+'F'+right(qx,len(qx)-2) from T_wilcom_sys_User
select @Sql='update wilcomDB.dbo.T_wilcom_sys_User set qx=left(qx,3)+''F''+right(qx,len(qx)-4) where Uid in (select Uid from T_sys_User where sid in ('+ @SID +'))'
EXECUTE sp_executesql @Sql output
end

--删除用户
--DECLARE @Sql Nvarchar(4000)
--不删除admin,不删除用户组
select @Sql='DELETE FROM RecordQuery.dbo.T_sys_User WHERE uid !=''admin'' and gtype=''F'' and SID IN (' + @SID + ')'
EXECUTE sp_executesql @Sql



if @@error <> 0
begin
set @IntReturn=-5
goto rol--删除用户出错
end

--删除用户组--条件是组下无成员
delete from RecordQuery.dbo.T_sys_User where sid in (
select D.sid from (
select t.sid,count(a.sid) as c1 from (
SELECT sid FROM T_sys_User where 1=1 and uid !='admin' and sid in (select sid from #Temp_sys_User_Del_gid) and gtype='T' group by sid
) T left join T_sys_User a on a.gid=t.sid group by T.sid
) D where isnull(D.c1,0)=0)

if @@error <> 0
begin
set @IntReturn=-6
goto rol--删除用户出错
end

Drop Table #Temp_sys_User_Del_sid
Drop Table #Temp_sys_User_Del_gid

Commit tran--提交事务

--写日志记录
Declare @Message varchar(1000)
set @Message = '删除用户,'+'用户系统ID-'+cast(@SID as varchar(4000))
--exec sp_User_Operator_Log @Operator_Userid,'',@Message

end

if @@error <> 0
--RETURN -1--失败
Return -2
else
--RETURN 1--成功
--print @sidentity
Return 2

rol:
Rollback--回滚事务
Return @IntReturn
GO

====================================================================

MYSQL SP:

DELIMITER $$

DROP PROCEDURE IF EXISTS `recordquery`.`sp_UserManager_Delete_SID`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UserManager_Delete_SID`(SID varchar(40),
GID varchar(40),
Operator_Userid varchar(20),
sSql text,
sSql2 text,
sSql3 text,
sTemp1 varchar(50),
sTemp2 varchar(50),
sTemp3 varchar(50),
sTemp4 varchar(50),
sTemp5 varchar(50),
sTemp6 text,
out ReturnValue int)
BEGIN
DECLARE SourceSql text;
DECLARE tempSql text;
DECLARE icnt int;

IF GID!='' THEN
START TRANSACTION;

SET SourceSql=GID;
SET SID=substring(SID,1,length(SID)-1);
SET GID=substring(GID,1,length(GID)-1);

DROP TABLE IF EXISTS `recordquery`.`Temp_sys_User_Del_sid`;
CREATE TABLE Temp_sys_User_Del_sid (SID Int,uid varchar(20));
DROP TABLE IF EXISTS `recordquery`.`Temp_sys_User_Del_gid`;
CREATE TABLE Temp_sys_User_Del_gid (sID Int,uid varchar(20));

SET tempSql=concat('insert into Temp_sys_User_Del_sid (sid,uid) (select sid,uid from RecordQuery.T_sys_user a where a.sid in (', SID, '))');

SET @RUNSQL = tempSql;
PREPARE A FROM @RUNSQL;
EXECUTE A;
DEALLOCATE PREPARE A;

SET tempSql=concat('insert into Temp_sys_User_Del_gid (sid,uid) (select sid,uid from RecordQuery.dbo.T_sys_user a where a.sid in (', gID, '))');

SET @RUNSQL = tempSql;
PREPARE A FROM @RUNSQL;
EXECUTE A;
DEALLOCATE PREPARE A;

CREATE TEMPORARY TABLE recordquery.Tempa(gid int,c1 int);
CREATE TEMPORARY TABLE recordquery.Tempb(gid int,c2 int);
CREATE TEMPORARY TABLE recordquery.Tempc(gid int,c1 int, c2 int);

INSERT INTO Tempa(gid,c1) (SELECT gid,count(sid) c1 FROM T_sys_User where 1=1 and gid in (select sid from Temp_sys_User_Del_gid) and sid not in (select sid from Temp_sys_User_Del_sid) group by gid);
INSERT INTO Tempb(gid,c2) (SELECT gid,count(sid) c2 FROM T_sys_User where 1=1 and gid in (select sid from Temp_sys_User_Del_gid) and sid not in (select sid from Temp_sys_User_Del_sid) group by gid);
INSERT INTO Tempc(gid,c1,c2)(select a.gid,a.c1 as c1,b.c2 as c2 from (Tempa a left join Tempb b on (a.gid=b.gid)));
INSERT INTO Temp_sys_User_Del_sid (sid,uid)(select T.gid,'delgid' from T_sys_User U,ccc T where U.sid=T.gid and t.c1>0 and c2=0);

DROP TABLE recordquery.Tempa;
DROP TABLE recordquery.Tempb;
DROP TABLE recordquery.Tempc;

/*SET icnt=select count(sid) from Temp_sys_User_Del_sid where uid='delgid';*/


DROP TABLE recordquery.Temp_sys_User_Del_sid;
DROP TABLE recordquery.Temp_sys_User_Del_gid;
COMMIT;
END IF;
END$$

DELIMITER ;

[isoftk]

顶,呼唤高手出现...

[懒得去死]

可真长那。

[uranas]

看来你要改不少地方。

http://dev.mysql.com/doc/refman/5.1/zh/index.html
↑MySQL 5.1参考手册，自己改改看吧

[whalefish2001]

把 select @aa=数据库字段 from ...where...
变更为 select 数据库字段 into @aa from ...where...

[whalefish2001]

看来要改的地方真是不少。

建议楼主查查手册，自己改吧
对了，关于动态执行存储过程的地方
看看 repeat 和 execute 函数，
游标的使用
用mysql的获取错误处理机制实现
或者是用 count（*）把记录结果集的总数目，然后用这个循环游标即可（用此方式可实现游标的嵌套）。
其余地方应该没有什么难度。

总体上，修改起来应该没有什么难度。