数据库21/10/18

数据库高级

存储过程

1.SQL指令执行过程

2.存储过程

• 将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果

3.存储过程的优点

• SQL指令无需客户编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改以保证安全性
• 存储过程是经过编译创建并保存在数据库中的，执行过程无需重复的进行编译操作，对SQL指令的执行过程进行了性能提升
• 存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支，循环），可以实现更为复杂的业务

4.存储过程的缺点

• 存储过程是根据不同的数据库进行编译，创建并存储在数据库中，当我们需要换到其他数据库时，需要重新编写针对该数据库的存储过程
• 存储过程受限于数据库产品（Mysql，Oracle等），高性能的优化是个问题
• 在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接执行时间（因为我们将复杂的业务交给了数据库处理）

5.创建存储过程

• 语法：create procedure 名（参数） 可以没有参数
  begin
  SQL指令集
  end；

案例：创建一个存储过程实现加法运算

CREATE PROCEDURE pro_add2(IN a INT,IN b INT,OUT c INT)
BEGIN
  SET c = a + b;
END;

6.调用存储过程

set @m = 0；
语法：call 名（参数）；
select @m from dual

案例：调用上述存储过程

set @a = 0;
call pro_add2(3,4,@a);
select @a from dual;

7.存储过程中变量的使用

1）局部变量：定义在存储过程中的变量，只能在存储过程内部使用

• 语法：局部变量要定义在开始
  declare 变量名 类型 default 默认值

案例：求a的平方+（a/2）

create PROCEDURE pro_test(in a int,out b int)
begin 
  DECLARE x int DEFAULT 0;
  DECLARE y int DEFAULT 0;
  SET x = a*a;
  SET y = a/2;
  SET b = x+y;
END;

set @a = 0;
call pro_test(4,@a);
select @a from DUAL;

2）用户变量：相当于全局变量，定义的变量可以通过select 变量名 from dual 查询

• 用户变量会存储在mysql数据库的数据字典中（dual）
• 用户变量使用set关键字直接定义，变量名要@开头，如set @a = 0;
  3）给变量设置值
• 都使用set关键字赋值

案例：创建存储过程，查询学生的数量并返回

CREATE PROCEDURE pro_test1(OUT c INT)
BEGIN
  SELECT COUNT(stu_num) INTO c FROM stu;
END;

CALL pro_test1(@c);
SELECT @c FROM DUAL;