Oracle&&Mysql存储过程简单使用

存储过程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;

优点：

1. 较高的执行效率。存储过程是预编译，在首次运行一个存储过程时，查询优化器会对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的sql语句每次运行都需要预编译和优化，所以速度就要慢一些。
2. 可以复用、修改。
3. 降低网络流量。存储过程编译好会放在数据库，调用时根据名称调用，不需要传输sql语句。
4. 可以进行权限控制。