mysql 存储过程案例

drop PROCEDURE if exists test_20200429;

CREATE  PROCEDURE `test_20200429`()
begin 
    DECLARE num INT DEFAULT 0;
    declare v_sql_0 varchar(500);
    declare mainId BIGINT;
    declare faren varchar(500);
    
    declare data_list cursor for    SELECT rid FROM TD_ZY_UNITBASICINFO WHERE LEGAL_PERSON = '1' ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET num=1;
        open data_list ;
        fetch data_list into mainId;
        while num <> 1 do 
            -- 使用max函数防止N/A,导致无法用null判断；注意字符串 ！="1" 是双引号
                SET v_sql_0 = CONCAT('SELECT MAX(T.LEGAL_PERSON) into @param1 FROM TD_ZY_UNITBASICINFO2 T WHERE T.LEGAL_PERSON !="1" and  T.DECLARE_STATUS =3 and T.MAIN_ID = ',mainId,' ORDER BY    T.DECLARE_DATE DESC LIMIT 1');
                SET @sql_0 = v_sql_0;
                -- 预处理需要执行的动态SQL，其中stmt是一个变量
                PREPARE stmt0 FROM @sql_0;  
                -- 执行SQL语句
                EXECUTE stmt0;   
                -- 释放掉预处理段   
                DEALLOCATE PREPARE stmt0;     
                -- @param1赋值给rid
                SET faren = @param1;
                
                 -- null 判断的方式
                if faren is not  null then 
                    UPDATE TD_ZY_UNITBASICINFO set LEGAL_PERSON = faren wHERE rid = mainId;
             end if;                    
            fetch data_list into mainId;
        end while;
    close data_list;
end;

call test_20200429();