SQL-存储过程（17）

存储过程和函数：类似于java 中的方法
好处：
1.提高代码的重用性
2.简化操作

一.存储过程

  含义：一组预先编译好的SQL语句的集合，理解成批处理语句
（1).提高代码的重用性
 (2).简化操作
 (3).减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

1.创建语法

create procedure 存储过程名（参数列表）
begin 
   存储过程体（一组合法的SQL语句）
end 

注意⚠️：1.参数列表包含三部分
参数模式 ，参数名，参数类型
举例：
in  stuname  varchar(20)
参数模式：
in :该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout ：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值。

2.如果存储过程体只有一句话，begin end 可以省略，存储过程体中的每一条SQL语句的结尾要求必须加分号。
存储过程结尾可以使用delimited重新设置
语法：
delimiter 结束标记
eg:delimiter $

2.调用语法

call 存储过程名（实参列表）；

案例一：创建一个存储过程往users表里批量插入数据

mysql> delimiter $
mysql> create procedure myp1()
    -> begin
    -> insert into users(username,password)
    -> values('john1',0000),('lily','1111'),('lucy',2222),('dany',3333),('jack',4444);
    -> end $
Query OK, 0 rows affected (0.38 sec)

mysql> call  myp1;
Query OK, 5 rows affected (0.19 sec)

mysql> select * from users;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | keen     |      666 |
|  2 | jobn     |     8888 |
|  3 | john1    |        0 |
|  4 | lily     |     1111 |
|  5 | lucy     |     2222 |
|  6 | dany     |     3333 |
|  7 | jack     |     4444 |
+----+----------+----------+
7 rows in set (0.00 sec)

案例二：（创建带in模式参数的存储过程）创建存储过程实现根据女神名，查询对应的男神信息。

mysql>  delimiter $
mysql> create procedure myt1(in beautyName varchar(20))
    ->      begin 
    ->      select boy.* from boys boy
    ->      right join beauty b on boy.id=b.boyfriend_id
    ->      where b.name = beautyName;
    ->      end $
Query OK, 0 rows affected (0.20 sec)

mysql> call myt1('keen')$

案例三：创建存储过程实现，用户是否登陆成功

mysql> create procedure myt2(in username varchar(20),in password varchar(20))
    ->     begin
    ->     declare result int default 0;
    ->     select count(*) into result
    ->     from users
    ->     where users.username =username
    ->     and users.password = password;
    ->     select if(result>0,'成功','失败');
    ->     end $
Query OK, 0 rows affected (0.17 sec)

mysql> select * from users;
    -> $
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | keen     |      666 |
|  2 | jobn     |     8888 |
|  3 | john1    |        0 |
|  4 | lily     |     1111 |
|  5 | lucy     |     2222 |
|  6 | dany     |     3333 |
|  7 | jack     |     4444 |
|  8 | john1    |        0 |
|  9 | lily     |     1111 |
| 10 | lucy     |     2222 |
| 11 | dany     |     3333 |
| 12 | jack     |     4444 |
| 13 | john1    |        0 |
| 14 | lily     |     1111 |
| 15 | lucy     |     2222 |
| 16 | dany     |     3333 |
| 17 | jack     |     4444 |
+----+----------+----------+
17 rows in set (0.00 sec)

mysql> call myt2('jack',4444)$
+--------------------------------+
| if(result>0,'成功','失败')     |
+--------------------------------+
| 成功                           |
+--------------------------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected, 2 warnings (0.10 sec)

案例四：创建存储过程，通过输入女神名返回对应的男神名

mysql> create procedure myt3(in beautyName varchar(20),out boyName varchar(20))
    -> begin
    -> select boy.boyName into boyName
    -> from boys boy
    -> inner join beauty b on boy.id = b.boyfriend_id
    -> where b.name=beautyName;
    -> end $
Query OK, 0 rows affected (0.06 sec)

mysql> call myt3('keen' ,@bname)$
mysql> select @bName$
+--------+
| @bName |
+--------+
|  kim   |
+--------+

案例五：创建带inout模式参数的存储过程
案例1:传入a和b两个值，最终a和b都翻倍并返回

mysql> create procedure myt4(inout a int,inout b int)
    -> begin
    -> set a=a*2;
    -> set b=b*2;
    -> end $
Query OK, 0 rows affected (0.09 sec)

mysql> #调用 
mysql> set @m=10$
Query OK, 0 rows affected (0.00 sec)

mysql> set @n=20$
Query OK, 0 rows affected (0.00 sec)

mysql> call myt4(@m,@n)$
Query OK, 0 rows affected (0.00 sec)

mysql> select @m,@n$
+------+------+
| @m   | @n   |
+------+------+
|   20 |   40 |
+------+------+
1 row in set (0.00 sec)

3.删除存储过程

语法：drop procedure 存储过程名

mysql> drop procedure myt4$
Query OK, 0 rows affected (0.13 sec)

#来查看一下myt4是不是真的删了。
mysql> show create procedure myt4$
ERROR 1305 (42000): PROCEDURE myt4 does not exist

4.查看存储过程的信息

语法 ：show create procedure 存储过程名

mysql> show create procedure myt4 $
+-----------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure                                                                                            | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| myt4      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `myt4`(inout a int,inout b int)
begin
set a=a*2;
set b=b*2;
end | utf8                 | utf8_general_ci      | utf8_unicode_ci    |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

💔 💔 💔（小白需要大神指点！！！！！）

 在案例二出现了很多不愉快，哪个结果我是因为字符集的问题出现bug
 bug：

mysql> call myt1('keen')$

ERROR 1267 (HY000): Illegal mix of

collations

(utf8_general_ci,IMPLICIT) and

(utf8_unicode_ci,IMPLICIT) for

operation '='

！！！期间我多次修改了 collation_connection的值还是解决不了，试过google 上的很多方法，我今天还是无法解决，55555。
  
mysql> show variables like '%connection%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| max_connections          | 151             |
| max_user_connections     | 0               |
| mysqlx_max_connections   | 100             |
+--------------------------+-----------------+
5 rows in set (0.00 sec)

mysql> set global  collation_connection ='utf8_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%connection%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_unicode_ci |
| max_connections          | 151             |
| max_user_connections     | 0               |
| mysqlx_max_connections   | 100             |
+--------------------------+-----------------+
5 rows in set (0.00 sec)

mysql> show variables like '%connection%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| max_connections          | 151             |
| max_user_connections     | 0               |
| mysqlx_max_connections   | 100             |
+--------------------------+-----------------+
5 rows in set (0.00 sec)

mysql> set session collation_connection ='utf8_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%connection%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_unicode_ci |
| max_connections          | 151             |
| max_user_connections     | 0               |
| mysqlx_max_connections   | 100             |
+--------------------------+-----------------+
5 rows in set (0.00 sec)

案例二：
mysql>  create procedure myt1(in beautyName varchar(20))
   ->          begin 
   ->          select boy.* from boys boy
   ->          right join beauty b on boy.id=b.boyfriend_id
   ->          where b.name = beautyName;
   ->          end $
Query OK, 0 rows affected (0.14 sec)

mysql> show create procedure myt1;
   -> $
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure                                                                                                                                                                                                                | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| myt1      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `myt1`(in beautyName varchar(20))
begin 
        select boy.* from boys boy
        right join beauty b on boy.id=b.boyfriend_id
        where b.name = beautyName;
        end | utf8                 | utf8_unicode_ci      | utf8_unicode_ci    |
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

mysql> call myt1('柳岩')$
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

mysql> set names gbk$
Query OK, 0 rows affected (0.01 sec)

mysql> call myt1('柳岩')$
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！