之前一直对mysql的存储过程不了解，总感觉哪里不对劲，所以有了这篇博客，这是这一序列的第二篇。。事实上这是对mysql存储过程的一个基本认识，里面还有好多疑问需要解决。另外那些标榜从入门到精通的书大多不够精通，个人认为一门技术很难靠一本书从入门到精通的，即使有写得这样全面的书，也很可能达到的效果变成了“从入门到放弃”。当然我并不是说我参考的书写得不好，相反，在这里我学习到了很多，很多mysql的书甚至连视图，存储过程都没有。后面可能还有函数，触发器。
存储过程
创建存储过程
每个参数有三部分组成：输入输出类型，参数名称，参数类型
create procedure procedure_name;
in 输入参数
out 输出参数
inout 输入/输出
DECLARE 过程体中声明变量，只能在begin,end之间声明，且在开始就声明，即声明再使用
set 赋值 也可以用select into,select name into name from test where id =2; name 需要先定义
delimeter 设置结束符，默认为;
delimiter ##
CREATE PROCEDURE del_pro(IN t_id INT)
BEGIN
DELETE FROM test WHERE id = t_id;
END ##
delimiter ;
delimiter ##
create procedure age_from_test(in name char(4),out ages int)
begin
select age into ages
from test
where name = name;
end ##
delimiter ;
查看：
MariaDB [oldboy]> show procedure status \G
*************************** 1. row ***************************
Db: mysql
Name: AddGeometryColumn
Type: PROCEDURE
Definer: root@localhost
Modified: 2019-12-19 11:20:15
Created: 2019-12-19 11:20:15
Security_type: INVOKER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Db: mysql
Name: DropGeometryColumn
Type: PROCEDURE
Definer: root@localhost
Modified: 2019-12-19 11:20:15
Created: 2019-12-19 11:20:15
Security_type: INVOKER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
Db: oldboy
Name: del_pro
Type: PROCEDURE
Definer: root@%
Modified: 2020-03-26 21:10:45
Created: 2020-03-26 21:10:45
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: latin1_swedish_ci
3 rows in set (0.00 sec)
带参数存储过程
delimiter //
create procedure get_count(out ct int)
begin
select count(1) into ct from test;
end //
delimiter ;
MariaDB [oldboy]> delimiter ;
MariaDB [oldboy]> call get_count(@ct);
Query OK, 1 row affected (0.00 sec)
MariaDB [oldboy]> select @ct;
+------+
| @ct |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
delimiter //
create procedure get_age(in id int, out _age int)
begin
select age into _age
from test
where id = id;
end //
delimiter ;
delimiter //
create procedure get_age(in _id int, out _age int)
begin
select age into _age
from test
where id = _id;
end //
delimiter ;
@ 注意参数名不能与列名相同，否则查不到数据
MariaDB [oldboy]> delimiter ;
MariaDB [oldboy]> call get_age(6,@_age);
Query OK, 1 row affected (0.00 sec)
MariaDB [oldboy]> select @_age;
+-------+
| @_age |
+-------+
| 18 |
+-------+
1 row in set (0.00 sec)
在执行上面的存储过程中出现 两个错误：
MariaDB [oldboy]> call get_age(1,_age);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine oldboy.get_age is not a variable or NEW pseudo-variable in BEFORE trigger
#错误原因是调用的参数_age 前面没有加@
MariaDB [oldboy]> call get_age(1,@_age);
ERROR 1172 (42000): Result consisted of more than one row
错误原因，存储过程中的where id = id; 参数名与列名相同 修改为where id = _id正常
流程控制 if
delimiter //
create procedure check_age(in _id int, out is_adult int)
begin
declare _age int;
select age into _age
from test
where id = _id;
if _age >18 then
set is_adult=1;
elseif _age < 18 then
set is_adult =0;
else
set is_adult =1;
end if;
end //
delimiter ;
MariaDB [oldboy]> delimiter ;
MariaDB [oldboy]> call check_age(1,@is_adult);
Query OK, 1 row affected (0.00 sec)
MariaDB [oldboy]> select @is_adult;
+-----------+
| @is_adult |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
#这里is_adult=1表示是成年，但上面的存储过程有个bug, else中的 is_adult=1,导致除了小于18的都返回1，另外declare 语句要在begin,end之间，且要加上分号（；）否则会报语法错误
流程控制 case
delimiter //
create procedure get_gender_count(in _gender char(6), out gender_count int)
begin
case _gender
when 'male' then select count(name) into gender_count
from test where gender='male';
when 'female' then select count(name) into gender_count
from test where gender='female';
else select count(1) into gender_count from test;
end case;
end //
delimiter ;
MariaDB [oldboy]> call get_gender_count('female',@gender_count);
Query OK, 1 row affected (0.00 sec)
MariaDB [oldboy]> select @gender_count;
+---------------+
| @gender_count |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
MariaDB [oldboy]> call get_gender_count('male',@gender_count);
Query OK, 1 row affected (0.00 sec)
MariaDB [oldboy]> select @gender_count;
+---------------+
| @gender_count |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)
MariaDB [oldboy]> call get_gender_count('fm',@gender_count);
Query OK, 1 row affected (0.00 sec)
MariaDB [oldboy]> select @gender_count;
+---------------+
| @gender_count |
+---------------+
| 5 |
+---------------+
1 row in set (0.00 sec)
循环语句 while
delimiter //
create procedure dowhile(inout v int)
begin
while @v >0 do
set @v = @v-1;
end while;
end //
delimiter ;
这里有个问题，这种情况怎么传参数进去呢?call dowhile(4)语法报错，而且不知道怎么获取存储过程的值，此坑待填。
循环语句loop
label是标志，可以省略不写 leave 跳出循环
delimiter //
create procedure doloop()
begin
set @a =10
lable:loop
set @a = @-1;
if @a<0 then
leave label;
end if;
end loop label;
end //
delimiter ;
循环语句 while
delimiter //
create procedure dorepeat()
begin
repeat
v = v-1
until v <1
end repeat;
end //
delimiter ;
再次循环iterate
delimiter //
create procedure doiterate()
begin
declare p int default 0;
my_loop: Loop
set p = p +1;
if p<10 then iterate my_loop;
elseif p >20 then leave my_loop;
end if;
select 'p is between 10 and 20';
end loop my_loop;
end//
delimiter ;
#执行后会多次打印下面的内容
MariaDB [oldboy]> call doiterate();
+------------------------+
| p is between 10 and 20 |
+------------------------+
| p is between 10 and 20 |
+------------------------+
1 row in set (0.00 sec)
修改存储过程
delimite //
CREATE PROCEDURE num_from_student(IN _birth DATE,OUT count_num INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) INTO count_num
FROM student
WHERE sbirthday=_birth;
END//
delimiter ;
alter procedure dowhnum_from_studentile
modifiles sql data # 将读写权限修改为modifiels sql data, 并指名调用者可以执行
sql security inviker;
删除存储过程
drop procedure if exists age_from_test;
MariaDB [oldboy]> drop procedure if exists age_from_test;
Query OK, 0 rows affected (0.00 sec)
存储函数
存储过程中的语句在存储函数中都可以用，但不能指定in,out,inout,且必须有返回return,当return语句中包含select时，返回结果只能有一行
创建存储函数
delimiter //
create function get_name(_id int)
returns char(6)
begin
return (select name from test where id=_id);
end//
delimiter ;
# 当添加in,out 等关键字时会报错，但mysql5.7从入门到精通书里竟然可以加，我实践后报错，然后help create function udf 提示的也没有这个参数。
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'in _id int)
调用存储函数
select get_name(1);
MariaDB [oldboy]> select get_name(1);
+-------------+
| get_name(1) |
+-------------+
| jack |
+-------------+
1 row in set (0.00 sec)
查看存储函数
MariaDB [oldboy]> show function status like 'get_name' \G
*************************** 1. row ***************************
Db: oldboy
Name: get_name
Type: FUNCTION
Definer: root@%
Modified: 2020-03-27 17:40:12
Created: 2020-03-27 17:40:12
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
修改存储函数
alter function get_name from oldboy
reads sql data
comment 'get name';
删除存储函数
drop function function_name;
ref: 《mysql网络数据库开发》 《mysql5.7 从入门到精通》
