MySQL入门-day07-存储过程

学习目标：
学习并使用存储过程

存储过程

什么是存储过程

存储过程是数据库的一段SQL集合，是数据库的一个执行程序，也是一种数据库脚本。

存储过程的优缺点

存储过程的优点

1. 增强了SQL语言的功能和灵活性
2. 存储过程可以写复杂的SQL语句，可以处理复杂的业务逻辑。
3. 存储过程能实现较快的执行速度
4. 存储过程能减少网络流量

存储过程的缺点

只能在特定的数据库上使用，若更换数据库则会无法使用，需要重新撰写

操作存储过程

创建存储过程

语法：

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
-- 声明过程名称、参数列表
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
-- 这是附加的一些存储过程描述信息，一般很少使用，这里不做详解
 
routine_body:
　　Valid SQL routine statement
-- SQL路由描述，一般也很少使用
 
[begin_label:] BEGIN
-- 开始存储过程标记
　　[statement_list]
-- 存储过程主体
　　　　……　　　
END [end_label]
-- 结束存储过程标记

调用存储过程：call 存储过程名([变量])

数据准备:

create table ptest_01(
  pid int primary key auto_increment,
	pname varchar(20)
);

无参存储过程

创建语法：

delimiter $-- 将结束符号改成美元符
create procedure p_01() -- 创建一个存储过程并命名
begin -- 开始
  insert into ptest_01(pname) value('bbb');-- 向上面的表插入一个bbb
end$ -- 结束
delimiter ;-- 将结束符号改成分号

有参存储过程

创建语法：

delimiter $
create procedure 存储过程(参数类型 变量名 数据类型())
begin
  查询语句
end$
delimiter ;

参数类型	用处
in	在过程体中被改变后，改变的值不能带出过程体，过程体外还是原值
out	不能输出从外部传进的值 会改变传进变量本来的值
inout	既可以从外部传进的值，也可以改变传进变量本来的值

带有入参的存储过程

具体案例：

可以证明在存储过程中更改值并不会影响原本的值

带有出参的存储过程

可以证明在存储过程中不能直接输出值

可以证明在存储过程中会改变原本的值

带有出入参的存储过程

可以证明在存储过程中能够输出值

可以证明在存储过程中能够更改原本的值

查看存储过程

语法：

-- 方法1
show procedure status where db='数据库';
-- 方法2
select * from information_schema.routines
where routine_schema='数据库名' and routine_type='PROCEDURE';

删除存储过程

语法：drop procedure if exists p_06;

存储过程语法体

分支结构

if-then-else语句

类似Java中的if…else语句

语法：

delimiter $
create procedure 存储过程名([参数列表])
begin
	if(判断条件) then
	   查询语句1
	else
	   查询语句2
	end if;
end$
delimiter ;
call 存储过程名();

若判断条件为true则执行查询语句1，否则执行查询语句2

case语句

类似Java的Switch

delimiter $
create procedure 存储过程名([参数列表])
begin
	case(参数)
	  when(判断1) then
	    查询1
	  when(判断2) then
	  	查询2
	  ...
	  else
	    查询n
	end case;
end$
delimiter ;
call 存储过程名();

若参数符合判断1则执行查询1，否则判断是否满足判断2，是执行查询语句2…都不满足则执行查询n

循环语句

while…end while语句

类似Java的while循环

delimiter $
create procedure 存储过程名([参数列表])
begin
	while(判断条件) do
	 查询语句
	end while;
end$
delimiter ;
call 存储过程名();

若判断条件为true则执行查询语句，知道判断条件为false

repeat…end repeat语句

类似Java的do…while循环

delimiter $
create procedure 存储过程名([参数列表])
begin
	repeat
	  查询语句
	  until(判断条件)
	end repeat;
end$
delimiter ;
call 存储过程名();

限制性依次，若判断条件为true则执行查询语句，知道判断条件为false

loop…end loop语句

死循环

delimiter $
create procedure 存储过程名([参数列表])
begin
	标号名: loop
	  查询语句
	  if(判断条件) then leave 标号名;
	  end if;
	end loop;
end$
delimiter ;
call 存储过程名();

循环执行查询语句，直到碰到leave后结束，if可以更改为其他，但是leave 标号名必须写，不然永远都在循环