create procedure Pro_GetUnderOrg(in userid varchar(50))

begin

declare lev int;

set lev=1;

drop table if exists tmp1;

    CREATE TABLE tmp1(`ID` BIGINT,LoginID VARCHAR(50),Username varchar(50),pre_LoginID varchar(50) ,levv INT);

    INSERT tmp1 SELECT `ID`, LoginID,Username,pre_LoginID,1 FROM tbUsers WHERE pre_LoginID=userid;

while  row_count()>0

do

     set lev=lev+1;

     INSERT tmp1 SELECT t.`ID`, t.LoginID,t.Username,t.pre_LoginID,lev from tbUsers t join tmp1 a on t.pre_LoginID=a.LoginID AND levv=lev-1;

end while ;

    INSERT tmp1 SELECT `ID`,LoginID,Username,pre_LoginID,0 FROM tbUsers WHERE LoginID=userid;

     SELECT * FROM tmp1 where LoginID <>userid;/*这一步是为了去除自己*/

end;

create PROCEDURE Pro_JH(in curr VARCHAR(50),in target VARCHAR(50),in pre VARCHAR(50))

BEGIN

DECLARE Li CHAR(18);

DECLARE t_error INTEGER  DEFAULT 0;  /*注意：申明字段 必须在前面 否则会报错*/

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

SET Li='';

START TRANSACTION;

select License INTO Li from tbLicense where UserID=curr and hasUsed=0 and Actual is NOT NULL and IsDele=0 LIMIT 1;

IF Li='' THEN

ROLLBACK;

SELECT '该用户没有可用激活码！';

ELSE

update tbLicense set hasUsed=1  where  License= Li and UserID=curr;

update tbUserMeny set Total=Total-1,Available=Available-1 where userid=curr and type=2;

INSERT into tbLicenseWallet_Log (userid,ChangeAmount,License,Remark,Price) VALUES ( curr,1,Li,CONCAT('为',target,'激活账户'),(select `Value` from tbdictionary where `Key`='jhmdj'));

UPDATE tbusers set License=Li,`Status`=1,`Role`=(select ID from tbrole where rolecode='VIP') where LoginID = target and pre_LoginID =pre;

IF t_error = 1 THEN

            ROLLBACK;

                        SELECT '错误';

        ELSE

            COMMIT;

                        SELECT '';

        END IF;

END IF;

END;

UPDATE tabl1

    SET filter1 = CASE id

        WHEN 1 THEN 3

        WHEN 2 THEN 4

        WHEN 3 THEN 5

    END

WHERE id IN (1,2,3)

/*获取上级所有的节点 一条语句中只能试用一次临时表*/

create PROCEDURE Pro_GetParents(in sid INT)

BEGIN

declare lev INT;

DECLARE userid VARCHAR(50);/*pre*/

select(select pre_LoginID from tbusers u where u.LoginID = temp.userid and IsDele=0) INTO userid from tbgethelp temp where temp.id=(SELECT temp.getid from tbhelp_successlist temp where id=sid);

set lev=1;

CREATE TEMPORARY TABLE if not exists tmp2(`ID` BIGINT,`Status` TINYINT(1),LoginID VARCHAR(50),Username varchar(50),pre_LoginID varchar(50),Mobile CHAR(15),CreateTime timestamp,levv INT)

ENGINE = memory;

truncate TABLE tmp2;

CREATE TEMPORARY TABLE if not exists tmp1(`ID` BIGINT,`Status` TINYINT(1),LoginID VARCHAR(50),Username varchar(50),pre_LoginID varchar(50),Mobile CHAR(15),CreateTime timestamp,levv INT)

ENGINE = memory;

truncate TABLE tmp1;

INSERT tmp2 SELECT `ID`,`Status`,LoginID,Username,pre_LoginID,Mobile,CreateTime,lev FROM tbUsers WHERE LoginID=userid and IsDele =0;

INSERT tmp1 select * from tmp2;

while row_count()>0 and EXISTS(SELECT * from tmp1)

do

     set lev=lev+1;

     INSERT tmp2 SELECT t.`ID`,t.`Status`, t.LoginID,t.Username,t.pre_LoginID,t.Mobile,t.CreateTime,lev from tbUsers t join tmp1 a on t.LoginID = a.pre_LoginID where a.LoginID<>a.pre_LoginID and t.IsDele = 0 AND a.levv=(lev-1);

         if        row_count()>0 THEN

            TRUNCATE TABLE tmp1;

            INSERT tmp1 select * from tmp2 WHERE levv=lev;

         END IF;

end while;

     SELECT `ID`,`Status`,CASE `Status` when 0 then '未激活' else '已激活' end as StatusName,Username,LoginID,pre_LoginID,Mobile,date_format(CreateTime,'%Y-%c-%d %h:%i:%s')CreateTime,levv FROM tmp2 ORDER BY CreateTime DESC;

         DROP TEMPORARY TABLE IF EXISTS tmp2;

         DROP TEMPORARY TABLE IF EXISTS tmp1;

end

--查询编码

show variables like 'char%'

--修改编码

character_set_server='utf8'; 

GRANT ALL PRIVILEGES ON 数据库.* TO 用户名@"%" IDENTIFIED BY "密码";