由于是综合提炼,文章中如有引用请告知出处添加:
存储过程:用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程;
函数:通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表。
存储过程和函数存在以下几个区别:
1)一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执行一组修改全局数据库状态的操作。
2)对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象。函数只能返回一个变量;而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型,而函数只能有IN类~~存储过程声明时不需要返回类型,而函数声明时需要描述返回类型,且函数体中必须包含一个有效的RETURN语句。
3)存储过程,可以使用非确定函数,不允许在用户定义函数主体中内置非确定函数。
4)存储过程一般是作为一个独立的部分来执行( EXECUTE 语句执行),而函数可以作为查询语句的一个部分来调用(SELECT调用),由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程,而可以使用函数。
语法:(存储过程没返回值,参数可以是 IN,OUT,IN OUT类型,有的人可能会理解成OUT 也算是返回值。)
创建存储过程:
CREATE [definer = {user|current_user}] PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]]) [ characteristics..] routime_body
其中:
proc_parameter : [IN|OUT|INOUT] parameter_name type
其中IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型
存储过程体中可以使用自定义函数(UDF)中使用的复合结构/流程控制/SQL语句/自定义变量等等内容,详情参阅 MySQL自定义函数用法详解-复合结构自定义变量/流程控制
调用存储过程:
CALL sp_name ([ proc_parameter [,proc_parameter ...]])
CALL sp_name
说明:当无参时,可以省略"()",当有参数时,不可省略"()"
存储过程修改:
ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建!
删除存储过程:
DROP PROCEDURE [IF EXISTS] sp_name
示例:
创建无参存储过程:
delimiter // CREATE PROCEDURE showTime() BEGIN SELECT now(); END// delimiter ; CALL showTime;
作用:显示当前时间,没什么实际意义
创建含参存储过程:
只有一个IN参数
delimiter // CREATE PROCEDURE seleById(IN uid SMALLINT UNSIGNED) BEGIN SELECT * FROM son WHERE id = uid; END// delimiter ; call seleById(2);
包含IN参数和OUT参数
delimiter // CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED) BEGIN DELETE FROM son WHERE id = uid; SELETE row_count() into num; END// delimiter ; call seleById(2,@changeLine); SELETE @changeLine;
说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行数值的用户变量@changeLine,select @changeLine;输出被影响行数.
存储过程存在的必要性(好处):
存储过程说白了就是把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要的时候从数据库中直接调用,省去了编译的过程.
提高了运行速度;
同时降低网络数据传输量(你觉得传一堆SQL代码快,还是传一个存储过程名字和几个参数快???)
函数必须指定返回值,且参数默认为IN类型。
调用方式:函数 select my_fun() ;过程 call my_pro( ) ;
DEMO
1
2
3
4
5
6
7
8
9
10
11
|
DELIMITER $$
DROP
FUNCTION
IF EXISTS my_fun$$
CREATE
FUNCTION
my_fun(a
INT
(2),b
INT
(2))
RETURNS
INT
(4)
BEGIN
DECLARE
sum_
INT
(2)
DEFAULT
0;
SET
sum_ = a + b;
RETURN
sum_;
END
$$
DELIMITER ;
|
1
2
3
4
5
6
7
8
|
DELIMITER $$
DROP
PROCEDURE
IF EXISTS my_pro$$
CREATE
PROCEDURE
my_pro(
IN
a
INT
(2),
IN
b
INT
(2) ,
OUT
c
INT
(2))
BEGIN
SET
c = a + b;
END
$$
DELIMITER ;
|
调用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> call my_pro(1,2,@c);
Query OK, 0
rows
affected (0.00 sec)
mysql>
select
@c;
+
------+
| @c |
+
------+
| 3 |
+
------+
1 row
in
set
(0.00 sec)
mysql>
select
my_fun(1,2);
+
-------------+
| my_fun(1,2) |
+
-------------+
| 3 |
+
-------------+
1 row
in
set
(0.00 sec)
|