存储过程

一、about

centos7.9 + mysql5.7.20

MySQL从5.0版本开始支持存储过程。

存储过程是一段SQL集的封装，一个子程序。

存储过程（Stored Procedure）是一种在数据库中存储的复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

说白了，存储过程就是保存在MySQL上的一个"别名"——封装SQL语句集。通过整个别名来调用封装好的语句集，相当方便。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

 

将下面的sql全部复制保存为school.sql，使用mysql -uroot -p123 <school.sql命令导入到数据库即可

/*
 数据导入：
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

DROP DATABASE IF EXISTS school;
CREATE DATABASE school CHARSET utf8;
USE school
SET AUTOCOMMIT=0;
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

二、 创建无参存储过程

一个简单的无参存储过程：

-- 创建无参存储过程
DELIMITER //
CREATE PROCEDURE p1()        -- 通过CREATE PROCEDURE声明存储过程，存储过程名称是 p1，无参
BEGIN
  -- 这里开始写SQL，当调用存储过程的时候，这里的所有SQL都会执行
  SELECT * FROM student WHERE sid > 10;
END    //
DELIMITER ;

-- 在终端中使用 call 调用存储过程
CALL p1();

上例，存储过程p1用来查询student表中sid大于10的记录。

注意，存储过程不能重复创建。

使用pymysql中这么调用：

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()

# 调用存储过程
cursor.callproc('p1')
conn.commit()

result = cursor.fetchall()
print(result)
"""
(
    (11, '男', 2, '李四'), (12, '女', 3, '如花'), (13, '男', 3, '刘三'), 
    (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四')
)
"""
cursor.close()
conn.close()

 

三、创建有参存储过程

存储过程允许传参，通常有三种传参方式：

• in
• out
• inout

一起来看看这三个参数怎么用。

 

3.1 in

in参数：

• 在调用存储过程时，必须指定。
• 在存储过程执行中，该参数不能被修改。
• 一般只需传参无需返回时使用in

创建有参存储过程：

 

-- 接受 n1 和 n2 两个外部传来的int数据
DELIMITER //
CREATE PROCEDURE p2(
  IN n1 INT,
  IN n2 INT
)
BEGIN
  SELECT * FROM student WHERE sid BETWEEN n1 AND n2;
END    //
DELIMITER ;

-- 使用 call 调用
CALL p2(10, 20)

 

 使用pymysql中这么调用

 

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()

# 调用存储过程，以元组的形式传参
cursor.callproc('p2', (10, 20))
conn.commit()

result = cursor.fetchall()
print(result)
"""
(
    (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), 
    (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四')
)
"""
cursor.close()
conn.close()

3.2  out

out参数：

• 存储过程执行时，可以被修改。
• 可以被返回。

创建有参存储过程

DELIMITER //
CREATE PROCEDURE p3(
  IN n1 INT,
  OUT n2 INT
)
BEGIN
  -- 存储过程执行时，首先修改 n2 的值，然后被select语句使用
  SET n2 = 20;
  SELECT * FROM student WHERE sid BETWEEN n1 AND n2;
END    //
DELIMITER ;

-- set定义一个局部的变量 n2,值是10，然后整个变量被当作存储过程的out参数传入存储过程中
SET @n2 = 10;
CALL p3(10, @n2);

-- 终端中使用select查看变量 n2
SELECT @n2;

 

 

mysql> SELECT @n2;
+------+
| @n2  |
+------+
|   20 |
+------+
1 row in set (0.00 sec)

 

对于out参数，pymysql操作起来，就有点麻烦了：

 

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()
# 调用存储过程，获取存储过程的执行结果
cursor.callproc('p3', (10, 10))
conn.commit()

result1 = cursor.fetchall()
print(result1)
"""
(
    (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), 
    (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四')
)
"""

# 获取out的返回值
cursor.execute("select @_p3_0,@_p3_1")  # 固定写法
result2 = cursor.fetchall()
print(result2)
"""
cursor.execute("select @_p3_0,@_p3_1")  
    - select开头 
    - 空格紧随其后
    - @_存储过程名称_参数序号，逗号分隔多个参数
    - 参数序号从0开始，从左往右依此类推
    
返回值：
((10, 20),)  第一个值10，是存储过程n1的值，第二值20，是存储过程执行时修改后的out参数n2的返回值
"""

cursor.close()
conn.close()

 

 

3.3 inout

顾名思义，inout参数：

• 能被修改。
• 能被返回。

创建有参存储过程：

DELIMITER //
CREATE PROCEDURE p4(
  INOUT n1 INT
)
BEGIN
  -- 存储过程执行时，首先修改 n1 的值，然后被select语句使用
  SET n1 = 20;
  SELECT * FROM student WHERE sid < n1;
END    //
DELIMITER ;

-- set定义一个局部的变量 n1,值是10，然后整个变量被当作存储过程的inout参数传入存储过程中
SET @n1 = 10;
CALL p4(@n1);

-- 终端中使用select查看变量 n1
SELECT @n1;

 

pymysql中的用法跟out类似：

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()
# 调用存储过程，获取存储过程的执行结果
cursor.callproc('p4', (10,))   # 注意，元组中只有一个元素时，要带逗号
conn.commit()

result1 = cursor.fetchall()
print(result1)
"""
(
    (1, '男', 1, '理解'), (2, '女', 1, '钢蛋'), (3, '男', 1, '张三'), (4, '男', 1, '张一'), 
    (5, '女', 1, '张二'), (6, '男', 1, '张四'), (7, '女', 2, '铁锤'), (8, '男', 2, '李三'), 
    (9, '男', 2, '李一'), (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), 
    (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四')
)
"""

# 获取inout的返回值
cursor.execute("select @_p4_0")  # 固定写法
result2 = cursor.fetchall()
print(result2)
"""
((20,),)  修改后的n1值是20
"""

cursor.close()
conn.close()

 

 

四 、存储过程管理

4.1 查看存储过程

-- 查询MySQL中所有的存储过程
SELECT db,NAME FROM mysql.proc;

--  查询指定数据库下的所有存储过程
SELECT * FROM mysql.proc WHERE db="school";
SHOW PROCEDURE STATUS WHERE db="school";

-- 查询指定存储过程的创建信息
SHOW CREATE PROCEDURE school.p1;

 

4.2 删除存储过程

-- 删除当前数据库下的存储过程
DROP PROCEDURE p1;

-- 删除指定数据下的指定存储过程
DROP PROCEDURE 数据库名.存储过程名;

 

 

更新存储过程

删了重建方便，这里不在多表。