MYSQL存储过程

SQL 请求过程：
客户端DBMS 编写运行SQL指令 -> 通过数据库连接将SQL发送到数据库 –> 数据库接收到指令，SQL引擎编译执行SQL指令 -> 将执行结果通过数据库连接传递给DBMS ->显示结果。
存储过程：
优点：
避免SQL被串改，因为指令是存在数据库的；
传输过程节省资源（不用传SQL,只传函数名）；
性能提升，数据库无需重复执行编译操作。
缺点：
切换数据库需要重新编写新的存储过程，如MYSQL切换到ORACLE；
高性能优化受限于数据库产品；
高并发使用存储过程会增加数据库连接时间，因为将业务都给到数据库处理。

#创建语法
delimiter$$
create procedure test (in a int, in b int, out c int)  #输入输出参数 （in / out / inout args）
begin 
	set c= a+b; 
END$$
delimiter;#意为把分隔符还原

#调用
set @m = 0;//接收
call test(3,2,@m)

#查询
#显示的变量值（dual是系统表，定义的变量存进去）
select @m from dual;

局部和全局变量

create procedure test (in a int, out c int)  #输入输出参数 （in / out args）
begin 
	declare x default 0; #局部变量
	set x = a * a; 
	set c= x+b; 
end;

set @m = 0;//用户变量，相当于全局变量
call test(3,@m)
#显示的变量值（dual是系统表，定义的变量存进去）

#查询
select @m from dual;

写SQL，作为存储过程的接收
select … into…

create procedure test ( out c int)  #输入输出参数 （in / out args）
begin 
	select count(name) into c from student;
end;

实际使用

#插入
create procedure test ( in name char(8),in age int)  #输入输出参数 （in / out args）
begin 
	insert into student(stu_name,stu_age) values(name,age);
end;
call test('张三',12);

#查询一
create procedure test2 (in age int, out name char(8))
begin 
	select stu_name into name into c from student where stu_age = age;
end;
set @name = '';
call test2 (11, @@name)
select @name from dual;

#查询二
create procedure test2 (in str varchar(20))
begin 
	select stu_name into str into c from student where stu_age = str ;
end;
set @name = '11';
call test2 (11, @@name)
select @name from dual;

分支
if…then…else…end if
case…when…then…else…end case

#单分支
create procedure test ( in a int)
begin 
	if a=1 then
		insert into student(stu_name,stu_age) values(name,age);
	end if;
end;
call test(2);

#双分支
#单分支
create procedure test ( in a int)
begin 
	if a=1 then
		insert into student(stu_name,stu_age) values(name,age);
	else
		insert into student(stu_name,stu_age) values(name,age);
	end if;
end;
call test(2);

create procedure test ( in a int)
begin 
	case a
		when 1 then
			insert into student(stu_name,stu_age) values(name,age);
		when 2 then
			insert into student(stu_name,stu_age) values(name,age);
		else
			insert into student(stu_name,stu_age) values(name,age);
	end case;
end;
call test(2);

循环
while
repeat
loop

create procedure test ( in a int)
begin 
	declare i int;
	while i < a do
		#SQL
	end while;
end;
call test(2);

create procedure test ( in a int)
begin 
	declare i int;
	set i =1;
	repeat
		#SQL
		set i = i+1;
	until i > a repeat ;
end;
call test(2);

create procedure test ( in a int)
begin 
	declare i int;
	set i =1;
	myloop: loop
		#SQL
		set i = i+1;
		if i = a then
			leave myloop;
		end if;
end;
call test(2);

常用操作指令

show procedure status;
show procedure status where db='xxx';

游标 cursor for
查询多条数据
一次去除查询结果集中的多条数据

#声明

create procedure test (out res varchar(200))
begin 
	#游标变量
	declare cid int;
	declare cname varchar(20);  

	#循环计数变量
	declare num int;
	declare i int;
	
	# 定义游标在 这查询语句上
	declare  myCursor cursor for select stu_id,stu_name from student;
	#记录总量
	select count(1) into mun from student;
	#打开游标
	open myCursor;
	set i = 0;
	#每条数据
	declare str varchar(100);
	#遍历
	while i< num do
		#提取游标中的数据，并赋值给变量
		fetch myCursor into cid ,cname;
		set i = i+1;
		#set str = concat_ws("~",cid,cname); # 第一个是连接符号，第二第三是要拼接的数据。
		selecl concat_ws("~",cid,cname) into str; #和上面意思一样
		set res = concat_ws(",",res,str);
	end while;
	#关闭游标
	close myCursor;
end;

set @r = '';
call test(@r);
select @r from dual;