use etoak;
drop procedure if exists t1;
create procedure t1(in a int,in b int,out d varchar(30))
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
/* select c as sum;*/
select 's' into d;
select d as '哈哈'; -- 输出一列
end;
/*调用存储过程*/
set @p_in = 1;
call t1(10,1,@p_in);
上面我们使用if then条件判断,下面来看使用case when来完成更多的条件:
drop procedure if exists t1;
create procedure t1(in a int,in b int,out c varchar(30))
begin
declare d int;
set d = a+1;
case d
when 1 then insert into student values(null,'dx',11,now());
when 2 then insert into student values(null,'aa',11,now());
else insert into student values(null,'bb',11,now());
end case;
select * from student;
end;
再来看两个循环,一个是while do循环,一个是loop循环:
/*使用while do循环*/
create procedure t1()
begin
declare i int DEFAULT 0;
while i<5 DO
insert into student(name) values(i);
set i=i+1;
end while;
select * from student;
end;
/*使用loop循环*/
drop procedure if exists t1;
create procedure t1()
begin
declare i int DEFAULT 0;
loop_label:LOOP
if i = 3 THEN
set i = i + 1;
ITERATE loop_label; -- iterate相当于java循环里的continue
end if;
insert into student values(null,i,i,now());
set i = i + 1;
if i >= 5 THEN
leave loop_label;
end if;
end loop;
select * from student;
end;
还有比较常用的模糊查询:
/*模糊查询*/
drop procedure if exists t1;
create procedure t1(in a varchar(30),out c varchar(30))
begin
declare d int;
select * from student where name like concat(&#39;%&#39;,a,&#39;%&#39;);
end;
create procedure t1(in a int,in b int,out d int)
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
select c into d;
end;
java中通过jdbc调用:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
public class TestProc {
public static void main(String[] args) throws SQLException {
TestProc tp = new TestProc();
int a = tp.testPro(5, 6);
System.out.println(a); //打印输出值
}
//获取数据库连接
private static DBConnection dbCOnnection=null;
static {
if (null == dbConnection) {
dbCOnnection= new DBConnection();
}
}
//执行存储过程的方法
public int testPro(int a,int b) throws SQLException{
Connection cOnn= null;
CallableStatement stmt = null;
int out = 0;
String sql="";
try {
cOnn= dbConnection.getConnection();
stmt = conn.prepareCall("{call t1(?,?,?) }");
stmt.setInt(1, a);
stmt.setInt(2, b);
stmt.registerOutParameter(3, Types.INTEGER);
stmt.execute();
out = stmt.getInt(3); //这里获取下输出参数
}finally {
dbConnection.close(conn);
dbConnection.close(stmt);
}
return out;
}
}
mybatis中存储过程的调用:
声明接口:
public Map proc(Map map);
xml:
测试:
Map map = new HashMap();
map.put("firstParam",1);
map.put("second", 2);
bi.proc(map);
System.out.println(map.toString());
本文讲述了如何通过代码在Android中更改Recycler视图项的背景颜色。通过在onBindViewHolder方法中设置条件判断,可以实现根据条件改变背景颜色的效果。同时,还介绍了如何修改底部边框颜色以及提供了RecyclerView Fragment layout.xml和项目布局文件的示例代码。 ...
[详细]