1. delimiter //
2. Create PROCEDURE dnt_deletetopicbytidlist()
3. begin
4. declare tidlist VARCHAR(2000);
5. declare posttablename VARCHAR(20);
6. DECLARE postcount int;
7. DECLARE topiccount int;
8. DECLARE todaycount int;
9. DECLARE sqlstr nvarchar(4000);
10. DECLARE fid varchar(2000);
11. DECLARE posterid varchar(200);
12. DECLARE tempFid int;
13. DECLARE tempPosterid int;
14. DECLARE tempLayer int;
15. DECLARE temppostdatetime datetime;
16. DECLARE tempfidlist VARCHAR(1000); 17. SET fid = '';
18. SET posterid = '';
19. SET postcount=0;
20. SET topiccount=0;
21. SET todaycount=0;
22. SET tempfidlist = '';
23. IF tidlist<>'' then
24. SET sqlstr = 'DECLARE cu_dnt_posts CURSOR FOR SELECT fid,posterid,layer,postdatetime FROM ' + posttablename + ' WHERE tid IN (' + tidlist + ')';
25.call sqlstr;
26.OPEN cu_dnt_posts;
27.FETCH NEXT FROM cu_dnt_posts into tempFid,tempPosterid,tempLayer,temppostdatetime;
28.WHILE @@FETCH_STATUS = 0
SET postcount = postcount + 1;
IF tempLayer = 0 then
SET topiccount = topiccount + 1;
END IF;
IF DATEDIFF(d,temppostdatetime,GETDATE()) = 0 then
SET todaycount = todaycount + 1;
END IF;
IF CHARINDEX(',' + LTRIM(STR(tempFid)) + ',',fid + ',') = 0 then
--SET @fid = @fid + ',' + LTRIM(STR(@tempFid))
SELECT tempfidlist = ISNULL(parentidlist,'') FROM dnt_forums WHERE fid = tempFid;
IF RTRIM(tempfidlist)<>'' then
SET fid = RTRIM(fid) + ',' + RTRIM(tempfidlist) + ',' + CAST(tempFid AS VARCHAR(10));
ELSE
SET fid =RTRIM(fid) + ',' + CAST(tempFid AS VARCHAR(10));
END IF;
END IF;
UPDATE dnt_users SET posts = posts - 1 WHERE uid = tempPosterid;
FETCH NEXT FROM cu_dnt_posts into tempFid,tempPosterid,tempLayer,temppostdatetime;
CLOSE cu_dnt_posts;
DEALLOCATE cu_dnt_posts;
IF LEN(fid)>0 then
SET fid = SUBSTRING(fid,2,LEN(fid)-1);
UPDATE dnt_statistics SET totaltopic=totaltopic - topiccount, totalpost=totalpost - postcount;
SET sqlstr = 'UPDATE dnt_forums SET posts=posts - ' + cast(postcount AS VARCHAR(10)) +
', topics=topics - ' + cast(topiccount AS VARCHAR(10)) +
', todayposts = todayposts - ' + cast(todaycount AS VARCHAR(10)) +
' WHERE [fid] IN (' + fid + ')';
call sqlstr;
SET sqlstr = 'DELETE FROM [dnt_favorites] WHERE [tid] IN (' + tidlist + ')';
call sqlstr;
SET sqlstr = 'DELETE FROM [dnt_polls] WHERE [tid] IN (' + tidlist + ')';
call sqlstr; SET sqlstr = 'DELETE FROM [' + posttablename + '] WHERE [tid] IN (' + tidlist + ')';
call sqlstr;
END IF;
SET sqlstr = 'DELETE FROM [dnt_topics] WHERE [closed] IN (' + tidlist + ') OR [tid] IN (' + tidlist + ')';
call sqlstr;
END IF;
end;//24行报错,报错信息为 Undefined CURSOR: cu_dnt_posts ,求高手帮看下24行-28行的代码,在mysql中怎么写
2. Create PROCEDURE dnt_deletetopicbytidlist()
3. begin
4. declare tidlist VARCHAR(2000);
5. declare posttablename VARCHAR(20);
6. DECLARE postcount int;
7. DECLARE topiccount int;
8. DECLARE todaycount int;
9. DECLARE sqlstr nvarchar(4000);
10. DECLARE fid varchar(2000);
11. DECLARE posterid varchar(200);
12. DECLARE tempFid int;
13. DECLARE tempPosterid int;
14. DECLARE tempLayer int;
15. DECLARE temppostdatetime datetime;
16. DECLARE tempfidlist VARCHAR(1000); 17. SET fid = '';
18. SET posterid = '';
19. SET postcount=0;
20. SET topiccount=0;
21. SET todaycount=0;
22. SET tempfidlist = '';
23. IF tidlist<>'' then
24. SET sqlstr = 'DECLARE cu_dnt_posts CURSOR FOR SELECT fid,posterid,layer,postdatetime FROM ' + posttablename + ' WHERE tid IN (' + tidlist + ')';
25.call sqlstr;
26.OPEN cu_dnt_posts;
27.FETCH NEXT FROM cu_dnt_posts into tempFid,tempPosterid,tempLayer,temppostdatetime;
28.WHILE @@FETCH_STATUS = 0
SET postcount = postcount + 1;
IF tempLayer = 0 then
SET topiccount = topiccount + 1;
END IF;
IF DATEDIFF(d,temppostdatetime,GETDATE()) = 0 then
SET todaycount = todaycount + 1;
END IF;
IF CHARINDEX(',' + LTRIM(STR(tempFid)) + ',',fid + ',') = 0 then
--SET @fid = @fid + ',' + LTRIM(STR(@tempFid))
SELECT tempfidlist = ISNULL(parentidlist,'') FROM dnt_forums WHERE fid = tempFid;
IF RTRIM(tempfidlist)<>'' then
SET fid = RTRIM(fid) + ',' + RTRIM(tempfidlist) + ',' + CAST(tempFid AS VARCHAR(10));
ELSE
SET fid =RTRIM(fid) + ',' + CAST(tempFid AS VARCHAR(10));
END IF;
END IF;
UPDATE dnt_users SET posts = posts - 1 WHERE uid = tempPosterid;
FETCH NEXT FROM cu_dnt_posts into tempFid,tempPosterid,tempLayer,temppostdatetime;
CLOSE cu_dnt_posts;
DEALLOCATE cu_dnt_posts;
IF LEN(fid)>0 then
SET fid = SUBSTRING(fid,2,LEN(fid)-1);
UPDATE dnt_statistics SET totaltopic=totaltopic - topiccount, totalpost=totalpost - postcount;
SET sqlstr = 'UPDATE dnt_forums SET posts=posts - ' + cast(postcount AS VARCHAR(10)) +
', topics=topics - ' + cast(topiccount AS VARCHAR(10)) +
', todayposts = todayposts - ' + cast(todaycount AS VARCHAR(10)) +
' WHERE [fid] IN (' + fid + ')';
call sqlstr;
SET sqlstr = 'DELETE FROM [dnt_favorites] WHERE [tid] IN (' + tidlist + ')';
call sqlstr;
SET sqlstr = 'DELETE FROM [dnt_polls] WHERE [tid] IN (' + tidlist + ')';
call sqlstr; SET sqlstr = 'DELETE FROM [' + posttablename + '] WHERE [tid] IN (' + tidlist + ')';
call sqlstr;
END IF;
SET sqlstr = 'DELETE FROM [dnt_topics] WHERE [closed] IN (' + tidlist + ') OR [tid] IN (' + tidlist + ')';
call sqlstr;
END IF;
end;//24行报错,报错信息为 Undefined CURSOR: cu_dnt_posts ,求高手帮看下24行-28行的代码,在mysql中怎么写
+ ->CONCATDECLARE done INT DEFAULT 0;
DECLARE cu_dnt_posts CURSOR FOR select * from newtt
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set @asql=concat('create table newtt as SELECT fid,posterid,layer,postdatetime FROM ',posttablename,' WHERE tid IN (', tidlist , ')';
prepare dd from @asql;
execute dd;
OPEN cu_dnt_posts;
fETCH cu_dnt_posts INTO v_a;
WHILE done=0 DO
...
END WHILE;
2. MYSQL中不需要用CONVERT,直接加就行了。set F_NextClassPath= concat (F_Path,',',YX_ID);