MySQL存储过程语法

先上一段代码

1/*
2创建字符串split函数
3*/
4DROP FUNCTION
5IF EXISTS SPLIT_STR;
6CREATE FUNCTION SPLIT_STR(
7  x VARCHAR(255),
8  delim VARCHAR(12),
9  pos INT
10)
11RETURNS VARCHAR(255)
12RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
13       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
14       delim, '');
15
16/*
17创建同步店铺头像的存储过程
18*/
19DROP PROCEDURE
20IF EXISTS SCHEMA_CHANGE_1;
21DELIMITER $$
22CREATE PROCEDURE SCHEMA_CHANGE_1 ()
23BEGIN
24    declare _merchant_id bigint;
25    declare _shop_icon varchar(1500);
26    DECLARE done INT DEFAULT FALSE; 
27    Declare maxPicId bigint;
28    declare cur cursor for (
29    select merchant_id,concat("https://",wenjianlj,".oss-cn-hangzhou.aliyuncs.com/",wenjianmc) as path
30from (
31select t2.merchant_id,SPLIT_STR(shanghutp,"_",1) as guid,SPLIT_STR(shanghutp,"_",2) as verfiCode
32from (select distinct merchant_id
33        from (
34        select
35        a.merchant_id as merchant_id,
36        b.id as picId
37        from mdse_shop as a
38        left join mdse_pic as b on a.shop_icon=b.id) t1 where picId is null) t2
39inner join yygl_v_shangjiaxx as shangjia on t2.merchant_id=shangjia.shanghubh) t3
40inner join xtgy_wenjianccsy as wenjian on t3.guid=wenjian.guid and t3.verfiCode=wenjian.yanzhengma
41    );
42    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 监听器  
43    set maxPicId = (select id from mdse_pic order by id desc limit 1);
44    open cur;
45    label:LOOP  
46        FETCH cur INTO _merchant_id,_shop_icon;  
47        select _merchant_id,_shop_icon;
48        set maxPicId = (maxPicId + 1);
49        insert into mdse_pic(id,path) value(maxPicId,_shop_icon);   -- 添加图片记录
50        update mdse_shop set shop_icon= maxPicId where merchant_id=_merchant_id;  -- 门店关联图片记录主键
51        IF done THEN LEAVE label;END IF;  
52    END LOOP label; 
53    close cur; 
54END$$
55DELIMITER ;
56
57
58CALL SCHEMA_CHANGE_1 ();

1、删除函数语法

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

2、创建函数语法

1create function 函数名称（
2
3参数名称 参数类型,
4
5参数名称 参数类型,
6
7...
8
9）
10
11returns 返回值类型
12
13return {
14
15sql 逻辑语句
16
17}

3、删除存储过程

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

4、指定指令结束符开始

DELIMITER $$

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

DELIMITER ;

6、创建存储过程语法

1create procedure 存储过程名称()
2
3begin
4
5存储过程内容
6
7end 指令结束符

7、声明存储过程变量

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

8、修改变量值

set 变量名称=变量值;

9、声明游标

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

10、声明游标监听器

1DECLARE done INT DEFAULT FALSE; 
2
3DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 监听器  
4
5如果找不到下一条记录，则把一个标志位变更

11、开启游标，关闭游标

1open cur;
2
3操作游标
4
5close cur;

12、遍历游标

1label:LOOP 
2
3FETCH cur INTO _merchant_id,_shop_icon;  
4
5IF done THEN LEAVE label;END IF;  
6
7END LOOP label;

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

1FETCH cur INTO _merchant_id,_shop_icon
2
3注意一点 游标中的列名称不能和变量名重复，负责不会对变量进行赋值

14、调用存储过程

CALL 存储过程名称 ();

MySQL存储过程语法

评论区

相关推荐