存储过程一直没仔细学，今天为了完成实验，老老实实看了书，在网上看了不少东西，想起来简单，实现起来不容易啊，经过一个多小时的攻坚克难，总算实现了老师的要求，把完美的命令过程贴在下面，用于以后的改进

 

问题描述：

　　现有三张表，教职工，转换，论文，教职工记录学院教职工的基本信息，这里要用的是姓名和所属系别，转换是教职工中文名和英文名的转换关系，论文是教职工所发表的论文信息，其中的作者数据可能是中文名也可能是英文名，要实现的是写一个存储过程实现以系中心为参数，查找各系中心发表的论文数。

************ Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 121Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use sstest;Database changedmysql> 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越做越好，以开源战胜商业！加油