1.存储过程
Mysql5.0版本之后支持储存过程。
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外不程序调用的一种数据库对象。
存储过程是为了完成特定功能的sql语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用。
通俗来讲:存储过程其实就是能完成一定操作的一组sql语句
优点
- 存储过程可封装,并隐藏复杂的商业逻辑。
- 存储过程可以回传值,并接受参数。
- 存储过程无法使用SELECT指令来运行,因为他是子程序,与查看表、数据表或用户定义函数不同。
- 存储过程可以用在数据检验,强制实行商业逻辑等。
缺点
- 存储过程往往定制在指定数据库上,以为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重新写原有的存储过程。
- 存储过程的性能调校与辍写,受限于各种数据库系统。
2.存储过程的创建和调用
- 存储过程就是具有名字的一段代码,用来完成一个特定的功能。
- 创建的存储过程保存在数据库的数据字典中。
2.1:创建存储过程:
# 创建存储过程(中括号属性非必填)
CREATE
[DEFINER = {user | CURRENT_USER}] # 指定当前操作的用户是谁
PROCEDURE sp_name([proc_parameter[,....]]) # 设置存储过程name 设置入参 出参
[characterisic ...] routine_body
# IN:入参、OUT:出参、INOUT:即是入参又是出参、param_name:参数name、type:参数类型
proc_parameter:
[IN | OUT | INOUT] param_name type
# 特性 COMMENT:注释、LANGUAGE:语言、SECURITY:安全
characteristic:
COMMENT 'string' |
LANGUAGE SQL |
[NOT] DETERMINISTIC |
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } |
SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
# 开始编写存储过程sql语句
[begin_label:] BEGIN
[statement_list]
# 结束编写存储过程sql语句
END [end_label]
2.2:存储过程中的关键语法:
2.2.1:声明语句符结束符,可自定义声明:(Mysql8.0版本之后结束符是分号)
DELIMITER $$
或
DELIMITER //
2.2.2:声明存储过程
CREATE PROCEDURE dome_in_parameter(IN p_in int)
2.2.3:存储过程开始和结束符号
BEGIN ... END
2.2.4:变量赋值
SET @p_in = 1
2.2.5:定义变量
DECLARE 1_int int unsigned default 400000
2.2.6:创建mysql存储过程、存储函数:
create procedure 存储过程名(参数)
2.2.7:存储过程体
create function 存储函数名(参数)
2.2.8:调用存储过程
call sp_name[(参数)]
2.3:存储过程的参数
MYSQL存储过程的参数用在存储过程的定义,共有三种参数类型:IN、OUT、INOUT,形式如下:
CREATE PROCEDURE 存储过程名([[IN | OUT | INOUT] 参数名 数据类型...])
- IN : 表示调用者向过程传入值(传入值可以是字面量或变量)。
- OUT :表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)。
- INOUT :即表示调用者向过程传入值,也表示过程向调用者传出值(值只能是变量)。
注意:
1、如果过程没有参数,也必须在过程名后加上小括号:
CREATE PROCEDURE sp_name([proc_parameter[...]]) ...
2、确保参数的名字不等于列的名字,否则在过程体中,参数名会被当做列名来处理(列名优先级高于参数名)。
建议:
- 输入值使用in参数。
- 输出值使用out参数。
2.4:变量
2.4.1:变量定义
局部变量声明一定要放在存储过程体的开始:
DECLARE variable_name [,variable_name ...] datatype [DEFAULT value];
2.4.2:变量赋值
SET 变量名 = 表达式值 [,variable_name = expression...]
2.5:用户变量
2.5.1:在存储过程中使用用户变量
CREATE PROCEDURE GreetWorld() SELECT CONCAT(@gretting,'world');
SET @gretting = 'hello'
CALL GreetWorld();
运行结果:
2.5.2:在存储过程中传递全局范围的用户变量
CREATE PROCEDURE p1() SET @last_procedure = 'p1',@p;
CREATE PROCEDURE p2() SELECT CONCAT('last procedure was',@last_procedure);
CALL p1();
CALL p2();
运行结果:
3.mybatis调用存储过程
创建存储过程函数:
CREATE PROCEDURE `deleteDep`(in did int,out result int)
begin
declare a int;
select count(*) into a from student where id=did AND student_parent = TRUE ;
if a=0 then set result=1;
else
select id into did from t_department where id=did;
end if;
END
使用mybatis调用存储过程函数:
<select id="" statementType="CALLABLE">
call deleteDep(#{id , mode=IN , jdbcType=INTEGER},
#{result , mode=OUT , jdbcType=INTEGER});
</select>