mysql 存储过程

 1. 跳出/return出 存储过程 

给整个存储过程的入口打了一个标记，当在遇到需要退出存储过程时，只要 leave + 此标记即可。

CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
proc_label:BEGIN
     IF tablename IS NULL THEN
          LEAVE proc_label; -- 跳出存储过程，proc_label是个别名，在begin前起的别名
     END IF;

2. 游标

1> 创建游标

create procedure simplecursor()
begin
    declare youbiaoName cursor
    for
    select name from user;
    
    open youbiaoName;  -- 打开游标
    -- //some code
    close youbiaoName; -- 关闭游标,释放游标使用的所有内部内存和资源
end;

BEGIN  
  
--定义变量  
declare testrangeid BIGINT;  
declare versionid BIGINT;   
declare done int;  
--创建游标，并存储数据  
declare cur_test CURSOR for   
   select id as testrangeid,version_id as versionid from tp_testrange;  
--游标中的内容执行完后将done设置为1  
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   
--打开游标  
open cur_test;  
--执行循环  
  posLoop:LOOP  
--判断是否结束循环  
        IF done=1 THEN    
      LEAVE posLoop;  
    END IF;   
--取游标中的值  
    FETCH  cur_test into testrangeid,versionid;  
--执行更新操作  
    update tp_data_execute set version_id=versionid where testrange_id = testrangeid;  
  END LOOP posLoop;  
--释放游标  
CLOSE cur_test;  
  
END  

2>游标嵌套

CREATE PROCEDURE StatisticStore3()  
BEGIN  
    declare _n varchar(20);  
    declare done int default false;  
    declare cur cursor for select name from store group by name;  
    declare continue HANDLER for not found set done = true;  
    open cur;  
    read_loop:loop  
    fetch cur into _n;  
    if done then  
        leave read_loop;  
    end if;  
    begin  
        declare c int;  
        declare n varchar(20);  
        declare total int default 0;  
        declare done int default false;  
        declare cur cursor for select name,count from store where name = 'iphone';  
        declare continue HANDLER for not found set done = true;  
        set total = 0;  
        open cur;  
        iphone_loop:loop  
        fetch cur into n,c;  
        if done then  
            leave iphone_loop;  
        end if;  
        set total = total + c;  
        end loop;  
        close cur;  
        select _n,n,total;  
    end;  
    begin  
            declare c int;  
            declare n varchar(20);  
            declare total int default 0;  
            declare done int default false;  
            declare cur cursor for select name,count from store where name = 'android';  
            declare continue HANDLER for not found set done = true;  
            set total = 0;  
            open cur;  
            android_loop:loop  
            fetch cur into n,c;  
            if done then  
                leave android_loop;  
            end if;  
            set total = total + c;  
            end loop;  
            close cur;  
        select _n,n,total;  
    end;  
    begin  
      
    end;  
    end loop;  
    close cur;  
END;  

 

3. Mysql存储过程无法传递、转换数组类型参数解决方案

 

4. 事物

错误码参考：https://www.yiibai.com/mysql/error-handling-in-stored-procedures.html

batchImport:BEGIN
	#Routine body goes here...
	
	
  DECLARE domainCount BIGINT DEFAULT 0;
	DECLARE err INT DEFAULT 0;
  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION  begin
ROLLBACK;
  select 'batch import error transaction rollback';
  set rspCode='9';
  set rspDesc='batch import error transaction rollback';
end;
	

  START TRANSACTION;
  INSERT INTO `daas`.`data_import_wxd` ( `en_name`) VALUES ( '222');
  #INSERT INTO `daas`.`data_import_wxd` (`id`, `en_name`) VALUES ('35', '333');
  INSERT INTO `daas`.`data_import_wxd` ( `en_name`) VALUES ( '444');
  COMMIT;
  select 'insert ok';


END

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;

START TRANSACTION;
--自己的sql
  
 
if err=1 THEN
 set vresult = -1;
 ROLLBACK;
ELSE
	COMMIT;
END IF;

 

4. mysql字符串拼接，存储过程

MySQL concat函数可以连接一个或者多个字符串,如
select concat('10');
结果：10 
select concat('11','22','33');
结果
112233
MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
select concat('11','22',null);
结果
NULL
concat(b.yearnumber,Lpad(b.monthofyear,2,0))拼接字符串。

Lpad(4,2,0)结果：04，‘4’的位数是1位小于2两位，所以在4的前边补充0
LPAD(s1,len,s2)函数将字符串s2填充到s1的开始处，使字符串的长度达到len，然后返回字符串s1。如果字符串s1的长度大于len，则返回值被缩短至len字符长度。
Rpad(4,2,0)结果：40，‘4’的位数是1位小于2两位，所以在4的后边补充0
RPAD(s1,len,s2)函数将字符串s2填充到s1的结尾处，使字符串的长度达到len，然后返回字符串s1。如果字符串s1的长度大于len，则返回值被缩短至len字符长度。

5. 从MySQL中的存储过程打印调试信息

选项1：将这个在你的过程中打印’stdout’运行时的注释。

SELECT 'Comment';

选项2：把这个在你的过程中打印一个变量到它的stdout：

declare myvar INT default 0;
SET myvar = 5;
SELECT concat('myvar is ', myvar);

这个打印myvar是5到stdout当程序运行时。

选项3，创建一个包含一个名为tmptable的文本列的表，并向其推送消息：

declare myvar INT default 0;
SET myvar = 5;
insert into tmptable select concat('myvar is ', myvar);

你可以把上面的一个存储过程，所以你必须写的是这样的：

CALL log(concat('the value is', myvar));

6. mysql 存储过程 字符串 转 数组 

 set pdm_purpose_ids_string=pdm_purpose_ids;

  SET arrLength = LENGTH(pdm_purpose_ids_string) - LENGTH(REPLACE(pdm_purpose_ids_string,',',''));
  if length(trim(pdm_purpose_ids_string))=0 and arrLength = 0  THEN
	
		set rsp_code='0002';
    set rsp_desc='经营团队为空';
    leave vms_purpose_add_label;
  end if; 

  set arrLength = arrLength+1;

  START TRANSACTION;
  purpose_label:WHILE arrLength > 0 DO
    set instr_count = instr(pdm_purpose_ids_string,',');
    if instr_count = 0 THEN
			set pdm_purpose_id_string=pdm_purpose_ids_string;
    else 
       set pdm_purpose_id_string = substr(pdm_purpose_ids_string,1,instr_count-1); 
			 set pdm_purpose_ids_string = substr(pdm_purpose_ids_string,length(pdm_purpose_id_string)+1+1); 
    end if;
		
    if trim(pdm_purpose_id_string) <> '' THEN
				set pdm_purpose_id_string=TRIM(pdm_purpose_id_string);
        -- 经营团队是否存在
        select count(1) into pdm_purpose_count from  bs_product_class where enabled_flag=1  and id=pdm_purpose_id_string ;
        if  pdm_purpose_count <> 1 THEN
          set e_code=1;
					set rsp_code='0003';
					set rsp_desc=concat(pdm_purpose_id_string,'经营团队不存在');
          leave  purpose_label;
        end if;