远程操作建立存储过程

存储过程的建立，操作远程数据库的时候表名最好加入数据实例，通用的建立用

delimiter $$

 

END $$

delimiter;

 

 

 

=====================================================

 

delimiter $$

CREATE  PROCEDURE `PD_CD_CHK`(IN INPARA VARCHAR(1000),

   OUT OUTPARA  VARCHAR(1000))

BEGIN

 DECLARE V_ERRMSG VARCHAR(128);

 DECLARE fid VARCHAR(100);

 DECLARE str VARCHAR(100);

 declare tmpMobile varchar(20) default '' ;

 declare allMobile varchar(255) default '' ;

 DECLARE cntod bigint  DEFAULT 0 ;

 declare cur1 CURSOR FOR SELECT MOBILE FROM financial_sales_prod.tb_userinfo WHERE ISASSIGN is NULL  or ISASSIGN='' ;

 declare CONTINUE HANDLER FOR NOT FOUND SET tmpMobile = NULL;

 declare CONTINUE HANDLER FOR SQLEXCEPTION SET V_ERRMSG = '002';

   OPEN cur1;

    FETCH cur1 INTO tmpMobile;

   WHILE ( tmpMobile is not null) DO

    SET INPARA=tmpMobile;

  IF INPARA IS NULL  THEN

   SET V_ERRMSG ='传入参数错误';

     ELSE

SET fid =(

SELECT

COALESCE (

COALESCE (COALESCE(l2.MOBILE, l4.MOBILE), l6.MOBILE),

l8.MOBILE

) MOBILE

FROM

(

SELECT

MOBILE,

INVITER_INVITATION_CODE

FROM

financial_sales_prod.tb_userinfo aa

WHERE

aa.MOBILE = INPARA

) a LEFT JOIN financial_sales_prod.tb_tele_userinfo l ON a.MOBILE=l.MOBILE

LEFT JOIN financial_sales_prod.tb_userinfo b ON a.INVITER_INVITATION_CODE = b.INVITATION_CODE

LEFT JOIN financial_sales_prod.tb_tele_userinfo l2 ON b.MOBILE=l2.MOBILE

LEFT JOIN financial_sales_prod.tb_userinfo c ON b.INVITER_INVITATION_CODE = c.INVITATION_CODE

LEFT JOIN financial_sales_prod.tb_tele_userinfo l4 ON c.MOBILE=l4.MOBILE

LEFT JOIN financial_sales_prod.tb_userinfo d ON c.INVITER_INVITATION_CODE = d.INVITATION_CODE

LEFT JOIN financial_sales_prod.tb_tele_userinfo l6 ON d.MOBILE=l6.MOBILE

LEFT JOIN financial_sales_prod.tb_userinfo e ON d.INVITER_INVITATION_CODE = e.INVITATION_CODE

LEFT JOIN financial_sales_prod.tb_tele_userinfo l8 ON e.MOBILE=l8.MOBILE

WHERE

    1 = 1  AND (l.ID is   null or l.ID ='')

AND (

(l8.MOBILE IS NOT NULL AND l8.MOBILE !='')

OR (l6.MOBILE IS NOT NULL AND l6.MOBILE !='')

OR (l4.MOBILE IS NOT NULL AND l4.MOBILE !='')

OR (l2.MOBILE IS NOT NULL AND l2.MOBILE !='')

) limit 1

);

IF fid is not null THEN

SET str = fid;

    SELECT ID,TEMID,EMPTYPE INTO @empid ,@temid ,@odtyp  FROM financial_sales_prod.financial_sales_employment WHERE MOBILE=fid;

    IF (@empid IS NOT NULL) OR ( @temid IS NOT NULL) THEN

      SET @cnt = (SELECT COUNT(1) FROM financial_sales_prod.financial_sales_order WHERE MOBILE=INPARA);

      IF @cnt > 0 THEN

     UPDATE financial_sales_prod.tb_userinfo SET RE_ID=str,ISASSIGN='1' WHERE MOBILE=INPARA;

     ELSE

     START TRANSACTION;

    INSERT INTO financial_sales_prod.financial_sales_order(MOBILE, REGISTER_TIME, CUSTOMER_NAME, ISINVEST,ORDER_TYPE,EMP_ID, TEM_ID, CREATED_TIME, ASSIGN_TIME, ISAUTOASSIGN, STATUS) SELECT MOBILE, REG_TIME, NAME, TENDER_STATUS, @odtyp ,@empid, @temid,date(now()) ,date(now()) , '1' ,'1'  FROM financial_sales_prod.tb_userinfo WHERE MOBILE=INPARA;

     SET cntod=cntod+1;

    UPDATE financial_sales_prod.tb_userinfo SET RE_ID=str,ISASSIGN='1' WHERE MOBILE=INPARA;

        IF  V_ERRMSG = '002' THEN

          SET cntod=0;

         ROLLBACK;

        ELSE

         COMMIT;

       END IF;

    END IF;

      END IF;

  ELSE

   SET str = fid;

   SET V_ERRMSG ='仓单不存在！';

    END IF;

  END IF;

        FETCH cur1 INTO tmpMobile;

      END WHILE;

 CLOSE cur1;

SET OUTPARA = cntod;

END $$

delimiter;

 

 需要员工也在邀请关系表中有自己作为客户的邀请关系记录