SQLsummary/存储过程/共享奖上拿1.0.sql

CREATE DEFINER=`bi`@`%` PROCEDURE `BONUS_GX_UP`( `PRM_PERIOD_NUM` INT)
BEGIN#Routine body goes here...
    DECLARE
    Done INT DEFAULT 0;
    DECLARE
    R1_USER_NAME VARCHAR ( 30 );
    DECLARE
    R1_REAL_NAME VARCHAR ( 50 );
    DECLARE
    R1_PARENT_NAME VARCHAR ( 30 );
    DECLARE
    GERENTUANDUI DECIMAL( 16,3 );
    DECLARE
    JIJIANGDAISHU INT DEFAULT 0;
    DECLARE
    R2_USER_NAME VARCHAR ( 30 );
    DECLARE
    R2_REAL_NAME VARCHAR ( 50 );
    DECLARE
    PARENT_USER_NAME VARCHAR ( 30 );
    DECLARE
    PARENT_REAL_NAME VARCHAR ( 50 );
    DECLARE
    ORI_BONUS_QY_BD_BL DECIMAL ( 16, 3 );
	DECLARE
	SHIJIDAISHU INT DEFAULT 0;
    /* 声明游标 */
    DECLARE
    rs CURSOR FOR
    SELECT
              U.USER_NAME
            , U.REAL_NAME
            , P.PV_PCS + P.PV_PSS AS GERENTUANDUI
            , U2.USER_NAME        AS PARENT_NAME
    FROM
              AR_PERF_PERIOD P
              LEFT JOIN
                        AR_USER U
                        ON
                                  U.ID = P.USER_ID
              LEFT JOIN
                        ar_user_relation_new R
                        ON
                                  R.USER_ID = U.ID
              LEFT JOIN
                        ar_user U2
                        ON
                                  U2.ID = R.PARENT_UID
    WHERE
              P.PERIOD_NUM = PRM_PERIOD_NUM
              AND
              (
                        XIAOYEJI(U.USER_NAME, PRM_PERIOD_NUM)
              )
              >= 980
    ;
    
    /* 异常处理 */
    DECLARE
    CONTINUE HANDLER FOR SQLSTATE '02000'
    SET Done = 1;
    /* 打开游标 */
    OPEN rs;
    /* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */
	DELETE FROM ek_temp_gx_zs_cs;
    FETCH NEXT
    FROM
          rs
    INTO
          R1_USER_NAME
        , R1_REAL_NAME
        , GERENTUANDUI
        , R1_PARENT_NAME
    ;
    
    /* 遍历数据表 */
    REPEAT
        SET JIJIANGDAISHU = 1;
				SET SHIJIDAISHU = 0;
        REPEAT
            SET R2_USER_NAME         = NULL;
            SET ORI_BONUS_QY_BD_BL = 0;
            SELECT
                      U.`会员编号`
                    , U.`会员姓名`
                    , U.`推荐编号`
                    , U.`推荐姓名`
                    , IFNULL(B.ORI_BONUS_QY_BD,0)
            INTO
                      R2_USER_NAME
                    , R2_REAL_NAME
                    , PARENT_USER_NAME
                    , PARENT_REAL_NAME
                    , ORI_BONUS_QY_BD_BL
            FROM
                      zr_user_vw U
                      LEFT JOIN
                                AR_CALC_BONUS B
                                ON
                                          U.`会员编号`         = B.LAST_USER_NAME
                                          and B.PERIOD_NUM = PRM_PERIOD_NUM
            WHERE
                      U.`会员编号` = R1_PARENT_NAME
            ;
            
            SET Done = 0;
						SET SHIJIDAISHU = SHIJIDAISHU + 1;
						IF  ORI_BONUS_QY_BD_BL = 0 THEN
						INSERT INTO ek_temp_gx_zs_cs
                       ( `得奖人编号`
                            , `得奖人姓名`
                            ,`个人团队`
                            , `给奖人编号`
                            , `给奖人姓名`
                            , `计奖代数`
                            , `给奖人报单团队奖`
							, `实际代数`
                       )
                       VALUES
                       ( R1_USER_NAME
                            , R1_REAL_NAME
                            , GERENTUANDUI
                            , R2_USER_NAME
                            , R2_REAL_NAME
                            , JIJIANGDAISHU
                            , ORI_BONUS_QY_BD_BL
							, SHIJIDAISHU
                       )
                ;
								
						END IF;
            IF
                ORI_BONUS_QY_BD_BL > 0 THEN
                
                INSERT INTO ek_temp_gx_zs_cs
                       ( `得奖人编号`
                            , `得奖人姓名`
                            ,`个人团队`
                            , `给奖人编号`
                            , `给奖人姓名`
                            , `计奖代数`
                            , `给奖人报单团队奖`
							, `实际代数`
                       )
                       VALUES
                       ( R1_USER_NAME
                            , R1_REAL_NAME
                            , GERENTUANDUI
                            , R2_USER_NAME
                            , R2_REAL_NAME
                            , JIJIANGDAISHU
                            , ORI_BONUS_QY_BD_BL
							, SHIJIDAISHU
                       )
                ;
								SET JIJIANGDAISHU       = JIJIANGDAISHU + 1;
            END IF;
						SET R1_PARENT_NAME = PARENT_USER_NAME;
            UNTIL JIJIANGDAISHU >= 3 AND ORI_BONUS_QY_BD_BL >0 OR R2_USER_NAME IS NULL
        END REPEAT;
        -- INSERT INTO ORI_BONUS_GX_CS(USER_NAME,REAL_NAME,GERENTUANDUI,PARENT_NAME,ORI_BONUS_GX) VALUES (USER_NAME,REAL_NAME,GERENTUANDUI,PARENT_NAME,ORI_BONUS_GX);
        FETCH NEXT
        FROM
              rs
        INTO
              R1_USER_NAME
            , R1_REAL_NAME
            , GERENTUANDUI
            , R1_PARENT_NAME
        ;
        
        UNTIL Done
    END REPEAT;
    /* 关闭游标 */
    CLOSE rs;
END