MySQL编程语法以及自定义函数

前言：我们在写sql语句时，如果我们熟悉库里的大多数函数，在写起来效率会很高；但有时候我们要实现一些想法，但库里没有相应的函数，则需要写很长很绕的sql语句来实现。这时候，就像Java封装好的方法一样，我们如果写一个函数调用，则会大大减少我们的工作量。在写自定义函数之前，我们需要了解MySQL的编程语法

一、变量

和其他编程语言类似，这里面的变量也分为局部变量和全局变量
1、局部变量的声明：

declare val_name date_type default df_value;

也可以写做：

declare val_name date_type;
set val_name = value;

2、全局变量，可以跨函数访问

set @val_name = value;

3、给该变量赋值，同时还会作为一个select语句输出‘结果集’

select @val_name:=expr/value；

4、不输出结果集，也是一直赋值语句

select expr/value into @value_name;

二、运算符

逻辑运算符：+、-、*、/、%、<>、=
关系运算符：and 、 not 、 or

三、逻辑结构

1、分支结构：if里没有括号，elseif是连起来的，每一个结果语句后都要加分号

	if expr1 then
	...
	elseif expr2 then
	...
	else
	...
	end if;

2、循环结构

	while expr do
...
	end while

四、函数

1、因为写函数时语句的结尾是分号，而分号又是sql语句的结尾符，因此需要更改sql语句结尾符，用delimiter+要替换的符号。举例：delimiter //
2、创建函数

	create function FUNC_NAME(params...) returns DATA_TYPE
	begin
	...
	end //

3、调用当前函数

	select FUNC_NAME(params...)

4、查看当前函数

	show function status //

5、删除函数

	drop function FUNC_NAME //

五、存储过程

语法：

	create precedure PRO_NAME(in/on/inout VAR_NAME DATA_TYPE)

其中，in输入参数、on输出参数、inout输入输出，有时候可以用存储过程代替函数。
举例：

	create procedure pro_selscore(in pageNo int,in pageSize int,out total int)
	begin
		declare _begin int default (pageNo-1)*pageSize;
		select ceil(count(1)/pageSize) into total from score;//查出来的值赋给total
		select * from score limit _begin,pageSize;
	end //

调用存储过程

	set @total=0;
	call pro_selscore(1,8,@total);//调用：call存储过程的名称(参数)
	select @total;

过程存储案例：转账功能的实现
1、其中start transaction;commit;分别是事务的开启和在增删改时可将修改内容从内存写入文件中。如果不满足条件则可以将commit换成rollback，就不会修改源文件了。
2、continue handler for sqlexception set 为异常代码的处理

	--创建表
		create table account(
			userid bigint(20),
			balance decimal(10,2)
			);
		insert into account(userid,balance) values(1001,5000.00),(1002,6000.00);
		--创建事务存储
		create procedure pro_transforAcc(in fromUserid int,in toUserid int, in amount decimal,out rst int)
		begin
			declare accnum int default 0;
			declare _balance decimal(10,2) default 0;
			declare continue handler for sqlexception set rst=-1;
			set rst=0;
			select balance into _balance from account where userid=fromUserid;
			if _balance<amount then
				set rst=-1;
			else
				start transaction;
				update account set balance=balance-amount where userid=fromUserid;
				update account set balance=balance+amount where userid=toUserid;	
				set rst=1;
				commit;
			end if;
		end //
	--调用过程存储
		set @rst=0;
		call pro_transforAcc(1002,1001,300,@rst);
		select @rst;//

输出结果：
案例二：
使用存储过程生成年月的维度表
创建表：create table dim_times(fyear varchar(20),fmonth varchar(20));

delimiter $$
create procedure insert_tm(begintime varchar(20),overtime varchar(20))
begin
	declare tm int;
	declare mth int;
	declare y int;
	declare m int;
	set mth=timestampdiff(month,begintime,overtime);
	set tm=0;
	while tm<=mth do
		set y=year(begintime);
		set m=month(begintime);
		insert into dim_times(fyear,fmonth) values(y,m);
		set begintime=date_add(begintime,interval 1 month);
		set tm=tm+1;
	end while;
end;
$$

调用存储过程：CALL insert_tm('2020-1-1','2021-12-31')
输出如下：

六、函数和存储过程异同

相同点：都类似于java中的类方法，是一组预先编译好的SQL语句的集合，提高代码的重用性，简化操作，减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：存储过程可以有0个返回，也可以有多个(out参数)返回，适合做批量插入、批量更新；使用call调用。
函数有且仅有1 个返回，适合做处理数据后返回一个结果(return 返回类型)；使用select调用