Mysql-存储过程

1： 使用游标

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `cq_c1_mal_rips`(IN search_time VARCHAR(20),IN search_domains TEXT
                ,IN startIndex INT,IN pageSize INT,OUT return_total INT)
BEGIN
        DECLARE rip_total INT; # RIP数量
        DECLARE v_rip VARCHAR(4000); #存储RIP
        DECLARE num INT DEFAULT 1; # 循环使用的变量
          DECLARE tempDetailSql TEXT;        
    #定义游标遍历时，作为判断是否遍历完全部记录的标记
        DECLARE no_more_departments INTEGER DEFAULT 0;               
    DECLARE cur_detail CURSOR FOR
        SELECT rip_num,rip FROM c1_temp_mal_detail;
        #声明当游标遍历完全部记录后将标志变量置成某个值
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
          DROP TABLE IF EXISTS c1_temp_mal_detail;
        SET @tempDetailSql = CONCAT('CREATE TEMPORARY TABLE c1_temp_mal_detail('
                ,' SELECT LENGTH(rip)-LENGTH(REPLACE(rip, ",", "")) + 1 rip_num,rip'
                ,' FROM `mal_domains_detail`'
                ,' WHERE `date` = ',search_time,''
                ,' AND domain IN (',search_domains,'))');
        PREPARE stmt FROM @tempDetailSql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        #创建临时表
        DROP TABLE IF EXISTS temp_mal_rips;
        CREATE TEMPORARY TABLE temp_mal_rips (
                temp_rip VARCHAR(512) NOT NULL
        );
        OPEN cur_detail;
        REPEAT
        FETCH cur_detail INTO rip_total, v_rip;
            WHILE num<= rip_total DO
            #临时表插入数据
                INSERT INTO  temp_mal_rips(temp_rip) VALUES(SUBSTRING_INDEX(SUBSTRING_INDEX(v_rip, ',', num),',',-1));
                SET num=num+1;
            END WHILE; 
            SET num=1;
        UNTIL no_more_departments  END REPEAT;#循环结束
        CLOSE cur_detail;     
        
        SELECT COUNT(distinct(temp_rip)) INTO return_total FROM temp_mal_rips;
    
        SELECT (@i:=@i+1) AS lnum,a.temp_rip ip,'rip' `type` FROM
        (
            SELECT temp_rip FROM temp_mal_rips GROUP BY temp_rip ORDER BY INET_ATON(temp_rip)
        ) a,(SELECT @i:=startIndex) AS it 
        LIMIT startIndex,pageSize;
        DROP TEMPORARY TABLE c1_temp_mal_detail;
        DROP TEMPORARY TABLE temp_mal_rips;
    END ;;

 

2：未使用游标

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `PD_ddos_indicator_attacksource`(
    in in_dip varchar(20),IN in_begintime VARCHAR(30),IN in_endtime VARCHAR(30), IN `pageNum` INT, IN `pageSize` INT,OUT `total` INT)
BEGIN
DECLARE num1 INT;
    SELECT COUNT(1) INTO total
    FROM dns_m_ddos_relation
    WHERE dip = in_dip
    AND DATETIME >= in_begintime
    AND DATETIME <= in_endtime;
    
    SELECT (pageNum-1)*pageSize INTO num1; 
    
        SELECT  sip_city as 区县,
        sip AS 攻击IP,
                ROUND(SUM(VALUE*10)/(1024),2) AS 流量
    FROM dns_m_ddos_relation
    WHERE dip = in_dip
    AND DATETIME >= in_begintime
    AND DATETIME <= in_endtime
    GROUP BY sip
        ORDER BY VALUE DESC
        LIMIT num1, pageSize;
    END ;;
DELIMITER ;

 

3: 使用临时表

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `PD_mal_test`(in v_rip varchar(20), in begin_time varchar(20))
BEGIN
    
    declare total int; # 域名数量
    declare v_domain varchar(4000); #存储域名
    declare aa int default 1; # 循环使用的变量
    
     drop table if exists temp_domains;
     
     CREATE TEMPORARY TABLE temp_domains (  
      temp_domain varchar(255) not null ,
      temp_rip varchar(20) not null  
      );
      
     
  
    select LENGTH(domain)-LENGTH(REPLACE(domain, ',', '')) + 1 , domain into  total , v_domain # 将域名数量和 域名字符串赋值给 变量
    from mal_rips_result 
    where rip = v_rip
    and date = date_format(begin_time, '%Y%m%d');
    
   # select total, v_domain;
       
       
   # 开始循环体,将截取的域名，插入到临时表中
     truncate table temp_domains;
     
     WHILE aa<= total DO
     
      #select v_rip,SUBSTRING_INDEX(SUBSTRING_INDEX(v_domain, ',', aa),',',-1) as size;
      
      insert into  temp_domains(temp_rip,temp_domain) values( v_rip,SUBSTRING_INDEX(SUBSTRING_INDEX(v_domain, ',', aa),',',-1));#临时表插入数据
      
     #SELECT temp_rip, temp_domain FROM temp_domains; 
      
       SET aa=aa+1;
        
     END WHILE; 
    
   
    select a.temp_domain, b.dgatype
    from temp_domains a
      inner join (select domain, dgatype from mal_domains_raw 
                  where DATE = DATE_FORMAT(begin_time, '%Y%m%d')) b
      on  a.temp_domain = b.domain ;  #从临时表查询数据 */
    END ;;
DELIMITER ;

 

4：使用用户变量

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `PD_traff_attackedcycle`(IN v_id VARCHAR(50),IN v_total VARCHAR(20))
BEGIN
    
    /*
    作者：闫菁
    本过程用来获得 异常流量-异常流量首页, 攻击环图数据。
    
    v_id: 从被攻击表格传递的eventiid。
    v_total：从被攻击表格传递的总攻击流量
    
    */
    
    #DECLARE  attack_total DECIMAL(38,2);
    DECLARE  attack_top5 float(10,2);
    
    #select CAST(v_total AS DECIMAL) into attack_total;  #将传递总流量转化为数字
    #select v_total into attack_total;
    #select v_total,attack_total;
    
    select sum(value) into attack_top5  #得到TOP5的总流量
    from ( SELECT IF( NATION ='非洲','埃及',NATION) AS NAME, ROUND(SUM(IPFLOW),2) AS VALUE
        FROM cradar1.traff_i_attackedtop20 
        WHERE eventid =  v_id
        GROUP BY NAME 
        ORDER BY VALUE DESC
        LIMIT 5) top5;
     
      
    SELECT ORD, NAME, VALUE, CONCAT(NAME,'流量：', VALUE, ' GB') AS info
    FROM
    ( SELECT ORD ,NAME, VALUE   #得到TOP5的国家流量和排名
      FROM  
      (
        SELECT @num := (@num +1) AS ORD, IF( NATION ='非洲','埃及',NATION) AS NAME, ROUND(sum(IPFLOW),2) AS VALUE
        FROM cradar1.traff_i_attackedtop20 ,( SELECT @num :=0 )t
        WHERE eventid =  v_id
        group by name 
        ORDER BY VALUE DESC
        LIMIT 5 ) result1
     
      UNION all
      
      
       SELECT 6 AS ORD, '其他' AS NAME  ,ROUND( v_total - IFNULL(attack_top5,0),2) AS VALUE
      
     ) A;
 
 
    END ;;

 

5： 使用分页查询

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `PD_traff_attacklist`(IN in_eventid VARCHAR(50),IN pageNum INT, IN pageSize INT,OUT total INT)
BEGIN
    
    /*
    作者：闫菁
    本过程用来获得 异常流量-异常流量首页，被攻击信息列表数据。
    
    begin_time: 从时间轴传递的开始时间。
    endt_ime：从时间轴传递的结束时间。
    total:  传递给产品工厂的，输出总得表格条目数量。
    pageSize: 产品工厂，分页表格应用的每页条目数量。
    pageNum: 产品工厂，分页表格应用的总页面数量。
    */
    
    DECLARE  num1 INT;
   
 
    SELECT COUNT(src_ip) INTO total  
    FROM cradar1.traff_i_attackedmsg
    WHERE eventid = in_eventid; #获得选择的事件，攻击信息数量，并传递给total 输出变量
 
    
    SELECT (pageNum-1)*pageSize INTO num1; # 或者 limit 分页的开始数据条目
    
    SELECT src_ip AS 攻击IP, NATION AS 攻击地址, 
           #DATE_FORMAT(beg_time,'%y-%m %H:%i:%s') AS 开始时间,DATE_FORMAT(end_time,'%y-%m %H:%i:%s') AS 结束时间,
           CONCAT(CAST(ROUND(IPFLOW,2) AS CHAR), 'GB') AS 攻击流量
    FROM cradar1.traff_i_attackedmsg 
    WHERE eventid = in_eventid
    ORDER BY ROUND(IPFLOW,2) DESC
    LIMIT num1, pageSize;    #查询符合要求的数据，并使用limit，返回分页的数据
    
    
    END ;;
DELIMITER ;

 

6：使用动态sql

 

DELIMITER $$

USE `nms`$$

DROP PROCEDURE IF EXISTS `nms_version_app_detail`$$

CREATE DEFINER=`root`@`%` PROCEDURE `nms_version_app_detail`(IN v_date VARCHAR(20),IN in_app VARCHAR(50),IN in_type VARCHAR(10),IN in_input VARCHAR(255),IN in_sysid VARCHAR(50),IN pageNum INT, IN pageSize INT,OUT total INT)
BEGIN
/*
本产品为 版本对比监测页面的表格
IN v_date VARCHAR(20) 查询时间,
IN in_app varchar(50)    下拉框应用id，value = 0为全部
IN in_type varchar(10)    单选框选项id，版本号字段为0, MD5为1，IP为2
IN in_input varchar(255)    文本框输入内容
IN in_sysid varchar(50)    系统权限id
情况分为：
1、应用为全部，选择项任意，输入框为空 （查询结果多合一）
2、应用为全部，选择MD5，输入框输入MD5    （查询结果为单独数据）
3、应用为全部，选择IP，输入框输入IP    （查询结果多合一）
4、应用为独立应用，输入框为空    （查询结果多合一）
5、应用为独立应用，选择MD5，输入框输入MD5 （查询结果为单独数据）
6、应用为独立应用，选择IP，输入框输入IP （查询结果为单独数据）
*/
#1、选择了具体的应用，在查询条件中拼接查询条件。 
DECLARE vv_input  VARCHAR(255); #文本输入框去空格
DECLARE begin_time DATETIME; #开始时间
DECLARE end_time DATETIME; #结束时间
DECLARE where_appname VARCHAR(500); #app名称 条件
DECLARE where_policy_no VARCHAR(500);  # 版本条件
DECLARE where_md5_result VARCHAR(500); #md5条件
DECLARE where_ip VARCHAR(500);  #ip条件
DECLARE v_info VARCHAR(500);  #info
DECLARE v_sql_base VARCHAR(1000);
DECLARE v_total VARCHAR(500);
DECLARE v_sql VARCHAR(1000);
DECLARE v_select1 VARCHAR(1000);
DECLARE v_select2 VARCHAR(1000);
DECLARE v_sql_limit VARCHAR(100);
DECLARE sum1 INT;
SET where_appname = '';
SET where_policy_no = '';
SET where_md5_result = '';
SET where_ip = '';
SELECT (pageNum-1)*pageSize INTO sum1;  

IF v_date = '' THEN SET v_date = '2099-01-01';
END IF;
        
SELECT CONCAT(v_date, ' 00:00:00'), CONCAT(v_date, ' 23:59:59')  INTO begin_time, end_time;


#拼接应用名称
IF in_app != '0' THEN SET where_appname = CONCAT( ' and app_name =  ', '''',  in_app , ''''); 
END IF;
#拼接版本号码
IF in_type = '0' THEN
   IF vv_input != ''  THEN SET where_policy_no = CONCAT( '  and policy_no =  ', '''', vv_input , '''');
   END IF;
   
   
 #拼接MD5
ELSEIF in_type = '1' THEN
   IF vv_input != ''  THEN SET where_md5_result = CONCAT( '  and md5_result =  ', '''', vv_input , '''');
  
   END IF;
   
#拼接ip条件   
ELSEIF in_type = '2' THEN
   IF vv_input != ''  THEN SET where_ip = CONCAT( '  and ip =  ', '''', vv_input , '''');
   
   END IF;
END IF;
SET v_select1 = CONCAT(' select count(1) into @ret_total ');
SET v_select2 = CONCAT(' select  app_name as ', '''', '应用名称','''', ' , ip as','''', '服务器IP','''', ' , Md5_result as ' ,'''','MD5值','''',  
                  ',case when result = 1 then ','''','版本一致','''',' when result = 0 then ','''','版本不一致' ,'''',
                   ' else ','''','获取失败','''',' end as ','''','对比结果','''');
                   
                   
#拼接查询sqlbase条件
SET v_sql_base = CONCAT(
' 
  from nms_file_md5_m_info_his 
  where 1=1
  and file_type = ','''','4','''','
  and app_class = ( select  sys_name from nms_tb_sys where id = ','''', in_sysid, '''',' )  
  and create_time >= ' ,'''',begin_time,'''','   and create_time <=  ','''',end_time,'''') ;
  
  
  
  
 SET v_sql_limit = CONCAT(' limit ', sum1, ' , ', pageSize );
 
#拼接产品工厂全部条件
SET v_total = CONCAT(v_select1, v_sql_base, where_appname, where_policy_no,where_md5_result,  where_ip);
SET v_sql = CONCAT(v_select2, v_sql_base, where_appname, where_policy_no,where_md5_result,  where_ip,v_sql_limit);




SET @v_sql1 = v_total;   #注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
PREPARE stmt1 FROM @v_sql1;  #--预处理需要执行的动态SQL，其中stmt是一个变量
EXECUTE  stmt1 ;      #--执行SQL语句
DEALLOCATE PREPARE stmt1;    # --释放掉预处理段  
SET  total = @ret_total; 
SET @v_sql2 = v_sql;   #注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
PREPARE stmt2 FROM @v_sql2;  #--预处理需要执行的动态SQL，其中stmt是一个变量
EXECUTE  stmt2 ;      #--执行SQL语句
DEALLOCATE PREPARE stmt2;    # --释放掉预处理段   
SELECT v_total;
    
END$$

DELIMITER ;

 

 

7：使用循环代码块

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
     DECLARE aa INT ;
 SET aa=1;
WHILE aa<100 DO
           INSERT INTO traff_i_attackedmsg (id,eventid,beattack_ip,src_ip,src_province,src_nation,bags,ipflow, beg_time,end_time)
     (SELECT 10+round(rand()*1000,0),2,'192.168.1.4',src_ip,src_province,src_nation,bags,ROUND(RAND()*100,2),adddate( beg_time, interval - 3 hour),end_time
     FROM traff_i_attackedmsg );
 
  SET aa=aa+1;
  END WHILE;
END ;;
DELIMITER ;