mysql存储过程示例

mysql > delimiter //
mysql > create procedure hello()
-> begin
-> select ' It is not a HelloWorld ';
-> end
-> //
Query OK, 0 rows affected ( 0.01 sec) 其中“delimiter //”的意思是定义结束符号为“//”，以此来替换mysql中的“；”
在mysql中查询上面的过程hello():

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)

向table mapping中插入一些初始化的数据：

mysql > load data infile ' d:\\userInfo\\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();
}
}

在到MySQL中查询可看到插入一条新的记录