【mysql】三、mysql的学习---存储过程和函数
mysql的学习
资料来源 https://www.bilibili.com/video/BV1CZ4y1M7MQ?from=search&seid=3518646188262100291
三、存储过程和函数：【mysql】三、mysql的学习---存储过程和函数
四、触发器：【mysql】四、mysql的学习---触发器
五、存储引擎：【mysql】五、mysql的学习---存储引擎
六、SQL优化：【mysql】六、mysql的学习---SQL优化
七、应用优化：【mysql】七、mysql的学习---应用优化
八、查询缓存：【mysql】八、mysql的学习---查询缓存
九、内存优化：【mysql】九、mysql的学习---内存优化
十、Mysql并发参数调整和锁： 【mysql】十、mysql的学习---Mysql并发参数调整和锁
十一、常用的SQL技巧：【mysql】十一、mysql的学习---常用的sql技巧
本篇文章主要介绍 存储过程和函数 的相关知识
1. 创建存储过程
-- delimiter 该关键字用来声明sql语句的分隔符,告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。默认情况下,delimiter是分号;。在命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令。 DROP PROCEDURE IF EXISTS `pro_test1`; delimiter $ CREATE PROCEDURE `pro_test1`() begin select 'hello Mysql'; end$ delimiter ;
2. 调用存储过程
call pro_test1()
3. 查看存储过程
-- 查询存储过程的状态信息 show PROCEDURE status
--查询某个存储过程的定义 show create PROCEDURE test.pro_test1
-- 查看当前数据库存储过程信息 show procedure status where db='test';
4. 删除存储过程
drop PROCEDURE if exists pro_test1
5. 语法
-- 存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。
5.1 declare && set
-- 通过declare可以定义一个局部变量，该变量的作用范围只能在begin..end块中。 -- 直接赋值使用set，可以赋常量或者赋表达式 DROP PROCEDURE IF EXISTS `pro_test2`; delimiter $ CREATE PROCEDURE `pro_test2`() begin declare num int default 0; declare t_name VARCHAR(20); set num=num+10; set t_name = '张三'; select num; select t_name; end$ delimiter ;
5.2 select...into
--- 使用select...into赋值 DROP PROCEDURE IF EXISTS `pro_test4`; delimiter $ CREATE PROCEDURE `pro_test4`() begin declare num int; select count(*) into num from city ; select num; end$ delimiter ;
5.3 if条件判断
-- 语法为： -- if then -- elseif then -- else -- end if; DROP PROCEDURE IF EXISTS `pro_test5`; delimiter $ CREATE PROCEDURE `pro_test5`() begin declare height int default 175; declare description varchar(50) default ''; if height >= 180 then set description='高挑身材'; elseif height>=170 and height <180 then set description='标准身材'; else set description='一般身材'; end if; select concat('身高',height,'对应的身材类型为：', description); end$ delimiter ;
5.4 传入参数
-- in 输入参数 -- out 输出参数 -- inout 既作为输入参数又作为输出参数 DROP PROCEDURE IF EXISTS `pro_test6`; delimiter $ CREATE PROCEDURE `pro_test6`(in height int) begin declare description varchar(50) default ''; if height >= 180 then set description='高挑身材'; elseif height>=170 and height <180 then set description='标准身材'; else set description='一般身材'; end if; select concat('身高',height,'对应的身材类型为：', description); end$ delimiter ; DROP PROCEDURE IF EXISTS `pro_test7`; delimiter $ CREATE PROCEDURE `pro_test7`(in height int, out description varchar(50)) begin if height >= 180 then set description='高挑身材'; elseif height>=170 and height <180 then set description='标准身材'; else set description='一般身材'; end if; end$ delimiter ; -- 前面加上@符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似全局变量一样 -- 前面加上@@符号,这种叫做系统变量 call pro_test7(177,@descriptionTemp) select @descriptionTemp
5.5 case结构
-- 语法： -- case -- when then -- when then -- end case; DROP PROCEDURE IF EXISTS `pro_test8`; delimiter $ CREATE PROCEDURE `pro_test8`(month int) begin declare result varchar(10); case when month>=1 and month <=3 then set result='第一季度'; when month>=4 and month <=6 then set result='第二季度'; when month>=7 and month <=9 then set result='第三季度'; when month>=10 and month <=12 then set result='第四季度'; end case; select result; end$ delimiter ;
5.6 while循环
-- 计算从1加到n的值 -- 语法:满足条件继续循环 -- while do -- end while; DROP PROCEDURE IF EXISTS `pro_test9`; delimiter $ CREATE PROCEDURE `pro_test9`(n int) begin declare sum int default 0; declare i int default 0; while i<=n do set sum = sum+i; set i = i+1; end while; select concat('1到',n,'之和=',sum) AS content; end$ delimiter ;
5.7 repeat循环
-- 语法：满足条件退出循环 -- repeat -- until DROP PROCEDURE IF EXISTS `pro_test10`; delimiter $ CREATE PROCEDURE `pro_test10`(n int) begin declare sum int default 0; declare i int default 0; repeat set sum = sum +n; set n = n - 1; until n=0 end repeat; select concat('1到',n,'之和=',sum) AS content; end$ delimiter ;
5.8 loop循环
-- loop实现简单的循环,退出循环的条件需要使用其它的语句定义，通常可以使用leave语句实现 -- 下面的c随便定义 DROP PROCEDURE IF EXISTS `pro_test11`; delimiter $ CREATE PROCEDURE `pro_test11`(n int) begin declare s int default 0; declare i int default 0; c: loop set s=s+n; set n=n-1; if n<=0 then leave c; end if; end loop c; select concat('1到',n,'之和=',s) AS content; end$ delimiter ;
5.9 leave语句
-- 用来退出循环，具体实例参考loop循环部分
5.10 游标/光标
-- 游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、open、fetch和close。 -- (1)声明光标：declare cursor_name cursor for select_statement -- (2)open光标：open cursor_name; -- (3)fetch光标：fetch cursor_name into var_name [,var_name] -- (4)clase光标：close cursor_name; create table emp( id int(11) not null auto_increment, name varchar(50) not null comment'姓名' , age int(11) comment'年龄' , salary int(11) comment'薪水' , primary key(id) )engine=innodb, default charset=utf8; insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000), (null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800); drop procedure pro_test12; delimiter $ create procedure pro_test12() begin declare e_id int(11); declare e_name varchar(100); declare e_age int(11); declare e_salary int(11); declare has_data int default 1; declare emp_result cursor for select * from emp; declare exit handler for not found set has_data=0; open emp_result; repeat fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id,'name=',e_name,'age=',e_age,'salary=',e_salary); until has_data=0 end repeat; close emp_result; end$ delimiter ;
6. 存储函数
delimiter $ create function fun1(countryId int) returns int begin declare cnum int; select count(*) into cnum from city where country_id = countryId; return cnum; end$ delimiter ;
持续更新！！！
