mysql (jinhong)取类目函数

最新推荐文章于 2025-09-06 09:33:29 发布

转载最新推荐文章于 2025-09-06 09:33:29 发布 · 84 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://blog.51cto.com/fluagen/155530

文章标签：

#数据库

本文介绍了一种在MySQL中创建自定义函数的方法，用于解析特定格式的字符串，并返回相应的编码和项值。通过使用字符串操作函数和游标，该函数能够处理带有分隔符的输入字符串，提取每个部分的编码，并利用另一个自定义函数获取其对应的项值。

/**DROP function IF EXISTS aidGetLeiMuCode;
DELIMITER $$
create function    aidGetLeiMuCode(str VARCHAR(2000))
         RETURNS    VARCHAR(2000)
         BEGIN
            DECLARE pos INT;
            DECLARE fgstr VARCHAR(2000);
            DECLARE vstr VARCHAR(2000);
            DECLARE rstr VARCHAR(2000);
            DECLARE vcode VARCHAR(2000);
            SET vstr=TRIM(str);
            SET pos=Instr(str, '-');
            set rstr = '';
            set fgstr = '';
            WHILE pos > 0 DO
  set vcode = SUBSTRING(str, 1, pos - 1);
  set rstr = Concat(rstr,aidGetLeiMuItem(vcode));
  set fgstr = Concat(fgstr,',');
  set rstr = Concat(rstr,fgstr);
  SET pos=Locate('-', str, pos + 1);
            END WHILE;
            set rstr = Concat(rstr,aidGetLeiMuItem(vstr),fgstr,',');
            return rstr ;
        end $$
DELIMITER ;
**/

DROP function IF EXISTS aidGetLeiMuCode;
DELIMITER $$
create function    aidGetLeiMuCode(str VARCHAR(2000))
         RETURNS    VARCHAR(2000)
         BEGIN
            DECLARE pos INT;
            DECLARE fgstr VARCHAR(2000);
            DECLARE vstr VARCHAR(2000);
            DECLARE rstr VARCHAR(2000);
            DECLARE vcode VARCHAR(2000);
            DECLARE indexs INT;
            SET vstr=TRIM(str);
            SET pos=Instr(str, '-');
            set rstr = '';
            set fgstr = '';
            set indexs = 1;
            WHILE pos > 0 DO
  set vcode = SUBSTRING(str, 1, pos - 1);
  set rstr = Concat(rstr,aidGetLeiMuItem(vcode));
  case(indexs)
    when 1 Then set fgstr = 'AA';
    when 2 Then set fgstr = 'BB';
    when 3 Then set fgstr = 'CC';
    when 4 Then set fgstr = 'DD';
    when 5 Then set fgstr = 'EE';
    when 6 Then set fgstr = 'FF';
    when 7 Then set fgstr = 'GG';
    when 8 Then set fgstr = 'HH';
    when 9 Then set fgstr = 'II';
    when 10 Then set fgstr = 'JJ';
    when 11 Then set fgstr = 'KK';
    when 12 Then set fgstr = 'LL';
    when 13 Then set fgstr = 'MM';
    when 14 Then set fgstr = 'NN';
    when 15 Then set fgstr = 'OO';
    when 16 Then set fgstr = 'PP';
    when 17 Then set fgstr = 'QQ';
    when 18 Then set fgstr = 'RR';
    when 19 Then set fgstr = 'SS';
    when 20 Then set fgstr = 'TT';
    when 21 Then set fgstr = 'UU';
    when 22 Then set fgstr = 'VV';
    when 23 Then set fgstr = 'WW';
    when 24 Then set fgstr = 'XX';
    when 25 Then set fgstr = 'YY';
    when 26 Then set fgstr = 'ZZ';
    else set fgstr = ',';
  end case;
  set rstr = Concat(rstr,fgstr);
  SET pos=Locate('-', str, pos + 1);
                set indexs = indexs + 1;
            END WHILE;
  case(indexs)
    when 1 Then set fgstr = 'AA';
    when 2 Then set fgstr = 'BB';
    when 3 Then set fgstr = 'CC';
    when 4 Then set fgstr = 'DD';
    when 5 Then set fgstr = 'EE';
    when 6 Then set fgstr = 'FF';
    when 7 Then set fgstr = 'GG';
    when 8 Then set fgstr = 'HH';
    when 9 Then set fgstr = 'II';
    when 10 Then set fgstr = 'JJ';
    when 11 Then set fgstr = 'KK';
    when 12 Then set fgstr = 'LL';
    when 13 Then set fgstr = 'MM';
    when 14 Then set fgstr = 'NN';
    when 15 Then set fgstr = 'OO';
    when 16 Then set fgstr = 'PP';
    when 17 Then set fgstr = 'QQ';
    when 18 Then set fgstr = 'RR';
    when 19 Then set fgstr = 'SS';
    when 20 Then set fgstr = 'TT';
    when 21 Then set fgstr = 'UU';
    when 22 Then set fgstr = 'VV';
    when 23 Then set fgstr = 'WW';
    when 24 Then set fgstr = 'XX';
    when 25 Then set fgstr = 'YY';
    when 26 Then set fgstr = 'ZZ';
    else set fgstr = ',';
  end case;
            set rstr = Concat(rstr,aidGetLeiMuItem(vstr),fgstr);
            return rstr ;
        end $$
DELIMITER ;

DROP function IF EXISTS aidGetLeiMuItem;
DELIMITER $$
create function    aidGetLeiMuItem(lmCode VARCHAR(2000))
  RETURNS VARCHAR(2000)
  BEGIN
    DECLARE rstr VARCHAR(2000);
    DECLARE done INT DEFAULT 0;
    DECLARE Col_c1 VARCHAR(200);


    DECLARE cur CURSOR FOR (select Item from sys_Classsetup where ItemCode in(lmCode));
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    Set rstr = '';
    Open cur;
    Repeat
    FETCH cur INTO Col_c1;
      IF NOT done THEN
        Set rstr = Col_c1;
      end if;
    UNTIL done END REPEAT;
    Close cur;
    return rstr;
  end $$

DELIMITER ;

##select aidGetLeiMuCode(t1.LeiMu) from app_assetcustominfo50 t1

转载于:https://blog.51cto.com/fluagen/155530