MySQL存储过程语法

先上一段代码

/*
创建字符串split函数
*/
DROP FUNCTION
IF EXISTS SPLIT_STR;
CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

/*
创建同步店铺头像的存储过程
*/
DROP PROCEDURE
IF EXISTS SCHEMA_CHANGE_1;
DELIMITER $$
CREATE PROCEDURE SCHEMA_CHANGE_1 ()
BEGIN
    declare _merchant_id bigint;
    declare _shop_icon varchar(1500);
    DECLARE done INT DEFAULT FALSE; 
    Declare maxPicId bigint;
    declare cur cursor for (
    select merchant_id,concat("https://",wenjianlj,".oss-cn-hangzhou.aliyuncs.com/",wenjianmc) as path
from (
select t2.merchant_id,SPLIT_STR(shanghutp,"_",1) as guid,SPLIT_STR(shanghutp,"_",2) as verfiCode
from (select distinct merchant_id
        from (
        select
        a.merchant_id as merchant_id,
        b.id as picId
        from mdse_shop as a
        left join mdse_pic as b on a.shop_icon=b.id) t1 where picId is null) t2
inner join yygl_v_shangjiaxx as shangjia on t2.merchant_id=shangjia.shanghubh) t3
inner join xtgy_wenjianccsy as wenjian on t3.guid=wenjian.guid and t3.verfiCode=wenjian.yanzhengma
    );
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 监听器  
    set maxPicId = (select id from mdse_pic order by id desc limit 1);
    open cur;
    label:LOOP  
        FETCH cur INTO _merchant_id,_shop_icon;  
        select _merchant_id,_shop_icon;
        set maxPicId = (maxPicId + 1);
        insert into mdse_pic(id,path) value(maxPicId,_shop_icon);   -- 添加图片记录
        update mdse_shop set shop_icon= maxPicId where merchant_id=_merchant_id;  -- 门店关联图片记录主键
        IF done THEN LEAVE label;END IF;  
    END LOOP label; 
    close cur; 
END$$
DELIMITER ;


CALL SCHEMA_CHANGE_1 ();

　　

1、删除函数语法

drop function 函数名称;

通常会和if exists联合使用。drop function if exists 函数名称;

 

2、创建函数语法

create function 函数名称（

参数名称 参数类型,

参数名称 参数类型,

...

）

returns 返回值类型

return {

sql 逻辑语句

}

 

3、删除存储过程

drop procedure 存储过程名称;

通常会和if exists联合使用，drop procedure if exists 存储过程名称;

 

4、指定指令结束符 开始

DELIMITER $$

 

5、重新指定指令结束符为;

DELIMITER ;

 

6、创建存储过程语法

create procedure 存储过程名称()

begin

存储过程内容

end 指令结束符

 

7、声明存储过程变量

declare 变量名称  变量类型 [default 默认值];

 

8、修改变量值

set 变量名称=变量值;

 

9、声明游标

declare 游标名称 cursor for (select查询);

 

10、声明游标监听器

DECLARE done INT DEFAULT FALSE; 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 监听器  

如果找不到下一条记录，则把一个标志位变更

 

11、开启游标，关闭游标

open cur;

操作游标

close cur;

 

12、遍历游标

label:LOOP 

FETCH cur INTO _merchant_id,_shop_icon;  

IF done THEN LEAVE label;END IF;  

END LOOP label; 

 

13、从游标获取一行记录到变量中

FETCH cur INTO _merchant_id,_shop_icon

注意一点 游标中的列名称不能和变量名重复，负责不会对变量进行赋值

 

14、调用存储过程

CALL 存储过程名称 ();