存储过程
存储过程参数
前期数据准备
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();