MySQL存储过程学习及Java调用存储过程
MySQL存储过程学习及Java调用存储过程
存储过程虽然经常听到,但是我遇到的项目还没有使用过,比较郁闷,只能选择自己入门了,也顺便与大家分享,希望大家也能如愿,呵呵。
首先我用的环境是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)
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)
- 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
- -> //
- 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 |
- +----------+-------------+
|
评论暂时关闭