存储过程和函数的介绍
- 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合
- 存储过程和函数的好处
1.提高代码的复用性
2.减少数据在数据库和应用服务器之间的传输,提高效率
3.减少代码层面的业务处理- 存储过程和函数
1.存储函数必须有返回值
2.存储过程可以没有返回值
存储过程的创建和调用
- 创建存储过程
-- 修改结束分分隔符
DELIMITER $
-- 创建存储过程
CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
SQL 语句列表;
END$
-- 修改结束分隔符
DELIMITER;
- 调用存储过程
CALL 存储过程名称(实际参数);
存储过程的查看和删除
- 查询数据库中所有的存储过程
SELECT * FROM mysql.proc WHERE db='数据库名称';- 删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;
CREATE DATABASE db6;
USE db6;
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 学生id
NAME VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
gender VARCHAR(5), -- 学生性别
score INT -- 学生成绩 );
INSERT INTO student VALUES
(NULL,'张三',23,'男',95),
(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),
(NULL,'赵六',26,'女',90);
-- 按照性别进行分组,查询每组学生的总成绩,按照总成绩的升序排序
SELECT gender,SUM(score) getsum FROM student GROUP BY gender ORDER BY getsum ASC;
-- 创建stu_group() 存储过程,封装分组查询总成绩,并按照总成绩升序排序的功能
DELIMITER $
CREATE PROCEDURE stu_group()
BEGIN
SELECT gender,SUM(score) getsum FROM student GROUP BY gender ORDER BY getsum ASC;
END$
DELIMITER ;
-- 调用存储过程
CALL stu_group();
-- 查看db6数据库中所有的存储过程
SELECT * FROM mysql.proc WHERE db='db6';
-- 删除存储过程
DROP PROCEDURE IF EXISTS stu_group;
存储过程语法 - 变量
- 变量赋值方式一
SET 变量名 =变量值;- 变量赋值方式二
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
-- 创建测试 db6 数据库
CREATE DATABASE db6;
-- 使用 db6 数据库
USE db6;
-- 创建测试 student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 学生id
NAME VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
gender VARCHAR(5), -- 学生性别
score INT -- 学生成绩 )
-- 插入数据
INSERT INTO student VALUES
(NULL,'张三',23,'男',95),
(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),
(NULL,'赵六',26,'女',90);
-- 按照性别进行分组,查询每组学生的总成绩,按照总成绩的升序排序
SELECT gender,SUM(score) getsum FROM student GROUP BY gender ORDER BY getsum ASC;
-- 创建stu_group() 存储过程,封装分组查询总成绩,并按照总成绩升序排序的功能
DELIMITER $
CREATE PROCEDURE stu_group()
BEGIN
SELECT gender,SUM(score) getsum FROM student GROUP BY gender ORDER BY getsum ASC;
END$
DELIMITER ;
-- 调用存储过程
CALL stu_group();
-- 查看db6数据库中所有的存储过程
SELECT * FROM mysql.proc WHERE db='db6';
-- 删除存储过程
DROP PROCEDURE IF EXISTS stu_group;
/*存储过程定义变量*/
/*赋值方式一*/
-- SET 变量名 =变量值
-- 定义一个int类型变量,并赋默认值为10
DELIMITER $
CREATE PROCEDURE pro_test1()
BEGIN
-- 定义变量
DECLARE num INT DEFAULT 10;
-- 使用变量
SELECT num;
END$
DELIMITER ;
-- 调佣pro_test1的存储过程
CALL pro_test1();
-- 定义一个varchar类型的变量并赋值
DELIMITER $
CREATE PROCEDURE pro_test2()
BEGIN
-- 定义变量
DECLARE NAME VARCHAR(20);
-- 为变量赋值
SET NAME='存储过程';
-- 使用变量
SELECT NAME;
END$
DELIMITER ;
DROP PROCEDURE pro_test2;
-- 调用pro_test2存储过程
CALL pro_test2();
/*赋值方式二*/
-- SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
-- 定义两个init变量,用于存储男女同学的总数情况
DELIMITER $
CREATE PROCEDURE sum_score()
BEGIN
-- 定义两个变量
DECLARE man,woman INT;
-- 查询男同学的总分数,为man赋值
SELECT SUM(score) INTO man FROM student WHERE gender='男';
-- 查询女同学的总分数,为woman赋值
SELECT SUM(score) INTO woman FROM student WHERE gender='女';
-- 使用变量
SELECT man,woman;
END$
DELIMITER ;
-- 调用sum_score存储过程
CALL sum_score();
存储过程的语法 -IF语句
- IF 语句标准语法
IF 判断条件1 THEN 执行的sql语句; [ELSEIF 判断条件2 THEN 执行的SQL语句2;] [ELSE 执行的SQL语句n;] END IF;
/*
定义一个int变量,用于存储班级总成绩
定义一个varchar 变量,用于存储分数描述
根据总成绩判断:
380分及以上 学习优秀
320-380 学习不错
320以下 学习一般
*/
-- 创建存储过程
DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
DECLARE total INT;
DECLARE info VARCHAR(10);
-- 查询班级总成绩 为total 赋值
SELECT SUM(score) INTO total FROM student;
-- 对总成绩做条件判断
IF total > 380 THEN
SET info = '学习优秀';
ELSEIF total >= 320 AND total <=380 THEN
SET info = '学习不错';
ELSE
SET info = '学习一般';
END IF;
-- 查询总成绩和分数的描述信息
SELECT total,info;
END $
DELIMITER ;
-- 调用存储过程
CALL pro_test4();
存储过程语法 - 参数传递
- 存储过程的参数语法和返回值
CREATE PROCEDURE 存储过程名称([IN |OUT| INOUT] 参数名 数据类型) BEGIN SQL语句列表 END$
IN: 代表输入类型参数,需要由调用者传递实际数据(默认)
OUT: 代表输出参数,该参数可以作为返回值
INOUT: 代表既可以作为输入参数,又可以作为输出参数
/*
输入总成绩变量,代表学生总成绩
输出分数表述变量,代表血神总成绩的描述信息
根据总成绩判断:
380分及以上 学习优秀
320-380 学习不错
320以下 学习一般
*/
-- 创建存储过程
DELIMITER $
CREATE PROCEDURE pro_test5(IN total INT,OUT info VARCHAR(10))
BEGIN
-- 对总成绩进行条件判断
IF total > 380 THEN
SET info = '学习优秀';
ELSEIF total >= 320 AND total <=380 THEN
SET info = '学习不错';
ELSE
SET info = '学习一般';
END IF;
END$
DELIMITER ;
-- 调用存储过程
CALL pro_test5(350,@info);
CALL pro_test5((SELECT SUM(score) FROM student),@info);
SELECT @info;
存储过程语法 - while循环
- while 循环语法
初始化语句; WHILE 条件判断语句 DO 循环体语句; 条件控制语句; END WHILE;
/*
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;
*/
-- 计算1-100之间的偶数和
DELIMITER $
CREATE PROCEDURE pro_test7()
BEGIN
-- 定义求和变量
DECLARE result INT DEFAULT 0;
-- 定义初始化变量
DECLARE num INT DEFAULT 1;
-- while循环
WHILE num <= 100 DO
IF num % 2 = 0 THEN
SET result = result + num;
END IF;
SET num = num + 1;
END WHILE;
-- 求和结果
SELECT result;
END$
DELIMITER ;
-- 调用pro_test7存储过程
CALL pro_test7();
存储函数
- 存储函数和存储个过程非常相似的,区别在于存储函数必须有返回值.
- 创建存储函数
CREATE FUNCTION 函数名称([参数 数据类型]) RETURNS 返回值类型 BEGIN SQL 语句列表; RETURN 结果; END$- 调用函数
SELECT 函数名称(实际参数);- 删除存储函数
DROP FUNCTION 函数名称;
/*
创建存储函数
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
SQL 语句列表;
RETURN 结果;
END$
*/
-- 定义存储函数,获取学生表中成绩大于95分的学生数量
DELIMITER $
CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
-- 定义一个变量
DECLARE s_count INT;
-- 查询成绩大于95分的数量,为s_count赋值
SELECT COUNT(*) INTO s_count FROM student WHERE score > 95;
-- 返回统计结果
RETURN s_count;
END$
DELIMITER ;
-- 调用函数
SELECT fun_test1();
-- 删除函数
DROP FUNCTION fun_test1;
