7.1存储过程基础

基础知识和小技巧：

1. 为了方便书写，可以在记事本里写存储过程，写完了贴到mysql里。
2. delimiter命令用来指定mysql的命令结束符（默认是分号）。因为存储过程是一段（若干行）mysql代码，为了避免在录入的时候就被执行了，需要用这个命令在写存储过程之前，把结束符改掉，写完之后再改回来。所以常见的创建存储过程代码片段都以delimiter开头和结尾。
3. 为了方便调试（反复录入），可以在存储过程开始之前，先删除可能存在错误存储过程。命令：drop procedure if exists 存储过程名
4. 创建存储过程，通常以create procedure 存储过程名(参数)开头，紧跟begin，end结尾。中间是编写者发挥的地方。

简单例子数据库：

数据库d1，表t1，t2。数字都是tinyint，字符串都是varchar(5)。

 

 

---

 

 最简单的存储过程（不熟悉的时候，敲到记事本里，方便后面修改。之后不再提）：

delimiter ~
drop procedure if exists get_one~
create procedure get_one()
 begin
  select xm from t1 limit 1;
 end~
delimiter ;

简析：

7行，其实是4条命令。第1行，把结束符从分号换成~；第2行，如果存在则删除；第3-6行是一条命令，创建存储过程；第7行改回分号结束符。

重点在第3行，“create procedure”是创建存储过程的命令，get_one是存储过程的名称，括号里是参数（没有就不写，但括号必须有）。

第4、6行是格式，也就是开始结束的标记。

运行：

call 存储过程名。

 

 

---

关于存储过程参数：

存储过程的参数，用“变量名 类型"的方式写在参数括号里。多个参数用逗号隔开。

在调用存储过程的时候，由call命令传入，在存储过程的代码里使用。

delimiter ~
drop procedure if exists get_one~
create procedure get_one(axh int)
 begin
  select * from t2 where xh=axh;
 end~
delimiter ;

运行结果：

 

---

 

关于变量和更多参数内容：

在存储过程中可以使用以前学过的用户变量。这些变量出了存储过程仍然有效。

在存储过程中还可以使用”declare 变量名 变量类型“来声明变量（不带@，注意不要和列名相同），这些变量必须放在begin后的第1行，且仅在存储过程内有效。

如果希望存储过程的参数能带出来值，还可以在它前面加”in/out/inout“("in"可以省略，"out"表示只出不进,"inout"可进可出)。

 例：

delimiter ~
drop procedure if exists get_one~
create procedure get_one(inout axh int,akm varchar(5))
 begin
  declare b varchar(5);
  set b=akm;
  select * from t2 where xh=axh and km=b;
  set axh=100;
 end~
delimiter ;

结果：

  简析：

第3行有两个参数，所以调用的时候也用两个参数，对应位置传递值。

第一个参数可进可出，所以第8行设置axh为100之后，结果图片里的”@a“的值也就变成了100。

第5行声明变量b，在第6行中就正常使用。这种变量不需要加”@“，出了存储过程也就自动销毁。

---

存储过程结果的保存：

普通的查询标量结果，可以用select into保存到变量中。其中用户变量可以在存储过程外面使用。

delimiter ~
drop procedure if exists get_one~
create procedure get_one(axh int)
 begin
  select * into @a,@b,@c from t1 where xh=axh;
  select @a;
  select @b;
  select @c;
 end~
delimiter ;

运行结果：

 

 查询结果是行、列、表的，可以考虑放在临时表中。

临时表是仅对当前连接有效的表。下面例子里涉及到的用法和含义，不清楚的可参考P21中下和P24中下(第四版教材在P54中)。

delimiter ~
drop procedure if exists get_one~
create procedure get_one(axh int)
 begin
  drop temporary table if exists tmp_t1;
  create temporary table tmp_t1 as select * from t2 where xh=axh;
  select km,cj from tmp_t1;
 end~
delimiter ;

运行结果：

 

---

如果要查询当前数据库里有哪些存储过程（root创建的），可以使用命令：

show PROCEDURE status where definer='root@localhost';

结果：