MySQL存储过程编写

记录一下自己写的一个mysql存储过程，在游标方面和oracle有些不一样，mysql是使用一个HANDLER来处理数据读取完的情况，如下：

DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_mobilearea = 1;

当没有数据时，把no_more_mobilearea这个变量设置为1，如下：

FETCH  cur_dm_mobile INTO temp_id, temp_mobile, temp_province_name, temp_city_name;
UNTIL  no_more_mobilearea = 1

mysql中，如果一个整数和另一个整数相除，得到的如果是一个有小数点的数字，即使把它赋给一个整数值，得到的还是一个小数，如下：

DECLARE  temp_mobile_pre INT DEFAULT 0;
set temp_mobile_pre = 1395007/10000;

此时得到的值为会139.5007，即使把它赋给一个整数变量也没有，如果想得到一个整数值，可以用FLOOR（向下取整）或ceil(向上取整)或round(四舍五入)，如：

set temp_mobile_pre = FLOOR(temp_mobile/10000);

得到就是139了

mysql中的查找某个字符在字符串的函数：LOCATE（substr, str)，注意是要查找的字符串在前面，后面是被查找的字符串，我开始弄错了。

mysql中的截取字符串的函数：substring（str, pos），left等，注意substring好象不支持从某个位置开始截取指定的长度，虽然给出的api说是可以，但我实际试了不行，可能跟我的数据库版本有关系，只能用left函数

mysql中连接两个字符串：concat

完整的一个存储过程如下：

CREATE DEFINER = 'root'@'localhost'
PROCEDURE ddo.test2()
BEGIN
    #Routine body goes here...
DECLARE  no_more_mobilearea, temp_idx, temp_nums, temp_type, temp_mobile_pre, temp_count INT DEFAULT 0;
DECLARE  temp_id VARCHAR(32);
DECLARE  temp_province_name, temp_city_name VARCHAR(50);
DECLARE  temp_province_code, temp_city_code VARCHAR(10);
DECLARE temp_mobile NUMERIC(7);
DECLARE  cur_dm_mobile CURSOR FOR SELECT id, mobilenumber, province_name, city_name FROM dm_mobile;
#DECLARE  cur_dm_mobile CURSOR FOR SELECT id, mobilenumber, province_name, city_name FROM dm_mobile WHERE province_code IS null;
#DECLARE  cur_dm_mobile CURSOR FOR SELECT id, mobilenumber, province_name, city_name FROM dm_mobile WHERE id = '285001';
DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_mobilearea = 1;
OPEN  cur_dm_mobile;
FETCH  cur_dm_mobile INTO temp_id, temp_mobile, temp_province_name, temp_city_name;
REPEAT
 set temp_mobile_pre = FLOOR(temp_mobile/10000);
 if (temp_mobile_pre = 133 || temp_mobile_pre = 153 || temp_mobile_pre = 180 || temp_mobile_pre = 189 || temp_mobile_pre = 181 || temp_mobile_pre = 170
     || temp_mobile_pre = 177) THEN
    set temp_type = 1;
 ELSEIF (temp_mobile_pre = 134 || temp_mobile_pre = 135 || temp_mobile_pre = 136 || temp_mobile_pre = 137 || temp_mobile_pre = 138 || temp_mobile_pre = 139
     || temp_mobile_pre = 150 || temp_mobile_pre = 151 || temp_mobile_pre = 152 || temp_mobile_pre = 157 || temp_mobile_pre = 158 || temp_mobile_pre = 159 
     || temp_mobile_pre = 182 || temp_mobile_pre = 183 || temp_mobile_pre = 184 || temp_mobile_pre = 187 || temp_mobile_pre = 188) THEN
    set temp_type = 2;
 ELSEIF (temp_mobile_pre = 130 || temp_mobile_pre = 131 || temp_mobile_pre = 132 || temp_mobile_pre = 155 || temp_mobile_pre = 156 || temp_mobile_pre = 185
     || temp_mobile_pre = 186 || temp_mobile_pre = 176) THEN
    set temp_type = 3;
 ELSEIF (temp_mobile_pre = 145 || temp_mobile_pre = 147) THEN
    set temp_type = 4;
 ELSE
    SET temp_type = 5;
end if;
 select count(*) into temp_count from t_s_territory where territoryname like concat(temp_province_name, '%');
 if (temp_count = 1) THEN
     select territorycode into temp_province_code from t_s_territory where territoryname like concat(temp_province_name, '%');
 ELSEIF (temp_count > 1) THEN
     select territorycode into temp_province_code from t_s_territory where territoryname like concat(temp_province_name, '%') limit 1;
 ELSE
     set temp_province_code = 'null';
 end if;
 select count(*) into temp_count from t_s_territory where territoryname like concat(temp_city_name, '%');
 if (temp_count = 1) THEN
     select territorycode into temp_city_code from t_s_territory where territoryname like concat(temp_city_name, '%');
 ELSEIF (temp_count > 1) THEN
     select territorycode into temp_city_code from t_s_territory where territoryname like concat(temp_city_name, '%') limit 1;
 ELSE
     set temp_city_code = 'null';
 end if;
if (temp_province_code <> 'null') THEN
  IF (temp_city_code = 'null') THEN
    set temp_city_code = temp_province_code;
  END IF;
  update dm_mobile set province_code = temp_province_code, city_code = temp_city_code, type = temp_type where id = temp_id;
end if;
 if (temp_nums = 1000) then
     set temp_nums = 0;
     commit;
 end if;
FETCH  cur_dm_mobile INTO temp_id, temp_mobile, temp_province_name, temp_city_name;
UNTIL  no_more_mobilearea = 1
end REPEAT;
CLOSE  cur_dm_mobile;
commit;
END

转载请保留固定链接： https://linuxeye.com/database/2415.html

热门标签:

MySQL存储过程编写