实现数据的列转入到行中的过程语言

最新推荐文章于 2024-12-23 14:34:09 发布

原创最新推荐文章于 2024-12-23 14:34:09 发布 · 201 阅读

0 ·

CC 4.0 BY-SA版权

数据库类专栏收录该内容

22 篇文章

订阅专栏

本文介绍了一段SQL脚本，用于批量更新用户所属多个机构的CMS系统中机构信息，通过删除现有记录并重新插入更新后的数据来实现机构转换。



drop  PROCEDURE RSPDB.GET_CMS_BRANCH  
/*
   功能:一个用户有机构多个机构。
   1   1
   1   2
   1   3
   转变成效果  1    1，2，3   
*/
go
CREATE OR REPLACE PROCEDURE RSPDB.GET_CMS_BRANCH  () 
LANGUAGE SQL
  BEGIN
        DECLARE  @USERNO    VARCHAR(200);
        DECLARE  @DEPTNO    VARCHAR(200);
        DECLARE  @ROWNUM    INT;
        DELETE FROM F_COM_CMS_SM_USER_SP_TEMP;
      FOR FORCURSOR AS  SELECT DEPTNO,USERNO,ROW_NUMBER() OVER(PARTITION BY USERNO ORDER BY USERNO,DEPTNO) ROWNUM FROM F_COM_CMS_SM_USER_SP do
           SET   @DEPTNO = DEPTNO;
           SET   @USERNO = USERNO;
           SET   @ROWNUM = ROWNUM;
        if @ROWNUM = 1  then
                INSERT INTO F_COM_CMS_SM_USER_SP_TEMP VALUES(@DEPTNO,@USERNO);
            ELSE
                MERGE INTO F_COM_CMS_SM_USER_SP_TEMP A
                USING (SELECT @USERNO USERNO,@DEPTNO DEPTNO FROM SYSIBM.DUAL) B
                ON A.USERNO = B.USERNO
                WHEN MATCHED THEN UPDATE 
                SET A.DEPTNO = A.DEPTNO|| ',' ||B.DEPTNO;
		end if;
      END FOR;     
             MERGE INTO SYS_USER_INFO A
                 USING (SELECT DEPTNO,USERNO  FROM F_COM_CMS_SM_USER_SP_TEMP) B
                ON A.LOGIN_NAME = B.USERNO
                WHEN MATCHED THEN UPDATE 
                SET A.BRANCH_GKNO_CMS = B.DEPTNO;

      END
GO