存储过程和函数: 类似于 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 '='
注: 这是本人的学习笔记及练习, 如果有错误的地方望指出一起讨论, 谢谢!
来源: http://www.jianshu.com/p/c607f421a5c9