MySQL存储过程测试语句

1. 传入字符串并切割

   drop procedure if exists sp_demo;
   
   DELIMITER $$
   CREATE PROCEDURE sp_demo(IN user_acounts VARCHAR(255))
   BEGIN
   
   DECLARE PointerIdx int default 1;
   DECLARE PointerNum int;
   DECLARE account VARCHAR(20);
   
   set PointerNum=LENGTH(user_acounts)-LENGTH(REPLACE(user_acounts,',',''))+1;
   while PointerIdx <= PointerNum do
   		set account=SUBSTRING_INDEX(SUBSTRING_INDEX(user_acounts,',',PointerIdx), ',', -1); 
   		SELECT PointerNum, PointerIdx, account;
   		set PointerIdx=PointerIdx+1;
   end while;
   
   END $$
   DELIMITER ;
   
   CALL sp_demo('user1,user2,user3');
   

2. 根据传入字符串分割后作为条件查询数据

   -- 根据账号列表，查询对应UID
   drop procedure if exists FindUidByAccounts;
   
   delimiter $$
   create procedure FindUidByAccounts(in accounts text)
   begin
   select User_ID from tb_User where User_Account in (
   	SELECT 
   			SUBSTRING_INDEX(SUBSTRING_INDEX(accounts,',',help_topic_id+1),',',-1) AS num 
   	FROM 
   			mysql.help_topic  
   	WHERE 
   			help_topic_id < LENGTH(accounts)-LENGTH(REPLACE(accounts,',',''))+1
   );
   end $$
   delimiter ;
   
   call FindUidByAccounts('user1,user2,user3');
   

3. 存储过程光标使用

   DROP PROCEDURE  IF EXISTS FindTopicId;
   
   DELIMITER $$
   CREATE PROCEDURE FindTopicId(IN max_id int)
   BEGIN
   DECLARE itmp BIGINT;
   DECLARE cur_id CURSOR FOR SELECT help_topic_id  FROM mysql.help_topic WHERE help_topic_id < max_id;
   DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
   OPEN cur_id;
   REPEAT
   	FETCH cur_id INTO itmp;
   		IF itmp < max_id THEN
   			SELECT itmp;
   		END IF;
   UNTIL 0 END REPEAT;
   CLOSE cur_id;
   END $$
   DELIMITER ;
   
   
   CALL FindTopicId(10)