oracle 排除对象被删除

我正在写一个pl/sql代码，它接受模式名称作为input 1并存储在变量l_schema_name中，需要排除的对象被丢弃到input 2并存储在变量l_exclude_objects中（默认值为NULL，如果传递多个对象，则用逗号分隔）
下面是代码块，我在我的代码中，但不知何故，它不排除对象，而运行脚本。
FOR i IN（SELECT owner，object_type，object_name FROM dba_objects WHERE owner = l_schema_name AND object_type IN（'SEQUENCE '，' PROCEDURE '，' PACKAGE'，'TRIGGER'，'MATERIALIZED VIEW'，' TABLE'，'VIEW'，' SYNONYM'，'FUNCTION'，' TYPE'，'PROGRAM'）AND（l_exclude_objects IS NULL OR object_name NOT IN（SELECT TRIM（REGEXP_SUBSTR（l_exclude_objects，'[^，]+'，1，LEVEL））FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT（l_exclude_objects，'，'）+ 1））ORDER BY DECODE（object_type，' TRIGGER '，1，' PACKAGE '，2，' PROCEDURE '，3，' FUNCTION'，4，'VIEW'，5，'SYNONYM'，6，'MATERIALIZED VIEW '，7，' TABLE'，8，'SEQUENCE'，9））
尝试过但没有成功

它实际上是 * 工作 *：

SQL> set serveroutput on
SQL> DECLARE
  2     l_schema_name      VARCHAR2 (20) := 'SCOTT';
  3     l_exclude_objects  VARCHAR2 (30) := 'TABLE,VIEW,FUNCTION';
  4  BEGIN
  5     FOR i
  6        IN (  SELECT owner, object_type, object_name
  7                FROM all_objects
  8               WHERE     owner = l_schema_name
  9                     AND object_type IN ('SEQUENCE',
 10                                         'PROCEDURE',
 11                                         'PACKAGE',
 12                                         'TRIGGER',
 13                                         'MATERIALIZED VIEW',
 14                                         'TABLE',
 15                                         'VIEW',
 16                                         'SYNONYM',
 17                                         'FUNCTION',
 18                                         'TYPE',
 19                                         'PROGRAM')
 20                     AND (   l_exclude_objects IS NULL
 21                          OR object_type NOT IN
 22                                (    SELECT TRIM (REGEXP_SUBSTR (l_exclude_objects,
 23                                                                 '[^,]+',
 24                                                                 1,
 25                                                                 LEVEL))
 26                                       FROM DUAL
 27                                 CONNECT BY LEVEL <=
 28                                            REGEXP_COUNT (l_exclude_objects, ',') + 1))
 29            ORDER BY DECODE (object_type,
 30                             'TRIGGER', 1,
 31                             'PACKAGE', 2,
 32                             'PROCEDURE', 3,
 33                             'FUNCTION', 4,
 34                             'VIEW', 5,
 35                             'SYNONYM', 6,
 36                             'MATERIALIZED VIEW', 7,
 37                             'TABLE', 8,
 38                             'SEQUENCE', 9))
 39     LOOP
 40        DBMS_OUTPUT.put_line (i.object_type || ' ' || i.object_name);
 41     END LOOP;
 42  END;
 43  /
TRIGGER DOB_CHECK
TRIGGER HIRE_DATE_CHECK
PACKAGE PKG_TEST
PROCEDURE LIST_LOG
PROCEDURE P_FETCH
PROCEDURE LIORBYBRANCH
PROCEDURE GETTORIMBYDOCTOR
PROCEDURE P_EMPTY
MATERIALIZED VIEW TEMP_20230509_PARENT_MV
TYPE PATRIK_SPLIT_VALUE
TYPE T_ROW
TYPE PATRIK_SPLIT_TABLE
TYPE TABLE_INVOICE
TYPE INVOICE_DATA_TABLE
TYPE T_TAB

PL/SQL procedure successfully completed.

SQL>

所以，有什么问题吗？在21号线。

如果l_exclude_objects包含对象type，则检查对象应该是object_type
如果包含对象名称，则检查object_name

我前面的例子假设它是 type。
好吧，让我们试试名字。这是Scott示例模式，它包含EMP和DEPT表，它们被排除在输出之外：

SQL> DECLARE
  2     l_schema_name      VARCHAR2 (20) := 'SCOTT';
  3     l_exclude_objects  VARCHAR2 (30) := 'EMP,DEPT, BONUS';
  4  BEGIN
  5     FOR i
  6        IN (  SELECT owner, object_type, object_name
  7                FROM all_objects
  8               WHERE     owner = l_schema_name
  9                     AND object_type IN ('SEQUENCE',
 10                                         'PROCEDURE',
 11                                         'PACKAGE',
 12                                         'TRIGGER',
 13                                         'MATERIALIZED VIEW',
 14                                         'TABLE',
 15                                         'VIEW',
 16                                         'SYNONYM',
 17                                         'FUNCTION',
 18                                         'TYPE',
 19                                         'PROGRAM')
 20                     AND (   l_exclude_objects IS NULL
 21                          OR object_name NOT IN
 22                                (    SELECT TRIM (REGEXP_SUBSTR (l_exclude_objects,
 23                                                                 '[^,]+',
 24                                                                 1,
 25                                                                 LEVEL))
 26                                       FROM DUAL
 27                                 CONNECT BY LEVEL <=
 28                                            REGEXP_COUNT (l_exclude_objects, ',') + 1))
 29            ORDER BY DECODE (object_type,
 30                             'TRIGGER', 1,
 31                             'PACKAGE', 2,
 32                             'PROCEDURE', 3,
 33                             'FUNCTION', 4,
 34                             'VIEW', 5,
 35                             'SYNONYM', 6,
 36                             'MATERIALIZED VIEW', 7,
 37                             'TABLE', 8,
 38                             'SEQUENCE', 9))
 39     LOOP
 40        DBMS_OUTPUT.put_line (i.object_type || ' ' || i.object_name);
 41     END LOOP;
 42  END;
 43  /
TRIGGER DOB_CHECK
TRIGGER HIRE_DATE_CHECK
PACKAGE PKG_TEST
PROCEDURE LIST_LOG
PROCEDURE P_FETCH
PROCEDURE LIORBYBRANCH
PROCEDURE GETTORIMBYDOCTOR
PROCEDURE P_EMPTY
FUNCTION F_TEST
FUNCTION COL_MAX_LENGTH
FUNCTION ABC
FUNCTION PATRIK_SPLIT_CLOB
VIEW V_TEST
VIEW V_EMP_20
MATERIALIZED VIEW TEMP_20230509_PARENT_MV
TABLE TABLE_B
TABLE STATUS
TABLE EMPLOYEE
TABLE TEST
TABLE SALGRADE
TABLE TABLE1
TABLE TABLE2
TABLE TEMP_20230509_PARENT_MV
TABLE RUPD$_TEMP_20230509_PARENT
TABLE MLOG$_TEMP_20230509_PARENT
TABLE TABLE_A
TABLE MSG
TABLE DEPARTMENT
TABLE EMP1
TABLE STAFF
TABLE DEMO_EMPLOYEE
TABLE ACTIVITY_HISTORY
TABLE ORDERS
TABLE EMPLOYEES
TABLE TEMP
TABLE TEMP_20230509_CHILD
TABLE USER_TABLE
TABLE NEW_TABLE
TABLE TEMP_20230509_PARENT
TYPE PATRIK_SPLIT_VALUE
TYPE PATRIK_SPLIT_TABLE
TYPE TABLE_INVOICE
TYPE INVOICE_DATA_TABLE
TYPE T_TAB
TYPE T_ROW

PL/SQL procedure successfully completed.

SQL>

因此，我认为这似乎是可以的。

oracle 排除对象被删除

1条答案

相关问题

热门标签

最新问答