用子查询代替游标实现多关联表插入

最新推荐文章于 2021-02-28 03:19:11 发布

play7788

最新推荐文章于 2021-02-28 03:19:11 发布

阅读量841

点赞数

CC 4.0 BY-SA版权

分类专栏： MSSQL 文章标签： system build structure layer up

本文链接：https://blog.youkuaiyun.com/play7788/article/details/4043739

MSSQL 专栏收录该内容

12 篇文章

订阅专栏

此程序用于复制房产数据，包括栋座、单元、楼层和房屋信息。通过事务处理确保数据的一致性和完整性，并根据不同的需求选择性地复制单元、楼层及房屋数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/**************************************************************************
程序名称： [HM].[pBuildingNosCopyBuildingNo]
功能描述：插入BNMDAT表一条数据
参数说明：参数名称     数据类型   输入/输出   字段说明
   bnm_id      bigint         编号
   bnm_com_id     bigint         公司编号
   bnm_bdm_id     bigint         楼盘编号
   bnm_no      nvarchar(16)       栋座
   bnm_unit_check    int          单元
   bnm_floor_check    int          楼层
   bnm_house_check    int          房屋
   bnm_new_usr_id    bigint         新建用户编号
返回值： ret       int
   0=成功;1=已存在
变更历史：作者  日期  版本  变更说明
   黄杨  2009-02-01 V1.0  创建
   王红亮 2009-04-02 V1.2  加事务，去游标
**************************************************************************/
ALTER PROCEDURE [HM].[pBuildingNosCopyBuildingNo]
(
@bnm_id      bigint,
@bnm_com_id     bigint,
@bnm_bdm_id     bigint,
@bnm_no      nvarchar(16),
@bnm_unit_check    int,
@bnm_floor_check   int,
@bnm_house_check   int,
@bnm_new_usr_id    bigint
)
AS
DECLARE @system_datetime datetime;  --系统时间
DECLARE @new_bnm_id   bigint;   --新栋座编号
DECLARE @bum_id    bigint;   --单元编号
DECLARE @new_bum_id   bigint;   --新单元编号
DECLARE @bfm_floor1   nvarchar(16); --标号楼层1
DECLARE @bfm_floor2   nvarchar(16); --标号楼层2
DECLARE @bhm_low_bfm_id  bigint;   --楼层编号1
DECLARE @bhm_high_bfm_id bigint;   --楼层编号2
DECLARE @bhm_id    bigint;   --房屋编号
BEGIN
SET @system_datetime = GETDATE();  --获取系统时间
SET @new_bnm_id   = 0;    --初始化新栋座编号
SET @bum_id    = 0;    --初始化单元编号
SET @new_bum_id   = 0;    --初始化新单元编号
SET @bfm_floor1   = '';    --初始化标号楼层1
SET @bfm_floor2   = '';    --初始化标号楼层2
SET @bhm_low_bfm_id  = 0;    --初始化楼层编号1
SET @bhm_high_bfm_id = 0;    --初始化楼层编号2
SET @bhm_id    = 0;    --初始化房屋编号
--插入栋座
IF EXISTS ( SELECT 1
    FROM HM.BNMDAT
    WHERE bnm_bdm_id  = @bnm_bdm_id
    AND  bnm_com_id  = @bnm_com_id
    AND  bnm_no   = @bnm_no
    )
  RETURN 1 --Key Duplicate

IF NOT EXISTS ( SELECT 1
      FROM HM.BDMMAS
      WHERE bdm_id = @bnm_bdm_id
     )
  RETURN 9 --楼盘编号不存在

BEGIN TRAN --事务开始

  INSERT
  INTO HM.BNMDAT
    (
     bnm_com_id,
     bnm_bdm_id,
     bnm_bdm_name,
     bnm_no,
     bnm_build_year,
     bnm_build_area,
     bnm_towards,
     bnm_up_floor_cnt,
     bnm_down_floor_cnt,
     bnm_pro_sol_id,
     bnm_pro_sol_name,
     bnm_unit_define_type,
     bnm_fact_unit_cnt,
     bnm_memo,
     bnm_status,
     bnm_new_usr_id,
     bnm_unit_cnt,
     bnm_new_time
    )
  SELECT  A.bnm_com_id,
     A.bnm_bdm_id,
     A.bnm_bdm_name,
     @bnm_no,
     A.bnm_build_year,
     A.bnm_build_area,
     A.bnm_towards,
     A.bnm_up_floor_cnt,
     A.bnm_down_floor_cnt,
     A.bnm_pro_sol_id,
     A.bnm_pro_sol_name,
     A.bnm_unit_define_type,
     A.bnm_fact_unit_cnt,
     A.bnm_memo,
     A.bnm_status,
     @bnm_new_usr_id,
     A.bnm_unit_cnt,
     @system_datetime
  FROM  HM.BNMDAT A
  WHERE  A.bnm_id = @bnm_id

  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRAN --事务回滚
   COMMIT           --事务结束
   RETURN -11 --返回错误
  END

  SET @new_bnm_id = @@IDENTITY;  --获取新栋座编号
  --新增成功后修改楼盘字典表中的总栋数
  UPDATE HM.BDMMAS
  SET  bdm_no_cnt = bdm_no_cnt +1
  WHERE bdm_id = @bnm_bdm_id;

  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRAN --事务回滚
   COMMIT           --事务结束
   RETURN -10 --返回错误
  END

  --插入单元
  IF @bnm_unit_check = 1
  BEGIN

    INSERT
    INTO HM.BUMDAT
      (
       bum_com_id,
       bum_bdm_id,
       bum_bnm_id,
       bum_unit,
       bum_up_floor_cnt,
       bum_down_floor_cnt,
       bum_floor_define_type,
       bum_fact_floor_cnt,
       bum_house_cnt,
       bum_memo,
       bum_status,
       bum_new_usr_id,
       bum_floor_cnt,
       bum_new_time
      )
    SELECT  B.bum_com_id,
       B.bum_bdm_id,
       @new_bnm_id,
       B.bum_unit,
       B.bum_up_floor_cnt,
       B.bum_down_floor_cnt,
       B.bum_floor_define_type,
       B.bum_fact_floor_cnt,
       B.bum_house_cnt,
       B.bum_memo,
       B.bum_status,
       @bnm_new_usr_id,
       B.bum_floor_cnt,
       @system_datetime
    FROM  HM.BUMDAT B
    WHERE  B.bum_bnm_id  = @bnm_id

    IF @@ERROR <> 0
    BEGIN
     ROLLBACK TRAN --事务回滚
     COMMIT           --事务结束
     RETURN -12 --返回错误
    END

    --插入楼层
    IF @bnm_floor_check = 1
    BEGIN
     INSERT
     INTO HM.BFMDAT
       (
        bfm_com_id,
        bfm_bdm_id,
        bfm_bnm_id,
        bfm_bum_id,
        bfm_floor,
        bfm_physics_floor,
        bfm_memo,
        bfm_exist,
        bfm_house_cnt,
        bfm_height,
        bfm_status,
        bfm_new_usr_id,
        bfm_new_time
       )
     SELECT  C.bfm_com_id,
        C.bfm_bdm_id,
        @new_bnm_id,
        (SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=C.bfm_bum_id) and bum_bnm_id=@new_bnm_id) AS bfm_bum_id,
        C.bfm_floor,
        C.bfm_physics_floor,
        C.bfm_memo,
        C.bfm_exist,
        C.bfm_house_cnt,
        C.bfm_height,
        C.bfm_status,
        @bnm_new_usr_id,
        @system_datetime
     FROM  HM.BFMDAT C
     WHERE  C.bfm_bnm_id = @bnm_id

     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN --事务回滚
      COMMIT           --事务结束
      RETURN -13 --返回错误
     END

     --插入房屋
     IF @bnm_house_check = 1
     BEGIN
       INSERT
       INTO HM.BHMDAT
         (
          bhm_bum_id,
          bhm_bdm_id,
          bhm_bnm_id,
          bhm_com_id,
          bhm_low_bfm_id,
          bhm_high_bfm_id,
          bhm_layer,
          bhm_door_no,
          bhm_rooms,
          bhm_halls,
          bhm_toilets,
          bhm_kitchens,
          bhm_fitment_sol_id,
          bhm_build_area,
          bhm_use_area,
          bhm_towards,
          bhm_pro_sol_id,
          bhm_usage,
          bhm_structure_sol_id,
          bhm_memo,
          bhm_status,
          bhm_new_usr_id,
          bhm_new_time
         )
       SELECT
          (SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id) AS bhm_bum_id,
          F.bhm_bdm_id,
          @new_bnm_id,
          F.bhm_com_id,
          (SELECT bfm_id FROM HM.BFMDAT WHERE bfm_floor=(SELECT bfm_floor FROM HM.BFMDAT WHERE bfm_id=F.bhm_low_bfm_id ) and bfm_bnm_id=@new_bnm_id and bfm_bum_id=(SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id)) AS bhm_low_bfm_id,
          (SELECT bfm_id FROM HM.BFMDAT WHERE bfm_floor=(SELECT bfm_floor FROM HM.BFMDAT WHERE bfm_id=F.bhm_high_bfm_id ) and bfm_bnm_id=@new_bnm_id and bfm_bum_id=(SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id)) AS bhm_high_bfm_id,
          F.bhm_layer,
          F.bhm_door_no,
          F.bhm_rooms,
          F.bhm_halls,
          F.bhm_toilets,
          F.bhm_kitchens,
          F.bhm_fitment_sol_id,
          F.bhm_build_area,
          F.bhm_use_area,
          F.bhm_towards,
          F.bhm_pro_sol_id,
          F.bhm_usage,
          F.bhm_structure_sol_id,
          F.bhm_memo,
          F.bhm_status,
          @bnm_new_usr_id,
          @system_datetime
       FROM  HM.BHMDAT F
       WHERE  F.bhm_bnm_id = @bnm_id

       IF @@ERROR <> 0
       BEGIN
        ROLLBACK TRAN --事务回滚
        COMMIT           --事务结束
        RETURN -14 --返回错误
       END
     END
    END
    ELSE
    BEGIN
     --如果不复制楼层，则采集楼层数置0
     UPDATE HM.BUMDAT
     SET  bum_floor_cnt = 0
     WHERE bum_id   = @new_bum_id

     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN --事务回滚
      COMMIT           --事务结束
      RETURN -12 --返回错误
     END
    END
  END
  ELSE
  BEGIN
   --如果不复制单元，则采集单元数置0
   UPDATE HM.BNMDAT
   SET  bnm_unit_cnt = 0
   WHERE bnm_id   = @new_bnm_id

   IF @@ERROR <> 0
   BEGIN
    ROLLBACK TRAN --事务回滚
    COMMIT           --事务结束
    RETURN -11 --返回错误
   END
  END

COMMIT --事务结束

RETURN 0
END