最全MySQL8.0实战教程 14 MySQL的存储过程 14.2 入门案例

水深无声 2024-03-26 14:18 22阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  14 MySQL的存储过程
         *   *  14.2 入门案例
             *   *  14.2.1 格式
                 *  14.2.2 操作 - 数据准备
                 *  14.2.3 操作 - 创建存储过程

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 14 MySQL的存储过程 ####

##### 14.2 入门案例 #####

###### 14.2.1 格式 ######

delimiter 自定义结束符号
    create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
    begin
      sql语句
    end 自定义的结束符合
    delimiter ;

###### 14.2.2 操作 - 数据准备 ######

-- 1：创建数据库 
    create database mydb7_procedure; 
    
    -- 2：在该数据库下导入sql脚本:procedure_data.sql

![在这里插入图片描述][63760c09b37945ca9dff37a4dcc230e0.png_pic_center]

procedure\_data.sql

/*
     Navicat Premium Data Transfer
    
     Source Server         : conn-localhost
     Source Server Type    : MySQL
     Source Server Version : 80025
     Source Host           : localhost:3306
     Source Schema         : mydb6_view
    
     Target Server Type    : MySQL
     Target Server Version : 80025
     File Encoding         : 65001
    
     Date: 14/10/2021 11:39:07
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for dept
    -- ----------------------------
    DROP TABLE IF EXISTS `dept`;
    CREATE TABLE `dept`  (
      `deptno` int NOT NULL,
      `dname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `loc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`deptno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of dept
    -- ----------------------------
    INSERT INTO `dept` VALUES (10, '教研部', '北京');
    INSERT INTO `dept` VALUES (20, '学工部', '上海');
    INSERT INTO `dept` VALUES (30, '销售部', '广州');
    INSERT INTO `dept` VALUES (40, '财务部', '武汉');
    
    -- ----------------------------
    -- Table structure for emp
    -- ----------------------------
    DROP TABLE IF EXISTS `emp`;
    CREATE TABLE `emp`  (
      `empno` int NOT NULL,
      `ename` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `job` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `mgr` int NULL DEFAULT NULL,
      `hiredate` date NULL DEFAULT NULL,
      `sal` decimal(7, 2) NULL DEFAULT NULL,
      `COMM` decimal(7, 2) NULL DEFAULT NULL,
      `deptno` int NULL DEFAULT NULL,
      PRIMARY KEY (`empno`) USING BTREE,
      INDEX `fk_emp`(`mgr`) USING BTREE,
      CONSTRAINT `fk_emp` FOREIGN KEY (`mgr`) REFERENCES `emp` (`empno`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of emp
    -- ----------------------------
    INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30);
    INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30);
    INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, NULL, 20);
    INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30);
    INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30);
    INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10);
    INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10);
    INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30);
    INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30);
    INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);
    
    -- ----------------------------
    -- Table structure for salgrade
    -- ----------------------------
    DROP TABLE IF EXISTS `salgrade`;
    CREATE TABLE `salgrade`  (
      `grade` int NOT NULL,
      `losal` int NULL DEFAULT NULL,
      `hisal` int NULL DEFAULT NULL,
      PRIMARY KEY (`grade`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of salgrade
    -- ----------------------------
    INSERT INTO `salgrade` VALUES (1, 7000, 12000);
    INSERT INTO `salgrade` VALUES (2, 12010, 14000);
    INSERT INTO `salgrade` VALUES (3, 14010, 20000);
    INSERT INTO `salgrade` VALUES (4, 20010, 30000);
    INSERT INTO `salgrade` VALUES (5, 30010, 99990);
    
    SET FOREIGN_KEY_CHECKS = 1;

![在这里插入图片描述][78e59bf495c74553939150c0cca5b28f.png_pic_center]

![在这里插入图片描述][8eaf742d029243889dd362a93fbdaee6.png_pic_center]

老三样，部门表、员工表、薪资等级表

###### 14.2.3 操作 - 创建存储过程 ######

delimiter $$
    create procedure proc01()
    begin
      select empno,ename from emp; 
    end  $$
    delimiter ;
    
    -- 调用存储过程
    call proc01();

![在这里插入图片描述][ed7aedf32f6647049f6fbc6a84351fef.png_pic_center]

![在这里插入图片描述][f0d9b2e34c0942c39104d8aad78a714a.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[63760c09b37945ca9dff37a4dcc230e0.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c76eb0d72eb3490db238577d78aa3dc1.png
[78e59bf495c74553939150c0cca5b28f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a279a8e996384ef28887c57cf17f357e.png
[8eaf742d029243889dd362a93fbdaee6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/670f8c07b8f149e1bade17b2d4dcc92f.png
[ed7aedf32f6647049f6fbc6a84351fef.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/95807e9885434bc8ab1988c41c17873f.png
[f0d9b2e34c0942c39104d8aad78a714a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/3ca3b7a7419848c1bf0e0816193961c9.png