CREATE OR REPLACE PACKAGE BODY STD_MODEL_STRUCTRUE_RES_P IS
-- AUTHOR : SHIC
-- CREATED : 2013-01-06
-- PURPOSE :
/**********************************************************************
* 功能描述:型号体系复制
* 创 建 人:SHIC
* 编 写 人:SHIC
* 编写日期:2013-1-06
* 修改记录:
**********************************************************************/
PROCEDURE STD_MODEL_SYSTEM_COPY_P(I_MODEL_SYSTEM_ID_TO IN STD_MODEL_SYSTEM.ID%TYPE, --复制的体系ID
I_MODEL_SYSTEM_ID_FROM IN STD_MODEL_SYSTEM.ID%TYPE, --被复制的体系ID
O_RETURN_INT OUT INTEGER,
O_RETURN_STRING OUT STRING) IS
--声明一个型号体系结构表类型变量
TYPE STD_MODEL_STRUCTURES IS TABLE OF STD_MODEL_STRUCTURE%ROWTYPE;
NEW_ROWS STD_MODEL_STRUCTURES;
--声明一个关系表变量
TYPE STD_MODEL_STRUCTRUE_RESS IS TABLE OF STD_MODEL_STRUCTURE_RES%ROWTYPE;
NEW_RES STD_MODEL_STRUCTRUE_RESS;
--声明一个为VARCHAR2(32)类型的数组,键值对的都是以VARCHAR2(32)
TYPE ID_LOOKUP IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(32);
ID_TRANSLATE ID_LOOKUP;
STD_MODEL_STRUCTURES_ID STD_MODEL_STRUCTURE.ID%TYPE;
STRUCTURE_NO STD_MODEL_STRUCTURE.STRUCTURE_NO%TYPE;
STRUCTURE_NAME STD_MODEL_STRUCTURE.STRUCTURE_NAME%TYPE;
BEGIN
--给集合中添加数据(型号体系结构数据)
SELECT INNERS.* BULK COLLECT
INTO NEW_ROWS
FROM (SELECT *
FROM STD_MODEL_STRUCTURE SMS
WHERE SMS.STD_SYSTEM_ID = I_MODEL_SYSTEM_ID_FROM) INNERS
START WITH INNERS.PARENT_ID = 'ROOT'
CONNECT BY PRIOR INNERS.ID = INNERS.PARENT_ID
ORDER BY LEVEL ASC;
--判断集合是否存在记录
IF NEW_ROWS.COUNT() > 0 THEN
--替换型号体系ID
FOR I IN NEW_ROWS.FIRST() .. NEW_ROWS.LAST() LOOP
--生成新的型号体系结构ID
STD_MODEL_STRUCTURES_ID := SYS_GUID();
--新型号ID和旧型号ID进行映射
ID_TRANSLATE(NEW_ROWS(I).ID) := STD_MODEL_STRUCTURES_ID;
--设置父子关系
IF NEW_ROWS(I).PARENT_ID != 'ROOT' THEN
NEW_ROWS(I).PARENT_ID := ID_TRANSLATE(NEW_ROWS(I).PARENT_ID);
ELSIF NEW_ROWS(I).PARENT_ID = 'ROOT' THEN
SELECT SMS.STRUCTURE_NO, SMS.STRUCTURE_NAME
INTO STRUCTURE_NO, STRUCTURE_NAME
FROM STD_MODEL_STRUCTURE SMS
WHERE SMS.STD_SYSTEM_ID = I_MODEL_SYSTEM_ID_TO
AND SMS.PARENT_ID = 'ROOT';
NEW_ROWS(I).STRUCTURE_NO := STRUCTURE_NO;
NEW_ROWS(I).STRUCTURE_NAME := STRUCTURE_NAME;
END IF;
INSERT INTO STD_MODEL_STRUCTURE
(ID,
PARENT_ID,
STD_SYSTEM_ID,
STRUCTURE_NO,
STRUCTURE_NAME,
NOTE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_IP,
VERSION)
VALUES
(STD_MODEL_STRUCTURES_ID,
NEW_ROWS (I).PARENT_ID,
I_MODEL_SYSTEM_ID_TO,
NEW_ROWS (I).STRUCTURE_NO,
NEW_ROWS (I).STRUCTURE_NAME,
NEW_ROWS (I).NOTE,
GET_SESSION_INFO.GET_USER_ID,
SYSDATE,
GET_SESSION_INFO.GET_USER_ID,
SYSDATE,
GET_SESSION_INFO.GET_LOGON_IP,
1);
--标准关系标准添加数据
SELECT INSMSR.* BULK COLLECT
INTO NEW_RES
FROM (SELECT SMSR.*
FROM STD_MODEL_STRUCTURE_RES SMSR
WHERE SMSR.STD_STRUCTURE_ID = NEW_ROWS(I).ID) INSMSR;
IF NEW_RES.COUNT() > 0 THEN
FOR J IN NEW_RES.FIRST() .. NEW_RES.LAST() LOOP
INSERT INTO STD_MODEL_STRUCTURE_RES
(ID,
STD_STRUCTURE_ID,
STD_STANDARD_ID,
PRIORITY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_IP,
VERSION)
VALUES
(SYS_GUID(),
STD_MODEL_STRUCTURES_ID,
NEW_RES(J).STD_STANDARD_ID,
NEW_RES(J).PRIORITY,
GET_SESSION_INFO.GET_USER_ID,
SYSDATE,
GET_SESSION_INFO.GET_USER_ID,
SYSDATE,
GET_SESSION_INFO.GET_LOGON_IP,
1);
END LOOP;
END IF;
END LOOP;
END IF;
COMMIT;
O_RETURN_INT := CODECOLLECTION.SUCCESS;
O_RETURN_STRING := '型号体系结构复制成功:' || SQLERRM;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
O_RETURN_INT := CODECOLLECTION.FAIL;
O_RETURN_STRING := '型号体系结构复制失败:' || SQLERRM;
END STD_MODEL_SYSTEM_COPY_P;
END STD_MODEL_STRUCTRUE_RES_P;
-- AUTHOR : SHIC
-- CREATED : 2013-01-06
-- PURPOSE :
/**********************************************************************
* 功能描述:型号体系复制
* 创 建 人:SHIC
* 编 写 人:SHIC
* 编写日期:2013-1-06
* 修改记录:
**********************************************************************/
PROCEDURE STD_MODEL_SYSTEM_COPY_P(I_MODEL_SYSTEM_ID_TO IN STD_MODEL_SYSTEM.ID%TYPE, --复制的体系ID
I_MODEL_SYSTEM_ID_FROM IN STD_MODEL_SYSTEM.ID%TYPE, --被复制的体系ID
O_RETURN_INT OUT INTEGER,
O_RETURN_STRING OUT STRING) IS
--声明一个型号体系结构表类型变量
TYPE STD_MODEL_STRUCTURES IS TABLE OF STD_MODEL_STRUCTURE%ROWTYPE;
NEW_ROWS STD_MODEL_STRUCTURES;
--声明一个关系表变量
TYPE STD_MODEL_STRUCTRUE_RESS IS TABLE OF STD_MODEL_STRUCTURE_RES%ROWTYPE;
NEW_RES STD_MODEL_STRUCTRUE_RESS;
--声明一个为VARCHAR2(32)类型的数组,键值对的都是以VARCHAR2(32)
TYPE ID_LOOKUP IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(32);
ID_TRANSLATE ID_LOOKUP;
STD_MODEL_STRUCTURES_ID STD_MODEL_STRUCTURE.ID%TYPE;
STRUCTURE_NO STD_MODEL_STRUCTURE.STRUCTURE_NO%TYPE;
STRUCTURE_NAME STD_MODEL_STRUCTURE.STRUCTURE_NAME%TYPE;
BEGIN
--给集合中添加数据(型号体系结构数据)
SELECT INNERS.* BULK COLLECT
INTO NEW_ROWS
FROM (SELECT *
FROM STD_MODEL_STRUCTURE SMS
WHERE SMS.STD_SYSTEM_ID = I_MODEL_SYSTEM_ID_FROM) INNERS
START WITH INNERS.PARENT_ID = 'ROOT'
CONNECT BY PRIOR INNERS.ID = INNERS.PARENT_ID
ORDER BY LEVEL ASC;
--判断集合是否存在记录
IF NEW_ROWS.COUNT() > 0 THEN
--替换型号体系ID
FOR I IN NEW_ROWS.FIRST() .. NEW_ROWS.LAST() LOOP
--生成新的型号体系结构ID
STD_MODEL_STRUCTURES_ID := SYS_GUID();
--新型号ID和旧型号ID进行映射
ID_TRANSLATE(NEW_ROWS(I).ID) := STD_MODEL_STRUCTURES_ID;
--设置父子关系
IF NEW_ROWS(I).PARENT_ID != 'ROOT' THEN
NEW_ROWS(I).PARENT_ID := ID_TRANSLATE(NEW_ROWS(I).PARENT_ID);
ELSIF NEW_ROWS(I).PARENT_ID = 'ROOT' THEN
SELECT SMS.STRUCTURE_NO, SMS.STRUCTURE_NAME
INTO STRUCTURE_NO, STRUCTURE_NAME
FROM STD_MODEL_STRUCTURE SMS
WHERE SMS.STD_SYSTEM_ID = I_MODEL_SYSTEM_ID_TO
AND SMS.PARENT_ID = 'ROOT';
NEW_ROWS(I).STRUCTURE_NO := STRUCTURE_NO;
NEW_ROWS(I).STRUCTURE_NAME := STRUCTURE_NAME;
END IF;
INSERT INTO STD_MODEL_STRUCTURE
(ID,
PARENT_ID,
STD_SYSTEM_ID,
STRUCTURE_NO,
STRUCTURE_NAME,
NOTE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_IP,
VERSION)
VALUES
(STD_MODEL_STRUCTURES_ID,
NEW_ROWS (I).PARENT_ID,
I_MODEL_SYSTEM_ID_TO,
NEW_ROWS (I).STRUCTURE_NO,
NEW_ROWS (I).STRUCTURE_NAME,
NEW_ROWS (I).NOTE,
GET_SESSION_INFO.GET_USER_ID,
SYSDATE,
GET_SESSION_INFO.GET_USER_ID,
SYSDATE,
GET_SESSION_INFO.GET_LOGON_IP,
1);
--标准关系标准添加数据
SELECT INSMSR.* BULK COLLECT
INTO NEW_RES
FROM (SELECT SMSR.*
FROM STD_MODEL_STRUCTURE_RES SMSR
WHERE SMSR.STD_STRUCTURE_ID = NEW_ROWS(I).ID) INSMSR;
IF NEW_RES.COUNT() > 0 THEN
FOR J IN NEW_RES.FIRST() .. NEW_RES.LAST() LOOP
INSERT INTO STD_MODEL_STRUCTURE_RES
(ID,
STD_STRUCTURE_ID,
STD_STANDARD_ID,
PRIORITY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_IP,
VERSION)
VALUES
(SYS_GUID(),
STD_MODEL_STRUCTURES_ID,
NEW_RES(J).STD_STANDARD_ID,
NEW_RES(J).PRIORITY,
GET_SESSION_INFO.GET_USER_ID,
SYSDATE,
GET_SESSION_INFO.GET_USER_ID,
SYSDATE,
GET_SESSION_INFO.GET_LOGON_IP,
1);
END LOOP;
END IF;
END LOOP;
END IF;
COMMIT;
O_RETURN_INT := CODECOLLECTION.SUCCESS;
O_RETURN_STRING := '型号体系结构复制成功:' || SQLERRM;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
O_RETURN_INT := CODECOLLECTION.FAIL;
O_RETURN_STRING := '型号体系结构复制失败:' || SQLERRM;
END STD_MODEL_SYSTEM_COPY_P;
END STD_MODEL_STRUCTRUE_RES_P;