mysql jdbc 创建存储过程_mysql JDBC 调用存储过程

与oracle不同,mysql不支持匿名块,如果需要写过程语句,则必需定义存储过程.如果你会oracle的存储过程,那么mysql的存储过程也很简单了.

一.创建存储过程

create procedure sp_name()

begin

.........

end

二.调用存储过程

1.基本语法：call sp_name()

注意：存储过程名称后面必需加括号,哪怕该存储过程没有参数传递

三.删除存储过程

1.基本语法：

drop procedure sp_name//

2.注意事项

(1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

四.其他常用命令

1.show procedure status

显示数据库中所有存储的存储过程基本信息,包含所属数据库,存储过程名称,创建时间等

2.show create procedure sp_name

显示某一个MySQL存储过程的详细信息

下面来几个实例吧：CREATE DATABASE `test`;

USE `test`;

CREATE TABLE `fruit` (

`id` char(36) NOTNULL COMMENT '标识',

`name` varchar(12) NOTNULL COMMENT '名称',

`price` decimal(8,2) NOTNULL COMMENT '单价',

`address` varchar(300) DEFAULTNULL COMMENT '产地',

PRIMARYKEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='水果表';

insertinto `fruit`(`id`,`name`,`price`,`address`)

values

('27640c30-8df5-4cf2-916e-c28e0b2b1b52','山竹','24.45','马来西亚'),

('46ac8392-9922-4593-89a3-517a9e516733','菠萝','19.41','巴西'),

('63061a9f-3a0e-4140-98e0-8b1e13e4eab3','哈密瓜','17.77','中国'),

('7ef0c286-b8b1-4e1e-9a8a-36bce703cf18','鳄梨','30.80','墨西哥'),

('a1cf5251-9311-4c7f-be10-3532d8c16291','树莓','117.50','瑞士'),

('c397aed0-a39a-49c5-91ee-7fc0579ddb20','莲雾','77.33','印度尼西亚'),

('e8068fa1-a8e7-4025-89e2-36c1d5d23c74','榴莲','16.50','泰国');

创建存储过程：

1 没有任何输入和输出参数的存储过程

DELIMITER $$ //这里可能有些看不懂,没关系,文章最后附录有详细说明.

CREATE PROCEDURE noParam()

BEGIN

SELECT AVG(price) AS priceAvg FROM fruit;

END$$

DELIMITER ;

JDBC代码:/**

* 没有任何输入和输出参数的存储过程

*

* @author GaoHuanjie

*/

publicclass NoParam {

publicstaticvoid main(String args[]) throws SQLException {

Connection connection = LinkDB.getMySqlConnection();

String proStr = "{call noParam}";

CallableStatement callableStatement = connection.prepareCall(proStr);

callableStatement.execute();

ResultSet resultSet = callableStatement.getResultSet();

while (resultSet.next()) {

System.out.println("产品的平均价格是：" + resultSet.getDouble("priceAvg") + "元");

}

LinkDB.close(connection, callableStatement, resultSet);

}

}

2、只有两个输入参数的存储过程

DELIMITER $$

CREATE PROCEDURE inTwoParam(IN fruitName VARCHAR(12),IN fruitPrice DECIMAL(9,2))

BEGIN

//注意：CONCAT('%',fruitName,'%')不能为'%'+fruitName+'%'

SELECT * FROM fruit WHERE NAME LIKE CONCAT('%',fruitName,'%') AND price < fruitPrice;

END$$

DELIMITER ;/**

* 只有两个输入参数的存储过程

*

* @author GaoHuanjie

*/

publicclass InTwoParam {

publicstaticvoid main(String args[]) throws SQLException {

Connection connection = LinkDB.getMySqlConnection();

String procStr = "{call inTwoParam(?,?)}";

CallableStatement callableStatement = connection.prepareCall(procStr);

callableStatement.setString(1, "莲");

callableStatement.setDouble(2, 88.88);//对DECIMAL类型的属性设值要使用setDouble办法.

callableStatement.execute();

ResultSet resultSet = callableStatement.getResultSet();

System.out.println("名称包括‘莲’字且价格小于88.88元的水果有：");

while (resultSet.next()) {

System.err.println("名称：" + resultSet.getString("name") +"、价格：" + resultSet.getDouble("price") + "元"+"、产地：" + resultSet.getString("address"));

}

LinkDB.close(connection, callableStatement, resultSet);

}

}

3、只有两个输出参数的存储过程

DELIMITER $$

CREATE PROCEDURE outTwoParam(OUT fruitName VARCHAR(12),OUT fruitPrice DECIMAL(5,3) )

BEGIN

SELECT name INTO fruitName FROM fruit WHERE name='莲雾';

SELECT price INTO fruitPrice FROM fruit WHERE NAME='莲雾';

END $$

DELIMITER ;

注意：上面两条查询语句不能合成一个SQL语句——SELECT NAME INTO fruitName, price INTO fruitPrice FROM fruit WHERE NAME='莲雾';/**

* 只有两个输出参数的存储过程

*

* @author GaoHuanjie

*/

publicclass OutTwoParam {

publicstaticvoid main(String args[]) throws SQLException {

Connection connection = LinkDB.getMySqlConnection();

String proStr = "{call outTwoParam(?,?)}";

CallableStatement callableStatement = connection.prepareCall(proStr);

callableStatement.registerOutParameter(1, Types.VARCHAR);

callableStatement.registerOutParameter(2, Types.DECIMAL);

callableStatement.execute();

String fruitName = callableStatement.getString(1);

double fruitPrice = callableStatement.getDouble(2);// 获取DECIMAL类型的属性要使用getDouble办法.

System.out.println("水果名称：" + fruitName +"、水果价格：" + fruitPrice + "元");

LinkDB.close(connection, callableStatement, null);

}

}

4、含有一个输入参数和一个输出参数的存储过程

DELIMITER $$

CREATE PROCEDURE inOneParamAndOutOneParam(IN fruitName VARCHAR(12),OUT fruitPrice DECIMAL(7,3))

BEGIN

SELECT price FROM fruit WHERE NAME=fruitName INTO fruitPrice;

END $$

DELIMITER ;/**

* 含有一个输入参数和一个输出参数的存储过程

*

* @author GaoHuanjie

*/

publicclass InOneParamAndOutOneParam {

publicstaticvoid main(String args[]) throws SQLException {

Connection connection=LinkDB.getMySqlConnection();

CallableStatement callableStatement=null;

String procStr="{call inOneParamAndOutOneParam(?,?)}";

callableStatement=connection.prepareCall(procStr);

String fruitName = "莲雾";

callableStatement.setString(1, fruitName);

callableStatement.registerOutParameter(2, Types.DECIMAL);

callableStatement.execute();

double fruitPrice=callableStatement.getDouble(2);//获取DECIMAL类型的属性要使用getDouble办法.

System.out.println(fruitName+"的价格为："+fruitPrice+"元");

LinkDB.close(connection, callableStatement, null);

}

}

5、输入参数即输出参数的存储过程

DELIMITER $$

CREATE PROCEDURE inOneParamISOutOneParam(INOUT fruitName VARCHAR(12))

BEGIN

SELECT NAME INTO fruitName FROM fruit WHERE NAME LIKE CONCAT('%', fruitName, '%') LIMIT 0,1;

END $$

DELIMITER ;

或

DELIMITER $$

CREATE PROCEDURE inOneParamISOutOneParam(INOUT fruitName VARCHAR(12))

BEGIN

SELECT NAME FROM fruit WHERE NAME LIKE CONCAT('%', fruitName, '%') LIMIT 0,1 INTO fruitName;

END $$

DELIMITER ;

-- 注意上面查询语句不能这样写：

-- SELECT NAME FROM fruit WHERE NAME LIKE CONCAT('%', fruitName, '%') INTO fruitName LIMIT 0,1;

-- 注意：对比3、4和5Java文件内创建存储过程脚本中“INTO”关键字的位置你必定深有收获/**

* 输入参数即输出参数的存储过程

*

* @author GaoHuanjie

*/

publicclass InOneParamISOutOneParam {

publicstaticvoid main(String args[]) throws SQLException {

Connection con = LinkDB.getMySqlConnection();

CallableStatement callableStatement = null;

String procStr = "{call inOneParamISOutOneParam(?)}";

callableStatement = con.prepareCall(procStr);

callableStatement.setString(1, "莲");

callableStatement.registerOutParameter(1, Types.VARCHAR);

callableStatement.execute();

String fruitName = callableStatement.getString(1);

System.out.println("表中水果名称含有‘莲’字的一中水果的名称是：" + fruitName);

LinkDB.close(con, callableStatement, null);

}

}

以上代码展示了mysql存储过程以及在jdbc中调用的过程.

如果必要在mysql中调用

/*定义delimiter为 // */

delimiter //

CREATE procedure sp_add3(a int, b int,out c int)

begin

set c=a+ b;

end//

在mysql中调用存储过程

/* 改回默认值 ; */

delimiter ;

//

call sp_add3(1,2,@c);

select@c;

附录:

MySql中 delimiter 详解

其实就是告诉MySQL解释器,该段命令是否已经结束了,mysql是否可以执行了.默认情况下,delimiter是分号;.在命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令.

如输入下面的语句

mysql> select * from test_table;

然后回车,那么MySQL将立即执行该语句.

但有时候,不希望MySQL这么做.在为可能输入较多的语句,且语句中包括有分号.

如试图在命令行客户端中输入如下语句

mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT)

mysql> RETURNS varchar(255)

mysql> BEGIN

mysql> IF ISNULL(S) THEN

mysql> RETURN '';

mysql> ELSEIF N<15 THEN

mysql> RETURN LEFT(S, N);

mysql> ELSE

mysql> IF CHAR_LENGTH(S) <=N THEN

mysql> RETURN S;

mysql> ELSE

mysql> RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5));

mysql> END IF;

mysql> END IF;

mysql> END;

默认情况下,不可能比及用户把这些语句全部输入完之后,再执行整段语句.

因为mysql一遇到分号,它就要自动执行.

即,在语句RETURN '';时,mysql解释器就要执行了.

这种情况下,就必要事先把delimiter换成其它符号,如//或$$.