数据库——存储过程及游标

?智能2112杨阳

一、目的与要求：

1、掌握存储过程的工作原理、定义及操作方法

2、掌握函数的工作原理、定义及操作方法

3、掌握游标的工作原理、定义及操作方法

二、内容：

1. 创建存储过程，用来自动统计给定订单号的订单总金额

源码：

delimiter //

create procedure `sum_price`(in s int)

??? begin

??? select SUM(quantity*item_price) from orderitems where o_num=s;

end //;

call sum_price(50010)

//

运行测试结果截图（输入订单号'50010'测试结果）：

?

2.创建存储过程，自动搜索并添加客户及供货商帐号信息到新建的用户信息表。

?①增加用户表信息user

表1?? user表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
id	ID号	int (11)	Y	N	Y	Y	Y
u_id	用户编号	int (11)	N	N	Y	Y	N
pwd	密码	blob	N	N	Y	N	N
remark	注释	varchar (255)	N	N	Y	N	N

源码：

create table user(

??? id int(11) not null unique auto_increment,

??? u_id int(11) not null unique,

??? pwd blob not null,

??? remark varchar(255) not null,

??? primary key(id));

② 创建两个存储过程，分别把客户表的c_id和供货商表s_id的字段自动添加到用户信息表,补充pwd和remark字段。

注：1. 添加客户用非游标实现；2. 添加供货商用游标实现

要求：id字段自动增加，u_id 字段即客户或供货商的编号，pwd字段用AES_ENCRYPT函数加密，密码统一设置为用户编号u_id的值连接123456（如在当前表中u_id为10001，则其密码是10001123456），密钥是'hello'; remark字段内容是‘customer'或'supplier’

源码：

添加客户表帐号：

delimiter //

create procedure insertC()

begin

declare u_id int;

declare total int;

select count(*) into total from customers;

while total>0 do

select c_id into u_id from customers where. c_id+total=10005;

insert ignore into user(u_id,pwd,remark)values(u_id,aes_encrypt(`123456`,`hello`),`customer`);

set total=total-1;

end while;

end //

运行测试结果截图：

?

添加供货商帐号：

create procedure insertS()

??? begin

??? declare numer int default 0;

??? declare u_id int;

??? declare inSup cursor for select s_id from suppliers;

??? declare continue handler for not found set numer=1;

??? open inSup;

??? read_Sup:loop

??? fetch inSup into u_id;

??? if numer then leave read_Sup;

??? end if;

??? insert ignore into. user(u_id,pwd,remark)values(u_id,aes_encrypt('123456','hello'),'supplier');

??? end loop read_Sup;

??? close inSup;

end//

运行测试结果截图：

?

3.批量修正订单详情表orderitems中的水果价格与水果表fruits中的价格一致。

源码：

delimiter //

create procedure Update_price()

??? begin

??? declare ff_price decimal(8,2);

??? declare ff_id char(10);

??? declare done int default 0;

??? declare update_price cursor for select f_price,f_id from. fruits;

??? declare continue handler for not found set done=1;

??? open update_price;

??? read_update:loop

??? fetch update_price into ff_price,ff_id;

??? if done then

??? leave read_update;

??? end if;

??? update orderitems set item_price=ff_price where orderitems.f_id=ff_id;

??? end loop read_update;

??? close update_price;

??? end//

运行测试结果截图:

?

三、小结

1．遇到的问题及解决过程

问题：构造过程函数时报错

解决过程：学习相关语法并正确运行

2.产生的错误及原因分析

??? 错误：Variable or condition declaration after cursor or handler declaration

??? 原因分析：定义变量必须放在游标之前，因为放在了游标后面，所以导致此错误

3．体会和收获。

本次博客我学会了存储过程、函数、游标的工作原理、定义及操作方法。通过学习更多数据库功能的综合运用，能够更加便捷地使用数据库系统。同时也了解了更多数据库在实际生活中的应用。总的来说收获满满。