存储过程
MySQL 5.0 版本开始支持存储过程。
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
通俗来讲:存储过程其实就是能完成一定操作的一组SQL语句
优点
- 存储过程可封装,并隐藏复杂的商业逻辑
- 存储过程可以回传值,并可以接受参数
- 存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同
- 存储过程可以用在数据检验,强制实行商业逻辑等
缺点
- 存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程
- 存储过程的性能调校与撰写,受限于各种数据库系统
创建存储过程
方括号包裹的可以忽略
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
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
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
MYSQL 存储过程中的关键语法
声明语句结束符,可以自定义:
DELIMITER $$
或
DELIMITER //
声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
存储过程开始和结束符号:
BEGIN .... END
变量赋值:
SET @p_in=1
变量定义:
DECLARE l_int int unsigned default 4000000;
创建mysql存储过程、存储函数:
create procedure 存储过程名(参数)
存储过程体:
create function 存储函数名(参数)
调用存储过程:
call sp_name[(传参)];
存储过程的参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
- IN输入参数 :表示调用者向过程传入值(传入值可以是字面量或变量)
- OUT输出参数 :表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
- INOUT输入输出参数 :既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
注意:
1、如果过程没有参数,也必须在过程名后面写上小括号例:
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……
2、确保参数的名字不等于列的名字,否则在过程体中,参数名被当做列名来处理
建议:
- 输入值使用 in参数
- 返回值使用 out参数
- inout参数 就尽量的少用
变量
变量定义
局部变量声明一定要放在存储过程体的开始:
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
在存储过程中使用用户变量
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql > SET @greeting='Hello';
mysql > CALL GreetWorld( );
+----------------------------+
| CONCAT(@greeting,' World') |
+----------------------------+
| Hello World |
+----------------------------+
在存储过程间传递全局范围的用户变量
mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was
',@last_procedure);
mysql> CALL p1( );
mysql> CALL p2( );
+-----------------------------------------------+
| CONCAT('Last procedure was ',@last_proc |
+-----------------------------------------------+
| Last procedure was p1 |
+-----------------------------------------------+
- 用户变量名一般以@开头
- 滥用用户变量会导致程序难以理解及管理
注释
MySQL 存储过程可使用两种风格的注释
– :一般用于单行注释。
/* */ : 一般用于多行注释。
例子(部门管理)
addDep
CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(in depName varchar(32),in parentId int,in enabled boolean,out result int,out result2 int)
begin
declare did int;
declare pDepPath varchar(64);
insert into t_department set name=depName,parentId=parentId,enabled=enabled;
select row_count() into result; -- 获取受影响的行数
select last_insert_id() into did; -- 获取最后一次插入的id
set result2=did;
select depPath into pDepPath from t_department where id=parentId;
update t_department set depPath=concat(pDepPath,'.',did) where id=did;
update t_department set isParent=true where id=parentId;
end
deleteDep
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteDep`(in did int,out result int)
begin
declare ecount int;
declare pid int;
declare pcount int;
declare a int;
select count(*) into a from t_department where id=did and isParent=false;
if a=0 then set result=-2;
else
select count(*) into ecount from t_employee where departmentId=did;
if ecount>0 then set result=-1;
else
select parentId into pid from t_department where id=did;
delete from t_department where id=did and isParent=false;
select row_count() into result;
select count(*) into pcount from t_department where parentId=pid;
if pcount=0 then update t_department set isParent=false where id=pid;
end if;
end if;
end if;
end
在部门类中添加子部门列表属性以及结果属性(存储过程需要)
@ApiModelProperty(value = "子部门列表")
@TableField(exist = false)
private List<Department> children;
@ApiModelProperty(value = "添加返回结果,存储过程使用")
@TableField(exist = false)
private Integer result;
Mapper.xml
<!--添加部门-->
<select id="addDep" statementType="CALLABLE">
call addDep(#{name,mode=IN,jdbcType=VARCHAR},#{parentId,mode=IN,jdbcType=INTEGER},
#{enabled,mode=IN,jdbcType=BOOLEAN},#{result,mode=OUT,jdbcType=INTEGER},#{id,mode=OUT,jdbcType=INTEGER})
</select>
<!--删除部门-->
<select id="deleteDep" statementType="CALLABLE">
call deleteDep(#{id,mode=IN,jdbcType=INTEGER},#{result,mode=OUT,jdbcType=INTEGER})
</select>
statementType :标记操作SQL的对象
- STATEMENT :直接操作sql,不进行预编译,获取数据: $
- PREPARED :预处理,参数,进行预编译,获取数据: # ,默认
- CALLABLE :执行存储过程