mysql存储过程存储一列_mysql 存储过程案列一个。

-- 设置分隔符

DELIMITER //

/*初始化*/

DROP PROCEDURE IF EXISTS useCursor //

/*建立 存储过程 create */

CREATE PROCEDURE useCursor(input_planGuid BIGINT )

BEGIN

-- 声明游标使用的变量----------

DECLARE nowID BIGINT;-- 本行自增id

DECLARE nowGuid BIGINT;-- 本行的guid,

DECLARE parentGuid BIGINT;-- 本行父级guid

DECLARE newIndex BIGINT;-- 要设置的本行的index

DECLARE preIndex BIGINT DEFAULT -1;-- 上一行设置的index.

DECLARE preParentGuid BIGINT DEFAULT -1;-- 上一行的父级guid

-- 声明游标,从临时表中读取数据.循环更新临时表-----------------------------------------------

DECLARE cur1 CURSOR FOR SELECT id,guid,PARENT_GUID FROM tb_tmp ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET nowID = NULL;

-- 声明临时表,并获取数据--------------------------------------------------------------------

-- 定义临时表 tb_tmp(自增主键，标题，拓展信息，原表主键ID)

DROP TEMPORARY TABLE IF EXISTS tb_tmp;

CREATE TEMPORARY TABLE tb_tmp (

id INT AUTO_INCREMENT PRIMARY KEY,

funcsOrder BIGINT,

ITEM_NO VARCHAR(200),

GUID BIGINT,

NAME VARCHAR(100) NULL,

ITEM_NO_INDEX BIGINT NULL,

PLAN_GUID BIGINT NULL,

PARENT_GUID BIGINT

);

#所有数据

INSERT INTO tb_tmp( funcsOrder,ITEM_NO,GUID,NAME,ITEM_NO_INDEX,PLAN_GUID,PARENT_GUID)

SELECT CASE WHEN (

CASE WHEN INSTR(item_No, '.')>0

THEN CAST( SUBSTRING(item_No,(LENGTH(item_No)-INSTR(REVERSE(item_No), '.')+2)) AS SIGNED)

ELSE CAST(item_No AS SIGNED)

END )=0

THEN 2147483647

ELSE

CASE WHEN INSTR(item_No, '.')>0

THEN CAST( SUBSTRING(item_No,(LENGTH(item_No)-INSTR(REVERSE(item_No), '.')+2)) AS SIGNED)

ELSE CAST(item_No AS SIGNED)

END

END AS funcsOrder,item_no,guid, NAME,ITEM_NO_INDEX ,PLAN_GUID,PARENT_GUID

FROM sn93_elggtw_goal

WHERE PLAN_GUID=input_planGuid

ORDER BY PARENT_GUID,

(CASE WHEN (

CASE WHEN INSTR(item_No, '.')>0

THEN CAST( SUBSTRING(item_No,(LENGTH(item_No)-INSTR(REVERSE(item_No), '.')+2)) AS SIGNED)

ELSE CAST(item_No AS SIGNED)

END )=0

THEN 2147483647

ELSE

CASE WHEN INSTR(item_No, '.')>0

THEN CAST( SUBSTRING(item_No,(LENGTH(item_No)-INSTR(REVERSE(item_No), '.')+2)) AS SIGNED)

ELSE CAST(item_No AS SIGNED)

END

END ),item_no,guid;

-- 临时表完毕-------------------------------------------------------------------------------

-- 开游标

OPEN cur1;

/*游标向下走一步*/

FETCH cur1 INTO nowID,nowGuid,parentGuid;

/* 循环体 */

WHILE ( nowID IS NOT NULL)

DO

-- 如果nowID=1,则表示第一行.设置item_no_index=1即可

IF nowID=1 THEN

SET newIndex=1;

ELSE

-- 如果不是第一行:

-- 再根据本行与上一行的数据进行判断:

-- A:本行父级id<>上一行的父级id,则本行为第一个子.设置index=1;

IF parentGuid!=preParentGuid THEN

SET newIndex=1;

ELSE

-- B:本行父级id==上一行的父级id,则本行为上一行的兄弟,设置index=上行index+1;

SET newIndex= preIndex + 1;

END IF;

END IF;

-- 更新index

UPDATE tb_tmp SET ITEM_NO_INDEX = newIndex WHERE id=nowID;

-- 更新上一行变量.

SET preIndex=newIndex;

SET preParentGuid=parentGuid;

/*游标向下走一步*/

FETCH cur1 INTO nowID,nowGuid,parentGuid;

END WHILE;

CLOSE cur1;

-- 游标处理临时表完毕---------------------------------------------------------------------

-- 根据临时表数据,更新真实表数据----------------------------------------------------------

update sn93_elggtw_goal inner join (sn93_elggtw_goal T1 INNER JOIN tb_tmp T2 ON T1.guid=T2.guid and T1.guid=T2.guid ) on T1.PLAN_GUID=input_planGuid

SET T1.ITEM_NO_INDEX=T2.ITEM_NO_INDEX ;

-- 删除临时表

DROP TEMPORARY TABLE IF EXISTS tb_tmp;

-- over ----------------------------------------------------------------------------------

END//

DELIMITER ;