一、概述
存储过程&函数,类似编程语言的方法
什么是方法?
完成特定功能的一组语句
方法的特点
1、可重用性
2、简化操作
二、存储过程【Stored Procedures】:
一组预先编译好的SQL语句的集合,可理解批处理语句
提供给Java操作复杂的SQL逻辑将会变得困难
交给存储过程将减少Java的逻辑复杂程度,减少编译次数和数据库服务的连接次数
提高SQL效率
适用场景:
大型系统项目
三、语法和使用
1、总体语法:
CREATE PROCEDURE 存储过程标识符(参数列表)
BEGIN (开始过程)
# 存储过程【一组符合语法的SQL语句】
END (结束过程)
2、参数的写法:
参数模式 参数名称 参数类型
样例:
IN aaa VARCHAR(24)
3、参数模式的种类:
IN
# 仅输入,需要调用方法传入值
OUT
# 仅输出,可以作为存储过程的返回值,等同Java的返回类型声明
INOUT
# 兼输入输出
4、语法事项:
1、如果存储过程只有一句SQL语句需要执行,可以不写BEGIN & END
2、每条SQL语句的结尾必须协商分号
3、存储过程的结尾关键字可以使用DELIMTER关键字重新设置
5、DELIMITER使用
DELIMITER 结束标记
6、存储过程的调用语法
CALL 存储过程名称(实际参数);
四、三种参数模式的案例:
无参存储过程
创建一个存储过程
DELIMITER $
CREATE PROCEDURE batchInsert()
BEGIN
INSERT INTO
admin(`username`,`password`)
VALUES
('john','3333'),
('rose','4444'),
('jack','5555'),
('tomy','6666'),
('jerry','7777');
END $
创建完成之后会保留在数据库中
调用存储过程:
在案例中需要使用$在存储过程的结尾写上
但是发现报错:
错误代码: 1064You have an errorin your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near '$' at line 1
SQL语法检查发现不需要添加$,执行成功
CALL batchInsert()
带IN模式参数的存储过程
报错发现每次必须要声明 结尾标识
这个结尾标识必须紧跟END
DELIMITER $
CREATE PROCEDURE queryBoyInfoByGirlName(IN beautyName VARCHAR(20))
BEGIN
SELECT
bo.*
FROM
boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE
b.name = beautyName;
END$
存储过程调用:
CALL `queryBoyInfoByGirlName`('热巴')
多IN模式参数存储过程:
DELIMITER $
CREATE PROCEDURE login
(
IN `username` VARCHAR(20),
IN `password` VARCHAR(20)
)
BEGIN
DECLARE result VARCHAR(20) DEFAULT '';
# ------------------------------------------
SELECT
COUNT(*)
INTO
result
FROM
admin
WHERE
admin.`username` = `username`
AND
admin.`password` = `password`;
# ------------------------------------------
SELECT result;
END $
存储过程调用:
CALL login('john', '8888');
如果还需要显示成功或者失败字符
DELIMITER $
CREATE PROCEDURE login2
(
IN `username` VARCHAR(20),
IN `password` VARCHAR(20)
)
BEGIN
DECLARE result VARCHAR(20) DEFAULT 0;
# ------------------------------------------
SELECT
COUNT(*)
INTO
result
FROM
admin
WHERE
admin.`username` = `username`
AND
admin.`password` = `password`;
# ------------------------------------------
SELECT IF(result > 0, '登录成功', '登录失败') AS '提示信息';
END $
带Out模式参数的存储过程
DELIMITER $
CREATE PROCEDURE queryGirlNameByBoy
(
IN beautyName VARCHAR(20),
OUT boyName VARCHAR(20)
)
BEGIN
SELECT
boys.`boyName`
INTO
boyName
FROM
boys INNER JOIN beauty ON boys.id = beauty.`boyfriend_id`
WHERE
beauty.`name` = beautyName;
END $
调用时注入一个变量,由变量查询返回
CALL queryGirlNameByBoy('小昭', @bName);
SELECT @bName AS '名称'
除了名字以外,再追加一个CP字段值,重写存储过程
DELIMITER $
CREATE PROCEDURE queryBoyNameAndCpValueByGirlName
(
IN beautyName VARCHAR(20),
OUT boyName VARCHAR(20),
OUT coupleValue INT
)
BEGIN
SELECT
boys.`boyName`, boys.`userCP`
INTO
boyName, coupleValue
FROM
boys INNER JOIN beauty ON boys.id = beauty.`boyfriend_id`
WHERE
beauty.`name` = beautyName;
END $
调用:
CALL queryBoyNameAndCpValueByGirlName
(
'小昭',
@bName,
@userCP
);
SELECT @bName 'NAME', @userCP 'CoupleValue';
带INOUT模式参数的存储过程:
DELIMITER $
CREATE PROCEDURE returnDouble
(
INOUT a INT,
INOUT b INT
)
BEGIN
SET a = a * 2;
SET b = b * 2;
END$
调用:
SET @varA = 125;
SET @varB = 334;
CALL returnDouble(@varA, @varB);
SELECT @varA, @varB;
五、存储过程的删除和查看
在之前的案例中我们已经创建了许多存储过程
删除语法:
DROP PROCEDURE 存储过程名称
删除语句一次只能删除一个,不可以实现多个删除
查看语法:
SHOW CREATE PROCEDURE 存储过程名称
六、为什么没有存储过程修改?
存储过程是由修改的SQL的,但是能够修改的只有特定的元数据
不能对存储过程的逻辑进行修改,改了没改都一样,没有太大的意义
详细资料参见:
https://www.cnblogs.com/geaozhang/p/6817698.html
如果要实现我们的真正意义上的修改,就需要先删除存储过程,再重新创建