一、存储过程的概述
存储过程:是一组为了完成特定功能的sql语句集,其实质上是一段存储在数据库中的代码,它由声明式语句(create、update、delete、select等语句)和过程式语句(如if-then-else、case等语句)组成。
这组语句经过编译存储在数据库中,用户只需要通过存储过程的名称和指定参数(如果该存储过程带有参数),既可以随时调用和处理它,而不需要重新编译,提高数据库操作语句的执行效率。
特点:
- 增强sql语言的功能和灵活性;
- 良好的封装性;
- 高性能;
- 可减少网络流量;
- 保证数据库的安全性和数据的完整性;
- 创建存储过程语法
Delimiter $$
Create procedure 存储过程名称(in|out|inout 参数名 数据类型)
Begin
存储过程体
End
$$
注意:(1)参数名一定不能跟表中列名相同;
- 存储过程名称是唯一的,不能重复;
- in|out|inout:只能三者选一;
- 如果没有参数时,小括号仍然需要写上;
1、存储过程的参数列表(in|out|inout 参数名 数据类型)
(1)in输入参数:表示调用者向存储过程传入值(传入值可以为具体数值或变量)
(2)out输出参数:表示存储过程向调用者传出值(传出值可以返回多个值,只能是变量)
(3)inout输入输出参数:既表示调用者向存储过程传入值,又表示存储过程向调用者传出值(值只能是变量)
2、in输入参数
例:在表tb_student中创建存储过程,根据学号修改学生的性别为一个指定的性别;
Delimiter $$
Create procedure sp_update_sex(in sno int,in ssex char(2))
Begin
Update tb_student set sex=ssex where studentno=sno;
End$$
调用1:
Delimiter ;
Set @sno=2013110101; #设置用户变量@sno赋值为2013110101
Set @ssex='女'; #设置用户变量@ssex赋值为女
Select @sno,@ssex; #查看用户变量@sno和@ssex的值
Call sp_update_sex(@sno,@ssex); #调用存储过程
Select * from tb_student; #验证存储过程是否调用成功
调用2:
Call sp_update_sex(2013110101,'男');
Select * from tb_student;
3、out输出参数
例:调用存储过程,得到学生表中的学生人数;
Delimiter $$
Create procedure sp_count(out count int)
Begin
Select count(*) into count from tb_student;
End$$
调用:
Delimiter ;
Call sp_count(@count); # 调用存储过程,并把得出的参数值10传出外部
Select @count; #存储过程的外部可以接收该参数值为10
练习:调用存储过程,通过学号查询该学生的总分。
Delimiter $$
Create procedure sp_sum(in sno int,out sum int)
Begin
Select sum(score) into sum from tb_score where studentno=sno;
End$$
调用:
Delimiter ;
Call sp_sum(2013310101,@sum);
Select @sum;
4、inout输入输出参数
例:创建存储过程,得出输入输出参数p的值;
Delimiter $$
Create procedure proc1(inout p int)
Begin
Select p;
Set p=p+10;
Select p;
End$$
调用:
Delimiter ;
Call proc1(@p); #调用存储过程,p先为null,后还是null,因为null 不能进行四则运算
Set @p=10; #给用户变量@p赋值为10
Call proc1(@p); #调用存储过程,p先为10,后为20且传出到外部
Select @p; #存储过程外部的用户变量@p会接收修改为20
注意:
- in输入参数:只传入,不传出;
- Out输出参数:只传出,不传入;
- inout输入输出参数:既传入,又传出。
- 调用存储过程
Call 存储过程名称(参数列表值);
注意:如果没有参数时,小括号可写,也可不写。