数据库MYSQL笔记9——存储过程和存储函数

最新推荐文章于 2023-02-26 20:46:59 发布

qq_52360873

最新推荐文章于 2023-02-26 20:46:59 发布

阅读量248

点赞数

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

本文链接：https://blog.csdn.net/qq_52360873/article/details/119102374

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.概述

定义：存储过程和函数，是事前经过编写和存储在数据库中的SQL语句的集合；

作用：不仅可以通过调用存储过程减少开发人员的工作，而且还减少了数据库和应用服务器之间的传输，从而提高工作效率。

存储过程和存储函数区别
	存储过程	存储函数
创建语句	CREATE PROCEDURE procedure_name ([pro_parameter])	CREATE PROCEDURE FUNCTION func([fun_parameter])
参数	(INT\OUT\INOUT)	(输入参数)
返回操作	BEGIN ... SELECT 返回值 END	BEGIN RETURNS 及其类型 RETURN 返回值 END
调用方式	CALL proc_name();	SELECT func_name;（输入条件）

2.创建存储过程

CREATE PROCEDURE procedure_name ([pro_parameter])
BEGIN 

    SQL语句

END;

3. 调用存储过程

CALL procedure_name();

4. 查看存储过程

查看db_name数据库中所有的存储过程 
select name from mysql.proc where db='db_name';

查看当前数据库的存储过程的状态信息
show procedure status; -- 末尾加/G 可以纵向查看

查看proc_test1存储过程的定义/创建语句
show create porcedure test.proc_test1; -- 末尾加/G 可以纵向查看

图形化操作界面也可以查看

5. 删除存储过程

DROP PORCEDURE procedure_name;

6. 语法

6.1 DECLARE 变量

DECLARE 变量名变量数据类型;

FOR EXAMPLE:

CREATE PROCEDURE proc_text1()
BEGIN 

DECLARE num int default;
SELECT num;

END;

6.2 SET 赋值

1、将变量基础上的增删改重新赋值给变量： SET 变量= 变量+-*、

2、将查询结果赋值给变量： SELECT ... INTO 变量 FROM ... WHERE ...

1、将变量基础上的增删改重新赋值给变量 ： SET 变量= 变量+-*、
FOR EXAMPLE: 将默认变量num（0） 加上10 设置成10

CREATE PROCEDURE proc_text1()
BEGIN 

DECLARE num int default 0;
SET num =num+10
SELECT num;

END;

2、将查询结果赋值给变量 ： SELECT ... INTO  变量 FROM ... WHERE ... 
FOR EXAMPLE: 将默认变量num（0） 设置成查询字段的计数值

CREATE PROCEDURE proc_text2()
BEGIN 

DECLARE num int default 0;
SELECT count(*) into num from where ...;
SELECT num;

END;

6.3 if条件判断

语法结构：
if 条件 then 结果;
else if 条件 then 结果;
else 结果;
end if;

FOR EXAMPLE: 根据身高判断身材描述
CREATE PROCEDURE proc_text3()
BEGIN 

    DECLARE height int default 175;
    DECLARE description varchar(10);

    if height >= 180 then set description = "身材高挑";
    elseif height >= 170 and height < 180 then set description = "标准身材";
    else description = "一般身材";
    end if;

    SELECT description;

END;

6.4 IN\OUT\INOUT 传递参数

语法结构：
CREATE PROCEDURE proc_text(IN\OUT\INTOUT parameter par_type)

IN参数的应用: 根据传递的身高判断身材描述 
CREATE PROCEDURE proc_text4(IN height INT)
BEGIN 

    DECLARE description varchar(10); -- 无需再declare身高

    if height >= 180 then set description = "身材高挑";
    elseif height >= 170 and height < 180 then set description = "标准身材";
    else description = "一般身材";
    end if;

    SELECT description;

END;

OUT参数的应用: 根据传递的身高、获取身材描述（OUT返回值）
CREATE PROCEDURE proc_text5(IN height INT,OUT description varchar(10))
BEGIN 

    -- 无需再declare身高\身材描述

    if height >= 180 then set description = "身材高挑";
    elseif height >= 170 and height < 180 then set description = "标准身材";
    else description = "一般身材";
    end if;

    -- 无需查询身材描述

END;

call proc_text5(188,@description) -- 调用 @是用户回话变量（相当于全局变量），在当前会话有效，推出会话无效，@@是系统变量

select @description; -- 查询身材描述 加上@

6.5 CASE 条件判断

语法结构：
CASE 
WHEN ... THEN ...;
WHEN ... THEN ...;
...
ELSE ...
END CASE;

应用：根据传入变量month （月份），返回变量result（所属季度）
CREATE PROCEDURE proc_text6(IN month INT)
BEGIN 

DECLARE result varchar(10); 

CASE 
WHEN month >=1 AND month <=3  THEN SET result ='第一季度' ;
WHEN month >=4 AND month <=6  THEN SET result ='第二季度' ;
WHEN month >=7 AND month <=9  THEN SET result ='第三季度' ;
ELSE SET result ='第四季度';
END CASE;

SELECT result;

END;

6.6. WHILE循环结构（满足条件执行）

语法结构：
WHLIE 条件 DO 
SET 赋值结果...;
END WHLIE;

应用：累加
CREATE PROCEDURE proc_text7(n INT)
BEGIN 

DECLARE total INT DAFALUT 0;  -- 定义累计值，初始值为0
DECLARE num INT DAFALUT 1; -- 定义数值，从1开始

WHLIE num <= n DO 
SET total = total + num;
SET num = num + 1;
END WHLIE;

SELECT total;

END;

6.7. REPEAT循环结构（满足条件退出）

语法结构：
REPEAT 
执行
UNTIL 退出执行的条件
END REPEAT ;

应用：累加
CREATE PROCEDURE proc_text8(n INT)
BEGIN 

DECLARE total INT DAFALUT 0;  -- 定义累计值，初始值为0
DECLARE num INT DAFALUT 1; -- 定义数值，从1开始

REPEAT 
SET total = total + num;
SET num = num + 1;
UNTIL num > n -- 无分号，千万不要加
END REPEAT;

SELECT total;

END;

6.8. LOOP循环结构（死循环）

退出条件需要借助IF ... LEAVE ;END IF;语句

语法结构：
loop别名 LOOP
执行
LEAVE 退出执行的条件
END LOOP loop别名;

应用：1到n累加
CREATE PROCEDURE proc_text9(n INT)
BEGIN 

DECLARE total INT DAFALUT 0;  -- 定义累计值，初始值为0
DECLARE num INT DAFALUT 1; -- 定义数值，从1开始

cul LOOP
    SET total = total + num;
    SET num = num + 1;
    IF num > n        --退出条件  IF ... LEAVE ;END IF;
        LEAVE cul;
    END IF;
END LOOP cul;

SELECT total;

END;

6.9 游标

游标可以实现对查询的每一行进行提取的功能

语法结构：
DECLARE has_data INT DEFAULT 1； -- 定义可执行FECTH时游标的状态

DECLARE 游标名字 CURSOR FOR SQL; -- 定义游标
DECLARE EXIT handler FOR NOT FOUND SET has_data = 0; -- 定义FECTH不到数据的时候退出，且设置游标不执行的状态

OPEN 游标名字;
REPEAT 
 FECTH 游标名字 INTO 对应字段名字;
 SELECT 对应字段;
 UNTIL 游标不执行的状态 --（FECTH不到数据时）
END REPEAT; 
CLOSE 游标名字;


应用：提取SQL查询结果的每一行，并自动停止
CREATE PROCEDURE proc_text10()
BEGIN 

DECLARE id int(10);  -- 定义查询字段名id 
DECLARE name varchar(10);  -- 定义查询字段名name 
DECLARE salary int(10);  -- 定义查询字段名salary 
DECLARE age int(2);  -- 定义查询字段名age 
DECLARE has_data INT DEFAULT 1 -- 定义执行FECTH的状态

DECLARE emp_result CURSOR FOR SELECT * FROM emp ; -- 定义游标
DECLARE EXIT handler FOR NOT FOUND SET has_data = 0; -- 定义FECTH不到数据的时候退出，且设置has_data = 0 

OPEN emp_result;

REPEAT 
 FECTH emp_result INTO id,name,salary,age;
 SELECT id,name,salary,age;
 UNTIL has_data = 0; 
END REPEAT; 

CLOSE emp_result;

END;

7. 存储函数

与存储过程的区别：

1、创建语句：CREATE FUNCTION func()

2、BEGIN前需要写RETURNS 及其类型

3、调用方式：SELECT func（条件）

语法结构:
CREATE FUNCTION func1(条件对应的字段)

RETURN 返回值
BEGIN
 declare 
 sql
END;

SELECT func(输入条件) -- 调用


应用：返回特定城市id的城市的数量
CREATE FUNCTION func1(country_id)
RETURNS INT -- 设置返回值类型

BEGIN
    DECLARE num INT; -- 定义返回值
    SELECT COUNT(*) INTO num FROM country where country_id = id; --赋值返回值
    RETURN num; -- 返回返回值
END;

SELECT func(1) -- 输入条件，调用函数