MySQL 存储过程和函数

存储过程就是一条或多条sql语句的集合

创建存储过程

使用 create procedure 创建存储过程，参数类型有三种 in，out，inout

IN 表示调用存储过程给的参数值
OUT 表示输出的值调用时可以接受
INOUT 即可做输出，也可做输入

BEGIN和END之间定义存储过程体
下面这个存储过程就是查询所有fruits 的数据

使用 delimiter来定义结束符，表示一个存储过程的结束 一般使用 $$ 或者 //

 DELIMITER //
 CREATE PROCEDURE Proc()
     BEGIN
     SELECT * FROM fruits;
     END //

# 这个存储过程的作用是 把查询到的fruits数量给到 param1，这个out是传出来的值，我们可以在外面接受
CREATE PROCEDURE CountProc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM fruits;
END;

创建存储函数

存储函数需要生命函数的返回值类型
使用 create function 创建函数，参数类型和存储过程类似，IN，OUT，INOUT

 DELIMITER //
 CREATE FUNCTION NameByZip()
 RETURNS CHAR(50)
 BEGIN
 RETURN   (SELECT s_name FROM suppliers WHERE s_call= '48075');
 END  //

变量的使用****

变量使用参考链接

用户变量

用户变量使用"@"开头
有以下三种方式设置用户变量：

• set语句，此时可以使用"=“或者”:="操作符;
• select语句，此时只能使用":=“格式赋值，因为除了set语句中，”="都会被视为比较操作符。;
• select … into var_name from TABLE语句，此时要求select语句只能返回标量值，即单行数据。

系统变量

全局系统变量使用global或者"@@global.“关键字来设置。会话系统变量使用session或者”@@session."关键字来设置，其中session可以替换为Local，它们是同义词。

-- 设置全局系统变量
set global sort_buffer_size=32M;
set @@global.sort_buffer_size=32M;
-- 设置会话系统变量
set session sort_buffer_size=32M;
set @@session.sort_buffer_size=32M;
set sort_buffer_size=32M;
-- 查看全局系统变量值
select @@global.sort_buffer_size;
show global variables like "sort_buffer%";
-- 查看会话系统变量，不能使用select sort_buffer_size
select @@session.sort_buffer_size;
select @@sort_buffer_size;
show [session] variables like "sort_buffer%";

局部变量

只能在begin…and语句块中生效。它不像用户变量，本地变量必须使用declare事先声明，所以declare也必须在begin…end中使用。

# 声明变量
BEGIN
declar var_name data_type
set var_name = value;
END

# 声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值，代码如下：
DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;

# MySQL中还可以通过SELECT ... INTO为一个或多个变量赋值，语法如下：
SELECT col_name[,...] INTO var_name[,...] table_expr;

游标的使用

个人在开发中几乎没怎么用过

# 声明光标
declare cursor_name  cursor for  select_statement


# 声明名称为cursor_fruit的光标
DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits ;
上面的示例中，光标的名称为cur_fruit，SELECT语句部分从fruits表中查询出f_name和f_price字段的值。

# 打开光标
open cursor_name

# 使用光标
fetch cursor_name into var_name1,var_name2

# 关闭光标 
close cursor_name

IF 语句

示例

IF val IS NULL
  THEN SELECT 'val is NULL';
  ELSE SELECT 'val is not NULL';
END IF;

CASE 语句

case val
when val1
then xxxx
when val2
then xxxx2
else xxxxx3
end case

# 使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0，
CASE
  WHEN val IS NULL THEN SELECT 'val is NULL';
  WHEN val < 0 THEN SELECT 'val is less than 0';
  WHEN val > 0 THEN SELECT 'val is greater than 0';
  ELSE SELECT 'val is 0';
END CASE;

调用存储过程

1. 使用 call
   call procedure_name

 DELIMITER //
 CREATE PROCEDURE CountProc1 (IN sid INT, OUT num INT)
     BEGIN
       SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
     END //
 # 调用存储过程
CALL CountProc1(101,@num)
# 传出来的变量
select @num

调用存储函数

定义存储函数CountProc2，然后调用这个函数，代码如下：

# 定义了一个存储函数，返回一个int类型的数据，数据是查询所有的水果，s_id=(sid,int)
 DELIMITER //
 CREATE FUNCTION  CountProc2 (sid INT)
     RETURNS INT
     BEGIN
     RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
     END //
  DELIMITER ;
  # 调用
  select CountProce2(102)

查看存储过程和函数

使用 show procedure status like ‘存储过程名，函数’
使用 show create procedure ‘存储过程名，函数’ ----- 查看当初是怎么定义的

MySQL中的存储过程，函数的信息在 information_schema.Routines 表中

# 先看下表中有哪些字段
select * from information_schema.Routines;

如果我们还需要查询满足我们需求的内容，我们只需要对这个information_schema.Routines 中过滤就行 ，where 等

删除存储过程，函数

语法格式

drop procedure procedure_name
drop function function_name