mysql 存储过程for新增数据 mysql存储过程使用

整理收藏存储过程相关语法和样例，方便后续使用；

【创建存储过程】

语法结构：

CREATE PROCEDURE <过程名>(参数列表 参数类型)
BEGIN
	<过程体 sql语句>
END

使用样例：

-- 1、创建存储过程
-- 返回为列表多条记录,但无外围接收变量，个人感觉无意见
drop PROCEDURE IF EXISTS getUserList;
-- 注1: 在mysql v5.7版本使用if not exists报错，在v8.0版本无问题； CREATE PROCEDURE if not exists getUserList ()
CREATE PROCEDURE getUserList ()
 BEGIN
  SELECT id, username, `password` FROM user ORDER BY score DESC LIMIT 20;
END;

-- 调用
call getUserList();

【删除存储过程】

语法结构：

DROP PROCEDURE 过程名;           #删除的时候不要加小括号，直接给定存储过程的名字即可
DROP PROCEDURE IF EXISTS 过程名; #如果存储删除，不存在不删除并且不会报错

使用样例：

-- 2、删除存储过程
-- 如果存在即删除存储过程
drop PROCEDURE IF EXISTS getUserList;

【调用存储过程】

语法结构：

CALL 过程名();

使用样例：

-- 3、调用存储过程(这里返回的是列表多条记录)
call test.getUserList();

【查看存储过程】

# 查询存储过程的状态信息
show procedure status;  

# 查询指定数据库中的所有的存储过程
select name from mysql.proc where db='test'; 

 # 根据存储过程名称查询 
SELECT name from mysql.proc where name like '%calculate%'

【变量声明和赋值】

语法结构：

DECLARE 变量名[,...] type [DEFAULT value]

使用样例：

-- 5、变量的声明和赋值
drop PROCEDURE calculate;
CREATE PROCEDURE calculate ()
BEGIN
 DECLARE num1 INT;
 DECLARE num2 INT DEFAULT 1;
 SELECT num1 + num2;
 #SELECT num1 * num2;
 #SELECT num2 - num1;
END
call calculate(); 
#num1是没有默认值的，所以SELECT num1 + num2; 是一个null值; 
#如赋值为给num1=0，返回值是1；
#如将“num1 + num2;”修改为“num1 * num2;”返回为0；
#如将"num1 + num2;"修改为"num2 - num1;"返回为null;
#表示如果声明变量不设置默认值，它与任何变量做基本运算返回将为null值；

变量赋值(SET)

注意：一次可以给多个变量赋值，中间使用逗号隔开。
语法结构：

SET 变量名 = 变量值 [,变量名 = 变量值] ...

使用样例：

-- 变量的赋值
CREATE PROCEDURE calculate2 ()
BEGIN
 DECLARE num1 INT;
 DECLARE num2 INT DEFAULT 1;
 SET num1=2, num2=3;
 SELECT num1 + num2;
END
call calculate2(); # 5

变量赋值(SELECT INTO)

语法结构：

SELECT <column | 聚合函数> INTO 变量名;

使用样例：

-- INTO赋值
 CREATE PROCEDURE calculate3 ()
 BEGIN
  DECLARE num1 INT;
  DECLARE num2 INT DEFAULT 1;
  SELECT count(1) INTO num1 FROM user;
  SELECT num1+num2;
 END
 
call calculate3(); # 8

条件判断(IF)

语法格式：

# 只有满足差选条件才会执行 then 后面的SQL语句
if search_condition(查询条件) then statement_list(SQL语句)
    [else if search_condition(查询条件) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end if;

使用样例：

-- if条件判断
 drop PROCEDURE if EXISTS calculate4;
 CREATE PROCEDURE calculate4 (IN uid INT(11))
 BEGIN
  DECLARE scoreStatus VARCHAR(20);
  DECLARE scoreValue INT(11);
  SELECT score INTO scoreValue FROM user WHERE id=uid LIMIT 1;
  IF scoreValue >= 90 THEN
   SET scoreStatus='成绩【优】';
  ELSEIF scoreValue >=80 THEN
    SET scoreStatus='成绩【良】';
  ELSEIF scoreValue >=60 THEN
    set scoreStatus ='成绩【中】'; 
  ELSE
   SET scoreStatus ='成绩【差】';
  END IF;
  SELECT scoreStatus;
 END
 call calculate4(4);

传递参数(in/out/inout)

语法格式：

CREATE PROCEDURE <过程名>([in/out/inout] 参数列表 参数类型)
BEGIN
	<过程体 sql语句>
END

1. in : 该参数可以作为输入，调用该存储过程需要传入的值，默认的选项
2. out : 该参数作为输出，调用该存储过程之后返回的值。
3. inout : 既可以作为输入参数也可以作为输出参数

in

-- in : 该参数可以作为输入，调用该存储过程需要传入的值，否则返回默认值
 drop PROCEDURE if EXISTS calculate5;
 CREATE PROCEDURE calculate5 (IN scoreValue INT(11))
 BEGIN
  DECLARE scoreStatus VARCHAR(20);
  IF scoreValue >= 90 THEN
   SET scoreStatus='成绩【优】';
  ELSEIF scoreValue >=80 THEN
    SET scoreStatus='成绩【良】';
  ELSEIF scoreValue >=60 THEN
    set scoreStatus ='成绩【中】'; 
  ELSE
   SET scoreStatus ='成绩【差】';
  END IF;
  SELECT scoreStatus;
 END
 
call calculate5(80);

out

-- out : 该参数作为输出，调用该存储过程之后返回的值。
drop PROCEDURE if EXISTS calculate6;
CREATE PROCEDURE calculate6 (IN scoreValue INT(11), OUT scoreStatus varchar(20))
BEGIN
 IF scoreValue >= 90 THEN
  SET scoreStatus='成绩【优】';
 ELSEIF scoreValue >=80 THEN
   SET scoreStatus='成绩【良】';
 ELSEIF scoreValue >=60 THEN
   set scoreStatus ='成绩【中】'; 
 ELSE
  SET scoreStatus ='成绩【差】';
 END IF;
 SELECT scoreStatus;
END

call calculate6(0, @scoreStatus);
SELECT @scoreStatus;

inout

-- inout : 既可以作为输入参数也可以作为输出参数
drop PROCEDURE if EXISTS calculate6;
CREATE PROCEDURE calculate6 (INOUT inoutStr varchar(20))
BEGIN
 IF inoutStr='优'  THEN
  SET inoutStr='成绩【优】';
 ELSEIF inoutStr='良' THEN
   SET inoutStr='成绩【良】';
 ELSEIF inoutStr='中' THEN
   set inoutStr ='成绩【中】'; 
 ELSE
  SET inoutStr ='成绩【差】';
 END IF;
 SELECT inoutStr;
END

set @inoutStr="优";
call calculate6(@inoutStr);
SELECT @inoutStr as inoutStrValue;

修改和获取系统变量

-- @@global : 这种在变量名前加上 “@@” 符号，叫做系统变量。
-- 通过@@修改和获取系统变量
-- 1、读取最大链接数 
 SELECT @@max_connections;
-- 2、更新最大链数 
 SET GLOBAL max_connections = 100;

case结构

语法格式：

# 方式一
case case_value(判断的值)
    when when_value(比较的值) then statement_list(SQL语句)
    [when when_value(比较的值) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end case;

# 方式二
case 
    when search_condition(查询条件) then statement_list(SQL语句)
    [when search_condition(查询条件) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end case;

使用样例(方式一):

drop PROCEDURE if EXISTS calculate71;
CREATE PROCEDURE calculate71 (IN scoreValue INT(11), OUT scoreStatus VARCHAR(20))
BEGIN
 CASE scoreValue != null && scoreValue > 100
	 WHEN scoreValue >= 90 THEN
		SET scoreStatus='成绩【优】';
	 WHEN scoreValue >= 80 THEN
		SET scoreStatus='成绩【良】';
	 WHEN scoreValue >= 60 THEN
		SET scoreStatus='成绩【中】';
	 WHEN scoreValue>=1 THEN 
	  SET scoreStatus='成绩【差】';
	 ELSE
		SET scoreStatus='成绩数据异常';
 END CASE;
END

set @scoreStatus = '暂无';
call calculate71(150, @scoreStatus);
SELECT @scoreStatus as scoreStatusVal;

使用样例(方式二):

drop PROCEDURE if EXISTS calculate7;
CREATE PROCEDURE calculate7 (IN scoreValue INT(11), OUT scoreStatus VARCHAR(20))
BEGIN
 CASE 
 WHEN scoreValue >= 90 THEN
  SET scoreStatus='成绩【优】';
 WHEN scoreValue >= 80 THEN
  SET scoreStatus='成绩【良】';
 WHEN scoreValue >= 60 THEN
  SET scoreStatus='成绩【中】';
 ELSE
  SET scoreStatus='成绩【差】';
 END CASE;
END

set @scoreStatus = '暂无';
call calculate7(null, @scoreStatus);
SELECT @scoreStatus as scoreStatusVal;

while循环

语法结构：

# 只要查询条件一直成立就会一直指定do后面的SQL语句，当查询条件不成立的时候直接跳出while循环
while search_condition(查询条件) do
    statement_list(SQL语句)
end while;

使用样例：

-- while结构
-- 需求：获取指定数内奇数的累计值
drop PROCEDURE IF EXISTS calculate8;
CREATE PROCEDURE calculate8 (IN num INT(11))
BEGIN
 DECLARE number INT DEFAULT 0;
 DECLARE total INT DEFAULT 0;

 WHILE number <= num DO
   IF number % 2 = 0 THEN
		 SET total = total + number;
   END IF;
	 SET number = number + 1;
 END WHILE;
 
 SELECT total;
END

call calculate8(10);

repeat循环

语法格式:

repeat 
    statement_list(SQL语句)
    until search_condition(查询添加)
end repeat;

使用样例：

drop PROCEDURE calculate9;
CREATE PROCEDURE calculate9 (IN num INT(11))
BEGIN
 DECLARE total INT DEFAULT 0;
 REPEAT 
    set total = total + num;
    set num = num - 1;
    # 注意：这个 unti 后的查询条件不要加分号，加分号会报错。
    until num = 0
  END REPEAT;
 SELECT total;
END
call calculate9(10); 
-- call calculate9(0); #注：这里如果传值为0,会一直执行，并没有调出循环，不知是否为使用或语法问题

loop循环与leave语句

语法结构:

[begin_label:] loop
    statement_list
end loop [end_label]

• loop：实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用leave语句实现，
• 如果不在statement_list中增加退出循环的语句，那么loop语句可以永安里实现简单的死循环。
• leave：用来从标注的流程构造中退出，通常和 begin…end 或循环一起使用。

CREATE PROCEDURE calculate10 (IN num INT(11))
 BEGIN
 	DECLARE total INT DEFAULT 0;
 	mySum:loop
         set total = total + num;
         set num = num - 1;
         # 借助leave组织退出条件
         if num <= 0 then
             leave mySum;
         end if; 
     end loop mySum;
 	SELECT total;
 END
 call calculate10(10);

-- 需求：查询排名前多少位用户的总分数
drop PROCEDURE if EXISTS calculate11;
CREATE PROCEDURE calculate11 (IN num INT(11))
BEGIN
	DECLARE total INT DEFAULT 0;    #总分数
	DECLARE scoreVal INT DEFAULT 0; #每次循环用户分数
	DECLARE offsetVal INT DEFAULT 0;#每次查询SQL的偏移值
	mySum:loop
		SELECT score INTO scoreVal from user order by score DESC limit offsetVal, 1;
    set total = total + scoreVal;   #添加总分数
		set offsetVal = offsetVal + 1;  #对偏移值+1
    set num = num - 1; #将需要查询的用户总数减1
    # 当获取的用户总结束时，借助leave组织退出条件
    if num <= 0 then
       leave mySum;
    end if; 
  end loop mySum;
	SELECT total;
END

call calculate11(2);

游标使用(循环遍历)

游标（英文：Cursor）是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。
语法结构：

DECLARE cursor_name CURSOR FOR select_statement;  #创建游标
OPEN cursor_name; #条开游标
FETCH cursor_name INTO var_name...; #从游标中获取数据
CLOSE cursor_name; #关闭游符
DEALLOCATE PREPARE cursor_name; #释放游标

使用样例：

#通过游标将所有分组小于60分的用户，添加10分；
drop PROCEDURE if EXISTS updateScore;
CREATE PROCEDURE updateScore()
BEGIN

	DECLARE uid VARCHAR(20);
	DECLARE scoreValue VARCHAR(20);
	DECLARE done INT DEFAULT FALSE;

    # 定义游标
	DECLARE curr CURSOR FOR SELECT id, score FROM user;

	# 声明异常处理
	# 执行到没有查询结果的sql语句，会抛出NOT FOUND，这样游标将不会继续循环；
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

    # 打开游标
	OPEN curr;
	
	# 循环读取
	read_loop : LOOP
		# 获取游标数据
		FETCH NEXT FROM curr INTO uid, scoreValue;
		# 判断游标结束
		IF done THEN
			LEAVE read_loop;
		END IF;

		# 调用更新线上数据脚本
		IF uid is not null AND scoreValue is not null THEN
			update user set score = score+10 where id = uid;
		END IF;
 	END LOOP;
	
	# 关闭游标
	CLOSE curr;

	# 释放游标 v5.7/v8.0报错 Unknown prepared statement handler (curr) given to DEALLOCATE PREPARE 
	#DEALLOCATE PREPARE curr;
END	

call updateScore();

测试表结构如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `city` varchar(128) DEFAULT NULL,
  `area` varchar(128) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `unixtime` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;