MySql数据库存储过程和函数

最新推荐文章于 2024-04-28 22:44:07 发布

安宁#

最新推荐文章于 2024-04-28 22:44:07 发布

阅读量197

点赞数 2

分类专栏： java学习文章标签： sql 数据库 java mysql

本文链接：https://blog.csdn.net/weixin_45084618/article/details/115197885

版权

java学习专栏收录该内容

11 篇文章 0 订阅

订阅专栏

存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化数数据库设计与开发的工作，减少数据在数据库和应用服务器之间的传输，因为应用服务器访问数据库的次数越多，数据传输效率越低，有了存储过程，应用服务器只需要访问一次数据库，就可以执行存储过程中封装的所有sql语句，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有

2 创建存储过程

语法：

CREATE PROCEDURE 存储过程名([存储过程需要的参数列表])
begin
-- 多条SQL语句
end;

案例：

-- 创建一个存储过程
-- 因为在命令行模式下end后要跟；表示存储过程结束，它前面的sql语句也要跟；表示该条语句结束，那么存储过程怎么写呢？
-- 我们可以用一条语句该别结束标记
DELIMITER $ -- 将结束标记修改为$
CREATE PROCEDURE pro_test()
BEGIN
SELECT 'Hello MySQL';
END$

3 调用存储过程

语法：

call 存储过程名()

     CALL pro_test();

4 查看存储过程

语法：

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

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

-- 查询某个存储过程的定义
show create procedure 存储过程名;

案例：

-- 查询指定数据库中的所有的存储过程
SELECT NAME FROM mysql.`proc` WHERE db='demo_01';

-- 查询存储过程的状态信息
SHOW PROCEDURE STATUS;

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE pro_test;

5 删除存储过程

语法：

drop procedure  存储结构名
     -- 删除存储过程
         DROP PROCEDURE pro_test;

6 变量

DECLARE

通过 DECLARE 关键字可以定义一个变量，该变量的作用范围只能在BEGIN - END 块中
语法
DECLARE 变量名[变量名2,变量名2...] 数据类型 [DEFAULT 默认值]

DELIMITER $
CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE num INT DEFAULT 10;-- 声明变量
	SELECT CONCAT('num的值为：',num);-- concat()函数，将字符串进行拼接
END$
-- 执行存储过程
CALL pro_test2();

SET

直接赋值使用 SET，可以赋常量或者赋表达式。

语法：
SET 变量名 = 值或表达式
案例：

-- 变量赋值
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE num INT DEFAULT 0;
	SET num = num + 10;
	SELECT CONCAT('num的值为：',num);
END$

CALL pro_test3();

也可以通过select … into 进行变量赋值
案例：

-- select...into 赋值
DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
	DECLARE num INT;
	SELECT COUNT(*) INTO num FROM city;
	SELECT CONCAT('共有',num,'个城市');
END$
-- 调用之后，我们给city表添加一条数据，再次执行存储过程，其值会相应的改变
CALL pro_test4();

7 if条件判断

语法结构

if 
	判断条件 
then 
	-- 如果成立则执行此处的sql语句
	sql语句
[elseif 判断条件 then sql语句] ...
[else sql语句]
end if;

案例：

-- if 选择结构
/*
 需求：根据成绩判断其所属的阶段：优秀，一般，良好
	socre>=90 优秀
	90>socre>=80 一般
	socre<80 良好
*/
DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
  DECLARE score INT DEFAULT 95; -- 成绩变量
  DECLARE description VARCHAR(10) DEFAULT ''; -- 所属分段的描述信息
  IF 
      score>=90
    THEN
      SET 
         description='优秀';
    ELSEIF 
         score>=80 AND score<90
    THEN
      SET 
         description='一般';
    ELSEIF 
         score<80
    THEN
      SET
	description='良好';
  END IF;	
SELECT CONCAT('该同学的成绩为：',score,',属于',description);	
END$

CALL pro_test5();

8 传递参数

语法格式：

create procedure 存储过程名([in/out/inout] 参数名	参数类型)
	...

IN :   该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT:   该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

IN - 输入参数

案例：

-- 传递参数
-- in-输入参数
DELIMITER $
CREATE PROCEDURE pro_test6(IN score INT)
BEGIN
  DECLARE description VARCHAR(10) DEFAULT ''; -- 所属分段的描述信息
  IF 
      score>=90
    THEN
      SET 
         description='优秀';
    ELSEIF 
         score>=80 AND score<90
    THEN
      SET 
         description='一般';
    ELSEIF 
         score<80
    THEN
      SET
	description='良好';
  END IF;	
SELECT CONCAT('该同学的成绩为：',score,',属于',description);	
END$

CALL pro_test6(70);

OUT - 输出参数：

/*
需求:根据出入的成绩变量，获取当前成绩所属的分段（返回值）
*/
-- out-输入参数
DELIMITER $
CREATE PROCEDURE pro_test7(IN score INT,OUT description VARCHAR(10))
BEGIN
  IF 
      score>=90
    THEN
      SET 
         description='优秀';
    ELSEIF 
         score>=80 AND score<90
    THEN
      SET 
         description='一般';
    ELSEIF 
         score<80
    THEN
      SET
	description='良好';
  END IF;	
-- SELECT CONCAT('该同学的成绩为：',score,',属于',description);	
END$

CALL pro_test7(90,@description); -- @description 用户会话变量
SELECT @description;

注意：

**@变量名：**这种变量叫做用户会话变量，代表这个会话过程中，它都是有效的，一旦断开连接，或会话窗口关闭，则@变量会被全部释放。

@@变量名：系统变量

9 case结构

语法结构

方式一 :
CASE 所要判断的值
WHEN 是否匹配的值 THEN sql语句 -- 是否匹配的值 就相当于java中 case后面跟的值
[WHEN 是否匹配的值 THEN sql语句]
...
[ELSE sql语句] -- 相当于java中的default
END CASE;
	
方式二 :
CASE
WHEN 条件表达式 THEN sql语句 -- 条件表达式为真，则执行sql语句
[WHEN 条件表达式 THEN sql语句] ...
[ELSE sql语句]
END CASE;

案例：

/*
给定一个月份，计算该月所在的季度
*/
DELIMITER $
CREATE PROCEDURE pro_test8(IN 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='第三季度';
     WHEN mon>=10 AND mon<=12 THEN
        SET result='第四季度';
  END CASE;
  SELECT CONCAT(mon,'属于',result);
END$

CALL pro_test8(2);

10 while循环

当满足条件时，则执行循环

语法结构

while 条件 do
	sql语句
end while;
-- 条件成立，执行do后面的语句

案例：

/*
 计算从1 加到 n 的和
*/
DELIMITER $
CREATE PROCEDURE pro_test9(n INT) -- 默认是in 所以in可以省略
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 CONCAT('从1加到',n,'的和为',total);
END$

CALL pro_test9(100);

11 repeat循环

当满足条件时，则退出循环

语法结构

REPEAT
	sql语句
UNTIL 条件
END REPEAT;
-- 条件成立 结束循环

案例

-- repeat循环
/*
 计算从1 加到 n 的和
*/
DELIMITER $
CREATE PROCEDURE pro_test10(n INT) -- 默认是in 所以in可以省略
BEGIN
DECLARE total INT DEFAULT 0;
REPEAT
  SET total = total+n;
  SET n = n -1;
  UNTIL n=0 -- 这里没有;号 一定要注意
END REPEAT;
SELECT CONCAT('从1加到',n,'的和为',total);
END$

CALL pro_test10(100);

12 loop循环

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现、
语法结构：

[别名:] LOOP
	sql语句
END LOOP [别名]
-- 如果不在 sql语句 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

案例：

-- loop循环
/*
 计算从1 加到 n 的和
*/
DELIMITER $
CREATE PROCEDURE pro_test11(n INT) -- 默认是in 所以in可以省略
BEGIN
DECLARE total INT DEFAULT 0;
s:LOOP
	SET total = total+n;
	SET n=n-1;
  IF n<0 THEN
     LEAVE s;-- 退出循环
  END IF;
END LOOP s;
SELECT total;
END$

CALL pro_test11(99);

13 游标

概述：

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。

我们使用select语句查询出来的结果通过展示界面我们可以看到结果中的数据，但是我还想对查询出的数据进行进一步的处理，这样的话我们就需要一个容器来存储这个结果集，这个容器就是游标。

游标的使用包括游戏】标的声明、OPEN、FETCH 和 CLOSE。

//	 声明光标：
	     DECLARE 游标名 CURSOR FOR select语句 ; 
//	   OPEN 游标：
	     OPEN 游标名 ;
//	    FETCH 光标：
	     FETCH 游标名 INTO var_name [, var_name] ...-- 相当于java中的迭代器
		-- fetch 游标名 相当于 iterator.next()
//	CLOSE 光标：	
	CLOSE 游标名 ;

-- 准备一张测试表和几条数据
CREATE TABLE IF NOT EXISTS student(
id INT,#学号
NAME VARCHAR(20),#姓名
age INT,#年龄
sex VARCHAR(5),#性别
address VARCHAR(100),#地址
math INT,#数学
english INT#英语
);

INSERT INTO student (id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',18,'男','杭州',80,80),
(2,'马化腾',19,'男','深圳',75,60),
(3,'埃隆马斯克',31,'男','美国',76,93),
(4,'扎克伯格',27,'男','美国',65,NULL),
(5,'郎平',16,'女','上海',90,98),
(6,'姚明',32,'男','上海',80,81);

-- 查询学生表中的数据，并进行逐行展示
DELIMITER $
CREATE PROCEDURE pro_test12()
BEGIN
   DECLARE id INT;#学号
   DECLARE NAME VARCHAR(20);#姓名
   DECLARE age INT;#年龄
   DECLARE sex VARCHAR(5);#性别
   DECLARE address VARCHAR(100);#地址
   DECLARE math INT;#数学
   DECLARE english INT;#英语
   DECLARE student_result CURSOR FOR SELECT * FROM student;-- 声明游标

   OPEN student_result;-- 开启游标
	FETCH student_result INTO id,NAME,age,sex,address,math,english; -- fetch一次，取一行记录
	SELECT CONCAT('id:',id,',name:',NAME,',age',age);
   CLOSE student_result;-- 关闭游标
END$

CALL pro_test12();

游标结合循环

思路1：

获取num = count(*)
每循环一次，num-1
当num=0时，退出

-- 循环改进
/*
 	1. 获取num = count(*)
 	2. 每循环一次，num-1
 	3. 当num=0时，退出
*/
DELIMITER $
CREATE PROCEDURE pro_test13()
BEGIN
   DECLARE id INT;#学号
   DECLARE NAME VARCHAR(20);#姓名
   DECLARE age INT;#年龄
   DECLARE sex VARCHAR(5);#性别
   DECLARE address VARCHAR(100);#地址
   DECLARE math INT;#数学
   DECLARE english INT;#英语
   DECLARE student_result CURSOR FOR SELECT * FROM student;-- 声明游标
   OPEN student_result;-- 开启游标
   SET @num = (SELECT COUNT(*)FROM student);
    REPEAT
	SET @num=@num-1;
	FETCH student_result INTO id,NAME,age,sex,address,math,english; -- fetch一次，取一行记录
	SELECT CONCAT('id:',id,',name:',NAME,',age',age);
    UNTIL @num=0
    END REPEAT;
   CLOSE student_result;-- 关闭游标
END$

CALL pro_test13();

思路2：句柄机制

先声明一个状态变量，初始值为1
声明句柄机制，当抓取不到数据就将该变量值设置为0
判断该状态变量的值决定循环是否继续进行

-- 方式2：句柄机制
DELIMITER $
CREATE PROCEDURE pro_test14()
BEGIN
   DECLARE id INT;#学号
   DECLARE NAME VARCHAR(20);#姓名
   DECLARE age INT;#年龄
   DECLARE sex VARCHAR(5);#性别
   DECLARE address VARCHAR(100);#地址
   DECLARE math INT;#数学
   DECLARE english INT;#英语
   DECLARE has_data INT DEFAULT 1; -- 状态变量，1代表有数据
   DECLARE student_result CURSOR FOR SELECT * FROM student;-- 声明游标
   -- 设置一个句柄机制，当抓取不到数据时触发句柄机制，执行set后操作
   DECLARE EXIT HANDLER FOR NOT FOUND SET has_data=0;
   OPEN student_result;-- 开启游标
    REPEAT
	FETCH student_result INTO id,NAME,age,sex,address,math,english; -- fetch一次，取一行记录
	SELECT CONCAT('id:',id,',name:',NAME,',age',age);
    UNTIL has_data = 0
    END REPEAT;
   CLOSE student_result;-- 关闭游标
END$
CALL pro_test14();

14 存储函数

存储过程与函数非常相似，存储过程虽然没有返回值，但是我们可以指定out来返回结果，所以存储函数能做的事情，存储过程也可以做。

语法结构

CREATE FUNCTION 函数名([参数名 数据类型 ... ])
RETURNS 返回值类型
BEGIN
...
END;

案例

-- 存储函数
-- 定义一个函数，通过国家id查询city表中对应有多少个城市
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$

SELECT fun1(1);

安宁#

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySql数据库存储过程和函数

存储过程和函数存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化数数据库设计与开发的工作，减少数据在数据库和应用服务器之间的传输，因为应用服务器访问数据库的次数越多，数据传输效率越低，有了存储过程，应用服务器只需要访问一次数据库，就可以执行存储过程中封装的所有sql语句，对于提高数据处理的效率是有好处的。存储过程和函数的区别在于函数必须有返回值，而存储过程没有2 创建存储过程语法：CREATE PROCEDURE 存储过程名([存储过程需要
复制链接

扫一扫