Mysql存储过程实战
1.实战案例一
1.1 背景
有teacher表，建表语句如下：
create table teacher
(id int(4) primary key not null unique auto_increment,
num int(10) not null unique,
name varchar(20) not null,
sex varchar(4) not null,
birthday datetime ,
address varchar(50));
其插入数据的值如下：
insert into teacher values
(1,1001,'Spark','m','1984-11-08','上海长宁'),
(2,1002,'Hadoop','w','1994-11-28','北京昌平'),
(3,1003,'Hive','m','1978-12-08','北京海淀'),
(4,1004,'HBase','w','1990-01-08','上海静安');
1.2有如下需求
创建存储过程 teacher_info。要求：
teacher_info有三个参数。输入参数为teacher_id和type，输出参数为info。存储过程的作用是根据编号teacher_id来查询teacher表中的记录。如果type为1，将姓名那么传给输出参数info；如果type的值为2，将年龄传给输出参数info；如果type值为其它值，则返回字符串Error。
存储过程如下：
delimiter //
create procedure teacher_info(in teacher_id int,in type int,out info varchar(20))
begin
case type
when 1 then
select name into info from teacher where id = teacher_id;
when 2 then
select year(now())-year(birthday) into info from teacher where id = teacher_id;
else
select 'error' into info;
end case;
end //
delimiter ;
show create procedure;
在mysql 命令行执行如下命令：
mysql> delimiter //
mysql> create procedure teacher_info(in teacher_id int,in type int,out info varchar(20))
-> begin
-> case type
-> when 1 then
-> select name into info from teacher where id = teacher_id;
-> when 2 then
-> select year(now())-year(birthday) into info from teacher where id = teacher_id;
-> else
-> select 'error' into info;
-> end case;
-> end //
Query OK, 0 rows affected (0.07 sec)
mysql> delimiter ;
- 01.调用存储过程需要使用call关键字
- 02.输出参数必须以@打头
mysql> call teacher_info(2,1,@info);
Query OK, 1 row affected (0.04 sec)
mysql> select @info;
+--------+
| @info |
+--------+
| Hadoop |
+--------+
1 row in set (0.00 sec)
2.实战案例二
2.1 代码
DROP PROCEDURE IF EXISTS text;
delimiter //
create procedure text( out rtn int )
begin
declare LoginId INT default 0; #mysql中的DECLARE语句是在复合语句中声明变量的指令
set rtn=1;
IF LoginId = 3
THEN set rtn=2;
ELSEIF LoginId = 0
THEN set rtn=3;
ELSE set rtn=4;
END IF;
end //
delimiter ;
2.2 执行结果
mysql> call text(@rtn); #调用存储过程
Query OK, 0 rows affected (0.00 sec)
mysql> select @rtn; #选择结果
+------+
| @rtn |
+------+
| 3 |
+------+
1 row in set (0.01 sec)
3.总结
存储过程的优点：
- 01.存储过程和函数是数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。
- 02.存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器的数据传输。
- 03.存储过程和存储函数一样，都是由SQL语句和过程式语句所组成的代码片段，并且可以被应用程序和其它SQL语句调用。
特点： - 存储函数不能拥有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。
- 存储函数可以直接对存储函数进行调用，而不需要使用call语句；而对存储过程的调用，则需要使用call语句。
- 存储函数中必须包含一条return语句，但是却不准包含在存储过程中。
