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
实现数据的列转入到行中的过程语言
最新推荐文章于 2023-06-08 21:56:57 发布