存儲過程雖然經常聽到,但是我遇到的項目還沒有使用過,比較郁悶,只能選擇自己入門了,也順便與大家分享,希望大家也能如願,呵呵。
首先我用的環境是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,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 |
- +----------+-------------+