MySQL存储过程创建与使用

大猫的Java笔记

发布于 2020-09-29 18:10:03

2.1K00

代码可运行

文章被收录于专栏：大猫的Java笔记大猫的Java笔记

运行总次数：0

代码可运行

学习点：

1.什么是存储过程？

2.为什么要使用存储过程？

3.存储过程应该怎么使用呢？

1.什么是存储过程？

存储过程是数据库中完成特定功能的SQL集，一次编译后永久有效，有点类似于Java里面的方法或C语言中的函数，我们可以在方法体中完成特定的功能，后续只要调用即可。

2.为什么要使用存储过程？

1.可重用

2.效率高（一次编译永久使用）

3.更加灵活，扩展性更强

4.简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化

３.存储过程应该怎么使用呢？

先看一下两张表的结构，user表和grade表是多对一的关系，且外键在user这一方，字段sex为bit类型（不知道为什么命令行显示不出来数据）

3.1.创建无参数存储过程的语法及使用

无参数传递的存储过程语法如下

CREATE PROCEDURE 存储过程的名字（）
BEGIN  
需要处理的业务SQL(相当于方法体);
END;

调用无参存储过程语法如下

CALL 存储过程的名字();

现在我们根据语法进行一次简单的使用，现在业务需求是查询出每个班平均年龄，这个SQL很简单，但是我们现在用存储过程怎么写呢？SQL语句如下所示

CREATE PROCEDURE gradeavg ()
BEGIN
  SELECT avg(u.age) FROM USER u INNER JOIN grade g
     ON u.gradeid = g.id GROUP BY u.gradeid;
     END;

在命令行中进行运行看一下是否创建能创建成功呢？

结果报错，报错说我SQL语法错误，但实际上检查几遍后发现并没有错误，尝试在Navicat中的命令行运行发现也没有错误可以创建，难道是DOS命令行的问题？结果不出所料果然是DOS命令行的问题，因为我们在SELECT后面加了分号表示SQL语句结束了，而需要在END后加分号表示我的命令结束了，所以发生了冲突，那么怎么解决呢？使用如下命令可以更改分隔符

DELIMITER 需要重新定义的分隔符

查看创建好的存储过程

show procedure status;

删除存储过程

DROP PROCEDURE 存储过程名称;

3.2.创建带参数(OUT)存储过程的语法及使用

带参数传递的存储过程语法如下，其中OUT表示的是返回的值，也就是后面在调用存储过程时如果选择的参数就会返回对应的结果，OUT相当于声明参数的格式一样,INTO就是把结果返回给哪一个参数。

CREATE PROCEDURE 存储过程的名字（
  OUT 参数1的名字 类型,
    OUT 参数2的名字 类型,
      OUT 参数3的名字 类型
      ）
      BEGIN  需要处理的业务SQL(相当于方法体) INTO 参数的名字;
      END;

　　调用有参存储过程语法如下，与无参不同的是无参的直接CALL 存储过程名字就可以查看结果了，但是有参不能这样，你必须使用SELECT 加对应的参数名字才能够查看对应的结果

CALL 存储过程的名字(@参数1，@参数2，@参数3);
SELECT @对应的参数

现在我们根据语法进行一次简单的使用，现在业务需求是查询出班上平均年龄，最高年龄，最低年龄，这个SQL很简单，但是我们现在用存储过程怎么写呢？SQL语句如下所示

CREATE PROCEDURE gradeparam (
  OUT agemin DECIMAL(8,2),
    OUT agemax DECIMAL(8,2),
      OUT ageavg DECIMAL(8,2)
      )BEGIN
      SELECT min(age) FROM USER INTO agemin;
      SELECT max(age) FROM USER INTO agemax;
      SELECT avg(age) FROM USER INTO ageavg;
      END;

在命令行中进行运行看一下是否创建能创建成功呢？

3.3.创建带参数(OUT和IN)存储过程的语法及使用

带参数传递的存储过程语法如下，其中OUT表示的是返回的值，也就是后面在调用存储过程时如果选择的参数就会返回对应的结果，OUT相当于声明参数的格式一样,INTO就是把结果返回给哪一个参数;IN表示传入的值。

CREATE PROCEDURE 存储过程的名字（
  IN 参数1的名字 类型,
    IN 参数2的名字 类型,
      OUT 参数2的名字 类型
      ）
      BEGIN
        需要处理的业务SQL(相当于方法体) INTO 参数的名字;
        END;

CALL 存储过程的名字(传入的参数1，传入的参数1，@参数2);
SELECT @对应的参数

现在我们根据语法进行一次简单的使用，现在业务需求是通过传入一个布尔值和对应的主键Id，如果为真(不是0就为真)就查询出对应主键的年龄并乘以10，如果为假(为0既假)就查询出对应主键的年龄并乘以100，现在用存储过程怎么写呢？SQL语句如下所示

CREATE PROCEDURE gradeinout (
  IN userid INT,
  IN type BOOLEAN,
  OUT agesum DECIMAL(8,2)
  )
  BEGIN
  --DECLARE是申明局部变量，number为变量名,INT表示为类型,DEFAULT设置默认值为10
  DECLARE  number1 INT DEFAULT 10;
  DECLARE  number2 INT DEFAULT 100;
--if进行条件判断，如果为真执行if下面的第一条语句,为假就执行else后的语句，END if是表示if判断结束的标识符  
IF type THEN
--将传入的userid赋值给where后面的条件过滤,最后把值返回给agesum     
SELECT age*number1 FROM USER WHERE ID = userid INTO agesum;
   ELSE    
    SELECT age*number2 FROM USER WHERE ID = userid INTO agesum;
       END IF;
       END;