MySql学习10-----存储过程和函数

存储程序可以分为存储过程和函数。存储过程和存储函数都是一组sql语句的集合。这些语句集合被当做一个整体存入数据库中。

一. 存储过程

1.1 创建存储过程

create procedure 存储过程名(参数列表)

sql语句集合

（note:begin....end是用于多条sql语句，当只有一条sql语句的时候可以省掉begin....end)

delimiter //
create procedure pro()
begin
select * from stu;  begin和end中间是存储过程体，可以放很多的sql语句
end
//

1.2 调用存储过程

在只是创建了一个存储过程，那么我们怎么来使用这个存储过程呢？

语法：call  存储过程名()

将上一个存储过程pro使用的例子：

call pro();

1.3 创建一个带参数的存储过程

参数列表：存储过程的参数有三种类型：in,out,inout 分别表示传入参数和传出参数，和即传入也传出参数

（1）示例一：统计user表中记录总数并输出

mysql> create table user(
    -> id int primary key auto_increment,
    -> name varchar(30),
    -> age int);
Query OK, 0 rows affected (0.05 sec)

mysql> select *from user;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   23 |
|  2 | lisi     |   14 |
|  3 | wangwu   |   45 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure proc(out param1 int)
    -> begin
    -> select count(*) into param1 from user;
    -> end
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> call proc(@param1);
Query OK, 1 row affected (0.00 sec)

mysql> select @param1;
+---------+
| @param1 |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

（2）示例二：从User表中查找name等于zhangsan的id号码，并输出

mysql> delimiter //
mysql> create procedure proc2(in param1 varchar(30),out param2 int)
    -> begin
    -> select id into param2 from user where name=param1;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc2('zhangsan',@param2);
Query OK, 1 row affected (0.00 sec)

mysql> select @param2;
+---------+
| @param2 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Note: 也可以通过  set @param1=23 这种方式来给输入参数赋值

二. 存储函数

2.1 存储过程和存储函数的不同

• 在函数中必须要有return返回值
• 在存储过程中参数有in  out   inout三种，默认为in类型，但是在函数中只有一种in类型

2.2 创建存储函数

语法：create  function 函数名()

　　　      return 返回类型

　　　　　　　　  sql语句集合

mysql> delimiter //
mysql> create function fun1(num int)
    -> returns int
    -> begin
    -> return num+100;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select fun1(100);
+-----------+
| fun1(100) |
+-----------+
|       200 |
+-----------+
1 row in set (0.00 sec)

2.3 调用存储函数

用语法不在使用关键字call，而是关键字select ，select  函数名

三. 删除存储过程和存储函数

语法：drop   procedure  |   function  存储过程名或者是函数名

 例子：

drop procedure pro;

注意他是不带括号的

四. 游标的使用

4.1 为何需要使用游标

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程存储函数中使用游标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。

4.2 使用游标的注意点

• 游标不能单独存在，必须在存储过程或者是存储函数中使用。
• 游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

4.3 具体使用语法

语法：

• 1.创建游标：declare  游标名  cursor  for  select语句  
• 2.打开游标：open  游标名
• 3.使用游标：fetch  游标名  into  变量名
• 4.关闭光标：close 游标名

4.4 举例

参考：https://blog.csdn.net/xushouwei/article/details/52201360

 

当然，还可以修改存储过程和函数，以及查看它们的状态等功能。