MySQL存儲過程學習及Java調用存儲過程

存儲過程雖然經常聽到，但是我遇到的項目還沒有使用過，比較郁悶，只能選擇自己入門了，也順便與大家分享，希望大家也能如願，呵呵。

首先我用的環境是window xp+mysql5，這個大家應該都有的，沒有的安裝一下吧。好裝好了，不要忘記將mysql.exe的路徑目錄放入環境變量path中，這樣在cmd裡輸入以下命令就可以進入mysql的命令行模式：mysql -u root -p -> mysql密碼即可。

1. mysql> delimiter //
2. mysql> create procedure hello()
3. -> begin
4. -> select 'It is not a HelloWorld';
5. -> end
6. -> //
7. Query OK, 0 rows affected (0.01 sec)

delimiter // 是將結束符由默認的；換成了//，如果不這樣select......語句就會導致上面的代碼出錯，不信你可以試一下哈，呵呵。接著在mysql中查詢上面的過程hello():

1. mysql> call hello()//
2. +------------------------+
3. | It is not a HelloWorld |
4. +------------------------+
5. | It is not a HelloWorld |
6. +------------------------+
7. 1 row in set (0.00 sec)

我們再來一個好點的入門小實例吧

1. mysql> DROP TABLE IF EXISTS `userinfo`.`mapping`;
2. -> CREATE TABLE `userinfo`.`mapping` (
3. -> `cFieldID` smallint(5) unsigned NOT NULL,
4. -> `cFieldName` varchar(30) NOT NULL,
5. -> PRIMARY KEY (`cFieldID`)
6. -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
7. -> //
8. Query OK, 0 rows affected (0.14 sec)

想表中load值進空表 文件為：

filed.txt

1. 1,MarketValue
2. 2,P/L
3. 3,EName
4. 4,Nominal
5. 5,Chg

1. mysql> load data infile 'd:\\field.txt' into table mapping
2. -> fields terminated by ',' lines terminated by '\r\n' //
3. Query OK, 5 rows affected (0.02 sec)
4. Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
5. mysql> select *from mapping//
6. +----------+-------------+
7. | cFieldID | cFieldName |
8. +----------+-------------+
9. | 1 | MarketValue |
10. | 2 | P/L |
11. | 3 | EName |
12. | 4 | Nominal |
13. | 5 | Chg |
14. +----------+-------------+
15. 5 rows in set (0.02 sec)

現在簡歷一個向mapping中插入一條記錄並返回記錄的總和

1. mysql> drop procedure if exists mappingProc;
2. -> create procedure mappingProc(out cnt int)
3. -> begin
4. -> declare maxid int;
5. -> select max(cFieldID)+1 into maxid from mapping;
6. -> insert into mapping(cFieldID,cFieldName) values(maxid,'hello');
7. -> select count(cFieldID) into cnt from mapping;
8. -> end
9. -> //

查找mappingProc():

1. mysql> call mappingProc(@a)//
2. mysql> select @a//
3. +------+
4. | @a |
5. +------+
6. | 6 |
7. +------+
8. mysql> select * from mapping//
9. +----------+-------------+
10. | cFieldID | cFieldName |
11. +----------+-------------+
12. | 1 | MarketValue |
13. | 2 | P/L |
14. | 3 | EName |
15. | 4 | Nominal |
16. | 5 | Chg |
17. | 6 | hello |
18. +----------+-------------+