Mysql 存储过程,触发器,函数,游标示例

示例

 

##基本语法
if  条件  then
...
elseif  条件 then 
...
else
...
end if ;

while  条件  do
...
end while;


##存储过程
drop procedure if exists proc_temp_insert_data;
DELIMITER ;;
CREATE PROCEDURE  proc_temp_insert_data(in number int)
	comment"临时插入数据"
BEGIN
	declare num int;
	set num=0;
	set autocommit = 0 ;
	WHILE num < number do
		INSERT INTO etl_static_dayuser(user_num,create_date) VALUES(FLOOR(RAND()*1000),DATE(DATE_ADD('2015-6-1',INTERVAL  num DAY)) );

		IF MOD(num,5000) = 0  THEN
			COMMIT ;
		END IF ;  
		SET num=num+1;
	end while;
	set autocommit = 1 ;
end ;;
delimiter ;

##触发器
DROP TRIGGER IF EXISTS trir_ieso_ins;
DELIMITER ;;
CREATE TRIGGER trir_ieso_ins 
    AFTER UPDATE 
    ON ims_ewei_shop_order
    FOR EACH ROW 
BEGIN
    IF new.agentid <> old.agentid  THEN  
    UPDATE ims_ewei_shop_member SET agentid = 0 WHERE openid=new.openid AND agentid <> 0;
    END IF;
END ;;
DELIMITER ;


##函数
DROP FUNCTION IF EXISTS ten_to_two;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `ten_to_two`( COL bigint ) RETURNS varchar(100) CHARSET utf8mb4
BEGIN 
	DECLARE result_string VARCHAR(100) DEFAULT '' ;
	DECLARE two_string varchar(100) DEFAULT '';
	DECLARE part1 varchar(32) DEFAULT '';
	DECLARE part2 varchar(32) DEFAULT '';
	DECLARE part3 varchar(32) DEFAULT '';
	DECLARE temp_part2 varchar(32) default '';

IF (LENGTH(COL) = 18 ) THEN 
	SELECT CAST((CONV(COL,10,2)) AS CHAR ) INTO two_string ;    -- 将原18位数字转换为二进制字符串

	SELECT RIGHT(two_string,13) INTO part3 ;
	SELECT LEFT(RIGHT(two_string,34),21) INTO part2 ;
	SELECT LEFT(two_string,(LENGTH(two_string)-34)) INTO part1 ;

	SELECT  CONV( MOD(CONV(part2,2,10),1000),10,2)  INTO temp_part2 ;  -- 将第二部分结果先转换为十进制，除以1000，取余数，再将余数转换为二进制

	WHILE LENGTH(temp_part2) < 10  do 
		SET temp_part2 = CONCAT('0',temp_part2);           -- 如果第二部分处理结果不够10位长度，再在前面补0，直到满足10位长度
	end while ;

	SET result_string=CONV( CONCAT(part1,temp_part2,part3) ,2,10 );   -- 重新拼接二进制串并转换为十进制

ELSE 
	SET result_string = COL ;
END IF ;

	RETURN result_string;
END;;
delimiter ;

CREATE DEFINER=`root`@`%` FUNCTION `lpad_tag_no`(COL VARCHAR(32)) RETURNS varchar(100) CHARSET utf8mb4
BEGIN 
	DECLARE res1 varchar(32) DEFAULT '';
	DECLARE part1 varchar(32) DEFAULT '';
	DECLARE part2 varchar(32) DEFAULT '';
	DECLARE part3 varchar(32) DEFAULT '';
	
	SELECT substring_index(COL,'.',1) INTO part1 ;
	SELECT substring_index(COL,'.',2) INTO part2 ;
	SELECT substring_index(COL,'.',-1) INTO part3 ;
	
	select substring(part2,LENGTH(part1)+2) INTO part2 ;
	SELECT substring_index(part3,'-',1) INTO part3 ;

	SET res1 = CONCAT(LPAD(part1,3,0),".",LPAD(part2,3,0),".",LPAD(part3,3,0)) ;
	RETURN res1;
END


##游标
DROP FUNCTION IF EXISTS rand_str;
delimiter ;;
create FUNCTION rand_str(strlen SMALLINT ) RETURNS VARCHAR(255)
BEGIN
    DECLARE randStr VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
    DECLARE i SMALLINT DEFAULT 0;
    DECLARE resultStr VARCHAR(255) DEFAULT '';
    WHILE i<strlen DO
        SET resultStr=CONCAT(SUBSTR(randStr,FLOOR(RAND()*LENGTH(randStr))+1,1),resultStr);
        SET i=i+1;
    END WHILE;
    RETURN resultStr;
END ;;
delimiter ;



DELIMITER $$
DROP PROCEDURE IF EXISTS `curTest`$$
CREATE PROCEDURE curTest()
BEGIN
	-- 定义变量用于接收游标的结果
	DECLARE cnos varchar(12) default '';
	DECLARE cnames varchar(24) default '';
  -- 遍历数据结束标志
  DECLARE done INT DEFAULT TRUE;
  -- 定义游标
  DECLARE cur CURSOR FOR select cno,cname from course;
  -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = FALSE;
  -- 打开游标
  OPEN cur;
  -- 提取游标的下一个值
	FETCH cur INTO cnos,cnames;
	WHILE done  DO 
		SELECT cnos,cnames;
		-- 代码体
		FETCH cur INTO cnos,cnames;
	END WHILE;
	-- 关闭游标
  CLOSE cur; 
END$$
DELIMITER ;