存储过程Procedure是一组为了完成特定功能的sql语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。
基本结构包含三部分:过程声明、执行过程部分和异常处理部
Mysql中存储过程
创建存储过程
-- 格式
create procedure 存储过程名称(in/out/inout 参数名 参数类型(长度))
begin
SQL语句;
end;
-- 用户变量:是用户根据需求自己定义的变量,用户变量不用提前声明,在用是时候直接用‘@变量名’使用就--- 可以。其作用域为当前连接。
set @name =: '';
-- 查看变量
select @var_name ;
-- 局部变量:是根据需要定义在局部生效的变量,访问之前,需要declare声明。可用作存储过程内的局部变量和输入参数,局部变量的作用范围是在其内声明的begin...end模块。
declare name =: '';
select a.name into name from user a;
-- 系统变量有2个@@,用户变量1个@,局部变量在存储过程或存储函数内部用declare声明
DROP PROCEDURE if exists queryUserName;
create PROCEDURE queryUserName(IN id int, OUT name varchar(10))
BEGIN
select u.user_name into name from sys_user u where u.user_id = id;
END;
call queryUserName(1, @name);
select @name;
-- if语法:
IF 条件1 THEN
...
ELSEIF 条件2 THEN
...
END IF;
-- loop语法:
label: LOOP
...
-- ITERATE label; 跳过当前进入下一次循环
-- LEAVE label; 退出指定循环
END LOOP label;
-- 游标的使用
create procedure insertUser()
begin
declare id int default 0;
declare name varchar(20) default '';
declare done boolean default 1;
-- 声明游标
declare rs cursor for SELECT user_id, user_name from user;
-- 定义异常:
declare continue handler for SQLSTATE '02000' SET done = 0;
-- 打开游标
open rs;
delete from test_user;
while done do
begin
fetch rs into id, name;
INSERT into test_user SELECT u.user_id , count(1) from user u WHERE u.user_id = id;
end ;
end while;
-- 关闭游标
close rs;
end;
java调用
<parameterMap type="paramsMap" id=“usermap">
<parameter property="id" jdbcType="VARCHAR" mode="IN"/>
<parameter property="name" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>
<insert id="queryUserNameById" parameterMap="paramsMap" statementType="CALLABLE">
{call queryUserName(?, ?)}
</insert >
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("id", "1");
userDao.queryUserNameById(map);
String name = map.get(“name”);
Oracle中存储过程
创建:
create or replace procedure hello_procedure
as
begin
dbms_output.put_line('Hello Procedure!');
end;
执行:
exec hello_procedure;
execute hello_procedure;
从一个pl/sql块调用
begin
hello_procedure;
end;
删除:
drop procedure hello_procedure;
查询:
select * from user_source where name = 'HELLO_PROCEDURE' order by line;
带参的存储过程(in:输入参数 out:输出 in out:输入输出)
create procedure findMin(x in number, y in number, z out number) is
begin
if x < y then
z := x;
else
z := y;
end if;
end;
// 调用
declare
a number := 11;
b number;
c number;
begin
for i in 10..12 loop
b := i;
findMin(a, b, c);
dbms_output.put_line('两个数:' || a || ',' || b || '的最小值是:' || c);
end loop;
end;
游标的使用
declare
name varchar;
cursor names is select name from user; // 声明
begin
open names; // 打开
loop
fetch names into name; // 获取
exit when names%notfound;
dbms_output.put_line(name);
end loop;
close names; // 关闭
end;
优点:
- 较高的执行效率。存储过程是预编译,在首次运行一个存储过程时,查询优化器会对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的sql语句每次运行都需要预编译和优化,所以速度就要慢一些。
- 可以复用、修改。
- 降低网络流量。存储过程编译好会放在数据库,调用时根据名称调用,不需要传输sql语句。
- 可以进行权限控制。