Mysql存储过程实战

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语句，但是却不准包含在存储过程中。