Mysql中的存储过程与函数

存储过程

含义

一组经过预先编译的sql语句的集合

优点

1、简化应用开发人员的很多工作
2、减少数据在数据库和应用服务器之间的传输
3、提高了数据处理的效率

存储过程分类

1、无返回无参
2、仅仅带in类型，无返回有参
3、仅仅带out类型，有返回无参
4、既带in又带out，有返回有参
5、带inout，有返回有参

注意：in、out、inout都可以在一个存储过程中带多个

创建存储过程

语法：

delimiter $
create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
begin
		sql语句1;
		sql语句2;
end
delimiter ;

调用存储过程

call 存储过程名(实参列表)

注意事项：

1、需要设置新的结束标记，比如：delimiter $

2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end

3、参数前面的符号的意思
in: 该参数只能作为输入 （该参数不能做返回值）
out：该参数只能作为输出（该参数只能做返回值）
inout：既能做输入又能做输出

演示存储过程

利用存储过程（这里不带参数）批量导入随机生成的数据

drop procedure if exists test;
delimiter $$
create procedure test()
begin
	-- create tmp table
	drop table if exists tmp;
	create table tmp as select * from sales_order where 1=0;
	-- 定义变量 declare var
	set @begin_time := unix_timestamp('2018-1-1');
	set @over_time := unix_timestamp('2018-11-23');
	set @i := 1;
	while @i <= 100000 do
		set @customer_number := floor(1+rand()*6);
		set @product_code := floor(1+rand()*3);
		set @tmp_date := from_unixtime(@begin_time+rand()*(@over_time-@begin_time));
		set @amount := floor(1000+rand()*9000);
		insert into tmp values(@i,@customer_number,@product_code,@tmp_date,@tmp_date,@amount);
		set @i := @i+1;
	end while;
	-- 清除sales_orders数据clear sales_order
	truncate table sales_order;
	insert sales_order select null,customer_number,product_code,order_date,entry_date,order_amount
		from tmp;
	commit;
	drop table tmp;
end$$
delimiter ;

-- 运行存储过程
call test();

函数

含义和优点和存储过程一致

创建函数

语法：

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体
END

注意事项：

1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

3.函数体中仅有一句话，则可以省略begin end

4.使用 delimiter语句设置结束标记

调用函数

SELECT 函数名（实参列表）

演示函数

create table s_getin(
Saleguid integer,
item varchar(20),
date date,
money integer
)

insert into s_getin(
Saleguid,item,date,money
) values 
(123,"定金","2013-12-01",10000),
(123,"首期","2013-12-20",200000),
(456,"定金","2018-12-01",20000)


set global log_bin_trust_function_creators=TRUE;
delimiter $$
create  function f1(s int) 
returns varchar(100)
begin 
declare temp varchar(100);
select group_concat(res) into temp from (select Saleguid, concat(date,"收",item,money,";") res from s_getin where Saleguid = s )a;
return temp;
end$$
delimiter ;

函数和存储过程的区别

             关键字		   调用语法	        返回值			 应用场景
函数		   FUNCTION	    SELECT 函数()	  只能是一个		  一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个
存储过程	   PROCEDURE	CALL 存储过程()    可以有0个或多个   一般用于更新