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_idtype,输出参数为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语句,但是却不准包含在存储过程中。
相关推荐
<p style="text-align:left;"> <span> </span> </p> <p class="ql-long-24357476" style="font-size:11pt;color:#494949;"> <span style="font-family:"color:#E53333;font-size:14px;background-color:#FFFFFF;line-height:24px;"><span style="line-height:24px;">限时福利1:</span></span><span style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;">购课进答疑群专享柳峰(刘运强)老师答疑服务。</span> </p> <p> <br /> </p> <p class="ql-long-24357476"> <strong><span style="color:#337FE5;font-size:14px;">为什么说每一个程序员都应该学习MySQL?</span></strong> </p> <p class="ql-long-24357476"> <span style="font-size:14px;">根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。</span> </p> <p class="ql-long-24357476"> <img src="https://img-bss.csdn.net/202003301212574051.png" alt="" /> </p> <p class="ql-long-24357476"> <span style="font-size:14px;">使用量大同时,掌握MySQL早已是运维、DBA的必备技能,甚至部分IT开发岗位也要求对数据库使用和原理有深入的了解和掌握。</span><br /> <br /> <span style="font-size:14px;">学习编程,你可能会犹豫选择 C++ 还是 Java;入门数据科学,你可能会纠结于选择 Python 还是 R;但无论如何, MySQL 都是 IT 从业人员不可或缺的技能!</span> </p> <span></span> <p> <br /> </p> <p> <span> </span> </p> <h3 class="ql-long-26664262"> <p style="font-size:12pt;"> <strong class="ql-author-26664262 ql-size-14"><span style="font-size:14px;color:#337FE5;">【课程设计】</span></strong> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;"><br /> </span> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;font-size:14px;">在本课程中,刘运强老师会结合自己十多年来对MySQL的心得体会,通过课程给你分享一条高效的MySQL入门捷径,让学员少走弯路,彻底搞懂MySQL。</span> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;"><br /> </span> </p> <p style="font-size:12pt;"> <span style="font-weight:normal;font-size:14px;">本课程包含3大模块:</span><span style="font-weight:normal;font-size:14px;"> </span> </p> </h3> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">一、基础篇:</span></strong> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;">主要以最新的MySQL8.0安装为例帮助学员解决安装与配置MySQL的问题,并对MySQL8.0的新特性做一定介绍,为后续的课程展开做好环境部署。</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;"><br /> </span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">二、SQL语言篇</span></strong><span class="ql-author-26664262" style="font-size:14px;">:</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;">本篇主要讲解SQL语言的四大部分数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL,</span><span style="font-size:14px;">学会熟练对库表进行增删改查等必备技能。</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span style="font-size:14px;"><br /> </span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">三、MySQL进阶篇</span></strong><span style="font-size:14px;">:</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span style="font-size:14px;">本篇可以帮助学员更加高效的管理线上的MySQL数据库;具备MySQL的日常运维能力,语句调优、备份恢复等思路。</span> </p> <span><span> <p style="font-size:11pt;color:#494949;"> <span style="font-size:14px;"> </span><img src="https://img-bss.csdn.net/202004220208351273.png" alt="" /> </p> </span></span>
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页