MySQL存储过程

MySQL存储过程

存储过程和函数

​ 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化很多工作，减少数据在数据库和应用服务器之间的传输，提高数据处理效率。

​ 存储过程和函数的区别在于函数必须有返回值，存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。

• IN 参数：输入参数(默认情况下为IN)，表示该参数的值必须由调用程序指定。

• OUT参数：输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序。

• INOUT参数：既是输入参数，又是输出参数，表示该参数的值既可以由调用程序指定，又可以将参数的计算结果返回给调用程序。

存储过程

============定义存储过程===========
create procedure 过程名 (参数1，参数2)
[存储过程选项]
begin
	sql语句：
end
===========调用存储过程============
call 过程名（参数1，参数2）；

存储过程选项	简要说明
delimiter	修改执行符号
language sql	说明下面过程使用SQL编写，这条也是系统默认的
[not]deterministic	deterministic确定的，即每次输入一样输出也一样的程序。 not deterministic
{contains sql | not sql | reads sql data | modifies sql data}	contains sql 表示子程序不包含读或写数据的语句。 no sql表示子程序不包含sql语句。 reads sql data表示子程序包含读数据的语句。 modified sql data表示子程序包含写数据的语句。 默认使用的值是contains sql
sql security {definer | invoker }	用来指定是使用创建该子程序的许可执行，还是调用者的许可来执行
comment ‘string’	注释信息

mysql> select * from student;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    1 | bob   | M      |
|    2 | alice | F      |
|    3 | john  | F      |
+------+-------+--------+
3 rows in set (0.00 sec)

mysql> delimiter $$

mysql> create procedure choose()
    -> reads sql data
    -> begin
    -> select id from student;
    -> end
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
###调用过程
mysql> call choose();
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
=============存储过程参数传递===============
mysql> delimiter $$
mysql> create procedure getInformation(in b int)
    -> begin
    -> select * from student where id=b;
    -> end
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> call getInformation (2)$$
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    2 | alice | F      |
+------+-------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

==============================
mysql> delimiter $$
mysql> create procedure demo(out str varchar(200))
    -> begin
    -> select name into str from student where id=2;
    -> end
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> call demo(@a)$$

mysql> select @a$$
+-------+
| @a    |
+-------+
| alice |
+-------+
1 row in set (0.00 sec)

查看存储过程

show procedure status like 'sp_name'
==================查看状态===============
mysql> show procedure status like 'demo'\G
*************************** 1. row ***************************
                  Db: student
                Name: demo
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2019-07-29 11:28:00
             Created: 2019-07-29 11:28:00
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

==================查看存储过程定义===============
show create procedure sp_name
mysql> show create procedure  demo\G
*************************** 1. row ***************************
           Procedure: demo
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`(out str varchar(200))
begin
select name into str from student where id=2;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)