目录
mysql存储过程
-----------------
定义新的终止符
,*****不要带空格*****
mysql>delimiter //
创建存储过程
mysql>CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM users; -- into 是赋值方式之一
END
//
查看存储过程的状态
mysql>show procedure status //
mysql>-- 查看指定存储过程创建语句
mysql>show create procedure simpleproc ;
mysql>-- 调用存储过程,@a在命令中定义变量
mysql>call simpleproc(@a)
定义加法存储过程,set赋值语句 :=
mysql>create procedure sp_add(in a int,in b int, out c int)
begin
set c := a + b ;
end
//
java访问存储过程
--------------------------
package com.it18zhang.jdbcdemo.test;
import org.junit.Test;
import java.sql.*;
/**
* 测试基本操作
*/
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/big4";
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/big4";
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);
}
mysql函数
------------------
1.函数和存储过程相似，只是多了返回值声明.
2.创建函数
mysql>create function sf_add(a int ,b int) returns int
begin
return a + b ;
end
//
3.显式创建的函数
mysql>show function status --
mysql>show function status like '%add%' --
mysql>select sf_add(1,2) --
4.java调用函数
@Test
public void testFunction() throws Exception {
long start = System.currentTimeMillis();
//创建连接
String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/big4";
String username = "root";
String password = "root";
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url, username, password);
//关闭自动提交
conn.setAutoCommit(false);
//创建可调用语句，调用存储过程
CallableStatement cst = conn.prepareCall("{? = call sf_add(?,?)}");
cst.setInt(2,100);
cst.setInt(3,200);
cst.registerOutParameter(1,Types.INTEGER);
//注册输出参数类型
cst.execute();
System.out.println(cst.getInt(1));
conn.commit();
conn.close();
System.out.println(System.currentTimeMillis() - start);
}
149
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
