mysql 存储过程脚本案例_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();