Oracle中根据列名的值，查询同值同列名的表

2019独角兽企业重金招聘Python工程师标准>>>

--根据列名的值，查询同值同列名的表
CREATE OR REPLACE FUNCTION GETTABLES(PRM_BAZ002 IN VARCHAR2)
  RETURN VARCHAR2 IS
  N_COUNT     NUMBER(10);
  V_RESULT    VARCHAR2(300);
  V_TABLE     VARCHAR2(300);
  V_TABLE_ALL VARCHAR2(300);
  V_SQL       VARCHAR2(1000);
BEGIN
   FOR AREC IN (SELECT T.TABLE_NAME
                 FROM ALL_TAB_COLS T
                 --业务用户名称
                WHERE T.OWNER = 'SCOTT'
                 --查询列
                  AND T.COLUMN_NAME = 'BAZ002'
                  AND LENGTH(T.TABLE_NAME) = 4
                  --去除视图
                  AND EXISTS
                (SELECT 1
                         FROM USER_OBJECTS A
                        WHERE A.OBJECT_TYPE = 'TABLE'
                          AND A.OBJECT_NAME = T.TABLE_NAME)) LOOP

    BEGIN
      V_TABLE := AREC.TABLE_NAME;
      V_SQL   := 'SELECT COUNT(1) FROM ' || V_TABLE ||
                 ' WHERE BAZ002 = ' || PRM_BAZ002 || ' ';
      EXECUTE IMMEDIATE V_SQL INTO N_COUNT;

      IF N_COUNT > 0 THEN
        V_TABLE := V_TABLE || ';';
      ELSE
        V_TABLE := '';
      END IF;
      V_TABLE_ALL := V_TABLE_ALL || V_TABLE;
    EXCEPTION
      WHEN OTHERS THEN
        V_RESULT := AREC.TABLE_NAME || ' 表查询出错!';
        RETURN V_RESULT;
    END;
  END LOOP;
  V_RESULT := V_TABLE_ALL;
  RETURN(V_RESULT);
END GJ_GETTABLES;

转载于:https://my.oschina.net/u/242479/blog/115586