Mysql存储过程

存储过程

存储过程参数

前期数据准备

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();

 

转载于:https://www.cnblogs.com/haaron/p/5430709.html