数据库编程实战

最近在写项目是遇到了一个问题,创建一个新实体,同时还要与原先的旧实体完成绑定。

原先实体为器具类别和器具实体,他们之间的关系为1:0..n 的关系,如图:

image.png

现在添加一个新实体:器具别名,他与器具类别之间关系为1..n :1。与器具之间的关系为1:0..n

image.png

为了保证实体之间的完整性,需要在创建新实体时,完成与原先实体的绑定。需要完成的任务为:

  1. 根据已有的器具类别创建默认的同名器具别名,例如根据游标尺类别生成游标尺别名。
  2. 将器具的别名绑定到器具类别默认的别名上,例如游标尺1的别名为游标尺别名。

解决方法

对于这种迭代的过程,自然而然的就想到了使用函数来实现。在上学期的数据库课程中,也学到sql编程的方法。

与函数的定义一样,首先是定义存储过程:

DELIMITER $$  
CREATE PROCEDURE insert_alias()  
BEGIN
    ........
END$$  
DELIMITER ;

我们需要根据器具类别表中的数据来创建默认的器具别名,基本思路就是依次读取器具类别表中的记录,取name字段和id字段,创建到器具别名的表中,这时候就用到sql编程中的游标,类似与c++语言中的指针操作数组。

declare cur cursor for select id,name from instrument_type; // 声明游标 查询instrument_type 取id,name 字段

在定义好游标后,可以使用fetch打开游标取其中中的数据,取出数据后,游标会自动移动到下一条数据,所以可以写一个循环,依次把数据读出:

declare instrument_type_id bigint;     // 声明变量 用于存储 器具类别id
declare alias_name varchar(200);    // 声明变量 用于存储 器具别名
declare done int default false;
declare continue HANDLER for not found set done = true;     // 当游标读至结尾时 done为true
open cur;  
fetch cur into instrument_type_id,alias_name;   // 打开游标读取记录 将id和name字段值 存储到变量中  
while(not done) do  
   INSERT INTO instrument_alias(name, instrument_type_id) VALUES(alias_name, instrument_type_id);  // 根据器具类别名称和id 创建别名记录
  
   UPDATE instrument i SET i.alias_id= LAST_INSERT_ID() WHERE i.instrument_type_id = instrument_type_id;  // 用新创建的别名id更新到器具表中
   fetch cur into instrument_type_id,alias_name;    // 取下一条记录的数据
end while;

在写完后,使用CALL指令调用就行了。

CALL insert_alias();
阅读 318

推荐阅读
我的专栏
用户专栏

一周的报告

5 人关注
53 篇文章
专栏主页