DROP PROCEDURE IF EXISTS P_UPDATE_RES_LOCATION;
CREATE PROCEDURE P_UPDATE_RES_LOCATION(IN STR_RES_ID text)
BEGIN
/** 声明参数*/
DECLARE res_id DECIMAL(22,0) DEFAULT 0;-- 初始化一个资源ID变量,默认值为0,用来存放从游标中提取的数据
DECLARE done INTEGER DEFAULT 0;-- 声明一个标志done,用来判断游标是否遍历完成
DECLARE DED_ID DECIMAL(22,0) DEFAULT 0;-- 被依赖资源ID
DECLARE ROOM_ID DECIMAL(22,0) DEFAULT 0;-- 机房ID
DECLARE NEW_ROOM_NAME VARCHAR(200) DEFAULT ''; -- 机房名称
DECLARE CITY_ID DECIMAL(22,0) DEFAULT 0;-- 城市ID
DECLARE NEW_CITY_NAME VARCHAR(200) DEFAULT ''; -- 城市名称
DECLARE GROUP_ID DECIMAL(22,0) DEFAULT 0;-- 业务系统ID
DECLARE NEW_GROUP_NAME VARCHAR(200) DEFAULT ''; -- 业务系统名称
DECLARE NEW_BRAND_NAME VARCHAR(200) DEFAULT '';-- 品牌名称
DECLARE NEW_MACHINE_MODEL VARCHAR(200) DEFAULT '';-- 机型
DECLARE NEW_BLADE_CHASSIS_NAME VARCHAR(200) DEFAULT '';-- 刀片机箱名称
DECLARE BLADE_CHASSIS_ID DECIMAL(22,0) DEFAULT 0;-- 刀片机箱ID
DECLARE NEW_RACK_NAME VARCHAR(200) DEFAULT '';-- 机柜名称
DECLARE RACK_ID DECIMAL(22,0) DEFAULT 0;-- 机柜ID
DECLARE CATE_ID DECIMAL(22,0) DEFAULT 0;-- 大类ID
DECLARE ORIGINAL_ROOM_NAME VARCHAR(200) default '';
DECLARE ORIGINAL_CITY_NAME VARCHAR(200) default '';
DECLARE ORIGINAL_GROUP_NAME VARCHAR(200) default '';
DECLARE ORIGINAL_BRAND_NAME VARCHAR(200) default '';
DECLARE ORIGINAL_MACHINE_MODEL VARCHAR(200) default '';
DECLARE ORIGINAL_BLADE_CHASSIS_NAME VARCHAR(200) default '';
DECLARE ORIGINAL_RACK_NAME VARCHAR(200) DEFAULT '';
DECLARE DISP_NAME VARCHAR(200) DEFAULT '';-- 资源显示名称
DECLARE ORIGINAL_SERVER_HEIGHT VARCHAR(4) DEFAULT '';-- 节点高度
DECLARE ORIGINAL_SERVER_LOCATION VARCHAR(4) DEFAULT '';-- 节点位置
DECLARE ORIGINAL_BMCIP VARCHAR(64) DEFAULT '';-- IPMI地址
DECLARE ORIGINAL_IPMI_USER VARCHAR(64) DEFAULT '';-- IPMI用户
DECLARE ORIGINAL_IPMI_PWD VARCHAR(64) DEFAULT '';-- IPMI密码
DECLARE NEW_SERVER_HEIGHT VARCHAR(4) DEFAULT '';-- 节点高度
DECLARE NEW_SERVER_LOCATION VARCHAR(4) DEFAULT '';-- 节点位置
DECLARE NEW_BMCIP VARCHAR(64) DEFAULT '';-- IPMI地址
DECLARE NEW_IPMI_USER VARCHAR(64) DEFAULT '';-- IPMI用户
DECLARE NEW_IPMI_PWD VARCHAR(64) DEFAULT '';-- IPMI密码
DECLARE NEW_BLADE_CHASSIS_CAPACITY VARCHAR(4) DEFAULT '';-- 刀片机箱容量
DECLARE NEW_BLADE_CHASSIS_ARRANGE_MODE VARCHAR(4) DEFAULT '';-- 刀片机箱排列模式
DECLARE NEW_BLADE_CHASSIS_LOCATION VARCHAR(4) DEFAULT '';-- 刀片机箱位置
DECLARE NEW_BLADE_CHASSIS_HEIGHT VARCHAR(4) DEFAULT '';-- 刀片机箱高度
DECLARE NEW_MANAGE_IP VARCHAR(64) DEFAULT '';-- 业务IP
DECLARE ORIGINAL_BLADE_CHASSIS_CAPACITY VARCHAR(4) DEFAULT '';-- 刀片机箱容量
DECLARE ORIGINAL_BLADE_CHASSIS_ARRANGE_MODE VARCHAR(4) DEFAULT '';-- 刀片机箱排列模式
DECLARE ORIGINAL_BLADE_CHASSIS_LOCATION VARCHAR(4) DEFAULT '';-- 刀片机箱位置
DECLARE ORIGINAL_BLADE_CHASSIS_HEIGHT VARCHAR(4) DEFAULT '';-- 刀片机箱高度
DECLARE ORIGINAL_MANAGE_IP VARCHAR(64) DEFAULT '';-- 原始业务IP
-- 定义游标对应的SQL
DECLARE rs_cursor CURSOR FOR SELECT ID,ROOM_NAME,CITY_NAME,GROUP_NAME,BRAND_NAME,MACHINE_MODEL,BLADE_CHASSIS_NAME,RACK_NAME
,SERVER_HEIGHT,BMCIP,IPMI_USER,IPMI_PWD,SERVER_LOCATION,BLADE_CHASSIS_CAPACITY,BLADE_CHASSIS_ARRANGE_MODE,BLADE_CHASSIS_LOCATION
,BLADE_CHASSIS_HEIGHT,MANAGE_IP FROM gv_rm_resource_location WHERE CATEGORY_ID=10002 AND ID=STR_RES_ID;
DECLARE r1_cursor CURSOR FOR SELECT ID,ROOM_NAME,CITY_NAME,GROUP_NAME,BRAND_NAME,MACHINE_MODEL,BLADE_CHASSIS_NAME,RACK_NAME
,SERVER_HEIGHT,BMCIP,IPMI_USER,IPMI_PWD,SERVER_LOCATION,BLADE_CHASSIS_CAPACITY,BLADE_CHASSIS_ARRANGE_MODE,BLADE_CHASSIS_LOCATION
,BLADE_CHASSIS_HEIGHT,MANAGE_IP FROM gv_rm_resource_location WHERE CATEGORY_ID=10002 AND
(ROOM_NAME IS NULL OR ROOM_NAME='') AND (CITY_NAME IS NULL OR CITY_NAME='') AND (GROUP_NAME IS NULL OR GROUP_NAME='')
AND (BRAND_NAME IS NULL OR BRAND_NAME='') AND (MACHINE_MODEL IS NULL OR MACHINE_MODEL='');
DECLARE rr_cursor CURSOR FOR SELECT DEPENDED_ID FROM gv_rm_resource_relation WHERE DEPENDANT_ID=res_id;
-- 在游标循环到最后会将done设置为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF STR_RES_ID='INSERT' THEN -- 说明是增加行为,则外循环应该打开游标r1_cursor
UPDATE gv_rm_resource_location SET ROOM_NAME = '',CITY_NAME='',GROUP_NAME='',BRAND_NAME='',MACHINE_MODEL='';
SELECT '正在初始化........';
OPEN r1_cursor;-- 打开游标
ELSE
OPEN rs_cursor;-- 打开游标
END IF;
-- 遍历游标每一行
cursor_loop:LOOP
/** 得到原始值*/
IF STR_RES_ID='INSERT' THEN
FETCH r1_cursor INTO res_id, ORIGINAL_ROOM_NAME,ORIGINAL_CITY_NAME,ORIGINAL_GROUP_NAME,ORIGINAL_BRAND_NAME,ORIGINAL_MACHINE_MODEL,
ORIGINAL_BLADE_CHASSIS_NAME,ORIGINAL_RACK_NAME,ORIGINAL_SERVER_HEIGHT,ORIGINAL_BMCIP,ORIGINAL_IPMI_USER,ORIGINAL_IPMI_PWD,
ORIGINAL_SERVER_LOCATION,ORIGINAL_BLADE_CHASSIS_CAPACITY,ORIGINAL_BLADE_CHASSIS_ARRANGE_MODE,ORIGINAL_BLADE_CHASSIS_LOCATION,
ORIGINAL_BLADE_CHASSIS_HEIGHT,ORIGINAL_MANAGE_IP;-- 把一行的信息存放在对应的变量中
ELSE
FETCH rs_cursor INTO res_id, ORIGINAL_ROOM_NAME,ORIGINAL_CITY_NAME,ORIGINAL_GROUP_NAME,ORIGINAL_BRAND_NAME,ORIGINAL_MACHINE_MODEL,
ORIGINAL_BLADE_CHASSIS_NAME,ORIGINAL_RACK_NAME,ORIGINAL_SERVER_HEIGHT,ORIGINAL_BMCIP,ORIGINAL_IPMI_USER,ORIGINAL_IPMI_PWD,
ORIGINAL_SERVER_LOCATION,ORIGINAL_BLADE_CHASSIS_CAPACITY,ORIGINAL_BLADE_CHASSIS_ARRANGE_MODE,ORIGINAL_BLADE_CHASSIS_LOCATION,
ORIGINAL_BLADE_CHASSIS_HEIGHT,ORIGINAL_MANAGE_IP;-- 把一行的信息存放在对应的变量中
END IF;
IF done = 1 THEN
LEAVE cursor_loop;
END IF;
OPEN rr_cursor;
rr1_loop:LOOP
FETCH rr_cursor INTO DED_ID;
IF done = 1 THEN
SET done = 0;
LEAVE rr1_loop;
END IF;
SELECT CATEGORY_ID,DISPLAY_NAME INTO CATE_ID,DISP_NAME FROM gv_rm_resource where id=DED_ID;
-- 如果服务器位于刀片机箱中
IF CATE_ID=10009 THEN
SET BLADE_CHASSIS_ID=DED_ID;
/** 处理刀片机箱资源参数 */
-- 得到刀片机箱位置
SELECT data1.`VALUE` INTO NEW_BLADE_CHASSIS_LOCATION from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='Location' and templ.SUBCATEGORY_ID=10008 and data1.RESOURCE_ID=BLADE_CHASSIS_ID;
-- 得到刀片机箱高度
SELECT data1.`VALUE` INTO NEW_BLADE_CHASSIS_HEIGHT from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='Height' and templ.SUBCATEGORY_ID=10008 and data1.RESOURCE_ID=BLADE_CHASSIS_ID;
-- 得到刀片机箱容量
SELECT data1.`VALUE` INTO NEW_BLADE_CHASSIS_CAPACITY from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='Capacity' and templ.SUBCATEGORY_ID=10008 and data1.RESOURCE_ID=BLADE_CHASSIS_ID;
-- 得到刀片机箱排列模式
SELECT data1.`VALUE` INTO NEW_BLADE_CHASSIS_ARRANGE_MODE from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='Arrange' and templ.SUBCATEGORY_ID=10008 and data1.RESOURCE_ID=BLADE_CHASSIS_ID;
SET NEW_BLADE_CHASSIS_NAME=DISP_NAME;
SELECT DEPENDED_ID INTO RACK_ID FROM gv_rm_resource_relation WHERE DEPENDANT_ID=DED_ID;-- 被依赖的资源ID应该是机柜
SELECT DISPLAY_NAME INTO NEW_RACK_NAME FROM gv_rm_resource where id=RACK_ID;
SELECT res.ID,res.DISPLAY_NAME INTO ROOM_ID,NEW_ROOM_NAME FROM gv_rm_resource res JOIN gv_rm_resource_relation rr on res.ID=rr.DEPENDED_ID WHERE rr.DEPENDANT_ID=RACK_ID;
SELECT res.ID,res.DISPLAY_NAME INTO CITY_ID,NEW_CITY_NAME FROM gv_rm_resource res JOIN gv_rm_resource_relation rr on res.ID=rr.DEPENDED_ID WHERE rr.DEPENDANT_ID=ROOM_ID;
-- 如果服务器位于机柜中
ELSEIF CATE_ID=10005 THEN
SET RACK_ID = DED_ID;
SELECT DISPLAY_NAME INTO NEW_RACK_NAME FROM gv_rm_resource where id=RACK_ID;
SELECT res.ID,res.DISPLAY_NAME INTO ROOM_ID,NEW_ROOM_NAME FROM gv_rm_resource res JOIN gv_rm_resource_relation rr on res.ID=rr.DEPENDED_ID WHERE rr.DEPENDANT_ID=DED_ID;
SELECT res.ID,res.DISPLAY_NAME INTO CITY_ID,NEW_CITY_NAME FROM gv_rm_resource res JOIN gv_rm_resource_relation rr on res.ID=rr.DEPENDED_ID WHERE rr.DEPENDANT_ID=ROOM_ID;
-- 如果是业务系统
ELSEIF CATE_ID=10010 THEN
SELECT ID,DISPLAY_NAME INTO GROUP_ID,NEW_GROUP_NAME FROM gv_rm_resource WHERE id=DED_ID;
END IF;
END LOOP rr1_loop;
CLOSE rr_cursor;-- 关闭内部游标
/** 处理资源参数 */
-- 得到品牌名称
SELECT data1.`VALUE` INTO NEW_BRAND_NAME from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='Brand' and templ.SUBCATEGORY_ID=10002 and data1.RESOURCE_ID=res_id;
-- 得到机型
SELECT data1.`VALUE` INTO NEW_MACHINE_MODEL from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='NodeModel' and templ.SUBCATEGORY_ID=10002 and data1.RESOURCE_ID=res_id;
-- 得到节点高度
SELECT data1.`VALUE` INTO NEW_SERVER_HEIGHT from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='Height' and templ.SUBCATEGORY_ID=10002 and data1.RESOURCE_ID=res_id;
-- 得到节点位置
SELECT data1.`VALUE` INTO NEW_SERVER_LOCATION from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='Location' and templ.SUBCATEGORY_ID=10002 and data1.RESOURCE_ID=res_id;
-- 得到IPMI地址
SELECT data1.`VALUE` INTO NEW_BMCIP from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='BMCIP' and templ.SUBCATEGORY_ID=10002 and data1.RESOURCE_ID=res_id;
-- 得到IPMI用户
SELECT data1.`VALUE` INTO NEW_IPMI_USER from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='IPMIUser' and templ.SUBCATEGORY_ID=10002 and data1.RESOURCE_ID=res_id;
-- 得到IPMI密码
SELECT data1.`VALUE` INTO NEW_IPMI_PWD from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='IPMIPassword' and templ.SUBCATEGORY_ID=10002 and data1.RESOURCE_ID=res_id;
-- 得到业务IP
SELECT data1.`VALUE` INTO NEW_MANAGE_IP from gv_rm_param_templ templ join gv_rm_param_data data1 on templ.ID=data1.PARAM_TEMPL_ID where templ.`NAME`='ManageIP' and templ.SUBCATEGORY_ID=10002 and data1.RESOURCE_ID=res_id;
/** 清理工作*/
IF ORIGINAL_ROOM_NAME IS NULL THEN
SET ORIGINAL_ROOM_NAME = '';
END IF;
IF ORIGINAL_CITY_NAME IS NULL THEN
SET ORIGINAL_CITY_NAME = '';
END IF;
IF ORIGINAL_GROUP_NAME IS NULL THEN
SET ORIGINAL_GROUP_NAME = '';
END IF;
IF ORIGINAL_BRAND_NAME IS NULL THEN
SET ORIGINAL_BRAND_NAME = '';
END IF;
IF ORIGINAL_MACHINE_MODEL IS NULL THEN
SET ORIGINAL_MACHINE_MODEL = '';
END IF;
IF ORIGINAL_BLADE_CHASSIS_NAME IS NULL THEN
SET ORIGINAL_BLADE_CHASSIS_NAME = '';
END IF;
IF ORIGINAL_RACK_NAME IS NULL THEN
SET ORIGINAL_RACK_NAME = '';
END IF;
IF ORIGINAL_SERVER_HEIGHT IS NULL THEN
SET ORIGINAL_SERVER_HEIGHT = '';
END IF;
IF ORIGINAL_SERVER_LOCATION IS NULL THEN
SET ORIGINAL_SERVER_LOCATION = '';
END IF;
IF ORIGINAL_BMCIP IS NULL THEN
SET ORIGINAL_BMCIP = '';
END IF;
IF ORIGINAL_IPMI_PWD IS NULL THEN
SET ORIGINAL_IPMI_PWD = '';
END IF;
IF ORIGINAL_IPMI_USER IS NULL THEN
SET ORIGINAL_IPMI_USER = '';
END IF;
IF ORIGINAL_BLADE_CHASSIS_CAPACITY IS NULL THEN
SET ORIGINAL_BLADE_CHASSIS_CAPACITY = '';
END IF;
IF ORIGINAL_BLADE_CHASSIS_ARRANGE_MODE IS NULL THEN
SET ORIGINAL_BLADE_CHASSIS_ARRANGE_MODE = '';
END IF;
IF ORIGINAL_BLADE_CHASSIS_LOCATION IS NULL THEN
SET ORIGINAL_BLADE_CHASSIS_LOCATION = '';
END IF;
IF ORIGINAL_BLADE_CHASSIS_HEIGHT IS NULL THEN
SET ORIGINAL_BLADE_CHASSIS_HEIGHT = '';
END IF;
IF ORIGINAL_MANAGE_IP IS NULL THEN
SET ORIGINAL_MANAGE_IP = '';
END IF;
IF NEW_ROOM_NAME IS NULL THEN
SET NEW_ROOM_NAME = '';
END IF;
IF NEW_CITY_NAME IS NULL THEN
SET NEW_CITY_NAME = '';
END IF;
IF NEW_GROUP_NAME IS NULL THEN
SET NEW_GROUP_NAME = '';
END IF;
IF NEW_BRAND_NAME IS NULL THEN
SET NEW_BRAND_NAME = '';
END IF;
IF NEW_MACHINE_MODEL IS NULL THEN
SET NEW_MACHINE_MODEL = '';
END IF;
IF NEW_BLADE_CHASSIS_NAME IS NULL THEN
SET NEW_BLADE_CHASSIS_NAME = '';
END IF;
IF NEW_RACK_NAME IS NULL THEN
SET NEW_RACK_NAME = '';
END IF;
IF NEW_SERVER_HEIGHT IS NULL THEN
SET NEW_SERVER_HEIGHT = '';
END IF;
IF NEW_SERVER_LOCATION IS NULL THEN
SET NEW_SERVER_LOCATION = '';
END IF;
IF NEW_BMCIP IS NULL THEN
SET NEW_BMCIP = '';
END IF;
IF NEW_IPMI_PWD IS NULL THEN
SET NEW_IPMI_PWD = '';
END IF;
IF NEW_IPMI_USER IS NULL THEN
SET NEW_IPMI_USER = '';
END IF;
IF NEW_BLADE_CHASSIS_CAPACITY IS NULL THEN
SET NEW_BLADE_CHASSIS_CAPACITY = '';
END IF;
IF NEW_BLADE_CHASSIS_ARRANGE_MODE IS NULL THEN
SET NEW_BLADE_CHASSIS_ARRANGE_MODE = '';
END IF;
IF NEW_BLADE_CHASSIS_LOCATION IS NULL THEN
SET NEW_BLADE_CHASSIS_LOCATION = '';
END IF;
IF NEW_BLADE_CHASSIS_HEIGHT IS NULL THEN
SET NEW_BLADE_CHASSIS_HEIGHT = '';
END IF;
IF NEW_MANAGE_IP IS NULL THEN
SET NEW_MANAGE_IP = '';
END IF;
/** 更新操作*/
IF STRCMP(ORIGINAL_ROOM_NAME,NEW_ROOM_NAME) !=0 OR STRCMP(ORIGINAL_CITY_NAME,NEW_CITY_NAME) !=0 OR STRCMP(ORIGINAL_GROUP_NAME,NEW_GROUP_NAME) !=0 OR
STRCMP(ORIGINAL_BRAND_NAME,NEW_BRAND_NAME) !=0 OR STRCMP(ORIGINAL_MACHINE_MODEL,NEW_MACHINE_MODEL) !=0 OR STRCMP(ORIGINAL_BLADE_CHASSIS_NAME,NEW_BLADE_CHASSIS_NAME) !=0 OR
STRCMP(ORIGINAL_RACK_NAME,NEW_RACK_NAME) !=0 OR STRCMP(ORIGINAL_SERVER_HEIGHT,NEW_SERVER_HEIGHT) !=0 OR STRCMP(ORIGINAL_SERVER_LOCATION,NEW_SERVER_LOCATION) !=0 OR
STRCMP(ORIGINAL_BMCIP,NEW_BMCIP) !=0 OR STRCMP(ORIGINAL_IPMI_USER,NEW_IPMI_USER) !=0 OR STRCMP(ORIGINAL_IPMI_PWD,NEW_IPMI_PWD) !=0 OR
STRCMP(ORIGINAL_BLADE_CHASSIS_CAPACITY,NEW_BLADE_CHASSIS_CAPACITY) !=0 OR STRCMP(ORIGINAL_BLADE_CHASSIS_ARRANGE_MODE,NEW_BLADE_CHASSIS_ARRANGE_MODE) !=0 OR
STRCMP(ORIGINAL_BLADE_CHASSIS_LOCATION,NEW_BLADE_CHASSIS_LOCATION) !=0 OR STRCMP(ORIGINAL_BLADE_CHASSIS_HEIGHT,NEW_BLADE_CHASSIS_HEIGHT) !=0 OR
STRCMP(ORIGINAL_MANAGE_IP,NEW_MANAGE_IP) !=0 THEN
UPDATE gv_rm_resource_location SET ROOM_ID=ROOM_ID,ROOM_NAME=NEW_ROOM_NAME,CITY_ID=CITY_ID,CITY_NAME=NEW_CITY_NAME,GROUP_ID=GROUP_ID,GROUP_NAME=NEW_GROUP_NAME,
BRAND_NAME=NEW_BRAND_NAME,MACHINE_MODEL=NEW_MACHINE_MODEL,BLADE_CHASSIS_ID=BLADE_CHASSIS_ID,BLADE_CHASSIS_NAME=NEW_BLADE_CHASSIS_NAME,RACK_ID=RACK_ID,
RACK_NAME=NEW_RACK_NAME,SERVER_HEIGHT=NEW_SERVER_HEIGHT,SERVER_LOCATION=NEW_SERVER_LOCATION,BMCIP=NEW_BMCIP,IPMI_USER=NEW_IPMI_USER,
IPMI_PWD=NEW_IPMI_PWD,BLADE_CHASSIS_CAPACITY=NEW_BLADE_CHASSIS_CAPACITY,BLADE_CHASSIS_ARRANGE_MODE=NEW_BLADE_CHASSIS_ARRANGE_MODE,
BLADE_CHASSIS_LOCATION=NEW_BLADE_CHASSIS_LOCATION,BLADE_CHASSIS_HEIGHT=NEW_BLADE_CHASSIS_HEIGHT,MANAGE_IP=NEW_MANAGE_IP WHERE ID=res_id;
END IF;
/** 清理工作*/
SET done = 0;
SET ROOM_ID=0;
SET NEW_ROOM_NAME='';
SET CITY_ID=0;
SET NEW_CITY_NAME='';
SET GROUP_ID=0;
SET NEW_GROUP_NAME='';
SET NEW_BRAND_NAME='';
SET NEW_MACHINE_MODEL='';
SET BLADE_CHASSIS_ID=0;
SET NEW_BLADE_CHASSIS_NAME='';
SET RACK_ID=0;
SET NEW_RACK_NAME='';
SET NEW_SERVER_HEIGHT = '';
SET NEW_SERVER_LOCATION = '';
SET NEW_BMCIP = '';
SET NEW_IPMI_PWD = '';
SET NEW_IPMI_USER = '';
SET NEW_BLADE_CHASSIS_CAPACITY='';
SET NEW_BLADE_CHASSIS_ARRANGE_MODE='';
SET NEW_BLADE_CHASSIS_LOCATION='';
SET NEW_BLADE_CHASSIS_HEIGHT='';
SET NEW_MANAGE_IP='';
END LOOP cursor_loop;
IF STR_RES_ID='INSERT' THEN
CLOSE r1_cursor;
ELSE
CLOSE rs_cursor;
END IF;
SELECT '恭喜,操作成功........';
END;/**
* 功能:对于IP地址，补充零
*/
DROP FUNCTION IF EXISTS F_ADD_ZERO_FOR_IP;
CREATE FUNCTION F_ADD_ZERO_FOR_IP(P_IP VARCHAR(64))
RETURNS VARCHAR(15)
BEGIN
DECLARE P_FIRST VARCHAR(5);
DECLARE P_SECOND VARCHAR(5);
DECLARE P_THIRD VARCHAR(5);
DECLARE P_FOURTH VARCHAR(5);
DECLARE P_TEMP VARCHAR(15);
DECLARE P_FORMAT_RESULT VARCHAR(15);
IF CHAR_LENGTH(P_IP) <> 15 THEN -- 如果长度不等于15，则说明IP地址不完整，需要补0
SET P_TEMP= SUBSTRING_INDEX(P_IP,'.',2);
SET P_FIRST= SUBSTRING_INDEX(P_TEMP,'.',1);
SET P_SECOND= SUBSTRING_INDEX(P_TEMP,'.',-1);
SET P_TEMP= SUBSTRING_INDEX(P_IP,'.',3);
SET P_THIRD= SUBSTRING_INDEX(P_TEMP,'.',-1);
SET P_FOURTH= SUBSTRING_INDEX(P_IP,'.',-1);
IF CHAR_LENGTH(P_FIRST)<>3 THEN
IF CHAR_LENGTH(P_FIRST)=1 THEN -- ip地址的第一段应补两个零
SET P_FIRST=CONCAT('0','0',P_FIRST);
ELSEIF CHAR_LENGTH(P_FIRST)=2 THEN -- ip地址的第一段应补一个零
SET P_FIRST=CONCAT('0',P_FIRST);
END IF;
END IF;
IF CHAR_LENGTH(P_SECOND)<>3 THEN
IF CHAR_LENGTH(P_SECOND)=1 THEN -- ip地址的第二段应补两个零
SET P_SECOND=CONCAT('0','0',P_SECOND);
ELSEIF CHAR_LENGTH(P_SECOND)=2 THEN -- ip地址的第二段应补一个零
SET P_SECOND=CONCAT('0',P_SECOND);
END IF;
END IF;
IF CHAR_LENGTH(P_THIRD)<>3 THEN
IF CHAR_LENGTH(P_THIRD)=1 THEN -- ip地址的第三段应补两个零
SET P_THIRD=CONCAT('0','0',P_THIRD);
ELSEIF CHAR_LENGTH(P_THIRD)=2 THEN -- ip地址的第三段应补一个零
SET P_THIRD=CONCAT('0',P_THIRD);
END IF;
END IF;
IF CHAR_LENGTH(P_FOURTH)<>3 THEN
IF CHAR_LENGTH(P_FOURTH)=1 THEN -- ip地址的第四段应补两个零
SET P_FOURTH=CONCAT('0','0',P_FOURTH);
ELSEIF CHAR_LENGTH(P_FOURTH)=2 THEN -- ip地址的第四段应补一个零
SET P_FOURTH=CONCAT('0',P_FOURTH);
END IF;
END IF;
SET P_IP=CONCAT(P_FIRST,".",P_SECOND,".",P_THIRD,".",P_FOURTH);
END IF;
SET P_FORMAT_RESULT=P_IP;
RETURN P_FORMAT_RESULT;
END;
/**
* 更新location表中的格式化后的管理IP
*/
DROP PROCEDURE IF EXISTS P_UPDATE_RES_LOCATION_IP;
CREATE PROCEDURE P_UPDATE_RES_LOCATION_IP()
BEGIN
DECLARE found int;
DECLARE P_RES_ID VARCHAR(32) DEFAULT '';
DECLARE P_MANAGEMENT_IP VARCHAR(15) DEFAULT '';
DECLARE P_FORMAT_MANAGEMENT_IP VARCHAR(15) DEFAULT '';
DECLARE rr_cursor CURSOR FOR SELECT ID,MANAGE_IP FROM gv_rm_resource_location;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET found=0;
OPEN rr_cursor;-- 打开游标
cursor_loop:LOOP
FETCH rr_cursor INTO P_RES_ID,P_MANAGEMENT_IP;
SET P_FORMAT_MANAGEMENT_IP=F_ADD_ZERO_FOR_IP(P_MANAGEMENT_IP);
UPDATE gv_rm_resource_location SET FORMAT_MANAGEMENT_IP=P_FORMAT_MANAGEMENT_IP WHERE ID=P_RES_ID;
IF found=0 THEN
LEAVE cursor_loop;
END IF;
END LOOP cursor_loop;
CLOSE rr_cursor;
END;本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
