mysql 游标大小,mysql 游标 排行

CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `paimin`()

BEGIN

DECLARE

zan1 INT DEFAULT 1;

DECLARE

cai1 INT DEFAULT 1;

DECLARE

ping1 INT DEFAULT 1;

DECLARE

fen1 INT DEFAULT 1;

DECLARE

shan1 INT DEFAULT 1;

DECLARE

bzan INT DEFAULT 1;

DECLARE

bcai INT DEFAULT 1;

DECLARE

bping INT DEFAULT 1;

DECLARE

bfen INT DEFAULT 1;

DECLARE

bshan INT DEFAULT 1;

DECLARE

Done INT DEFAULT 0;

DECLARE

CurrentPaiMing INT DEFAULT 0;

DECLARE

ZUBOID VARCHAR (6);

DECLARE

ZUBOGOLD INT DEFAULT 0;

DECLARE

ud_row VARCHAR (6);

DECLARE

paimin_row INT DEFAULT 0;

DECLARE

rs2 CURSOR FOR SELECT

ud,

zan,

cai,

ping,

fen,

shan,

(

zan * 1 - cai * 1 + ping * 1 + fen * 1 + shan * 1

) paimin

FROM

lfsj_sort_user_info;

/* 声明游标 */

DECLARE

rs CURSOR FOR SELECT

ZID,

GOLD

FROM

(

SELECT

ZID,

SUM(GOLD) GOLD

FROM

T_ANCHOR_GAINS

GROUP BY

ZID

) A

ORDER BY

GOLD DESC;

/* 异常处理 */

DECLARE

CONTINUE HANDLER FOR SQLSTATE '02000'

SET Done = 1;

/* 打开游标 */

OPEN rs;

/* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */

FETCH NEXT

FROM

rs INTO ZUBOID,

ZUBOGOLD;

/* 遍历数据表 */

REPEAT

IF NOT Done THEN

SET CurrentPaiMing = CurrentPaiMing + 1;

UPDATE t_user_info

SET RANKING = CurrentPaiMing

WHERE

ID = ZUBOID;

UPDATE lfsj_sort_user_info

SET shan = ZUBOGOLD

WHERE

ud = ZUBOID;

END

IF;

FETCH NEXT

FROM

rs INTO ZUBOID,

ZUBOGOLD;

UNTIL Done

END

REPEAT

;

/* 关闭游标 */

CLOSE rs;

/* 声明游标 */

/* 异常处理 */

-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done2 = 1;

UPDATE t_user_info

SET RANKING = 0;

SET Done = 0;

/* 打开游标 */

OPEN rs2;

/* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */

FETCH NEXT

FROM

rs2 INTO ud_row,

bzan,

bcai,

bping,

bfen,

bshan,

paimin_row;

/* 遍历数据表 */

REPEAT

IF NOT Done THEN

UPDATE t_user_info a

SET a.RANKING = paimin_row,

a.SNUM = concat(

'{"cai":"',

bcai,

'"cai":"',

bcai,

'","fen":',

bfen,

'","ping":',

bping,

'","shan":',

bshan,

'"}'

)

WHERE

a.ID = ud_row;

INSERT INTO `kwc`.`cc` (`id`) VALUES ('aa' );

INSERT INTO `kwc`.`cc` (`id`) VALUES (concat(

'{"cai":"',

bcai,

'"cai":"',

bcai,

'","fen":',

bfen,

'","ping":',

bping,

'","shan":',

bshan,

'"}'

) );

END

IF;

FETCH NEXT

FROM

rs2 INTO ud_row,

bzan,

bcai,

bping,

bfen,

bshan,

paimin_row;

UNTIL Done

END

REPEAT

;

/* 关闭游标 */

CLOSE rs2;

END