Java MySQL 存储过程 入门

存储过程（SQL）:

create procedure testproc1(in para1 int, in para2 int, out para3 varchar(50), inout para4 decimal(38,12))

label : begin

declare var1, var2 int default 10;
declare var3 varchar(50) default 'LiYuming';
declare var4 decimal(38,12) default 0.0001;
declare var5 double default 10.0;
declare var6 float;

select c5 into var6 from cursor_test3 where c1 = 7174543 and c2 = 0.6143697306;
set var2 = var2 + 1;

case para1
when 1 then insert into test1 values (var1, var3, var4, var5, var6);
else insert into test1 values (var2, var3, var4, var5, var6);
end case;

while para2 > 20 do
insert into test1 values (para2, var3, var4, var5, var6);
set para2 = para2 - 1;
end while;

select * from test1;

set para3 = var3;
set para4 = para4 - var4;

end label

控制台操作：

数据库初始状态：

Java程序（完整）：

package framework;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class ProcTest {


public static void main(String[] args) throws SQLException {

Connection conn = DBConnection.getMySQLConnection("127.0.0.1", "3306", "root", "root");

CallableStatement cstmt = conn.prepareCall("{call testproc1(?, ?, ?, ?)}");
cstmt.setInt(1, 0);
cstmt.setInt(2, 25);
cstmt.registerOutParameter(3, Types.VARCHAR);
cstmt.setBigDecimal(4, new BigDecimal("10.0001"));
cstmt.registerOutParameter(4, Types.DECIMAL);

ResultSet rs = cstmt.executeQuery();

String out1 = cstmt.getString(3);
BigDecimal out2 = cstmt.getBigDecimal(4);

System.out.println(out1);
System.out.println(out2);

while(rs.next()){
System.out.println(rs.getInt(1));
}

rs.close();
cstmt.close();
conn.close();
}
}

package framework;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
//get a connection of MySQL
public static Connection getMySQLConnection(String ip, String port, String userName, String passwd) {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://" + ip + ":" + port + "/cursor";
try {
Class.forName(driver);
return DriverManager.getConnection(url, userName, passwd);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}

运行结果（控制台）：

运行后数据库状态：