MySql存储过程快速上手

概述

1. 适合有一定数据库基础的人员
2. 用来快速掌握mysql的sp基本语法、逻辑控制、异常处理
3. 包括与Oracle数据库用法的对比，加深理解和记忆
4. 包括一个完整的存储过程示例

1、基本语法

1. 变量定义：必须declare 开头，默认值default关键字。eg：declare a,b int default 5;
2. 变量赋值：set a=5;
3. while循环：while do end while。
4. repeat循环：repeat until end repeat。
5. 异常处理：类似DB2的语法，预先定义异常处理方法，与变量类似。

DECLARE 
		{EXIT | CONTINUE} --异常时，主程序继续或退出
		HANDLER FOR
		{error-number |{SQLSTATE error-string}|condition} --异常标的
		SQL statement 

##SQLSTATE:异常可捕获的默认信息，如23000(表违反约束)，类似ora-errcode
##condition：为sqlstate人为设定的同义词。eg：
##预设的错误声明：NOT FOUND,SQLEXCEPTION,SQLWARNING

6. 调用存储过程：call sp();

2、SP示例

CREATE DEFINER=`test`@`%` PROCEDURE `sp_test`(in in_par1 int)
BEGIN
	#变量声明,declare 关键字
    declare vn_val1 int default 10;
    declare vn_val_plus int ;
    declare vn_i int;
    declare b int default 0;
    declare rs_cursor cursor for select col from c1 order by col;
    #异常声明,declare handler 关键字
    ##1）预先声明||ora是后续声明。【实际差异不大，开发时都可以先不加异常，有异常再处理。mysql游标退出需要借用异常实现】
    ##2）2种类型：continue和exit(退出整个SP)||ora用子块和异常的传递实现continue还是exit    
    declare exit/*continue*/ handler for sqlstate '23000' 
		begin 
			set b=1;
			#@x是mysql的会话级变量，不同step设置不同的变量，可用来调试程序；相当ora的dbms_output工具。
            set @expt=concat('23000 fired',DATE_FORMAT(NOW(),'%Y-%m-%d %T'));
		end;
    ########################
    #变量赋值,set 关键字    
    set vn_val_plus=in_par1+vn_val1;
    #@x1会话变量，会话期间都可访问，不同程序间传递值。||ora应该对应的ys_context
    set @x1=vn_val_plus;
    
    #while循环
    set vn_i=0;
    while vn_i<5 do
		insert into c1 values (vn_i);
        set vn_i=vn_i+1;
    end while;
	
    set vn_i=10;
    #loop循环
    label_loop1:loop
		if b then
			leave label_loop1;
        end if;
        set @vn_i_before=vn_i;
		insert into c1 values(vn_i);        
        set vn_i=vn_i-1;	
        set @vn_i_after=vn_i;
    end loop;
    set @end='this is end';
    
END

说明

1. c1是一张测试表，只有1个int型字段，且是主键。用来实验23000错误（违反主键约束）。
2. 通过call sp_test(10)调用SP。
3. continue handler，异常触发后，进度对应的handler，然后继续执行异常语句后的内容。
4. exit handler，异常触发后，直接退出整个程序。
   注意：不同类型下vn_i_after和end的差异。

handler类型	@x1	@expt	@vn_i_before	@vn_i_after	end
exit	20	23000 fired date	4	4
continue	20	23000 fired date	4	3	this is end

5. 每次执行后， 清空会话变量，否则上次的会话变量可能沿用，影响判断。这也是会话变量与函数内普通变量的差异！