2021-07-31--MySQL创建存储过程【java编程进阶】

贪吃蛇学院

于 2021-07-31 16:30:18 发布

阅读量800

点赞数 1

CC 4.0 BY-SA版权

分类专栏：编程 mysql mysql存储过程文章标签：数据库架构数据库开发 mysql

本文链接：https://blog.csdn.net/cyhlili100/article/details/119277618

编程同时被 3 个专栏收录

18 篇文章

订阅专栏

mysql

3 篇文章

订阅专栏

mysql存储过程

1 篇文章

订阅专栏

本文详细介绍了MySQL存储过程的创建、调用及参数使用，包括无参数和带参数的存储过程示例，以及如何通过DELIMITER命令处理多条SQL语句。通过实例展示了如何查询学生成绩信息，并提供了创建、调用存储过程的完整SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL 存储过程是一些 SQL 语句的集合，比如有的时候我们可能需要一大串的 SQL 语句，或者说在编写 SQL 语句的过程中还需要设置一些变量的值，这个时候我们就完全有必要编写一个存储过程。下面我们来介绍一下如何创建一个存储过程。

基本语法

可以使用 CREATE PROCEDURE 语句创建存储过程。

语法格式如下：

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

语法说明如下：

过程名

存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

过程参数

存储过程的参数列表。其中， <参数名>为参数名， <类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。

MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。

过程体

存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。为解决这个问题，通常可使用 DELIMITER 命令将结束命令修改为其他字符。

语法格式如下：

DELIMITER $$

语法说明如下：

$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

在 MySQL 命令行客户端输入如下SQL语句。

mysql > DELIMITER ??

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。

若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：

mysql > DELIMITER ;

注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。可以使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程，若要查看某个存储过程的具体信息，则可以使用 SHOW CREATE PROCEDURE <存储过程名>。

创建不带参数的存储过程

【实例 1】创建名称为 ShowStuScore 的存储过程，存储过程的作用是从学生成绩信息表中查询学生的成绩信息，输入的 SQL 语句和执行过程如下所示。

mysql> DELIMITER //
mysql> CREATE PROCEDURE ShowStuScore()
    -> BEGIN
    -> SELECT * FROM tb_students_score;
    -> END //
Query OK， 0 rows affected (0.09 sec)

创建存储过程 ShowStuScore 后，通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。

mysql> DELIMITER ;
mysql> CALL ShowStuScore();
+--------------+---------------+
| student_name | student_score |
+--------------+---------------+
| Dany         |            90 |
| Green        |            99 |
| Henry        |            95 |
| Jane         |            98 |
| Jim          |            88 |
| John         |            94 |
| Lily         |           100 |
| Susan        |            96 |
| Thomas       |            93 |
| Tom          |            89 |
+--------------+---------------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

创建带参数的存储过程

【实例 2】创建名称为 GetScoreByStu 的存储过程，输入参数是学生姓名。存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息，输入的 SQL 语句和执行过程如下所示。

mysql> DELIMITER //
mysql> CREATE PROCEDURE GetScoreByStu
    -> (IN name VARCHAR(30))
    -> BEGIN
    -> SELECT student_score FROM tb_students_score
    -> WHERE student_name=name;
    -> END //
Query OK, 0 rows affected (0.01 sec)

创建存储过程 GetScoreByStu 后，通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。

mysql> DELIMITER ;
mysql> CALL GetScoreByStu('Green');
+---------------+
| student_score |
+---------------+
|            99 |
+---------------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)

建表语句：t_student 和 t_score

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for t_student

-- ----------------------------

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `sid` int(32) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `sname` varchar(200) DEFAULT NULL COMMENT '学生姓名',
  `sex` int(1) DEFAULT '0' COMMENT '性别(0:男，1：女)',
  `scid` int(32) DEFAULT '0' COMMENT 'scid',
  `sphone` bigint(11) DEFAULT NULL COMMENT '电话号码',
  `sbrith` datetime DEFAULT NULL COMMENT '生日',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `address` varchar(300) DEFAULT NULL COMMENT '地址',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=145018017 DEFAULT CHARSET=utf8 COMMENT='学生信息表';

-- ----------------------------

-- Records of t_student

-- ----------------------------

INSERT INTO `t_student` VALUES ('145018001', 'fadsfasd', '0', '12', '13185245686', '1986-02-04 00:00:00', '123', '湖南长沙', '2021-08-01 00:00:00');
INSERT INTO `t_student` VALUES ('145018002', '李莉', '1', '13', '13185245687', '1986-02-05 00:00:00', '13', '湖南常德', '2021-08-02 00:00:00');
INSERT INTO `t_student` VALUES ('145018003', '王华', '1', '3424', '18923456789', '1986-05-06 00:00:00', '12', '湖南益阳', '2021-08-03 00:00:00');
INSERT INTO `t_student` VALUES ('145018004', '刘遵义', '1', '15', '13185245689', '1986-02-07 00:00:00', '15', '湖南衡阳', '2021-08-04 00:00:00');
INSERT INTO `t_student` VALUES ('145018005', '王曼1', '0', '16', '13185245690', '1986-02-08 00:00:00', '16', '湖南长沙', '2021-08-05 00:00:00');
INSERT INTO `t_student` VALUES ('145018006', 'nnn7676', '1', '17', '13185245691', '1986-02-09 00:00:00', '17', '湖南常德', '2021-08-06 00:00:00');
INSERT INTO `t_student` VALUES ('145018007', '玖姿', '0', '18', '13185245692', '1986-02-10 00:00:00', '18', '湖南益阳', '2021-08-07 00:00:00');
INSERT INTO `t_student` VALUES ('145018008', '曼森', '1', '19', '13185245693', '1986-02-11 00:00:00', '19', '湖南衡阳', '2021-08-08 00:00:00');
INSERT INTO `t_student` VALUES ('145018009', '马九三', '0', '20', '13185245694', '1986-02-12 00:00:00', '20', '湖南长沙', '2021-08-09 00:00:00');
INSERT INTO `t_student` VALUES ('145018010', '李立g', '1', '21', '13185245695', '1986-02-13 00:00:00', '21', '湖南常德', '2021-08-10 00:00:00');
INSERT INTO `t_student` VALUES ('145018011', '兰盖', '0', '22', '13185245696', '1986-02-14 00:00:00', '22', '湖南益阳', '2021-08-11 00:00:00');
INSERT INTO `t_student` VALUES ('145018012', '吴尊', '1', '23', '13185245697', '1986-02-15 00:00:00', '23', '湖南衡阳', '2021-08-12 00:00:00');
INSERT INTO `t_student` VALUES ('145018013', '吴亮', '0', '24', '13185245698', '1986-02-16 00:00:00', '24', '湖南长沙', '2021-08-13 00:00:00');
INSERT INTO `t_student` VALUES ('145018014', '李琪琪', '1', '25', '13185245699', '1986-02-17 00:00:00', '25', '湖南常德', '2021-08-14 00:00:00');
INSERT INTO `t_student` VALUES ('145018015', '王华', '1', '3424', '18923456789', '1986-05-06 00:00:00', '12', '', '2021-08-15 00:00:00');



SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for t_score

-- ----------------------------

DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score` (
  `id` int(32) NOT NULL COMMENT '主键id',
  `stu_id` int(32) DEFAULT NULL COMMENT '学生id',
  `c_name` varchar(100) DEFAULT NULL COMMENT '课程名称',
  `grade` int(3) DEFAULT NULL COMMENT '成绩分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生的成绩表';

-- ----------------------------

-- Records of t_score

-- ----------------------------

INSERT INTO `t_score` VALUES ('1', '145018001', '大学高数', '70');
INSERT INTO `t_score` VALUES ('2', '145018002', '计算机', '71');
INSERT INTO `t_score` VALUES ('3', '145018003', '邓小平理论', '72');
INSERT INTO `t_score` VALUES ('4', '145018004', '大学高数', '73');
INSERT INTO `t_score` VALUES ('5', '145018005', '计算机', '74');
INSERT INTO `t_score` VALUES ('6', '145018006', '邓小平理论', '75');
INSERT INTO `t_score` VALUES ('7', '145018007', '大学高数', '76');
INSERT INTO `t_score` VALUES ('8', '145018008', '计算机', '77');
INSERT INTO `t_score` VALUES ('9', '145018009', '邓小平理论', '78');
INSERT INTO `t_score` VALUES ('10', '145018010', '大学高数', '79');
INSERT INTO `t_score` VALUES ('11', '145018011', '计算机', '80');
INSERT INTO `t_score` VALUES ('12', '145018012', '邓小平理论', '81');
INSERT INTO `t_score` VALUES ('13', '145018015', '大学高数', '30');
INSERT INTO `t_score` VALUES ('14', '145018015', '计算机', '50');

详细的案例：如下

–创建mysql 存储过程

– 1、需求：传入一个学生ID，查询该学生的信息

– 带有输入参数的存储过程

delimiter $
create PROCEDURE stu_findByid(in id int)
begin
    select * from t_student where sid=id;
END $

查询存储过程：

call stu_findByid(153642);

删除存储过程

drop procedure stu_getInfoById;

select 语句比较多的话：如下

delimiter $
create procedure stu_getInfoById(in id int)
begin 
  select 
     t.sname,ts.c_name,ts.grade
     from t_student t left join t_score ts ON
     t.sid = ts.stu_id
     and sname <> ''
     and t.sid = id
     group by ts.c_name,t.sname,ts.grade DESC
     having sum(grade<80) >=1;
     
end $;

– 调用存储过程

call stu_getInfoById(145018003);

– 调用存储过程

call stu_findByid(145018003);

– 2、带有输入和输出参数的存储过程

delimiter $
create procedure stu_intOrOut(in  id int,out str varchar(20))
begin
    -- 声明变量
    DECLARE varibale varchar(200);
      select sname as varibale from t_student where sid =id;
    set str= varibale;  -- 将select的结果集赋值给str 
                        -- 给out赋值一定要用set 关键字
end $

删除储存过程

drop PROCEDURE stu_intOrOut;

调用储存过程：

call stu_intOrOut(145018003,@str);
select str;

– 删除存储过程

drop procedure stu_inout;

– 调用存储过程 – 1)定义了一个变量 – 2）定义了一个会话变量接收存储过程输出的参数

call stu_out(@name);
select @name;

– 3、输入输出参数的存储过程

delimiter $
create procedure stu_inout(inout n int)
begin
    declare grs int(11);
      select grade as grs  from t_score where stu_id = 145018003;
    set n= grs;
end $
set @n= 145018003 ;

call stu_inout(@n);

select @n;

– 4、带条件判断的存储过程 – 需求：输入一个正整数，如果1，返回“星期一”，如果2，返回“星期二。。。。其他返回输入错误”

delimiter $
create procedure stu_testIf(in num int,out str varchar(20))
begin
    if num=1 then   -- 开始
        set str='星期一';
    elseif num=2 then
        set str='星期二';
    elseif num=3 then
        set str='星期三';
    else
        set str='输入错误';
    end if;         -- 结束
end $

– 执行存储过程

call stu_testIf(2,@str);
select @str;

– 5、带循环功能的存储过程 – 输入一个整数，求和。比如输入100，统计1-100的和

delimiter $
create procedure stu_testWhile(in num int,out result int)
begin
        -- 定义两个局部变量
        DECLARE i int default 1;
        declare vsum int default 0;
        while i<=num do
                set vsum = vsum+i;
                set i = i+1;
        end while;
        set result = vsum;
END $
call stu_testWhile(100,@result);
select @result;
drop procedure stu_testWhile;

– 6、使用查询的结果赋值给变量（into）

delimiter $
create procedure stu_findByid2(in eid int,out vname varchar(20))
begin
    select name into vname from student where id=eid;
end
call stu_findByid2(1,@vname);
select @vname;
-- 7、练习，编写一个存储过程
use students;
select * from user;
    -- 如果学生的英语平均分小于等于70分，刚输出‘一般’
    -- 如果学生的英语平均分大于70，且小于等于90分，刚输出‘良好’
    -- 如果学生的英语平均分大于90分，刚输出‘优秀’
delimiter $
create procedure stu_testAvg(out str varchar(20))
begin
    -- 计算英语平均分
    declare savg double;
    select avg(score) into savg from user;
    if savg<=70 then
        set str='一般';
    elseif savg>70 and savg<=90 then
        set str='良好';
    else 
        set str='优秀';
    end if;
end $
调用存储过程
call stu_testAvg(@str);
# 查询返回的结构集
select @str;


CREATE DEFINER=`root`@`%` PROCEDURE `InsertNewConsultation`(IN `user_id` int,IN `doc_id` int,IN `now_time` datetime,
               IN `disease_id` int,IN `patient_id` int,IN `patient_speak_text` text,OUT `patient_speak_id` int)
BEGIN

声明变量

DECLARE consultation_id INT;
    insert into 
    consultation_basic
        (user_id,last_reply_text,last_reply_time,consultation_status,doctor_unread_number,disease_id,patient_unread_number,patient_id,doc_id)
    values
        (user_id,patient_speak_text,now_time,1,1,disease_id,0,patient_id,doc_id);
    

•   SET consultation_id = @@identity;
•   
•   insert into 
•   patient_speak
•       (patient_speak_text,patient_user_id,patient_speak_time,isread,consultation_id)
•   values
•       (patient_speak_text,user_id,now_time,0,consultation_id);
•   
•   SET patient_speak_id = @@identity;

END;