msyql>-- 定义新的终止符,*****不要带空格*****
mysql>delimiter //
mysql>-- 创建存储过程
mysql>CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM words;-- into 是赋值方式之一
END
//
mysql>-- 查看存储过程的状态
mysql>show procedure status //
mysql>-- 查看指定存储过程创建语句
mysql>show create procedure simpleproc ;
mysql>-- 调用存储过程,@a在命令中定义变量
mysql>call simpleproc(@a)
mysql>-- 查询存储过程结果
mysql>select @a //
mysql>-- 删除存储过程
mysql>drop procedure simpleproc //
mysql>-- 定义加法存储过程,set赋值语句 :=
mysql>create procedure sp_add(in a int,in b int, out c int)
begin
set c := a + b ;
end
//
java访问存储过程
import org.junit.Test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class TestCRUD {
/**
* 存储过程
*/
@Test
public void testCallableStatement() throws Exception {
long start = System.currentTimeMillis();
//创建连接
String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "root";
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url, username, password);
//关闭自动提交
conn.setAutoCommit(false);
//创建可调用语句,调用存储过程
CallableStatement cst = conn.prepareCall("{call sp_add(?,?,?)}");
cst.setInt(1,2); //绑定参数
cst.setInt(2,3);
//注册输出参数类型
cst.registerOutParameter(3, Types.INTEGER);
cst.execute();
int sum = cst.getInt(3);
System.out.println(sum);
conn.commit();
conn.close();
System.out.println(System.currentTimeMillis() - start);
}
}
百万数据插入,存储过程
1.创建存储过程
mysql>create procedure sp_batchinsert(in n int)
begin
DECLARE name0 varchar(20); -- 定义在begin内部
DECLARE age0 int;
DECLARE i int default 0 ;
while i < n do
set name0 := concat('tom',i) ;
set age0 := i % 100 ;
insert into users(name,age) values(name0,age0);
set i := i + 1 ;
end while ;
end
//
2.java代码
@Test
public void testCallableStatement() throws Exception {
long start = System.currentTimeMillis();
//创建连接
String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "root";
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url, username, password);
//关闭自动提交
conn.setAutoCommit(false);
//创建可调用语句,调用存储过程
CallableStatement cst = conn.prepareCall("{call sp_batchinsert(?)}");
cst.setInt(1,1000000); //绑定参数
//注册输出参数类型
cst.execute();
conn.commit();
conn.close();
System.out.println(System.currentTimeMillis() - start);
}