mysql 如果存在就删除存储过程

转载

mob64ca141139a2 2024-09-26 19:30:26

文章标签 mysql 如果存在就删除存储过程存储过程 mysql ci 文章分类 MySQL 数据库

文章目录

1. 概念
2. 使用

2.1 创建
2.2 调用
2.3 查看
2.4 删除

3. 语法

3.1 变量
3.2 条件判断
3.3 参数传递
3.4 case结构
3.5 while循环
3.6 repeat结构
3.7 loop
3.8 leave
3.9 游标

4. 存储函数
5. 总结

1. 概念

存储过程和存储函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和存储函数的区别就在于：过程是没有返回值的函数，函数是有返回值的过程。

2. 使用

创建存储过程和存储函数之前，可以使用delimiter $来将mysql中命令的结束符;替换为其他的字符。创建结束后，可以使用delimiter ;再改回来即可。

下面使用的演示继续使用之前用的那张accoutn表：

mysql> select * from account;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | Forlogen |  1000 |
|  2 | Kobe     |   500 |
|  3 | James    |   800 |
+----+----------+-------+
3 rows in set (0.00 sec)

2.1 创建

创建存储过程的语法如下：

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end ;

例如，使用account表来创建一个存储过程：

delimiter $

create procedure account_pro()
begin
	select * from account where money >= 800;
end$

delimiter ;

2.2 调用

调用创建好的存储过程使用call preocedure_name();即可，例如调用上面的account_pro：

mysql> call account_pro();
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | Forlogen |  1000 |
|  3 | James    |   800 |
+----+----------+-------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

2.3 查看

如果使用命令来查看存储过程，可以使用如下SQL语句：

-- 查询存储过程的状态信息
show procedure status like 'procedure_name' \G;
-- 查询某个存储过程的定义
show create procedure test.pro_test1 \G;

具体的使用效果如下：

mysql> show procedure status like 'account_pro' \G;
*************************** 1. row ***************************
                  Db: sql_store
                Name: account_pro
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-08-23 22:24:24
             Created: 2020-08-23 22:24:24
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> show create procedure account_pro \G;
*************************** 1. row ***************************
           Procedure: account_pro
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `account_pro`()
begin
select * from account where money >= 800;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

当然各种客户端都是可以查看的

mysql 如果存在就删除存储过程_存储过程

2.4 删除

删除直接使用drop即可，如下所示：

DROP PROCEDURE [1 IF EXISTS] sp_name;

3. 语法

上面介绍了存储过程简单的创建、调用、查看和删除，下面接着介绍一下如何使用其中所支持的各种语法来创建更加复杂的存储过程。

3.1 变量

使用declare可以用来声明变量在存储过程中使用，语法如下：

DECLARE var_name[,...] type [DEFAULT value]

例如，对上面的account的money字段求和，再打一个折扣，如下所示：

delimiter $

create procedure account_sum_pro()
begin
	declare percentage float default 0.85;
	select sum(a.money) * percentage from account a;
end$

delimiter ;

调用存储过程，结果如下：

mysql> call account_sum_pro();
+---------------------------+
| sum(a.money) * percentage |
+---------------------------+
|        1955.0000548362732 |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

此外，可以使用set进行直接赋值，可以赋常量或者表达式，语法如下：

SET var_name = expr [, var_name = expr] ...

例如：

delimiter $

create procedure account_set_pro()
begin
	declare percentage float;
	set percentage = 0.85;
	select sum(a.money) * percentage from account a;
end$

delimiter ;

调用存储过程，结果如下：

mysql> call account_set_pro();
+---------------------------+
| sum(a.money) * percentage |
+---------------------------+
|        1955.0000548362732 |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

也可以使用select ... into来进行赋值：

delimiter $

create procedure account_select_into_pro()
begin
	declare percentage float default 0.85;
	declare sum_money float;
	select sum(a.money) * percentage  into sum_money from account a;
	select sum_money;
end$

delimiter ;

调用存储过程，执行结果如下：

mysql> call account_select_into_pro();
+-----------+
| sum_money |
+-----------+
|      1955 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

3.2 条件判断

有关条件判断的基本语法如下所示：

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

继续操作上面的表：

delimiter $

create procedure account_if_pro()
begin
  declare sum_money float default 1500;
  declare description varchar(20);

  if sum_money < 500 then
    set description='poor';
  elseif sum_money >= 500 and sum_money < 1000 then
    set description='just';
  else
    set description='rich';
  end if;

  select money into sum_money from account where id=1;
  select description;
end$

delimiter ;

调用存储过程，结果为：

mysql> call account_if_pro();
+-------------+
| description |
+-------------+
| rich        |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3.3 参数传递

除了像上面在过程内部定义参数外，还可以向存储过程中传递参数，语法如下所示：

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

例如：

delimiter $

create procedure account_in_pro(in percentage float, out sum_money float)
begin
  declare sum_money float default 1500;

  select sum(a.money) * percentage into sum_money from account a;
end$

delimiter ;

调用存储过程，执行结果如下：

mysql> call account_in_pro(0.85, @sum_money);
Query OK, 1 row affected (0.00 sec)

mysql> select @sum_money;
+------------+
| @sum_money |
+------------+
|       1955 |
+------------+
1 row in set (0.00 sec)

@xxx : 这种变量要在变量名称前面加上"@"符号叫用户会话变量，代表整个会话过程都是有作用的，这个类似于全局变量一样
@@xxx : 这种在变量前加上 “@@” 符号叫系统变量

3.4 case结构

case结构的语法如下所示，case和end 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 $

create procedure account_case_pro()
begin
  declare sum_money float default 1500;
  declare description varchar(20);
  
  case
    when sum_money < 500 then
      set description='poor';
    when sum_money >= 500 and sum_money < 1000 then
      set description='just';
    when sum_money >= 1000 then 
      set description='rich';
  end case;
  
  select money into sum_money from account where id=1;
  select description;
end$

delimiter ;

调用存储过程，结果如下所示：

mysql> call account_case_pro();
+-------------+
| description |
+-------------+
| rich        |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3.5 while循环

while循环语法如下：

while search_condition do
  statement_list
end while;

假设求1到n的累加和，定义存储过程如下：

delimiter $

create procedure sum_pro(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 sum_pro(10);
+-------+
| total |
+-------+
|    55 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3.6 repeat结构

while 是满足条件才执行，repeat 是满足条件就退出循环。语法如下：

REPEAT
  statement_list
  UNTIL search_condition
END REPEAT;

同样是上面的例子，使用repeat结构实现如下所示：

delimiter $

create procedure account_repeat_pro(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 account_repeat_pro(10);
+-------+
| total |
+-------+
|    55 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3.7 loop

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

[begin_label:] LOOP
  statement_list
END LOOP [end_label]

3.8 leave

它用来从标注的流程构造中退出，通常和 begin ... end或者循环一起使用。下面使用 loop 和leave实现上面的累加求和功能 :

delimiter $

create procedure account_loop_leave(n int)
begin
  declare total int default 0;
  ins: LOOP
    if n <= 0 then
      leave ins;
    end if;
  
    set total = total + n;
    set n = n - 1;
  end loop ins;
  select total;
end$

delimiter ;

调用存储过程，结果如下：

mysql> call account_loop_leave(10);
+-------+
| total |
+-------+
|    55 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3.9 游标

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

-- 声明游标
DECLARE cursor_name CURSOR FOR 1 select_statement ;

-- open游标
OPEN cursor_name ;

-- fetch游标
FETCH cursor_name INTO var_name [, var_name] ...

-- 关闭游标
CLOSE cursor_name ;

例如，继续使用上面的account表，使用游标实现查询所有的记录并逐行输出，存储过程定义如下：

delimiter $

create procedure account_cursor()
begin
  declare id int;
  declare name varchar(40);
  declare money float;
  declare not_empty int default 1;
  
  declare res cursor for select * from account;
  declare exit handler for not found set not_empty = 0;
  
  open res;
  
  repeat
  	fetch res into id, name, money;
  	select concat('id:', id, 'name:', name, 'money=', money);
  	until not_empty = 0
  end repeat;
  
  close res;
  
end$

delimiter ;

调用存储过程，结果如下：

mysql> call account_cursor();
+-----------------------------------------------------+
| concat('id:', id, ' name:', name, ' money=', money) |
+-----------------------------------------------------+
| id:1 name:Forlogen money=1000                       |
+-----------------------------------------------------+
1 row in set (0.00 sec)

+-----------------------------------------------------+
| concat('id:', id, ' name:', name, ' money=', money) |
+-----------------------------------------------------+
| id:2 name:Kobe money=500                            |
+-----------------------------------------------------+
1 row in set (0.01 sec)

+-----------------------------------------------------+
| concat('id:', id, ' name:', name, ' money=', money) |
+-----------------------------------------------------+
| id:3 name:James money=800                           |
+-----------------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

4. 存储函数

存储函数就是一个有返回值的存储过程，它的语法如下：

CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;

例如，使用存储函数来统计account中money字段大于800的记录条数，定义存储函数如下：

set global log_bin_trust_function_creators=TRUE;

delimiter $

create function account_sum_fuc(money float)
returns int

begin
 declare number int;
 
 select count(*) into number from account a where a.money > money ;
 
 return number;
end$

delimiter ;

使用select调用存储函数，结果如下：

mysql> select account_sum_fuc(800);
+----------------------+
| account_sum_fuc(800) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)