下面是三个不同的游标：POTENTIAL_USERS，NO_WORKFLOWS和NO_MAPPINGS . 我试图找到一种方法从潜在的USERS CURSOR中删除它在NO_MAPPINGS和NO_WORKFLOWS游标中的位置 . 我正在使用游标，因为他们引用的查询相当长，这使我更容易遵循 . 此外，NO_WORKFLOWS和NO_MAPPINGS正在引用两个不同的WITH语句，我替换使用视图，因为我在只读数据库中，而POTENTIAL_USERS引用了一个提取非活动用户帐户的查询 . 我收集了在另一个数据库中创建类似方案的错误消息 . 我将不胜感激任何建议或建议 .
CURSOR USERS_WITHOUT_CHECKEDOUT_WORKFLOWS
IS
WITH POTENTIAL_USERS_TO_DELETE
AS (SELECT USER_NAME, USER_ID
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE), -12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE
'%CSTEINKAMP%'),
CHECKED_OUT_WORKFLOWS
AS (SELECT C.SUBJ_NAME,
A.TASK_NAME,
B.USER_NAME,
USER_ID
FROM GAI_PM.OPB_TASK A
JOIN GAI_PM.OPB_USERS B
ON A.CHECKOUT_USER_ID = B.USER_ID
JOIN GAI_PM.OPB_SUBJECT C
ON A.SUBJECT_ID = C.SUBJ_ID
WHERE A.CHECKOUT_USER_ID <> 0)
SELECT DISTINCT POTENTIAL_USERS_TO_DELETE.USER_NAME
FROM POTENTIAL_USERS_TO_DELETE
LEFT JOIN
CHECKED_OUT_WORKFLOWS
ON POTENTIAL_USERS_TO_DELETE.USER_ID =
CHECKED_OUT_WORKFLOWS.USER_ID
WHERE CHECKED_OUT_WORKFLOWS.USER_NAME IS NULL;
CURSOR POTENTIAL_USERS_TO_DELETE
IS
SELECT USER_NAME AS "USERS TO DELETE"
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE), -12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%CSTEINKAMP%';
BEGIN
DECLARE
CURSOR USERS_WITHOUT_CHECKEDOUT_MAPPINGS
IS
WITH POTENTIAL_USERS_TO_DELETE
AS (SELECT USER_NAME, USER_ID
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE), -12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE
'%CSTEINKAMP%'),
CHECKED_OUT_MAPPINGS
AS (SELECT C.SUBJ_NAME,
A.MAPPING_NAME,
B.USER_NAME,
B.USER_ID
FROM GAI_PM.OPB_MAPPING A
JOIN GAI_PM.OPB_USERS B
ON A.CHECKOUT_USER_ID = B.USER_ID
JOIN GAI_PM.OPB_SUBJECT C
ON A.SUBJECT_ID = C.SUBJ_ID
WHERE A.CHECKOUT_USER_ID <> 0)
SELECT DISTINCT POTENTIAL_USERS_TO_DELETE.USER_NAME, MAPPING_NAME
FROM POTENTIAL_USERS_TO_DELETE
LEFT JOIN
CHECKED_OUT_MAPPINGS
ON POTENTIAL_USERS_TO_DELETE.USER_ID =
CHECKED_OUT_MAPPINGS.USER_ID
WHERE CHECKED_OUT_MAPPINGS.USER_NAME IS NULL;
POTENTIAL_USERS POTENTIAL_USERS_TO_DELETE%ROWTYPE;
NO_WORKFLOWS USERS_WITHOUT_CHECKEDOUT_WORKFLOWS%ROWTYPE;
NO_MAPPINGS USERS_WITHOUT_CHECKEDOUT_MAPPINGS%ROWTYPE;
BEGIN
LOOP
IF NOT (POTENTIAL_USERS_TO_DELETE%ISOPEN)
THEN
OPEN POTENTIAL_USERS_TO_DELETE;
END IF;
IF NOT (USERS_WITHOUT_CHECKEDOUT_WORKFLOWS%ISOPEN)
THEN
OPEN USERS_WITHOUT_CHECKEDOUT_WORKFLOWS;
END IF;
IF NOT (USERS_WITHOUT_CHECKEDOUT_MAPPINGS%ISOPEN)
THEN
OPEN USERS_WITHOUT_CHECKEDOUT_MAPPINGS;
END IF;
FETCH POTENTIAL_USERS_TO_DELETE INTO POTENTIAL_USERS;
FETCH USERS_WITHOUT_CHECKEDOUT_WORKFLOWS INTO NO_WORKFLOWS;
FETCH USERS_WITHOUT_CHECKEDOUT_MAPPINGS INTO NO_MAPPINGS;
DELETE FROM POTENTIAL_USERS
WHERE POTENTIAL_USERS.USER_NAME = NO_WORKFLOWS.USER_NAME
OR POTENTIAL_USERS.USER_NAME = NO_MAPPINGS.USER_NAME;
IF (POTENTIAL_USERS_TO_DELETE%ISOPEN)
THEN
CLOSE POTENTIAL_USERS_TO_DELETE;
END IF;
IF (USERS_WITHOUT_CHECKEDOUT_WORKFLOWS%ISOPEN)
THEN
CLOSE USERS_WITHOUT_CHECKEDOUT_WORKFLOWS;
END IF;
IF (USERS_WITHOUT_CHECKEDOUT_MAPPINGS%ISOPEN)
THEN
CLOSE USERS_WITHOUT_CHECKEDOUT_MAPPINGS;
END IF;
END LOOP;
END;
END;
以下是我不断收到的错误消息：
ORA-06550：第22行，第29列：PLS-00103：当遇到以下情况之一时遇到符号“NO_WORKFLOWS”：
（符号“（”代替“NO_WORKFLOWS”继续.ORA-06550：第23行，第3列：PLS-00103：遇到以下其中一项时遇到符号“THEN”：
）和/或符号“）”代替“那么”继续 . ORA-06550：第26行，第40列：PLS-00103：当遇到以下情况之一时遇到符号“NO_WORKFLOWS”：
（符号“（”代替“NO_WORKFLOWS”继续.ORA-06550：第27行，第44栏：PLS-00103：遇到以下其中一项时遇到符号“NO_MAPPINGS”：
（
1 回答
我意识到我这样做太难了 . 我所要做的就是将查询分成两个单独的删除脚本，然后分别运行它们或作为脚本运行 .