存储过程&函数-sql

以一下例子以mysql为例：

1. 存储过程
2. 函数

函数

函数创建：

需要参入参数user_id是int

BEGIN
	DECLARE ss varchar(100);
	set ss='aaa';
	select name into ss 
	from testuser 
	where id=user_id;

	RETURN ss;
END

java调用：

  Connection con = DriverManager.getConnection(url,user,password);
  CallableStatement callableStatement =  con.prepareCall("{? = call testfunction(?)}");
  callableStatement.registerOutParameter(1, Types.VARCHAR);
  callableStatement.setInt(2,2);
  callableStatement.execute();
  System.out.println("-----------"+callableStatement.getString(1));

存储过程

存储过程创建：

需要参入参数user_id是int

BEGIN
	select id,name,remarks
	from testuser 
	where id<user_id;
END

java调用：

    Connection con = DriverManager.getConnection(url,user,password);
    CallableStatement callableStatement =  con.prepareCall("{ call testProcedure(?)}");
    callableStatement.setInt(1,5);
    ResultSet resultSet = callableStatement.executeQuery();
    ResultSetMetaData rsm = resultSet.getMetaData();
    while (resultSet.next()){
           Integer id = resultSet.getInt("id");
           String name = resultSet.getString("name");
           String remarks= resultSet.getString("remarks");
            System.out.println(id+"  "+name+" "+remarks);
     }