数据库编程实战

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

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

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

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

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

作者：鲸冬香

原文地址：https://segmentfault.com/a/1190000021810366

喜欢 0