存储过程虽然经常听到，但是我遇到的项目还没有使用过，比较郁闷，只能选择自己入门了，也顺便与大家分享，希望大家也能如愿，呵呵。 首先我用的环境是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)
delimiter // 是将结束符由默认的；换成了//，如果不这样select......语句就会导致上面的代码出错，不信你可以试一下哈，呵呵。接着在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)
想表中load值进空表  文件为：filed.txt
1,marketvalue2,p/l3,ename4,nominal5,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: 0mysql> 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中查询可看到插入一条新的记录