存储过程和函数
- 含义: 事先经过编译并存储在数据库中的一段Sql语句的集合
- 使用的好处:
1.简化应用开发人员的工作
2.减少数据在数据库和应用服务器之间的传输
3.提高数据处理的效率 - 分类:
1、无返回无参
2、仅仅带in类型,无返回有参
3、仅仅带out类型,有返回无参
4、既带in又带out,有返回有参
5、带inout,有返回有参
注意:in、out、inout都可以在一个存储过程中带多个
创建存储过程
语法:
`create procedure 存储过程名(in|out|inout 参数名 参数类型,…)
begin
存储过程体
end`
类似于方法:
修饰符 返回类型 方法名(参数类型 参数名,...){
方法体;
}
注意:
1、需要设置新的结束标记
delimiter 新的结束标记
示例:
delimiter $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
sql语句1;
sql语句2;
END $
2、存储过程体中可以有多条sql语句,如果仅仅一条sql语句,则可以省略begin end
3、参数前面的符号的意思
in:该参数只能作为输入 (该参数不能做返回值)
out:该参数只能作为输出(该参数只能做返回值)
inout:既能做输入又能做输出
调用存储过程
call 存储过程名(实参列表)
创建函数
学过的函数:LENGTH、SUBSTR、CONCAT等
语法:
CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
函数体
END
调用函数
SELECT 函数名(实参列表)
函数和存储过程的区别
循环批量插入数据
-- 创建存储过程
create procedure my_procedure01(in num int(2),out ii int(2))
begin
declare i int(2) default 0;
declare str int(2);
while i < num
do
set str = round(rand()*100) + 1;
insert into p_procedure (name) values (str);
set i = i + 1;
end while;
set ii = i;
end;
-- 删除存储过程
drop procedure my_procedure01;
-- 调用存储过程
call my_procedure01(2, @y);
-- 查询存储过程的输出
select @y;
拼接批量插入数
-- 创建存储过程
create procedure my_procedure02(in num int(2),out ii text)
begin
declare i int(2) default 0;
declare str int(2);
declare data text;
while i < num
do
set str = round(rand()*100) + 1;
set data = concat('(', str, ')');
-- 存储 alldata 内容的变量必须是全局变量,使用 @ 符修饰
if @insertData = '' then
set @insertData = CONCAT_WS(',', data);
else
set @insertData = CONCAT_WS(',', @insertData, data);
end if;
set i = i + 1;
end while;
-- 存储 sql 内容的变量必须是全局变量,使用 @ 符修饰
set @sql = CONCAT('INSERT INTO p_procedure (name) VALUES ', @insertData);
-- 预处理
PREPARE ins from @sql;
EXECUTE ins;
DEALLOCATE PREPARE ins;
-- 清空全局 @insertData 数据,否则数据会一直追加
set @insertData = '';
-- 输出 sql
set ii = @sql;
end;
-- 调用存储过程
call my_procedure02(2, @ii);
-- 查询存储过程的输出
select @ii;
-- 外部重置全局变量
set @insertData = '';
-- 删除存储过程
drop procedure my_procedure02;
-- 存储过程-实例02 - end