mysql 存储过程

访问量:1646 创建时间:2021-04-25

存储过程和函数概述

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程和函数可以简化应用开发人员很多工作，减少数据在数据库和应用服务器之间的传输，有利于提高数据处理效率。存储过程和函数的区别在于函数必须有返回值，而存储过程没有。函数：是一个有返回值的过程；过程：是一个没有返回值的函数。

存储过程创建、调用、查看、删除

创建

create procedure procedure_name ([porc_parameter[,...]])
begin
  -- SQL语句
end;
###示例：(delimiter用于定义mysql语句的分隔符)
delimiter $
create procedure pro_test1()
begin
     select 'hello Mysql';
end $
delimiter ;

调用

call procedure_name();
###示例
mysql> call pro_test1();
+-------------+
| hello Mysql |
+-------------+
| hello Mysql |
+-------------+
1 row in set (0.17 sec)

查询存储过程

##试了一下mysql8应该和老版本的查看有所不同
mysql> SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES  WHERE ROUTINE_SCHEMA='demo_01';
+--------------+--------------+
| ROUTINE_TYPE | ROUTINE_NAME |
+--------------+--------------+
| PROCEDURE    | pro_test1    |
+--------------+--------------+
###查看存储过程定义信息
mysql> show create procedure demo_01.pro_test1\G
*************************** 1. row ***************************
           Procedure: pro_test1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `pro_test1`()
begin
     select 'hello Mysql';
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.35 sec)

删除存储过程

drop procedure pro_test1;

存储过程的语法结构

存储过程是可以编程的，意味着可以使用变量，表达式，控制语句，来完成比较复杂的功能。

变量

DECLARE ：通过DECLARE 可以定义一个局部变量，该变量的作用范围只能在BEGIN...END块中。

DECLARE var_name[,...] type [DEFAULT value]
###示例：
delimiter $
create procedure pro_test2()
  begin declare num int default 5;
  select num+ 10;
end$
delimiter ;

SET ：直接赋值使用set，可以赋常量或者表达式，具体语法如下：

set var_name = expr [,var_name=expr] ...
示例：
delimiter $
create procedure pro_test3()
begin
  declare name  varchar(20);
  set name = 'MYSQL';
  select name;
end$
delimiter ;

使用select ... int 方式进行赋值操作

delimiter $
create procedure pro_test4()
begin
  declare countnum  int;
  select count(*) into countnum from city;
  select countnum;
end$
delimiter ;
mysql> call pro_test4();
+----------+
| countnum |
+----------+
|        4 |
+----------+

if 条件判断

if search_condition then statement_list
 [elseif search_condition then statement_list]...
 [slse statement_list]
end if;

需求：根据定义的身高变量，判断当前身高的所属的身材类型180 及以上 ---》身材高挑；170 -180 --》标准身材；170以下 ---》一般身材

#示例
delimiter $
create procedure pro_test6()
begin
     declare height int default 175;
     declare description varchar(50);
     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 ;
####调用
mysql> call pro_test6();
+-------------------------------------------------------------+
| concat('身高',height,'对应的身材类型: ',description)          |
+-------------------------------------------------------------+
| 身高175对应的身材类型: 标准身材                               |
+-------------------------------------------------------------+
1 row in set (0.02 sec)

参数传递

create procedure procedure_name([in/out/inout] 参数名 参数类型)
...
IN 该参数可以作为输入，也就是需要调用方传入的值，默认。
OUT 该参数作为输出，也就是该参数可以作为返回值。
INOUT 既可以作为输入参数，也可以作为输出参数

IN输入：需求定义的身高变量，判断当前身高的所属身材类型。

delimiter $
create procedure pro_test7(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 ;
###调用
mysql> call pro_test7(180);
+---------------------------------------------------------------+
| concat('身高',height,'对应的身材类型: ',description)          |
+---------------------------------------------------------------+
| 身高180对应的身材类型: 身材高挑                               |
+---------------------------------------------------------------+
1 row in set (0.02 sec)

OUT输出

delimiter $
create procedure pro_test8(in height int, out description varchar(100))
begin
     if height >= 180 then
         set description='身材高挑';
     elseif height>=170 and height< 180 then
         set description='标准身材';
     else
         set description='一般身材';
     end if;
end$
delimiter ;
####调用测试， 
mysql> call pro_test8(175,@des);
Query OK, 0 rows affected (0.00 sec)

mysql> select @des;
+--------------+
| @des         |
+--------------+
| 标准身材     |
+--------------+
1 row in set (0.00 sec)
####@变量 ：表时用户会话变量，当前连接有效，@@变量：时系统变量。
mysql> set name='aaas';
ERROR 1193 (HY000): Unknown system variable 'name'
mysql> set @name='aaas';
Query OK, 0 rows affected (0.00 sec)

mysql> select @name;
+-------+
| @name |
+-------+
| aaas  |
+-------+

case结构

语法结构：

#方式一
case case_value
  when when_value then statement_list
  [when when_value then statement_list] ...
  [else statement_list]
end case;
#方式二
case
  when search_condition then statement_list
  [when search_condition then statement_list] ...
  [else statement_list]
end case;

需求：给定一个月份，然后计算出所在的季度

delimiter $
-- 默认输入参数可以省略in
create procedure pro_test9(mon int)
begin
    declare result varchar(10);
    case
        when mon>=1 and mon <=3 then
        set result='第一季度';
        when mon>=4 and mon <=6 then
        set result='第二季度';
        when mon>=7 and mon <=9 then
        set result='第三季度';
        else
        set result='第四季度';
    end case;
    select concat('传递的月份为:',mon,',计算的结果为:',result) as content;
end$
delimiter ;
###调用测试
mysql> call pro_test9(8);
+------------------------------------------------------+
| content                                              |
+------------------------------------------------------+
| 传递的月份为:8,计算的结果为:第三季度                    |
+------------------------------------------------------+

while循环,语法：

while search_condition do
  statement_list
end while

需求从1加到n的值，示例：

delimiter $
-- 默认输入参数可以省略in
create procedure pro_test10(n int)
begin
    declare total int default 0;
    declare num int default 1;
    while num <= n do
        set total = total+num;
        set num=num+1;
    end while ;
    select total;
end$
delimiter ;
###调用
mysql> call pro_test10(100);
+-------+
| total |
+-------+
|  5050 |
+-------+

repeat结构，有条件循环控制语句，当满足条件的时候退出循环。while是满足条件才执行，repeat是满足条件就退出循环。

repeat
  statement_list
  until search_condition
end repeat;

需求，计算从1加到n:

delimiter $
-- 默认输入参数可以省略in
create procedure pro_test11(n int)
begin
    declare total int default 0;
    repeat
        set total=total +n;
        set n=n-1;
    until  n=0
    end repeat ;
    select total;
end$
delimiter ;
###调用测试
mysql> call pro_test11(50);
+-------+
| total |
+-------+
|  1275 |
+-------+

loop循环，实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用leave语句实现，语法如下：

[begin_label:] loop
  statement_list
end loop [end_label]

如果不在statement_list中早呢更加循环的语句，那么loop语句可以用来实现简单的死循环

delimiter $
-- 默认输入参数可以省略in
create procedure pro_test12(n int)
begin
    declare total int default 0;
    c:loop
        set total=total+n;
        set n = n-1;
        if n<=0 then
            leave c;
        end if ;
    end loop c;
    select total;
end$
delimiter ;
###调用测试
mysql> call pro_test12(4);
+-------+
| total |
+-------+
|    10 |
+-------+

游标/光标: 是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果进行循环的处理。光标的使用包括光标的声明、open、fetch和close,其语法分别如下：

#声明光标
declare cursor_name cursor for select_statement;
#open光标
open cursor_name;
#fetch光标
fetch cursor_name into var_name [,var_name]...
#close 光标
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,3300),(null,'白眉鹰王',44,3100),(null,'紫衫龙王',40,2900),(null,'张无忌',40,2900);
###创建存储过程
delimiter $
create procedure  pro_test13()
begin
    declare e_id int(11);
    declare e_name varchar(50);
    declare e_age int(11);
    declare  e_salary int(11);
    declare emp_result cursor for select * from emp;
    open emp_result;
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary);
    close emp_result;
end;
delimiter ;
####调用存储过程
mysql> call pro_test13();
+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=1,name=金毛狮王,e_age=55,e_salary=3300                                |
+--------------------------------------------------------------------------+
1 row in set (0.35 sec)

+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=2,name=白眉鹰王,e_age=44,e_salary=3100                                |
+--------------------------------------------------------------------------+

###完善的获取数据方法
delimiter $
create procedure  pro_test14()
begin
    declare e_id int(11);
    declare e_name varchar(50);
    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,',e_age=',e_age,',e_salary=',e_salary);
        until has_data=0
    end repeat ;
    close emp_result;
end;
delimiter ;

###调用测试
mysql> call pro_test14();
+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=1,name=金毛狮王,e_age=55,e_salary=3300                                |
+--------------------------------------------------------------------------+
1 row in set (0.36 sec)

+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=2,name=白眉鹰王,e_age=44,e_salary=3100                                |
+--------------------------------------------------------------------------+
1 row in set (0.36 sec)

+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=3,name=紫衫龙王,e_age=40,e_salary=2900                                |
+--------------------------------------------------------------------------+
1 row in set (0.36 sec)

+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=4,name=张无忌,e_age=40,e_salary=2900                                  |
+--------------------------------------------------------------------------+
1 row in set (0.37 sec)

存储函数

语法结构：

create function function_name([param type ...])
return type
begin
  ...
end;

案例：定义一个存储函数，请求满足条件的总记录数(创建如果报错：1418-此函数中没有声明DETERMINISTIC，NO SQL或READS SQL DATA，只用声明才会启用二进制日志记录，您可能想使用不太安全的log_bin_trust_function_creators变量)

delimiter $$
create function fun1(countryId int)
returns int
NO SQL
begin
    declare cnum int;
    select count(*) into cnum from city where country_id=countryId;
    return cnum;
end $$
delimiter ;
mysql> select fun1(1);
+---------+
| fun1(1) |
+---------+
|       3 |
+---------+

创建报错：ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
第一种办法，需要声明DETERMINISTIC，NO SQL或READS SQL DATA的一种。
第二种办法，信任子程序的创建者，设置变量log_bin_trust_function_creators值为1。

-- 查看该参数，默认为0
select @@log_bin_trust_function_creators;
-- 设置为1
set GLOBAL log_bin_trust_function_creators=1;

删除存储函数

mysql> drop function fun1;

<< 上一章节

下一章节 >>

登陆评论: 使用GITHUB登陆