【mysql】三、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 ;

持续更新！！！

相关阅读:
makefile实验二对目标的深入理解以及rebuild build clean的实现
 makefile实验一 make的基本原则、伪目标、以及不使用.PHONY确实现和伪目标一样功能的一种方法
 IP基础知识
 玩转Libmodbus（二）写代码体验
 故意使用free掉的内存的一个实验( 常量区/栈)
使用free掉的内存的危害
 数字签名数字证书
 哈希
 初识Makefile
约瑟夫问题及扩展问题的代码实现
原文地址：https://www.cnblogs.com/flyinghome/p/14722284.html