练习7：创建存储过程和函数

案例：创建一个名称为sch的数据表，表结构如表1所示，将表2中的数据插入sch表中。

字段名	数据类型	主键	外键	非空	唯一	自增
id	int(10)	是	否	是	是	否
name	varchar(50)	否	否	是	否	否
glass	varchar(50)	否	否	是	否	否

表2:

id	name	glass
1	xiaoming	glass1
2	xiaojun	glass2

---

第一步：创建一个sch表，并向sch表中插入表2中的数据，代码如下：

mysql> create table sch
    -> (
    -> id int(10),
    -> name varchar(50),
    -> glass varchar(50)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into sch value(1,'xiaoming','glass 1'),
    -> (2,'xiaojun','glass 2');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc sch;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| glass | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

通过select * from sch来查看插入表格的内容，SQL语句如下：

id	name	glass
1	xiaoming	glass 1
2	xiaojun	glass 2

第二步:创建一个存储函数用来统计表sch中的记录数。

mysql> create function count_sch()
    -> returns int
    -> return (select count(*) from sch);
Query OK, 0 rows affected (0.05 sec)

mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

创建的存储函数名称为count_sch，通过select count_sch()查看函数执行的情况，这个表中只有两条记录，得到的结果也是两条记录，说明函数成功执行。

第三步：创建一个存储过程，通过调用存储函数的方法来获取表sch中的记录数和sch表中id的和。

创建一个存储过程add_id，同时使用前面创建的存储函数返回表sch中的记录数，计算出表中所有的id之和。代码如下：

mysql> delimiter //
mysql> create procedure add_id(out count int)
    -> begin
    -> declare itmp int;
    -> declare cur_id cursor for select id from sch;
    -> declare exit handler for not found close cur_id;
    -> select count_sch() into count;
    -> set @sum=0;
    -> open cur_id;
    -> repeat
    -> fetch cur_id into itmp;
    -> if itmp<10
    -> then set @sum= @sum+itmp;
    -> end if;
    -> until 0 end repeat;
    -> close cur_id;
    -> end //
Query OK, 0 rows affected (0.06 sec)

mysql> select @a,@sum //
+------+------+
| @a   | @sum |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> delimiter ;

表sch中只有两条记录，所有id的和为3，和预想的执行结果完全相同。这个存储过程创建了一个cur_id的光标，使用这个光标来获取每条记录的id，使用repeat循环语句来实现所有id号相加。

---

几个小问题：

1、MySQL存储过程和存储函数有什么区别？

• 本质上都是存储程序。函数只能通过return语句返回单个值或表对象；存储过程不允许执行return，但可以通过out参数返回多个值。
• 函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用。存储过程的限制相对就比较少。
• 函数可以嵌入sql语句中使用，可以在select语句中作为查询语句中的一个部分调用，而存储过程一般是作为一个独立的部分来执行。

2.存储过程中的代码可以改变吗？

• MySQL还不提供对已存在的存储过程代码的修改。如果必须修改存储过程，就必须使用drop语句删除之后再重新编写，或者创建一个新的存储过程。

3.存储过程中可以调用其他存储过程吗？

存储过程包含用户定义的sql语句集合，可以使用call语句调用存储过程，当然在存储过程中也可以使用call语句调用其他存储程序，但是不能使用drop语句删除其他存储过程。

4.存储过程的参数不能与数据表中的字段名相同。

在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开，否则将出现无法预期的结果。

5.存储过程的参数可以使用中文吗？

一般情况下，可能会出现存储过程中传入中文参数的情况，例如，某个存储过程根据用户的名字查找该用户的信息，传入的参数值可能是中文。这时需要在定义存储过程的时候，在后面加上character set gbk，不然调用存储过程中使用中文参数会出错，比如定义userInfo存储过程，代码如下：

create procedure useInfo(In u_name varchar(50) character set gbk, out u_age int)