PROCEDURE HAND_MID_SYS_BPM_USE_ROLE(IORGTYPE IN BPM_COMPSITE_USER.ORGTYPE%TYPE,
IDATE IN DATE) IS
V_ORGTYPE BPM_COMPSITE_USER.ORGTYPE%TYPE := IORGTYPE;
V_IDATE DATE := IDATE;
/**异常记录参数初始化**/
V_PROCE_NAME VARCHAR2(100) := 'HAND_MID_SYS_BPM_USE_ROLE';
V_PROCE_BLO_NAME VARCHAR2(100) := 'HAND_MID_SYS_BPM_USE_ROLE_BLOCK';
V_PROCE_ERROR CLOB;
--TYPE1
TYPE BLACK_ROLE IS RECORD(
V_USERATTRID1 BPM_COMPSITE_USER.USERATTRID%TYPE);
TYPE T_BLACK_ROLE IS TABLE OF BLACK_ROLE;
V_BLACK_ROLE T_BLACK_ROLE;
/**定义游标**/
CURSOR CUR_BLACK_ROLE IS
SELECT DISTINCT R.USERATTRID
FROM BPM_COMPSITE_USER R
WHERE R.ORGTYPE = V_ORGTYPE
AND R.FINDTYPE = 3
AND R.USERATTRID IS NOT NULL;
/**存放游标中的变量***/
L_USERATTRID BPM_COMPSITE_USER.USERATTRID%TYPE;
L_ORGTYPE VARCHAR2(10) := CASE WHEN V_ORGTYPE = 1 THEN 'BO' WHEN V_ORGTYPE = 2 THEN 'BQ' WHEN V_ORGTYPE = 3 THEN 'BG' WHEN V_ORGTYPE = 4 THEN 'BF' WHEN V_ORGTYPE = 5 THEN 'BA' WHEN V_ORGTYPE = 6 THEN 'BM' ELSE '' END;
/**变量主要进行切割带有逗号的USERATTRID1**/
V_STR_START NUMBER;
V_STR_LEN NUMBER;
V_STR_END NUMBER;
V_STR_COUNT NUMBER;
V_STR_PARTCONTENT VARCHAR2(2000);
/**进行数据判断,看是否有数据存在***/
V_ISFIND_ROLENAME NUMBER;
V_ISFIND_DATA NUMBER;
BEGIN
/************************BUSINESS LOGIC START************************************/
OPEN CUR_BLACK_ROLE;
FETCH CUR_BLACK_ROLE BULK COLLECT
INTO V_BLACK_ROLE;
FOR I IN 1 .. V_BLACK_ROLE.COUNT LOOP
--获取游标中的数据
L_USERATTRID := V_BLACK_ROLE(I).V_USERATTRID1;
V_STR_COUNT := 0;
LOOP
V_STR_START := V_STR_COUNT + 1; --获取截取字符串起始索引
V_STR_COUNT := INSTR(L_USERATTRID, ',', V_STR_COUNT + 1, 1);
V_STR_END := V_STR_COUNT - 1; --获取截取字符串终止索引
--获取截取字符串长度 IF STR_COUNT<>0--判断,如果STR_COUNT不等于0
IF V_STR_COUNT <> 0 THEN
V_STR_LEN := V_STR_END - V_STR_START + 1; --获取截取字符串长度
ELSE
V_STR_LEN := LENGTH(L_USERATTRID); --获取全部长度
END IF;
SELECT SUBSTR(L_USERATTRID, V_STR_START, V_STR_LEN)
INTO V_STR_PARTCONTENT
FROM DUAL;
SELECT COUNT(*)
INTO V_ISFIND_DATA
FROM MID_SYS_BPM_USE_ROLE
WHERE USERATTRID = V_STR_PARTCONTENT
AND ORGTYPE = L_ORGTYPE;
SELECT COUNT(*)
INTO V_ISFIND_ROLENAME
FROM SYS_ROLE
WHERE ROLEID = V_STR_PARTCONTENT
AND ROLETYPE = 2
AND ENABLED = 1;
IF V_ISFIND_DATA = 0 AND V_ISFIND_ROLENAME > 0 THEN
INSERT INTO MID_SYS_BPM_USE_ROLE
(USERATTRID,
USERATTRNAME,
ORGTYPE,
AREAID,
CATEGORYID,
CREATETIME)
VALUES
(V_STR_PARTCONTENT,
(SELECT ROLENAME
FROM SYS_ROLE T
WHERE ROLEID = V_STR_PARTCONTENT),
L_ORGTYPE,
(SELECT AREAID FROM SYS_ROLE WHERE ROLEID = V_STR_PARTCONTENT),
(SELECT CATEGORYID
FROM SYS_ROLE
WHERE ROLEID = V_STR_PARTCONTENT),
V_IDATE);
END IF;
COMMIT;
EXIT WHEN V_STR_COUNT = 0; --当在所剩字符串中未找到','时退出
END LOOP;
END LOOP;
CLOSE CUR_BLACK_ROLE;
/************************BUSINESS LOGIC END*************************************/
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_PROCE_ERROR := 'FORMAT_ERROR_BACKTRACE:' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ';SQLERRM:' ||
SQLERRM || ';SQLCODE:' || SQLCODE;
INSERT INTO SYS_PRO_ERROR_LOG
(ID,
PROCE_NAME,
PROCE_TYPE,
PROCE_BLO_NAME,
PROCE_ERROR,
ADD_BY,
STATUS,
CREATETIME)
VALUES
(UNIQUEIDUTIL_ID.NEXTVAL,
V_PROCE_NAME,
'P',
V_PROCE_BLO_NAME,
V_PROCE_ERROR,
'ADMIN',
'0',
V_IDATE);
COMMIT;
END HAND_MID_SYS_BPM_USE_ROLE;