1.存储过程
1.概述:一条或多条SQl语句的集合
2.创建方法: CREATE PROCEDURE sp_name ([proc_parameter])
[characteristics....] routine_body
注释:CREATE PROCEDURE :创建存储函数的关键字
sp_name : 存储过程名称
proc_parameter:指定存储过程的参数列表(具体参数如下:
IN:输入参数
OUT:输出参数
INOUT:可输入也可输出
param_name:参数名称
type:参数类型
charateristics:存储特性
routine_body:SQl代码内容 可用BEDIN..........END来表示SQL代码的开始和结束)
删除存储过程: DROP PROCEDURE sp_name
*** 存储的意义
1.简化操作,减少重复冗余的操作。
2.减少操作过程中的失误,提高效率。
2.DELIMITER 语句
作用:改变结束符
使用流程: DELIMITER // ,将MySQL的结束符设置成为 // 其他符号也可以
原因:MySQL默认语句的结束符为;,为了避免与存储过程发生冲突,需要运用此语句改变储存过程的结束符,且以END // 结束存储过程。
!!!!!!要注意的是恢复默认的结束符 DELIMITER;
完整的代码实现:
delimiter //
create procedure test() #存储名称
begin
select * from test()
end //
delimiter; #恢复默认的结束符 ;
ps: begin ........end 中间的为封装好的查询语句,需要运用此语句时可以调用此存储即可。
3.调用存储过程
方法:CALL pro_name([proc_parameter]) ;
完整的代码实现:
delimiter //
create procedure te()
begin
select * from te;
end //
delimiter ;
call te(); #调用了名称为te的存储过程
4.变量的介绍
4.1局部变量 declare 声明
定义:子程序中声明并使用 作用在 begin …end 程序中
流程:
1.定义变量 : declare 语句定义变量
delcare var_name[,var_name]........data_type [default value]
注释: var_name : 局部变量名称
deafult value :初始默认值 若未设定,初始值则为null
例: delcare myparam int default 100 ;
注释:声明 myparam 整数 初始默认值为100
2.为变量赋值
a. set 语句
例如:
delcare test1 int ;
set test = 1+2 ; #设置test=1+2
b.select..... into 语句
例如:
delcare test2 int ;
select 1+2 into test2 ; #将1+2 放入test2 中
4.2 用户变量 与链接有关
定义:一个客户端定义的变量不能被其他客户端看到及使用,当客户端退出时所有变量将自动释放。
用户变量的形式:@var_name @+变量名
特点:不需要declare定义,只需要变量进行赋值即可。赋值方式与局部变量的相同。
例: a.set语句
set @t=1+2 ;
#查看结果
select @t ;
b.select....into语句
select 1+2 into @t1 ;
#查看结果
select @t1 ;
局部变量与用户变量的比较
异:
局部变量: declare声明 内容: (局部变量名称 数据类型 默认值) 总结:1、定义变量 2、变量赋值
用户变量: 用户变量名称 @var_name 总结:1、赋值 2、查看结果
同:
赋值方式:a.set 语句 b.select into 语句
5.存储过程参数的详细介绍
5.1 in 输入参数
有in参数没有out参数时
调用:只需输入参数的值
查看结果:需要 select @var_name ; 查看。
delimiter //
create procedure sum1(in a int ,in b int) #创建变量sum1 ,输入参数 a int 整数型 、b int 整数型
begin
select a+b into @sumvalue;
select @sumvalue; #将查看用户变量的语句存放于存储过程中
end //
delimiter ; #恢复默认结束符 ;
#调用:
call sum1(2,3);
#得出@sumvalue 的结果为 5,不需要用select @sumvalue ; ,因为此语句事先存放于begin end // 语句中
注注注:因为没有out类型变量,但存储过程要返回结果,故而使用存储过程时要借助用户变量返回执行结果
5.2 out 输出参数 用于输出参数的返回值
调用:需要输入参数值和@输出参数
查看:调用时输入参数及 @输出参数
delimiter //
create procedure sum2(in a int ,in b int ,out c int )
begin
set c =a+b ;
select c;
end //
delimiter ;
#调用
call sum2(2,3,@c); #直接可以查看结果 @c 表示使用用户变量为out进行赋值
注注注: 虽然有out输出类型的变量,但在调用存储过程时要使用用户变量(@var_name)为out进行赋值。
5.3 流程控制 if 判断 case 判断 while 循环
判断:
5.3.1 if判断
if语句:根据判断结果True或False执行相应的语句
语法格式:
IF expor_condition THEN statement_list ;
[ELSEIF expor_condition THEN statement_list] ;
[ELSE statement_list] ;
END IF ; #特别注意不要忘了end if
注注注:[ ]内的表示可选用,条件多时会用到,但判断简单时可能不会用到。
小例子:判断一个数是正数、负数、0中哪一类
方法一、 运用到out参数
delimiter //
create procedure cl(in d float ,out e varchar(20))
begin
if d >0 then select "正数" into e ;
elseif d=0 then select "零" into e;
else set e ="负数";
end if ;
end //
delimiter ;
#调用
call cl(-3.14,@e);
方法二、 不设置out参数
delimiter //
create procedure cl_1(in g float)
begin
if g<0 then set @final ="负数" ; #set语句赋值
elseif g=0 then set @final="0"; #set赋值
else set @final="正数"; #set 赋值
end if ; # 千千万万要注注注意,if开头要end if结尾
end //
delimiter ;
#调用
call cl_1(4.5);
#查看结果
select @final;
注意:if判断语句中select @final;语句可以放入存储过程也可以放在外面即调用后查看结果时运行select @final;语句
此外在存储过程中的位置在end if ; 前后均可,总之只要为位于begin... end 之间就行。
5.3.2 case判断
语法格式:
case
when expr_condition then statement_list
[when expr_condition then statement_list]
[else ststement_list]
end case
例:
delimiter //
create procedure case1(in h int)
begin
case
when h < 0 then set @result="负数" ;
when h = 0 then set @result="零" ;
else set @result="正数" ;
end case ;
end //
delimiter ;
#调用
call case1(5) ;
#查看
select @result ;
判断小总结:
a.if 判断:
if
esleif
else
end if
b.case when 判断
case
when
when
else
end case
case then 的一个小应用
循环:
5.3.3 while 循环
while 语句:创建了一个带条件判断的循环过程
整体思路:while 后跟条件判断,结果为True则执行while 语句内的程序,反之退出循环。
语法格式:
while expro_condition do
statement_list
end while
例: 参数值小于10时执行循环语句
delimiter //
create procedure w1(in p int)
begin
while p < 100 do
set p = p + 1 ;
end while ;
end //
delimiter ;
5.4 执行预处理语句 三部曲:1、设定模板,2、准备,3、执行
set preparable_SQl_statement =" select 查询语句"
语法格式:
prepare statement_name from preparable_SQl_statement
execute statement_name
注释:ststement_name 预处理名称
preparable_SQL_statement 预先准备好的文本SQl语句
例1:预处理语句执行“查询t1表”的SQL语句
# 设定执行的SQL语句文本
set @SQLstatement = "select * from t1" ;
# 预处理准备阶段
prepare statement from @SQLstatment ;
# 执行阶段
execute statement;
例2: 预处理语句执行“查询t1表中v1=r1的记录”的SQL语句
-- 执行的SQL语句文本
set @haha = "select * from t1 group by key1 having v1=r1" ;
-- 预处理准备阶段
prepare haha from @haha ; #prepare 准备
-- 执行
execute haha ;