sql - 从每个模式的表中选择行

Question

我想从具有给定 LIKE 名称的每个模式的 DATABASECHANGELOGLOCK 表中选择行。即 ...LIKE 'myschemaprefix%';

我想要 UNIONS 以外的东西，因为我有很多用户。

SELECT 'MySchema1' as SCHEMA_NAME, LOCKED, LOCKGRANTED, LOCKEDBY FROM MySchema1.DATABASECHANGELOGLOCK
UNION
SELECT 'MySchema2' as SCHEMA_NAME, LOCKED, LOCKGRANTED, LOCKEDBY FROM MySchema1.DATABASECHANGELOGLOCK;

我使用捕获所有模式名称的过程解决了这个问题。

SET serveroutput ON
BEGIN
  FOR item IN (
    SELECT USERNAME FROM dba_users WHERE USERNAME LIKE 'MySchemaPrefix%'
  ) LOOP

    DBMS_OUTPUT.PUT_LINE
      (item.username||'.DATABASECHANGELOGLOCK');
  END LOOP;
END;
/

我需要的是循环中的一个 SELECT，它使用输出值来获取 LOCKED = 1 的任何行；

想法？

Answer 1

这可能会获取所需的数据

SET serveroutput ON
declare
  cur_locks sys_refcursor; -- cursor to fetch lock info

  -- variables to store data from a row of lock info
  v_locked number;
  v_lock_granted varchar2(30);
  v_locked_by varchar2(30);

BEGIN
  FOR item IN (
    SELECT USERNAME FROM dba_users WHERE USERNAME LIKE 'MySchemaPrefix%'
  ) LOOP

      OPEN cur_locks FOR
        'SELECT LOCKED, LOCKGRANTED, ' ||
        ' LOCKEDBY FROM '  || item.USERNAME  || '.DATABASECHANGELOGLOCK '
        || ' WHERE LOCKED = 1 ';       

      -- loop to fetch and print
      LOOP
        fetch cur_locks into 
         v_locked, v_lock_granted, v_locked_by;
        EXIT WHEN cur_locks%NOTFOUND;
        dbms_output.put_line(
          item.USERNAME ||','||
          v_locked ||','||
          v_lock_granted ||','||
          v_locked_by
        );
      end loop;

      close cur_locks;

    --DBMS_OUTPUT.PUT_LINE
      --(item.username||'.DATABASECHANGELOGLOCK');
  END LOOP;
END;
/