mysql> delimiter //
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
mysql
>
create
procedure hello()
->
begin
->
select
'
It is not a HelloWorld
';
->
end
->
//
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
Query OK,
0 rows affected (
0.01 sec)
其中“delimiter //”的意思是定义结束符号为“//”,以此来替换mysql中的“;”
在mysql中查询上面的过程hello():
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
mysql
> call hello()
//
+
--
----------------------+
| It
is
not a HelloWorld
|
+
--
----------------------+
| It
is
not a HelloWorld
|
+
--
----------------------+
1 row
in
set (
0.00 sec)
建立一个简单的测试用表:
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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;
->
//
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
Query OK,
0 rows affected (
0.14 sec)
向table mapping中插入一些初始化的数据:
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
mysql
>
load data infile
'
d:\\userInfo\\field.txt
'
into
table mapping
-> fields terminated
by
'
,
' lines terminated
by
'
\r\n
'
//
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
Query OK,
5 rows affected (
0.02 sec)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
Records:
5 Deleted:
0 Skipped:
0 Warnings:
0
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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中插入一条记录并返回记录的总和
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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():
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
mysql
> call mappingProc(
@a)
//
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
mysql
>
select
@a
//
+
--
----+
|
@a
|
+
--
----+
|
6
|
+
--
----+
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
mysql
>
select
*
from mapping
//
+
--
--------+-------------+
| cFieldID
| cFieldName
|
+
--
--------+-------------+
|
1
| MarketValue
|
|
2
| P
/L
|
|
3
| EName
|
|
4
| Nominal
|
|
5
| Chg
|
|
6
| hello
|
+
--
--------+-------------+
下面是java代码用来调用MySQL的存储过程:
在到MySQL中查询可看到插入一条新的记录