mysql存储过程和函数

概念

    存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
    存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

    由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

一个简单的procedure

/** mysql命令行中执行，重新定义终止符*/
/** mysql命令行中注释，使用"-- " */
mysql>delimiter //

mysql>-- 创建存储过程
/** 创建存储过程PROCEDURE 名称"simpleproc",OUT 代表输出参数 INT表示类型*/
mysql>CREATE PROCEDURE simpleproc (OUT param1 INT)
		BEGIN
		SELECT COUNT(*) INTO param1 FROM users;		-- into 是赋值方式之一
		END
		//

    这个存储过程就是简单的查看“users”表的行数。

//查看所有的存储过程
mysql>show procedure status //
//查看指定存储过程的创建语句
show create procedure simpleproc //
//调用存储过程，把结果存在变量"@a"中
mysql>call simpleproc(@a)//
//查看上面执行结果,表中有“10000”行数据
mysql>select @a//

定义一个加法存储过程

//求a,b的和赋值给c
mysql> create procedure sp_add(in a int, in b int,out c int)
       begin
       set c:= a+b;
       end
       //

java访问存储过程

@Test
public void testCallProcedure() throws Exception {
	long start = System.currentTimeMillis();
	//创建连接
	String driverClass = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/school";
	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);
}

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/school";
    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);
}

基础知识

1.变量定义

declare age int;关键字“declare”

2.while循环体

while
    ...
end while;

3.if语句

if (boolean语句)
then
    ...

end if;

4.赋值语句

set a := 100;

实战定义批量插入存储过程

CREATE PROCEDURE `batchInsert`(IN `num` int)
BEGIN
	DECLARE name0 VARCHAR(20);
	DECLARE age INT;
	DECLARE i INT DEFAULT 0;
	WHILE i < num do
		SET name0 := CONCAT('tom',i);
		SET age := i % 100;
		INSERT INTO users(name,age) VALUES (name0,age);
		SET i := i + 1 ;
		IF i%1000 = 0
		THEN
			COMMIT;
		END IF;
	END WHILE;
COMMIT;
END