存储过程虽然经常听到，但是我遇到的项目还没有使用过，比较郁闷，只能选择自己入门了，也顺便与大家分享，希望大家也能如愿，呵呵。 首先我用的环境是window xpmysql5，这个大家应该都有的，没有的安装一下吧。好装好了，不要忘记将mysql.exe的路径目录放入
存储过程虽然经常听到，但是我遇到的项目还没有使用过，比较郁闷，只能选择自己入门了，也顺便与大家分享，希望大家也能如愿，呵呵。
首先我用的环境是window xp+mysql5，这个大家应该都有的，没有的安装一下吧。好装好了，不要忘记将mysql.exe的路径目录放入环境变量path中，这样在cmd里输入以下命令就可以进入mysql的命令行模式：mysql -u root -p -> mysql密码即可。
mysql> delimiter // mysql> create procedure hello() -> begin -> select 'It is not a HelloWorld'; -> end -> // Query OK, 0 rows affected (0.01 sec)
mysql> call hello()// +------------------------+ | It is not a HelloWorld | +------------------------+ | It is not a HelloWorld | +------------------------+ 1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS `userinfo`.`mapping`; -> CREATE TABLE `userinfo`.`mapping` ( -> `cFieldID` smallint(5) unsigned NOT NULL, -> `cFieldName` varchar(30) NOT NULL, -> PRIMARY KEY (`cFieldID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -> // Query OK, 0 rows affected (0.14 sec)想表中load值进空表 文件为：
filed.txt
1,MarketValue 2,P/L 3,EName 4,Nominal 5,Chg
mysql> load data infile 'd:\\field.txt' into table mapping -> fields terminated by ',' lines terminated by '\r\n' // Query OK, 5 rows affected (0.02 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select *from mapping// +----------+-------------+ | cFieldID | cFieldName | +----------+-------------+ | 1 | MarketValue | | 2 | P/L | | 3 | EName | | 4 | Nominal | | 5 | Chg | +----------+-------------+ 5 rows in set (0.02 sec)现在简历一个向mapping中插入一条记录并返回记录的总和
mysql> drop procedure if exists mappingProc; -> create procedure mappingProc(out cnt int) -> begin -> declare maxid int; -> select max(cFieldID)+1 into maxid from mapping; -> insert into mapping(cFieldID,cFieldName) values(maxid,'hello'); -> select count(cFieldID) into cnt from mapping; -> end -> //查找mappingProc():
mysql> call mappingProc(@a)// mysql> select @a// +------+ | @a | +------+ | 6 | +------+ mysql> select * from mapping// +----------+-------------+ | cFieldID | cFieldName | +----------+-------------+ | 1 | MarketValue | | 2 | P/L | | 3 | EName | | 4 | Nominal | | 5 | Chg | | 6 | hello | +----------+-------------+下面是java代码用来调用MySQL的存储过程：
package kissJava.sql;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class SQLUtils {
String url = "jdbc:mysql://127.0.0.1:3306/userInfo";
String userName = "root";
String password = "zhui007";
public Connection getConnection() {
Connection con=null;
try{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
con = DriverManager.getConnection(url, this.userName, this.password);
}catch(SQLException sw){
}
return con;
}
public void testProc(){
Connection conn = getConnection();
CallableStatement stmt = null;
try{
stmt = conn.prepareCall("{call mappingProc(?)}");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.execute();
int i= stmt.getInt(1);
System.out.println("count = " + i);
}catch(Exception e){
System.out.println("hahad = "+e.toString());
}finally{
try {
stmt.close();
conn.close();
}catch (Exception ex) {
System.out.println("ex : "+ ex.getMessage());
}
}
}
public static void main(String[] args) {
new SQLUtils().testProc();
}
}