存储过程模板

MySQL 存储过程常用语法合集

CREATE DEFINER=`dev`@`localhost` PROCEDURE `p_data_send`(
  p__id  int,			
  p_Date datetime, 
)
BEGIN		
                DECLARE done INT DEFAULT 0;
		DECLARE v_a  varchar(20); 				-- 当前游标指定的值
		DECLARE v_b varchar(20); 				-- 当前游标指定的值
		DECLARE v_c varchar(20); 				-- 当前游标指定的值
						 
		
	
		DECLARE sendlist CURSOR FOR                                    -- 创建游标
		 SELECT a from [table];
            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;	        -- 游标最后一条为空时的操作 
		
	SET done = 0;
	OPEN sendlist;
	SET done=0; sendlist INTO xxx; -- 游标指定当前值
        while done = 0 do
              -- 进行逻辑操作
          	IF p_ReCreate = 1 then 	 
      				
         	end if;

                If        v_oil_use <= v_oil_stand  then 
      
                elseif   v_oil_use > v_oil_stand  then

                END IF;
                CASE
                WHEN y.type = '01' THEN
                '到达'
                WHEN y.type = '02' THEN
                '结束'
                END;

		SET done=0;FETCH sendlist INTO xxx; -- 游标指定当前值
	end while;
	
	CLOSE sendlist;

END

---

MySQL 游标双层循环方法

DROP PROCEDURE IF EXISTS pro_cursor_nest;
CREATE PROCEDURE pro_cursor_nest()
BEGIN -- out BEGIN
		DECLARE c_name VARCHAR(200) ;
		DECLARE out_done INT DEFAULT FALSE ; -- 外层游标控制变量
		DECLARE out_cursor CURSOR FOR (SELECT NAME FROM client ); -- 外层游标
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET out_done = TRUE ; -- 外层游标执行结束，置为TRUE
		
		OPEN out_cursor ; -- 打开外层游标
		WHILE NOT out_done DO -- out WHILE
				FETCH out_cursor INTO c_name ; -- 从【外层游标】中获取数据，赋值到定义变量中
				IF NOT out_done THEN -- out IF
						-- 开始定义内层游标
						BEGIN -- inner BEGIN
								DECLARE money INT ;
								DECLARE inner_done int DEFAULT FALSE ;
								DECLARE inner_cursor CURSOR FOR ( SELECT balance FROM account  );
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET inner_done = TRUE ;
								
								OPEN inner_cursor ; -- 打开内层游标
								WHILE NOT inner_done DO -- inner WHILE
									FETCH inner_cursor INTO money ; -- 从【内层游标】中获取数据，赋值到定义变量中
									IF NOT inner_done THEN
										INSERT INTO `batch` (`id`, `name`, `age`) VALUES (UUID(),c_name ,money);
									END IF;
								END WHILE ; -- END inner WHILE
								CLOSE inner_cursor; -- 循环结束后，关闭内层游标
						END; -- END inner BEGIN
				END IF; -- END out IF
		END WHILE; -- END out WHILE
		CLOSE out_cursor ; -- 循环结束后 ，关闭外层游标
END; -- END out BEGIN