Mysql存储过程新手尝试坑及其调

时间：2024-05-21 08:07:23

创建存储过程

创建固定模板

自定义部分解释：

p_xxx ---- 存储过程名称

（IN ... OUT ...）----存储过程入参出参（调用时出参需要在相同位置用参数@b来接收）

xxx为存储过程实际部分

DELIMITER //
  CREATE PROCEDURE P_XXX(IN a varchar(x),OUT b int)
    BEGIN
      xxx
    END
    //
DELIMITER ;

❤ 动态SQL例子 ❤

sql需要用到动态变量时，基本使用动态SQL来执行需要执行的SQL，如下为基本固定写法：


-- 1 label关系表v_labellist 2  打标任务表v_batchlist 3本次打标标签表v_temptaglist
  
SET labellist_e = CONCAT(labellist,coalesce(epidtb,''));
  
SET temptaglist_e = CONCAT('temptaglist',coalesce(epidtb,''));
  
SET @SQL1 = CONCAT('create or replace view v_batchlist as select guid from ',batchlist,coalesce(epidtb,''));
  
SET @SQL2 = CONCAT('create or replace view v_temptaglist as select label_guid from ', temptaglist_e,' where tag = ''',temptag,'''');


	PREPARE stmt FROM @SQL1;  -- 预处理需要执行的动态SQL，其中stmt是一个变量
	EXECUTE stmt; -- 执行SQL语句
	DEALLOCATE PREPARE stmt;  --  释放掉预处理段
    
    PREPARE stmt2 FROM @SQL2;  -- 预处理需要执行的动态SQL，其中stmt是一个变量
	EXECUTE stmt2; -- 执行SQL语句
	DEALLOCATE PREPARE stmt2;  --  释放掉预处理段

 

❤ 游标例子 ❤

做循环就需要使用到游标，游标的基本固定写法也是如下：


  DECLARE done int DEFAULT FALSE;
-- 1 循环出要打的customer-guid
  DECLARE cur_labelid CURSOR FOR select label_guid from v_temptaglist;
   
OPEN cur_labelid;

read_loop: LOOP
    --  提取游标里的数据，这里只有一个，多个的话也一样；
FETCH cur_labelid INTO  v_guid;
    --  声明结束的时候

    IF done THEN
	      LEAVE read_loop;
    END IF;

/*实际执行代码段位置*/

  end LOOP;
  close cur_labelid;

❤ 传参可能为null的参数 ❤

如果传参可能为null的参数，请完全使用以下写法替换，否则会报错的

把写参数epid的地方全部用coalesce(epidtb,'')替换

SET labellist_e = CONCAT(labellist,coalesce(epidtb,''));

 

成功调试出的创建存储过程例子

最后调试出来可运行的‘给客户批量贴标签’的存储过程实例

DELIMITER //
  CREATE PROCEDURE jh.p_customerlabeling_batch(IN batchlist VARCHAR(50),IN labellist VARCHAR(50),temptag varchar(20),epidtb char(7),OUT result INT)
BEGIN
  /*
  2-1 取到所有参数 1打标表batchlist，2标签关系表labellist，3标签temptaglist的tag字段temptag，4epid 5 out result 
  2-2 取到要打的标guid列表v_xxx： temptaglist&tag 
  */
--  declare v_int int;
	declare v_guid char(36);
  declare v_batchlist varchar(50);/*需要批量达标的视图名：表名+epid（eg：batchmarking_bytype20180928）*/
  declare labellist_e varchar(50);/*打标关联表名+epid（eg：cutomerlabelo）*/
	declare temptaglist_e varchar(50);/**/
  declare v_temptaglist varchar(50);/*视图名：存储本次要打标的标签*/

  DECLARE done int DEFAULT FALSE;
-- 1 循环出要打的customer-guid
  DECLARE cur_labelid CURSOR FOR select label_guid from v_temptaglist;

  -- 1 label关系表v_labellist 2  打标任务表v_batchlist 3本次打标标签表v_temptaglist
	SET labellist_e = CONCAT(labellist,coalesce(epidtb,''));
  SET temptaglist_e = CONCAT('temptaglist',coalesce(epidtb,''));
  SET @SQL1 = CONCAT('create or replace view v_batchlist as select guid from ',batchlist,coalesce(epidtb,''));
  SET @SQL2 = CONCAT('create or replace view v_temptaglist as select label_guid from ', temptaglist_e,' where tag = ''',temptag,'''');


	PREPARE stmt FROM @SQL1;  -- 预处理需要执行的动态SQL，其中stmt是一个变量
	EXECUTE stmt; -- 执行SQL语句
	DEALLOCATE PREPARE stmt;  --  释放掉预处理段
  PREPARE stmt2 FROM @SQL2;  -- 预处理需要执行的动态SQL，其中stmt是一个变量
	EXECUTE stmt2; -- 执行SQL语句
	DEALLOCATE PREPARE stmt2;  --  释放掉预处理段
  
OPEN cur_labelid;
  read_loop: LOOP
    --  提取游标里的数据，这里只有一个，多个的话也一样；
    FETCH cur_labelid INTO  v_guid;
    --  声明结束的时候

    IF done THEN
	      LEAVE read_loop;
    END IF;
/*
  2-2 取到要打的标guid列表v_xxx： temptaglist&tag 
  2-3 游标循坏guid----call proceduce （1customer_label列表-拼接好epid 2本次打标的label_guid ）
*/
    CALL p_customerlabeling_single(labellist_e,v_guid);

/*
delete from v_labellist where relation_guid = cur_guid and relation_labelid = 
*/
  set result = 1;

  end LOOP;
  close cur_labelid;


/*尾*/
END
    //
DELIMITER ;

 

调试存储过程

 

神器 dbForge Studio for MySQL

官网：https://www.devart.com/dbforge/mysql/studio/editions.html

30天免费试用完全够够的

下载后连接上数据库，模拟传参，F10对存储过程进行一步步执行，可以看到哪一句产生报错。

Mysql存储过程新手尝试坑及其调

 

并且可以通过watchs对变量进行查看

Mysql存储过程新手尝试坑及其调

唯一有点不好的地方，是需要用root权限才能调试存储过程，连接公司存储过程失败了，只能dump到本机mysql进行调试。

 

相关文章