mysql使用光标的例子_MySQL使用存储过程，光标的使用的简单示例

************

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 121Server version:5.5.16MySQL Community Server (GPL)

Copyright (c)2000, 2011, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> usesstest;Databasechanged

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.04sec)

mysql> desc转换;+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| 英文名 | varchar(30) | YES | | NULL | |

| 中文名 | varchar(30) | YES | | NULL | |

+--------+-------------+------+-----+---------+-------+

2 rows in set (0.00sec)

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.00sec)

mysql> delimiter //mysql> drop procedure if exists `get_count_of_paper_of_major`;//Query OK,0 rows affected (0.04sec)

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;->

-> openmycursor;-> fetch mycursor intotmp_name;-> while(tmp_name is not null)->do-> select count(*) from 论文 where 作者=tmp_name intotmp;-> set cnt=cnt+tmp;-> set tmp=0;-> fetch mycursor intotmp_name;-> end while;-> closemycursor;-> set count_of_papers =cnt;-> end

-> //Query OK,0 rows affected (0.00sec)

mysql>delimiter ;

mysql> call get_count_of_paper_of_major('软件工程系', @count_of_papers);

Query OK,0 rows affected, 1 warning (0.00sec)

mysql> select @count_of_papers;+------------------+

| @count_of_papers |

+------------------+

| 21 |

+------------------+

1 row in set (0.00sec)

mysql> call get_count_of_paper_of_major('数字媒体技术系', @count_of_papers);

Query OK,0 rows affected, 1 warning (0.00sec)

mysql> select @count_of_papers;+------------------+

| @count_of_papers |

+------------------+

| 0 |

+------------------+

1 row in set (0.00sec)

mysql> call get_count_of_paper_of_major('信息安全系', @count_of_papers);

Query OK,0 rows affected, 1 warning (0.00sec)

mysql> select @count_of_papers;+------------------+

| @count_of_papers |

+------------------+

| 2 |

+------------------+

1 row in set (0.00sec)

mysql>