Mysql中的存储程序——存储过程

存储函数与存储过程

• 存储函数：
  • 定义时需要使用RETURNS声明返回类型，并在函数体使用RETURN指定返回值
  • 使用时直接进行函数调用
  • 不支持IN、OUT、INOUT指定参数前缀
  • 只可返回一个结果
  • 执行过程中，产生的结果集不会显示出来
• 存储过程：
  • 无需返回值
  • 使用call 关键字进行调用
  • 支持指定IN、OUT、INOUT指定参数前缀
  • 通过设置多个参数返回多个结果
  • 执行过程中产生的结果集会显示到客户端

存储过程的基本操作

创建存储过程

mysql> create procedure add_user(       //关键字：create procedure 存储过程名
    -> id_v int,                        //参数列表
    -> name_v varchar(20),
    -> pwd_v varchar(20),
    -> rname_v char(5)
    -> )
    -> begin
    -> select * from users;
    -> insert into users (id, userName, passWord,realName) values (id_v, name_v, pwd_v, rname_v);  //插入语句的字段一一对应
    -> select * from users;
    -> end #
Query OK, 0 rows affected (0.00 sec)

mysql> call add_user(3,'linkdo','!Linkdo#365', 'csop')#      //使用存储函数时，使用call 存储过程名([参数])
+----+----------+---------------+----------+
| id | userName | passWord      | realName |
+----+----------+---------------+----------+
|  1 | root     | root_psd      | zyl      |
|  2 | zlz123   | zhoulz1234567 | zlz      |
+----+----------+---------------+----------+
2 rows in set (0.00 sec)

+----+----------+---------------+----------+
| id | userName | passWord      | realName |
+----+----------+---------------+----------+
|  1 | root     | root_psd      | zyl      |
|  2 | zlz123   | zhoulz1234567 | zlz      |
|  3 | linkdo   | !Linkdo#365   | csop     |
+----+----------+---------------+----------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

查看存储过程

//查看已存在的存储过程，可以使用like模糊查询
mysql> show procedure status\G
*************************** 1. row ***************************
                  Db: mysql
                Name: add_user
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-12-22 20:30:56
             Created: 2021-12-22 20:30:56
       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)

//查看某存储过程的定义过程
mysql> show create procedure add_user\G
*************************** 1. row ***************************
Procedure: add_user
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `add_user`(
id_v int,
name_v varchar(20),
pwd_v varchar(20),
rname_v char(5)
)
begin
select * from users;
insert into users (id, userName, passWord,realName) values (id_v, name_v, pwd_v, rname_v);
select * from users;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

 

删除存储过程

mysql> drop procedure add_user#
Query OK, 0 rows affected (0.00 sec)

 

 存储过程详解

 存储函数在定义参数时，可以为参数添加前缀，在使用时可以限制其输入输出。

• IN：仅用于从外部传递参数至存储过程，而在存储过程内部，就算参数被修改，外部也是不可见的。因此 IN参数 只用于读取，一般将常数作为 IN参数 
• OUT：仅用于存储过程中执行的结果传递传递至存储过程的调用者。就算参数已赋值，在内部是读取不到的（读取到的是变量的默认值NULL）。而在过程内部，对参数赋值后，外部看来是可见的
• INOUT：结合两者，即参数可以在存储过程内部读取，赋值后又可以在外部可见。

 注意：如果不写参数前缀，则默认是IN 。如果传递了多个OUT或INOUT参数，则可获得多个结果

 案例（IN）：

mysql> delimiter #  //将分隔符换成 #

mysql> set @ids=2# //设置用户变量ids=2
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure in_test(in dd int)  
    -> begin-> set dd=999;  //该存储过程只两传入的参数做一个赋值处理
    -> end #
Query OK, 0 rows affected (0.00 sec)

mysql> call in_test(2)#  //调用存储过程
Query OK, 1 row affected (0.01 sec)

mysql> select @ids#   //再次查询，发现ids的值并没有修改
+------+
| @ids |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

 

案例（OUT）

mysql> create procedure out_test(out dd int)
    -> begin
    -> select dd;   //执行存储过程后，先查询一次传入参数的值
    -> set dd=123;   //将参数重新赋值为123
    -> end #
Query OK, 0 rows affected (0.00 sec)
mysql> call out_test(@ids)#  //这是村春过程内部的查询执行出来的结果集，此时参数的值读取到是NULL
+------+
| dd   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @ids#  //再次查询，变量ids的值已经变为被修改后的123
+------+
| @ids |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

mysql> 

 

案例（INOUT）

mysql> set @ids=2#
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure inout_test(inout dd int)
-> begin
-> select dd;
-> set dd=888;
-> end #
Query OK, 0 rows affected (0.00 sec)

mysql> call inout_test(@ids)#  //调用过程后，可以查出参数的值为2
+------+
| dd   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @ids#  //调用后，可查到参数已被正常修改
+------+
| @ids |
+------+
|  888 |
+------+
1 row in set (0.00 sec)