数据存储过程

存储过程：procedure：就是一段可执行程序。这个程序运行在数据中。
Begin = {
End = }
If
Else
也可以接收参数。

定义一个procedure:
DELIMITER //

CREATE PROCEDURE proc1()
BEGIN ――{
SELECT * FROM users;
END // ―― }

DELIMITER ;


CALL proc1();


定义一个接收参数的:
DELIMITER $$
CREATE PROCEDURE proc2(IN _id VARCHAR(32),IN _nm VARCHAR(30))
BEGIN
INSERT INTO users(id,NAME) VALUES(_id,_nm);
END $$

CALL proc2('U003','Rose');


定义一个返回类型的参数：
DELIMITER $$
CREATE PROCEDURE proc3(IN _id VARCHAR(32),IN _nm VARCHAR(30),OUT _size INT)
BEGIN
INSERT INTO users(id,NAME) VALUES(_id,_nm);
SELECT COUNT(1) INTO _size FROM users;
IF(_size=10) THEN
INSERT INTO users VALUES('U0000','ddd','ddddd');
END IF;
END $$


CALL proc3('U002882','Rose',@wj);

SELECT @wj;

在Java代码中调用Procedure

public class ProceDemo {
	@Test
	public void proc1() throws Exception{
		//dbutils不提供调用存储过程的能力
		Connection con = DataSourceUtils.getDatasSource().getConnection();
		//获取调用过程的对象
		CallableStatement cs = 
				con.prepareCall("{call proc1()}");
		//执行
		boolean boo = cs.execute();//如果返回true,指最后一句执行的是select语句
		if(boo){
			ResultSet rs = cs.getResultSet();
			while(rs.next()){
				System.err.println(rs.getString("name"));
			}
		}
		con.close();
	}
	
	@Test
	public void proc2() throws Exception{
		Connection con = DataSourceUtils.getDatasSource().getConnection();
		//获取调用过程的对象
		CallableStatement cs = 
				con.prepareCall("{call proc2(?,?)}");
		cs.setString(1,"UAAA");
		cs.setString(2, "王健");
		boolean boo = cs.execute();
		System.err.println(boo);
		con.close();
	}
	
	@Test
	public void proc3() throws Exception{
		Connection con = DataSourceUtils.getDatasSource().getConnection();
		//获取调用过程的对象
		CallableStatement cs = 
				con.prepareCall("{call proc5(?,?,?)}");
		cs.setString(1,"UBDDB");
		cs.setString(2, "张三");
		cs.registerOutParameter(3,Types.INTEGER);//--int,
		boolean boo = cs.execute();
		System.err.println(">>:"+boo);//true
		//从call中获取返回的值
		int size = cs.getInt(3);
		System.err.println("行数:"+size);
		if(boo){
			ResultSet rs= cs.getResultSet();
			rs.next();
			int ss = rs.getInt(1);
			System.err.println("sss:"+ss);
		}
		con.close();
	}
	
	@Test
	public void proc6() throws Exception{
		Connection con = DataSourceUtils.getDatasSource().getConnection();
		//获取调用过程的对象
		CallableStatement cs = 
				con.prepareCall("{call proc6(?,?,?,?)}");
		cs.setString(1,"UBafadsB");
		cs.setString(2, "张三");
		cs.registerOutParameter(3,Types.INTEGER);//--int,
		cs.registerOutParameter(4, Types.INTEGER);
		boolean boo = cs.execute();
		System.err.println(">>:"+boo);//faluse
		//从call中获取返回的值
		int size = cs.getInt(3);
		int _s = cs.getInt(4);
		System.err.println("行数:"+size+","+_s);
		con.close();
	}
}