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

记录篇: 专栏收录该内容
211 篇文章 4 订阅

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

字段名数据类型主键外键非空唯一自增
idint(10)
namevarchar(50)
glassvarchar(50)

表2:

idnameglass
1xiaomingglass1
2xiaojunglass2

第一步:创建一个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语句如下:

idnameglass
1xiaomingglass 1
2xiaojunglass 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)
  • 2
    点赞
  • 0
    评论
  • 9
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:撸撸猫 设计师:马嘣嘣 返回首页

打赏作者

Hudie.

不要打赏!不要打赏!不要打赏!

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值