什么是存储过程
存储过程，是经过编译并存储在数据库中的一段SQL语句集合。
存储过程和函数的区别，在于存储过程无返回值，而函数有返回值。
存储过程的好处
-
提高了代码的重用性。
-
减少了SQL语句的编译次数，提高了效率。
存储过程的语法
1. 创建
1
2
3
4
|
CREATE PROCEDURE 过程名(参数列表)
BEGIN
存储过程体
END
|
说明：
-
参数列表包含三部分：参数模式、参数名、参数类型
-
参数模式有三种：IN输入，OUT输出，INOUT输入和输出。
-
如果存储过程体只有一句话，BEGIN和END可以省略。
-
存储过程体中，每句SQL语句以分号表示结束。
-
在存储过程的结尾END处，需要使用DELIMITER设置的结束标记。
2. 调用
3. 删除
1
|
DROP PROCEDURE [IF EXISTS] 过程名;
|
4. 查看存储过程
1
|
SHOW CREATE PROCEDURE 过程名;
|
或者：
还可以从mysql.proc中获取存储过程的信息，但是有的版本不支持该方法。
1
|
SELECT * FROM mysql.proc;
|
5. 修改
存储过程无法修改。
示例
1. 无参
定义：
1
2
3
4
5
|
DELIMITER $
CREATE PROCEDURE test_pro1()
BEGIN
INSERT INTO admin(username, password) VALUES ('a', '1234'), ('b', '1234');
END $
|
调用：
2. IN
定义：
1
2
3
4
5
6
7
8
9
10
11
12
|
DELIMITER $
CREATE PROCEDURE test_pro2(IN username VARCHAR(20), IN password VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT
COUNT(*) INTO result
FROM
admin
WHERE
admin.username = username AND admin.password = password;
SELECT IF(result>0,'成功','失败');
END $
|
调用：
1
|
CALL test_pro2('abc', '1234')$
|
3. OUT
定义：
1
2
3
4
5
|
DELIMITER $
CREATE PROCEDURE test_pro3(IN a int, IN b int)
BEGIN
SET b = a*2;
END$
|
调用：
1
2
|
CALL test_pro3(10, @res)$
SELECT @res$
|
5. INOUT
定义：
1
2
3
4
5
|
DELIMITER $
CREATE PROCEDURE test_pro4(INOUT a INT)
BEGIN
SET a=a*2
END$
|
调用：
1
2
|
SET @m=10$
CALL test_pro4(@m)$
|
什么是函数
函数和存储过程类似，都是经过编译并存储在数据库中的一段SQL语句集合。但是函数有且仅有一个返回值，而存储过程没有返回值。
函数的语法
1. 创建函数
1
2
3
4
|
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
|
说明：
-
参数列表包含两部分：参数名和参数类型。
-
函数体中必须包含RETURN。
-
如果函数体仅有一句话，可以省略BEGIN和END。
-
使用DELIMITER语句设置函数结束标记。
2. 调用
3. 查看函数
1
|
SHOW CREATE FUNCTION 函数名;
|
4. 删除函数
5. 示例
定义：
1
2
3
4
5
6
|
DELIMITER $
CREATE FUNCTION test_func1() RETURNS int
BEGIN
DECLARE a int DEFAULT 10;
RETURN a;
END$
|
调用：
定义：
1
2
3
4
5
6
|
DELIMITER $
CREATE FUNCTION test_func2(a int) RETURNS int
BEGIN
SET a = a*2;
RETURN a;
END$
|
调用：
游标
游标用于存储查询的结果集，主要应用于存储过程和函数。
1. 声明
1
|
DECLARE cursor_name CURSOR FOR 查询语句;
|
2. OPEN
3. FETCH
1
|
FETCH cursor_name INTO 变量...;
|
4. CLOSE
5. DECLARE EXIT HANDLER FOR NOT FOUND
在存储过程中，当游标配合循环语句一起使用时，需要知道循环何时结束，此时就需要使用如下机制：
1
|
DECLARE EXIT|COUTINUE HANDLER FOR NOT FOUND 语句;
|
该语句的含义是，当游标无法获取下一条数据时，就执行FOR NOT FOUND后面的语句。
6. 示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
DELIMITER $
CREATE PROCEDURE test_cursor()
BEGIN
DECLARE a int(11);
DECLARE b int(11);
DECLARE c varchar(10);
DECLARE over int default 0;
DECLARE cursor_t CURSOR FOR select * from t1;
DECLARE EXIT HANDLER FOR NOT FOUND SET over=1;
OPEN cursor_t;
WHILE over=0 DO
FETCH cursor_t INTO a, b, c;
SELECT a, b, c;
END WHILE;
CLOSE cursor_t;
END$
|