存储过程一直没仔细学，今天为了完成实验，老老实实看了书，在网上看了不少东西，想起来简单，实现起来不容易啊，经过一个多小时的攻坚克难，总算实现了老师的要求，把完美的命令过程贴在下面，用于以后的改进
问题描述：
现有三张表，教职工，转换，论文，教职工记录学院教职工的基本信息，这里要用的是姓名和所属系别，转换是教职工中文名和英文名的转换关系，论文是教职工所发表的论文信息，其中的作者数据可能是中文名也可能是英文名，要实现的是写一个存储过程实现以系中心为参数，查找各系中心发表的论文数。
************
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL connection id
is
121
Server version:
5.5
.
16
MySQL Community Server (GPL)
Copyright (c)
2000
,
2011
, Oracle
and/or
its affiliates.
All
rights reserved.
Oracle
is
a registered trademark
of
Oracle Corporation
and/or
its
affiliates. Other names may be trademarks
of
their respective
owners.
Type
'
help;
'
or
'
\h
'
for
help. Type
'
\c
'
to
clear the
current
input statement.
mysql
>
use
sstest;
Database
changed
mysql
>
desc
教职工;
+
--
----------+--------------+------+-----+---------+----------------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
--
----------+--------------+------+-----+---------+----------------+
|
教职工ID
|
int
(
11
)
|
NO
|
PRI
|
NULL
|
auto_increment
|
|
工号
|
mediumint(
9
)
|
YES
|
|
NULL
|
|
|
姓名
|
varchar
(
30
)
|
NO
|
|
NULL
|
|
|
性别
|
varchar
(
5
)
|
NO
|
|
NULL
|
|
|
系中心
|
varchar
(
40
)
|
YES
|
|
NULL
|
|
|
职务
|
varchar
(
30
)
|
YES
|
|
NULL
|
|
|
职称
|
varchar
(
30
)
|
YES
|
|
NULL
|
|
|
退休前职务
|
varchar
(
30
)
|
NO
|
|
NULL
|
|
|
调离前职务
|
varchar
(
50
)
|
YES
|
|
NULL
|
|
|
调离前所属
|
varchar
(
50
)
|
YES
|
|
NULL
|
|
|
固定电话
|
varchar
(
11
)
|
YES
|
|
NULL
|
|
|
移动电话
|
int
(
11
)
|
YES
|
|
NULL
|
|
|
出生日期
|
date
|
YES
|
|
NULL
|
|
|
电子邮件
|
varchar
(
30
)
|
YES
|
|
NULL
|
|
|
备注
|
varchar
(
50
)
|
YES
|
|
NULL
|
|
+
--
----------+--------------+------+-----+---------+----------------+
15
rows
in
set
(
0.04
sec)
mysql
>
desc
转换;
+
--
------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
--
------+-------------+------+-----+---------+-------+
|
英文名
|
varchar
(
30
)
|
YES
|
|
NULL
|
|
|
中文名
|
varchar
(
30
)
|
YES
|
|
NULL
|
|
+
--
------+-------------+------+-----+---------+-------+
2
rows
in
set
(
0.00
sec)
mysql
>
desc
论文;
+
--
------+--------------+------+-----+---------+----------------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
--
------+--------------+------+-----+---------+----------------+
|
成果ID
|
int
(
11
)
|
NO
|
PRI
|
NULL
|
auto_increment
|
|
作者
|
varchar
(
100
)
|
NO
|
|
NULL
|
|
|
题名
|
varchar
(
160
)
|
YES
|
|
NULL
|
|
|
刊名
|
varchar
(
160
)
|
YES
|
|
NULL
|
|
+
--
------+--------------+------+-----+---------+----------------+
4
rows
in
set
(
0.00
sec)
mysql
>
delimiter
//
mysql
>
drop
procedure
if
exists
`get_count_of_paper_of_major`;
//
Query OK,
0
rows affected (
0.04
sec)
mysql
>
create
procedure
get_count_of_paper_of_major(
in
major_name
varchar
(
40
), out count_of_papers
int
)
->
reads sql data
->
begin
->
declare
cnt
int
default
0
;
->
declare
tmp
int
default
0
;
->
declare
tmp_name
varchar
(
30
)
default
"";
->
declare
mycursor
cursor
for
select
英文名
from
教职工,转换
where
教职工.系中心
=
major_name
and
转换.中文名
=
教职工.姓名;
->
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'
02000
'
SET
tmp_name
=
NULL
;
->
->
open
mycursor;
->
fetch
mycursor
into
tmp_name;
->
while
(tmp_name
is
not
null
)
->
do
->
select
count
(
*
)
from
论文
where
作者
=
tmp_name
into
tmp;
->
set
cnt
=
cnt
+
tmp;
->
set
tmp
=
0
;
->
fetch
mycursor
into
tmp_name;
->
end
while
;
->
close
mycursor;
->
set
count_of_papers
=
cnt;
->
end
->
//
Query OK,
0
rows affected (
0.00
sec)
mysql
>
delimiter ;
mysql
>
call get_count_of_paper_of_major(
'
软件工程系
'
,
@count_of_papers
);
Query OK,
0
rows affected,
1
warning (
0.00
sec)
mysql
>
select
@count_of_papers
;
+
--
----------------+
|
@count_of_papers
|
+
--
----------------+
|
21
|
+
--
----------------+
1
row
in
set
(
0.00
sec)
mysql
>
call get_count_of_paper_of_major(
'
数字媒体技术系
'
,
@count_of_papers
);
Query OK,
0
rows affected,
1
warning (
0.00
sec)
mysql
>
select
@count_of_papers
;
+
--
----------------+
|
@count_of_papers
|
+
--
----------------+
|
0
|
+
--
----------------+
1
row
in
set
(
0.00
sec)
mysql
>
call get_count_of_paper_of_major(
'
信息安全系
'
,
@count_of_papers
);
Query OK,
0
rows affected,
1
warning (
0.00
sec)
mysql
>
select
@count_of_papers
;
+
--
----------------+
|
@count_of_papers
|
+
--
----------------+
|
2
|
+
--
----------------+
1
row
in
set
(
0.00
sec)
mysql
>
对比以前写的语句，恩，好像没有问题
不得不说MySQL在存储过程和触发器还是和MS SQL Server，DB2，Oracle的差距还很大，很多的功能就MySQL没有实现，实现了的也相对比较麻烦，嗯，不管怎么样，希望MySQL越做越好，以开源战胜商业！加油
