mysql 存储过程实例

BEGIN


 DECLARE i INT(7) DEFAULT 0;
 DECLARE inst_db_table1_name_cnt INT(7) DEFAULT 0;
 DECLARE total_inst_db_table1_name_cnt INT(7) DEFAULT 0;
 DECLARE del_db_table2_name_cnt INT(7) DEFAULT 0;
 DECLARE total_del_db_table2_name_cnt INT(7) DEFAULT 0;
 DECLARE del_db_table3_name_cnt INT(7) DEFAULT 0;
 DECLARE total_del_db_table3_name_cnt INT(7) DEFAULT 0;


 REPEAT SET i = i + 1; 


 　　 INSERT INTO 
db_table1_name 
SELECT 
* 
FROM 
db_table2_name 
WHERE 
key_id=TRIM((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(parno, ',', i), ',', -1)));

SELECT ROW_COUNT() INTO inst_db_table1_name_cnt;  
SET total_inst_db_table1_name_cnt = total_inst_db_table1_name_cnt + inst_db_table1_name_cnt;


DELETE FROM 
db_table2_name 
WHERE 
   key_id=TRIM((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(parno, ',', i), ',', -1)));

SELECT ROW_COUNT() INTO del_db_table2_name_cnt;  
SET total_del_db_table2_name_cnt = total_del_db_table2_name_cnt + del_db_table2_name_cnt;


DELETE FROM 
db_table3_name 
WHERE 
key_id=TRIM((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(parno, ',', i), ',', -1)));

SELECT ROW_COUNT() INTO del_db_table3_name_cnt;  
  　　SET total_del_db_table3_name_cnt = total_del_db_table3_name_cnt + del_db_table3_name_cnt;
  
  UNTIL i >= (SELECT length(TRIM(parno))-length(replace(TRIM(parno),',',''))+1)

 END REPEAT;


 SELECT total_inst_db_table1_name_cnt,total_del_db_table2_name_cnt,total_del_db_table3_name_cnt;
 
END
----------パラメタ------------

IN parno VARCHAR(50),OUT  total_inst_db_table1_name_cnt INT(7),OUT total_del_db_table2_name_cnt INT(7) ,OUT  total_del_db_table3_name_cnt INT(7)

----------実行方法：------------

CALL nowtest('1,2',@a,@b,@c);

----------テスト方法：------------
select
(select count(*)  from db_table2_name
WHERE
key_id IN ('1','2')) as db_table2_name_cnt
 ,
(select count(*)  from db_table1_name
WHERE
key_id IN ('1','2') ) as  db_table1_name_cnt,
(select count(*) from db_table3_name
WHERE
key_id IN ('1','2'))  as db_table3_name_cnt ;
----------------------