MySql（二十五）存储过程

冷不防 2021-12-12 09:41 309阅读 0赞

# 文章目录 #

存储过程  
        创建语法  
        调用语法  
        存储过程的删除  
        存储过程的信息查看

# 存储过程 #

引入：存储过程和函数类似于java中的方法

> Java中方法的好处：
> 
> 1.  提高了代码的重用性
> 2.  简化操作

存储过程定义：是一组预先编译好的sql语句的集合，理解成批处理语句

> 存储过程的好处：
> 
> 1.  提高了代码的重用性
> 2.  简化操作
> 3.  减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

## 创建语法 ##

创建语法：

> Create procedure 存储过程名(参数列表)
> 
>     Begin
> 
>     存储过程体（一组合法的sql语句）
> 
>     End

注意事项1：

> 参数列表包含三部分：参数模式 参数名 参数类型
> 
> 参数模式：
> 
> 1.  In    ：该参数可以作为输入也就是说该参数需要调用方传入值
> 2.  Out ：该参数可以作为输出也就是说该参数可以作为返回值
> 3.  Inout ：该参数既可以作为输入也可以作为输出，即该参数既需要传入值，又可以返回值
> 
> 举个栗子： in stuname varchar(20);

注意事项2：

> 如果存储过程体仅仅只有一句话，begin end可以省略；存储过程体中的每一条sql语句的结尾必须要求加分号结尾；而存储过程的结尾可以使用delimiter重新设置。
> 
> 语法：delimiter 结束标记
> 
> Eg:delimiter ;//即这里的 ; //即这里的;//即这里的就代表结束标记

在本章中的存储过程的结尾这里就均使用$符号做为结束标记，下面就不在赘述了…

## 调用语法 ##

调用语法：

> Call 存储过程名(实参列表);

案例一：空参列表

1． 插入到admin表中五条记录
    
    Delimiter $  /**设置结束标记(后面结束时都将使用这个$)**/
    /**创建**/
    Create procedure myp1()
    Begin
        Insert into admin(username,pwd)
        Values
        ('join1','000'),
        ('join1','000'),
        ('join1','000'),
        ('join1','000'),
        ('join1','000');
    End $
    /**调用**/
    Call myp1()$

案例二：创建带in模式参数的存储过程

表beauty

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">id</th> 
   <th style="width:169px;text-align:center;">name</th> 
   <th style="width:83px;text-align:center;">sex</th> 
   <th style="text-align:center;">phone</th> 
   <th style="text-align:center;">borndate</th> 
   <th style="text-align:center;">boyfriend</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">1</td> 
   <td style="text-align:center;width:169px;">柳岩</td> 
   <td style="text-align:center;width:83px;">女</td> 
   <td style="text-align:center;">15090833772</td> 
   <td style="text-align:center;">2018-1-2</td> 
   <td style="text-align:center;">1</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">2</td> 
   <td style="text-align:center;width:169px;">AngleBaby</td> 
   <td style="text-align:center;width:83px;">女</td> 
   <td style="text-align:center;">19037399002</td> 
   <td style="text-align:center;">2012-3-2</td> 
   <td style="text-align:center;">3</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">3</td> 
   <td style="text-align:center;width:169px;">赵丽颖</td> 
   <td style="text-align:center;width:83px;">女&nbsp;</td> 
   <td style="text-align:center;">15909877112</td> 
   <td style="text-align:center;">1998-2-1</td> 
   <td style="text-align:center;">4</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">4</td> 
   <td style="text-align:center;width:169px;">赵敏</td> 
   <td style="text-align:center;width:83px;">女</td> 
   <td style="text-align:center;">15980291854</td> 
   <td style="text-align:center;">1998-2-3</td> 
   <td style="text-align:center;">2</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">5</td> 
   <td style="text-align:center;width:169px;">周芷若</td> 
   <td style="text-align:center;width:83px;">女</td> 
   <td style="text-align:center;">15892041335</td> 
   <td style="text-align:center;">1993-12-4</td> 
   <td style="text-align:center;">2</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">6</td> 
   <td style="text-align:center;width:169px;">金星</td> 
   <td style="text-align:center;width:83px;">女</td> 
   <td style="text-align:center;">18390833220</td> 
   <td style="text-align:center;">1992-1-2</td> 
   <td style="text-align:center;">9</td> 
  </tr> 
 </tbody> 
</table>

表boy

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">id</th> 
   <th style="text-align:center;">boyname</th> 
   <th style="text-align:center;">usercp(魅力值)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">1</td> 
   <td style="text-align:center;">大鹏</td> 
   <td style="text-align:center;">100</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">2&nbsp;</td> 
   <td style="text-align:center;">张无忌&nbsp;</td> 
   <td style="text-align:center;">339</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">3</td> 
   <td style="text-align:center;">黄晓明</td> 
   <td style="text-align:center;">99</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">4</td> 
   <td style="text-align:center;">冯绍峰</td> 
   <td style="text-align:center;">400</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">5</td> 
   <td style="text-align:center;">男神</td> 
   <td style="text-align:center;">5</td> 
  </tr> 
 </tbody> 
</table>

根据女神名，查询对应的男神信息

/**创建**/
    Create procedure myp2(IN beautyName varchar(4))
    Begin
        Select b.*,bea.name from boy b
        Right join beauty bea
        On b.id = bea.boyfriend_id
        Where bea.name = beautyName;
    End $
    /**调用**/
    Call myp2(‘赵敏’)$

案例三：创建带out模式参数的存储过程

1.根据女神名返回男神名

/**创建带一个参数的存储过程**/
    Create procedure myp3(in beautyName varchar()4,out boyName varchar(4))
    Begin
        select b.boyName into boyName
        from boys b
        inner join beauty bea
        on b.id = bea.boyfriendid
        where beautyName = bea.name; 
    End $
    /**调用**/
    /**定义变量，用于存储从表中查询到的数据**/
    set @bName$
    Call myp3('柳岩',bName)$
    /**查询**/
    select @bName$

2.根据女神名，返回对应的男神名和魅力值

/**创年**/
    create procedure myp4(in beautyName varchar(4), out boyName varchar(4),out usercp int)
    begin
        select b.boyName,b.usercp into boyName,usercp
        from boys b
        inner join beauty bea 
        in b.id = bea.boyfriend
        where baeutyName = bea.name;
    end $
    /**调用这里的“@bName和@ucp是直接创建的变量，用于存储从表中查询到满足条件的数据”**/
    call myp4("柳岩",@bName,@ucp)$
    /**查看**/
    select @bName,@ucp$

案例四：创建带inout模式参数的存储过程

3.传入a和b的值，最后a和b都翻倍并返回

/**创建存储过程**/
    create procedure myp4(inout a int, inout b int)
    begin
        set a = a*1;
        set b = b*2;
    end $
    /**调用(创建用户变量用于存取数值)**/
    set @num1 = 2$
    set @num2 = 2$
    call myp4(@num1,@num2)$
    /**查看**/
    select @num1,@num2$

案例讲解

案例一：创建存储过程实现传入用户名和密码，插入到admin表中

/**创建存储过程**/
    create procedure myp(in userName varchar(4), in pwd varchar(6))
    begin
        insert into admin(admin.userName, admin.pwd)
        values(userName,pwd);
    end $

案例二：创建存储过程或函数实现传入女神编号，返回女神名称和电话

表beauty

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>sex</th> 
   <th>phone</th> 
   <th>borndate</th> 
   <th>boyfriend</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>柳岩</td> 
   <td>女</td> 
   <td>15090833772</td> 
   <td>2018-1-2</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>AngleBaby</td> 
   <td>女</td> 
   <td>19037399002</td> 
   <td>2012-3-2</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>赵丽颖</td> 
   <td>女</td> 
   <td>15909877112</td> 
   <td>1998-2-1</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>赵敏</td> 
   <td>女</td> 
   <td>15980291854</td> 
   <td>1998-2-3</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>周芷若</td> 
   <td>女</td> 
   <td>15892041335</td> 
   <td>1993-12-4</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>金星</td> 
   <td>女</td> 
   <td>18390833220</td> 
   <td>1992-1-2</td> 
   <td>9</td> 
  </tr> 
 </tbody> 
</table>

/**创建存储过程**/
    create procedure myp1(in beauty_id int ,out beautyName varchar(4), out phone varchar(11))
    begin
        select bea.beautyName,bea.phone into beautyName,phone
        from beauty
        where beauty_id = bea.beauty_id;
    end$
    /**调用(这里的“@n和@p是直接创建的用户变量用于存储从表中查找的数据”)**/
    call myp1(1,@n,@p)$
    /**查看**/
    select @n,@p$

案例三：创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回

/**创建存储过程**/
    create procedure myp3(in mydt datetime,out rdt varchar(10) )
    begin
        select  date_format(mydt,'%Y年%c月%d日') into rdt;
    end$
    /**调用**/
    call mp3(now(),@returnDt)$
    /**查看**/
    select @returnDt $

案例四：创建存储过程或函数实现传入一个女神名，返回：“女神 and 男神” 格式的字符串

表beauty

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>&nbsp;sex</th> 
   <th>phone</th> 
   <th>borndate</th> 
   <th>boyfriend</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>柳岩</td> 
   <td>女</td> 
   <td>15090833772</td> 
   <td>2018-1-2</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>AngleBaby</td> 
   <td>女</td> 
   <td>19037399002</td> 
   <td>2012-3-2</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>赵丽颖</td> 
   <td>女</td> 
   <td>15909877112</td> 
   <td>1998-2-1</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>赵敏</td> 
   <td>女</td> 
   <td>15980291854</td> 
   <td>1998-2-3</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>5&nbsp;</td> 
   <td>周芷若</td> 
   <td>女</td> 
   <td>15892041335</td> 
   <td>1993-12-4</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>金星</td> 
   <td>女</td> 
   <td>18390833220</td> 
   <td>1992-1-2</td> 
   <td>9</td> 
  </tr> 
 </tbody> 
</table>

表boy

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>boyname</th> 
   <th>usercp(魅力值)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>大鹏</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>张无忌&nbsp;</td> 
   <td>339</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>黄晓明</td> 
   <td>99</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>冯绍峰</td> 
   <td>400</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>男神</td> 
   <td>500</td> 
  </tr> 
 </tbody> 
</table>

/**创建存储过程**/
    create procedure myp4(in beautyName varchar(4),out couple varchar(10))
    begin
     select concat(beautyName,' and ',ifnull(b.boyName ,'null'))  into couple
     from boy b into couple
     right join beauty beau
     in beau.'boyfriend' = b.'id'
     where beautyName = beau.name;
    end $
    /**调用**/
    call myp4('AngleBaby',@str) $
    /**查看**/
    select @str$

案例五：创建存储过程或函数：根据传入的条目数和起始索引，查询beauty表的记录

/**创建存储过程**/
    create procedure myp5(in startindex int, in size int)
    begin
        select * from beauty 
        limit startindex,size;
    end$
    /**调用：显示beauty表中索引从3开始的共5条数据**/
    call myp5(3,5)$

## 存储过程的删除 ##

语法：

> drop procedure 存储过程名;
> 
> 注意：存储过程的删除一次只能删除一个

## 存储过程的信息查看 ##

语法：

> show create procedure 存储过程名;

本文原文地址：[https://blog.csdn.net/qq\_43952245/article/details/91353716][https_blog.csdn.net_qq_43952245_article_details_91353716]

[https_blog.csdn.net_qq_43952245_article_details_91353716]: https://blog.csdn.net/qq_43952245/article/details/91353716