存储过程
存储过程参数
前期数据准备
SET FOREIGN_KEY_CHECKS=0;
-- ---------------------------- -- Table structure for `tb_user` -- ---------------------------- DROP TABLE IF EXISTS `tb_user`; CREATE TABLE `tb_user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `password` varchar(20) NOT NULL, `file` blob, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of tb_user -- ---------------------------- INSERT INTO `tb_user` VALUES ('1', 'aaron', '123456', null); INSERT INTO `tb_user` VALUES ('2', 'test', 'test', null);
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT 输出参数:该值可在存储过程内部被改变,并可返回
INOUT 输入输出参数:调用时指定,并且可被改变和返回
IN参数例子
新建存储过程
--根据param 判断需要执行的sql
delimiter//
create procedure sp_name(in param integer) begin if param=1 then select * from tb_user where id=1; else select * from tb_user where id=2; end if; end; // delimiter
调用存储过程
call sp_name(1);
call sp_name(2);
-------------------------------
或者这里调用存储过程使用数据库变量
set @param=1;
call sp_name(@param);
OUT参数例子
drop procedure if exists out_demo;
create procedure out_demo(in param varchar(20),out o int) begin select count(*) into o from tb_user where password=param; select count(*) from tb_user where password=param; end; call out_demo('test',@y); select @y;
INOUT参数例子
drop procedure if exists inout_demo;
create procedure inout_demo(in inputParam varchar(20),inout inoutParam varchar(20)) begin select inputParam; select concat('xyz',inputParam) as inoutParam; set inoutParam=11; end;
例子总结
1) 如果仅仅想把数据传给MySQL存储过程,那就用in类型参数;
2) 如果仅仅从MySQL存储过程返回值,那就用out类型参数;
3) 如果需要把数据传给MySQL存储过程经过计算再传回给我们,那就用inout类型参数。
Java调用存储过程
In参数
private static void inProcedure() {
try { conn = DBConfig.getConnection(); System.out.println("数据库连接成功!"); String sql = "{call in_demo(?,?)}"; CallableStatement statement = conn.prepareCall(sql); statement.setString(1, "xx"); statement.setInt(2, 2); resultSet = statement.executeQuery(); while(resultSet.next()) { System.out.println(resultSet.getInt(1)+"...."+resultSet.getString(2)+"...."+ resultSet.getString(3)+"...."+resultSet.getBlob(4)); } }catch (Exception e) { System.out.println("数据库连接失败!"); e.printStackTrace(); }finally{ try { DBConfig.closeConnection(conn, preparedStatement, resultSet); System.out.println("关闭数据库连接"); } catch (Exception e) { e.printStackTrace(); } } }
Out参数
private static void outProcedure() {
try { conn = DBConfig.getConnection(); conn.setAutoCommit(true); //call out_demo('test',@y); String sql = "call out_demo(?,?)"; CallableStatement statement = conn.prepareCall(sql); statement.setString(1, "test"); statement.registerOutParameter(2, java.sql.Types.INTEGER); statement.execute(); System.out.println(statement.getInt(2)); resultSet = statement.executeQuery(); while(resultSet.next()) { System.out.println(resultSet.getInt(1)); } }catch (Exception e) { System.out.println("数据库连接失败!"); e.printStackTrace(); }finally{ try { DBConfig.closeConnection(conn, preparedStatement, resultSet); System.out.println("关闭数据库连接"); } catch (Exception e) { e.printStackTrace(); } } }
Inout参数
private static void inoutProcedure() {
try { conn = DBConfig.getConnection(); conn.setAutoCommit(true); //call inout_demo('aa',@x); String sql = "call inout_demo(?,?);"; CallableStatement statement = conn.prepareCall(sql); statement.setString(1, "'aa'"); statement.registerOutParameter(2, java.sql.Types.INTEGER); statement.execute(); int i= statement.getInt(2); System.out.println(i); }catch (Exception e) { System.out.println("数据库连接失败!"); e.printStackTrace(); }finally{ try { DBConfig.closeConnection(conn, preparedStatement, resultSet); System.out.println("关闭数据库连接"); } catch (Exception e) { e.printStackTrace(); } } }
存储过程游标的三种循环方式
while ...end while
create procedure sp_while()
begin
declare x int; set x = 0; while x<5 do insert into tb_user(name,password) value('name','pass'); set x=x+1; end while; end;
这是WHILE循环的方式。它跟IF语句相似,使用"SET v = 0;"语句使为了防止一个常见的错误,如果没有初始化,默认变量值为NULL,而NULL和任何值操作结果都为NULL。
loop ... end loop
create procedure sp_loop()
begin
declare z int; set z=0; loop_label:loop update tb_user set name='name2' where id=z; set z=z+1; if z>5 then leave loop_label; end if; end loop; end;
以上是LOOP循环的例子。LOOP循环不需要初始条件,这点和WHILE循环相似,同时它又和REPEAT循环一样也不需要结束条件。
repeat ... end repeat
create procedure sp_repeat()
begin
declare y int; set y=7; repeat update tb_user set name='name1',password='pass1' where id=y; set y=y+1; until y>10 end repeat; end;
这是REPEAT循环的例子,功能和前面WHILE循环一样。区别在于它在执行后检查结果,而WHILE则是执行前检查。类似于do while语句
存储过程游标的使用
使用游标需要遵循下面步骤
1.首先用DECLARE语句声明一个游标
declare cursor_name cursor for select_statement;
上面这条语句就对,我们执行的select语句返回的记录指定了一个游标
2.其次需要使用OPEN语句来打开上面你定义的游标
open cursor_name
3.接下来你可以用FETCH语句来获得下一行数据,并且游标也将移动到对应的记录上(这个就类似java里面的那个iterator)。
fetch cursor_name into variable list;
4.然后最后当我们所需要进行的操作都结束后我们要把游标释放掉。
close cursor_name;
在使用游标时需要注意的是,使用定义一个针对NOT FOUND的条件处理函数(condition handler)来避免出现no data to fetch这样的错误,条件处理函数就是当某种条件产生时所执行的代码,这里但我们游标指到记录的末尾时,便达到NOT FOUND这样条件,这个时候我们希望继续进行后面的操作,所以我们会在下面的代码中看到一个CONTINUE
案例
drop procedure IF EXISTS test_proc;
delimiter // create procedure test_proc() begin -- 声明一个标志done, 用来判断游标是否遍历完成 DECLARE done INT DEFAULT 0; -- 声明一个变量,用来存放从游标中提取的数据 -- 特别注意这里的名字不能与由游标中使用的列明相同,否则得到的数据都是NULL DECLARE tname varchar(50) DEFAULT NULL; DECLARE tpass varchar(50) DEFAULT NULL; -- 声明游标对应的 SQL 语句 DECLARE cur CURSOR FOR select name, password from netingcn_proc_test; -- 在游标循环到最后会将 done 设置为 1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 执行查询 open cur; -- 遍历游标每一行 REPEAT -- 把一行的信息存放在对应的变量中 FETCH cur INTO tname, tpass; if not done then -- 这里就可以使用 tname, tpass 对应的信息了 select tname, tpass; end if; UNTIL done END REPEAT; CLOSE cur; end // delimiter ; -- 执行存储过程 call test_proc();