一、关于存储过程
1.1 存储过程的定义
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,一次编译后永久有效,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
1.2 存储过程的优缺点
优点
:
- 执行速度快,存储过程经过编译之后会比单独一条一条编译执行要快很多。
- 存储过程可封装,并隐藏复杂的商业逻辑。
- 减少网络传输流量。
- 便于开发者或DBA使用和维护。
- 在相同数据库语法的情况下,改善了可移植性。
缺点
:
- 过程化编程,复杂业务处理的维护成本高。
- 受限于各种数据库系统。因为不同数据库语法不一致,不同数据库之间可移植性差。
- 调试不便。
1.3 存储过程程序
当我们了了解存储过程是什么之后,就需要了解数据库中存在的这三种类型的数据库存储类型程序,如下:
- 存储过程: 存储过程是最常见的存储程序,存储过程是能够接受输入和输出参数并且能够在请求时被执行的程序单元。
- 存储函数: 存储函数和存储过程很相像,但是它的执行结果会返回一个值。最重要的是存储函数可以被用来充当标准的 SQL 语句,允许程序员有效的扩展 SQL 语言的能力。
- 触发器: 触发器是用来响应激活或者触发数据库行为事件的存储程序。通常,触发器用来作为数据库操作语言的响应而被调用,触发器可以被用来作为数据校验和自动反向格式化。
注意: 其他的数据库提供了别的数据存储程序,包括包和类。目前MySQL不提供这种结构。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
二 、存储过程简单创建及使用
2.1先列出student
表中的数据
mysql> select * from student;
+----+-----------+-------+-----+---------------------+------+
| id | sname | age | cid | times | sex |
+----+-----------+-------+-----+---------------------+------+
| 2 | 潘多拉 | 21.00 | 2 | 2020-07-16 18:56:03 | 1 |
| 3 | 光头强 | 21.00 | 3 | 2020-07-16 18:56:07 | 2 |
| 4 | 鸡大腿 | 23.00 | 4 | 2020-07-29 18:56:11 | 1 |
| 5 | 李达康 | 21.00 | 2 | 2020-07-31 18:56:15 | 2 |
+----+-----------+-------+-----+---------------------+------+
4 rows in set (0.00 sec)
2.2 创建一个简单的存储过程
CREATE PROCEDURE `test`()
BEGIN
SELECT * FROM student;
END
2.3 调用存储过程
语法:call 存储过程名称
mysql> call test;
+----+-----------+-------+-----+---------------------+------+
| id | sname | age | cid | times | sex |
+----+-----------+-------+-----+---------------------+------+
| 2 | 潘多拉 | 21.00 | 2 | 2020-07-16 18:56:03 | 1 |
| 3 | 光头强 | 21.00 | 3 | 2020-07-16 18:56:07 | 2 |
| 4 | 鸡大腿 | 23.00 | 4 | 2020-07-29 18:56:11 | 1 |
| 5 | 李达康 | 21.00 | 2 | 2020-07-31 18:56:15 | 2 |
+----+-----------+-------+-----+---------------------+------+
4 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
2.4 查看某个数据库的所有存储过程
语法:SHOW PROCEDURE STATUS WHERE db='数据库名’
## 指定数据库名字
mysql> SHOW PROCEDURE STATUS WHERE db='ceshi';
+-------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| ceshi | case_test | PROCEDURE | root@localhost | 2020-09-03 17:04:32 | 2020-09-03 17:04:32 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| ceshi | if_test | PROCEDURE | ceshi@% | 2020-09-03 16:02:20 | 2020-09-03 16:02:20 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
| ceshi | test | PROCEDURE | ceshi@% | 2020-09-04 10:20:09 | 2020-09-04 10:20:09 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
| ceshi | variable | PROCEDURE | ceshi@% | 2020-09-02 14:33:50 | 2020-09-02 14:33:50 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
| ceshi | variable_in | PROCEDURE | root@localhost | 2020-09-03 15:29:06 | 2020-09-03 15:29:06 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| ceshi | variable_into | PROCEDURE | ceshi@% | 2020-09-02 14:41:41 | 2020-09-02 14:41:41 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
| ceshi | variable_out | PROCEDURE | root@localhost | 2020-09-03 15:34:16 | 2020-09-03 15:34:16 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| ceshi | variable_user | PROCEDURE | ceshi@% | 2020-09-03 15:01:04 | 2020-09-03 15:01:04 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
+-------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
8 rows in set (0.00 sec)
2.5 查看存储过程详细的定义信息
语法:SHOW CREATE PROCEDURE 数据库名.存储过程名
mysql> SHOW CREATE PROCEDURE ceshi.if_test;
+-----------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| if_test | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`ceshi`@`%` PROCEDURE `if_test`(in vid int)
begin
# 声明一个局部变量res年龄工资标准结果
declare res varchar(32);
# 声明一个局部变量存放年龄
declare s double;
# 根据入参id查询年龄
select age into s from student where id = vid;
# if判断的使用
if s <= 10 then
set res = '小孩子';
elseif s <= 15 then
set res = '中孩子';
elseif s <= 20 then
set res = '大孩子';
else
set res = '老大孩子';
end if;
# 查询年龄区间结果
select res;
end | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
+-----------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
2.6 删除某个存储过程
语法:drop procedure [ IF EXISTS ] 存储过程名字
mysql> drop procedure test;
Query OK, 0 rows affected (0.00 sec)
2.7 关于结束符(比较重要)
默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀。 在定义过程时,使用 DELIMITER ]]
命令将语句的结束符号从分号 ; 临时改为两个]]
,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。
可以随意更改为一些符号,但是不能触碰到mysql关键词。
在图形化界面(如:navicat)
可以直接使用;
分号,但是命令行界面新建存储过程需要结束符。
例如 :
mysql> delimiter ]]
mysql> CREATE PROCEDURE `test`()
-> BEGIN
-> SELECT * FROM student;
-> END ]]
Query OK, 0 rows affected (0.00 sec)
三、变量的几种类型
3.1 局部变量
语法:
declare 变量名 变量类型(如,int,char,varchar) defalut ‘默认值’(可选)
作用域:
只在begin end中生效
设置变量:
delimiter //
create procedure variable()
begin
--声明一个int类型的变量age
declare age int;
--给该变量进行赋值
set age = 20;
--输出这个变量
select age;
end //
调用:
mysql> call variable;
+------+
| age |
+------+
| 20 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
局部变量使用into接受参数:
delimiter //
create procedure variable_into()
begin
declare var_name varchar(20) default '';
set var_name = "曼陀罗";
select sname into var_name from student where id = 2;
select var_name;
end //
调用:
mysql> call variable_into;
+-----------+
| var_name |
+-----------+
| 潘多拉 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3.2 用户变量
语法:
set @变量名
作用域:
当前会话(连接)有效
设置用户变量:
delimiter //
CREATE procedure variable_user()
begin
set @username = '王钢炮';
end //
调用该存储过程并查询用户变量:
mysql> call variable_user();
Query OK, 0 rows affected (0.00 sec)
mysql> select @username;
+-----------+
| @username |
+-----------+
| 王钢炮 |
+-----------+
1 row in set (0.00 sec)
3.3 会话变量
语法:
select @@session.变量名
作用域:
当前会话(连接)有效
查看关于sql的会话变量:
show session variables like '%sql%';
mysql> show session variables like '%sql%';
+---------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------+
| slave_sql_verify_checksum | ON |
| sql_auto_is_null | OFF |
| sql_big_selects | ON |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | 0 |
| sql_warnings | OFF |
+---------------------------+--------------------------------------------+
13 rows in set (0.01 sec)
查看指定的会话变量:
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
修改指定的会话变量:
set @@session.big_tables = 1;
3.4 全局变量
查看全局变量中变量名有sql的记录:
mysql> show global variables like '%sql%';
+---------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------+
| slave_sql_verify_checksum | ON |
| sql_auto_is_null | OFF |
| sql_big_selects | ON |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | 0 |
| sql_warnings | OFF |
+---------------------------+--------------------------------------------+
13 rows in set (0.01 sec)
查看全局变量sql_mode的值:
mysql> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
四、入参和出参
语法:in|out|inout 参数名 参数 类型
其中,int为入参,out为出参,inout为
4.1 入参in
delimiter //
create procedure variable_in(in value_name varchar(20))
begin
# 使用用户变量出参(为用户变量赋参数值)
set @username = value_name;
end //
mysql> call variable_in('英雄终将归来');
Query OK, 0 rows affected (0.00 sec)
mysql> select @username;
+--------------------+
| @username |
+--------------------+
| 英雄终将归来 |
+--------------------+
1 row in set (0.00 sec)
4.2 出参out
delimiter //
# 创建一个入参和出参的存储过程
create procedure variable_out(in vid int,out vname varchar(20))
begin
# 传入参数vid查询学生返回sname 值(查询出的name值用出参接收并返回)
select sname into vname from student where id = vid;
end //
mysql> call variable_out(2, @username) //
Query OK, 1 row affected (0.00 sec)
mysql> select @username //
+-----------+
| @username |
+-----------+
| 潘多拉 |
+-----------+
1 row in set (0.00 sec)
4.3 入参出参inout
如上所述。
五、流程控制语句
先查看一下现在student
表中的数据:
mysql> select * from student;
+----+-----------+-------+-----+---------------------+------+
| id | sname | age | cid | times | sex |
+----+-----------+-------+-----+---------------------+------+
| 2 | 潘多拉 | 10.00 | 2 | 2020-07-16 18:56:03 | 1 |
| 3 | 光头强 | 15.00 | 3 | 2020-07-16 18:56:07 | 2 |
| 4 | 鸡大腿 | 20.00 | 4 | 2020-07-29 18:56:11 | 1 |
| 5 | 李达康 | 25.00 | 2 | 2020-07-31 18:56:15 | 2 |
+----+-----------+-------+-----+---------------------+------+
4 rows in set (0.00 sec)
5.1 if语句的使用
delimiter //
create procedure if_test(in vid int)
begin
# 声明一个局部变量res年龄工资标准结果
declare res varchar(32);
# 声明一个局部变量存放年龄
declare s double;
# 根据入参id查询年龄
select age into s from student where id = vid;
# if判断的使用
if s <= 10 then
set res = '小孩子';
elseif s <= 15 then
set res = '中孩子';
elseif s <= 20 then
set res = '大孩子';
else
set res = '老大孩子';
end if;
# 查询年龄区间结果
select res;
end //
调用存储过程并传入id为1:
mysql> call if_test(2);
+-----------+
| res |
+-----------+
| 小孩子 |
+-----------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.04 sec)
5.2 case when的使用
delimiter //
create procedure case_test(in vid int)
begin
# 声明一个局部变量res年龄工资标准结果
declare res varchar(20);
# 声明一个局部变量存放年龄
declare s double;
# 根据入参id查询年龄
select age into s from student where id = vid;
CASE
WHEN s <= 10 THEN
set res = '小孩子';
WHEN s <= 15 THEN
set res = '小孩子';
WHEN s <= 20 THEN
set res = '小孩子';
ELSE
set res = '老大孩子';
END CASE;
# 查询年龄区间结果
select res;
end //
调用存储过程并传入id为2:
mysql> call case_test(2);
+-----------+
| res |
+-----------+
| 小孩子 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
5.3 while的使用
CREATE DEFINER=`ceshi`@`%` PROCEDURE `test_while`()
begin
declare i int default 1;
declare str varchar(20) default '1';
# 开始while循环
# 指定while循环结束条件
while i < 7 do
set i = i + 1;
set str = concat(str, '$', i);
# while循环结束
end while;
# 查询while循环拼接字符串
select str;
end
mysql> call test_while();
+---------------+
| str |
+---------------+
| 1$2$3$4$5$6$7 |
+---------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
六、总结
以上为存储过程的简单学习,以后如果还有对其更深入的理解,将会补充到里面。