数据库Mysql---之存储过程

1:存储过程中的流程控制

1:WHILE控制

    SET @a1=10;
    SET @a2=0;
    WHILE @a1>@a2 DO

        SET @a1 = @a1-1;      

    END WHILE;

2:IF 控制
   
    SET @a1=1;
    SET @a2=2;
    IF @a1 >@a2 THEN

       SELECT "ok";
    END IF;


3:LOOP  跳转


   out_loop:LOOP

        IF 1>0 THEN
          
            LEAVE out_loop; # 跳转离开，否则会一直循环
        END IF;

   END LOOP out_loop;

 

2:两个存储过程实例

# IN 传入变量   
CREATE DEFINER=`作者名` PROCEDURE `存储过程名`(IN `begintime` varchar(50),IN `endtime` varchar(50))
BEGIN
    #Routine body goes here...
    # 传入的参数  SET &a1=1  是设置变量
    SET @agr_begin_date = begintime;
    SET @arg_end_date   = endtime;
    # 转成时间戳
    SET @agr_begin_time = UNIX_TIMESTAMP(@agr_begin_date);
    SET @agr_end_time   = UNIX_TIMESTAMP(@arg_end_date);

    # 将查询的结果赋值给变量
    SET @log_min := (SELECT MIN(id) FROM t_xx_log WHERE STimestamp>=@agr_begin_time);
    SET @log_max := (SELECT MAX(id) FROM t_xx_log WHERE STimestamp<= @agr_end_time);
    
    # 创建视图 减小查找数据量
    # 因为创建视图语句里 不能有变量，因此直接拼成字符串执行
    DROP VIEW IF EXISTS view_xx_log;
    SET @sqlstr =  CONCAT('CREATE VIEW view_xx_log AS (select * from  t_xxx_log WHERE id>=', @log_min,' AND id<=',@log_max, ');');
    
    # PREPARE 绑定语句，EXECUTE  执行语句，DEALLOCATE PREPARE 释放绑定
    PREPARE stmt1 FROM        @sqlstr;
    EXECUTE  stmt1;
    DEALLOCATE PREPARE stmt1;

    DROP TABLE IF EXISTS tmp_table;
    CREATE TEMPORARY TABLE tmp_table (
        sdate varchar(20) default '',
        method varchar(30) default '',
    num varchar(10) default '',
        gold int default 0
    );


    SET @data_time_begin = @agr_begin_time;
    SET @data_time_end    = @agr_end_time;
    # WHILE 循环判断
    WHILE @data_time_begin <= @data_time_end DO
        
        SET @show_date := FROM_UNIXTIME(@data_time_begin,'%Y-%m-%d');
        SET @query_date := CONCAT( @show_date, '%');

        SET @query_begin_sec = @data_time_begin;
        SET @query_end_sec =@data_time_begin + 24*60*60;

        SET @data_time_begin := @data_time_begin + 24*60*60;

        INSERT INTO tmp_table (sdate, method,num,gold)
        SELECT @show_date,"add_new_player_num" ,'1',COUNT(PlayerID) 
        FROM view_xx_log
        WHERE STimestamp>=@query_begin_sec AND  STimestamp<@query_end_sec AND EventID=9

        UNION
        SELECT @show_date,"renshu",'1',COUNT(DISTINCT(PlayerID)) 
        FROM view_xx_log
        WHERE STimestamp>=@query_begin_sec AND  STimestamp<@query_end_sec AND 
        EventID=8
;

    END WHILE;

SELECT * FROM tmp_table;

END

a:在存储构成中使用游标

CREATE DEFINER=`作者名` PROCEDURE `存储过程名`()
BEGIN
	#Routine body goes here...
	# DECLARE 要放在前面，否则会报错，定义可以再游标中使用的变量
	DECLARE m_playerID_01 INT;
	DECLARE m_playerID_02 INT;
	DECLARE m_flag INT;
	
    # 游标绑定 sql语句
	DECLARE m_cursor_01  CURSOR FOR SELECT PlayerID FROM t_xx WHERE UserID NOT LIKE 'czx_%';

    # 绑定变量，当游标跑完的时候将 修改该变量值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET m_flag =1;
	# 设置变量
    SET m_flag =0;

    # 开启游标   
	OPEN m_cursor_01;
    out_loop:LOOP	
            # FETCH  将游标m_cursor_01 执行的子项结果赋值给变量m_playerID_01
            FETCH m_cursor_01 INTO m_playerID_01;

            # 判断语句，当绑定的变量改变，说明游标变量完了，退出循环
            IF m_flag = 1 THEN
               LEAVE out_loop;
            END IF;
            SELECT m_playerID_01;
			
     END LOOP out_loop;
  # 释放游标绑定
  CLOSE m_cursor_01;

END