目录
SQL指令的执行过程
SQL指令执行过程中出现的问题:
1.如果需要多次重复执行相同的SQL,SQL执行都需要通过连接传递到MySQL,并且需要经过编译和执行的步骤。
2.如果需要连续执行多个SQL指令,并且第二个SQL指令需要使用第一个SQL指令执行的结果作为参数则不能实现。
SQL存储过程
概念
将能够完成特定功能的SQL指令进行封装(SQL指令集),编译之后存储在数据库服务器上,并为之取一个名字,客户端可以通过名字直接调用这个SQL指令集,获取执行结果。
存储过程的优缺点
优点
1.sql指令无需客户端编写,通过网络传送可以节省网络开销,同事避免SQL指令在网络传输过程中被恶意篡改,保证安全性。
2.存储过程经过变异创建并保存在数据库中的,执行过程无需重复进行变异操作,对SQL指令的执行过程尽心了性能提升。
3.存储过程中多个SQL指令之间存在逻辑关系,支持流程语句(分支,循环),可以实现更为复杂的业务。
缺点
1.存储过程是根据不同的数据库进行编译、创建并存储在数据库中。当我们需要切换到其他的数据库产品时,需要重新编写针对于新数据库的存储过程。
2.存储过程受限于数据库产品,如果需要高性能的优化会成为一个问题。
3.在互联网项目中,如果需要数据库的高并发访问,适应存储过程会增加数据库的连接执行时间(因为我们把复杂的业务交给了数据库进行处理)。
创建和使用存储过程
创建存储过程语法
create procedure <proc_name>([IN/OUT 参数 参数类型])
begin
sql逻辑
end;
-- 创建加法的存储过程
create procedure proc_test01(IN a int,IN b int,OUT c int)
begin
set c = a + b;
end;
使用存储过程语法
set @结果;
call proc_name(具体参数......);
select @结果 from dual;
set @m = 0;
call proc_test01(1,2,@m);
select @m from dual;
存储过程的变量
定义局部变量
定义在存储过程中的变量,只能在存储过程内部使用
语法:declare 变量名 类型 [default 默认值];
实例:
create procedure proc_test02(in a int,out b int)
begin
declare x int default 0; -- 定义x初始默认值为0
declare y int default 0; -- 定义y初始默认值为0
set x = a*a;
set y = a/2;
set b = x + y;
end;
定义用户变量
相当于成员变量,定义的用户变量可以通过“select 变量名 from dual”查询到。
用户变量是存储在MySQL数据库中的数据字典中;
用户变量的定义直接用set关键字直接定义,变量名以@开头:set @a = 1;
给变量赋值
无论是局部变量还是用户变量,都用set赋值
set @n = 1;
call proc_test02(6,@n);
select @n from dual;
-- @n = 39
将查询结果的值赋值给用户变量
create procedure proc_test03(out c int)
begin
select count(stu_num) into c from students;
end;
call proc_test03(@n);
select @n from dual;
注意事项:因为用户变量相当于静态变量,可以在SQL指令以及多个存储过程中使用,在开发过程中尽量少使用用户变量。用户变量过多,会导致程序不易理解,难以维护。
存储过程参数
存储过程的参数分为三种:IN;OUT;INOUT
IN--输入参数
在调用存储过程中传递数据给存储过程的参数(调用的过程必须为具有实际值的变量,或者字面值)。
-- 创建存储过程,添加学生信息
create procedure insertStu(in stu_num char(8),in stu_name varchar(20),in stu_age int,in stu_gender char(2),in cid int)
begin
insert into students(stu_num,stu_name,stu_age,stu_gender,cid) values
(stu_num,stu_name,stu_age,stu_gender,cid) ;
end;
call insertStu(12140209,'李天资',20,'男',3);
out--输出参数
将存储过程中产生的数据返回给过程调用者,相当于Java方法的返回值,但不同的是一个存储过程可以有多个输出参数
-- 创建存储过程,根据学生num查询学生姓名
create procedure selectStu(in stunum char(8),out stuname varchar(20))
begin
select stu_name into stuname from students where stu_num = stunum;
end;
set @stuname = '';
call selectStu('12140209',@stuname);
select @stuname from dual;
inout--输入输出参数
create procedure selstu(inout str varchar(20))
begin
select stu_name into str from students where stu_num = str;
end;
set @str = '12140209';
call selstu(@str);
select @str from dual;