Oracle 判断一个表或者列是否存在

问题：

为一系列已经存在的表，添加一个相同的字段。(比如：添加SURVEYRESULT字段)

注：表名和字段名一定要大写，否则结果不正确

1、判断表是否在数据库中存在

'SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER (''' || V_TBL_NAME || ''')'

2、判断列是否在数据库中存在

‘SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('''||V_TBL_NAME || ''') AND COLUMN_NAME = ''SURVEYRESULT'''

CREATE OR REPLACE PROCEDURE INSERTTABLESBYENTERPRISEID (
   ENTERPRISEID   IN STRING
)
IS
   TYPE CURSOR_TEMP IS REF CURSOR;

   V_CURSOR     CURSOR_TEMP;
   V_TBL_NAME   VARCHAR2 (100) := '';
   V_SQL        VARCHAR2 (500) := '';
   V_EXESQL     VARCHAR2 (500) := '';
   V_EXITOBJ      INTEGER := 0;
BEGIN
   V_SQL :=
         'SELECT DISTINCT(RESULTTABLENAME) FROM SD_T_A01_'
      || ENTERPRISEID
      || ' WHERE ISDEL=0';

   OPEN V_CURSOR FOR V_SQL;

   LOOP
      FETCH V_CURSOR INTO   V_TBL_NAME;

      EXIT WHEN V_CURSOR%NOTFOUND;

      IF (V_TBL_NAME IS NULL)
      THEN
         BEGIN
            V_TBL_NAME := 'SD_T_A07_' || ENTERPRISEID;
         END;
      END IF;

     V_EXESQL := 'SELECT COUNT(*)  FROM   USER_OBJECTS WHERE OBJECT_NAME = UPPER (''' || V_TBL_NAME || ''')';
     EXECUTE IMMEDIATE V_EXESQL INTO V_EXITOBJ;

     IF V_EXITOBJ > 0
      THEN
        NULL;
         V_EXITOBJ := 0;
         V_EXESQL := 'SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('''|| V_TBL_NAME || ''') AND COLUMN_NAME = ''SURVEYRESULT''';
         EXECUTE IMMEDIATE V_EXESQL INTO V_EXITOBJ;
         IF V_EXITOBJ > 0
         THEN
            V_SQL := 'ALTER TABLE '|| V_TBL_NAME|| ' ADD SurveyResult VARCHAR2(500)';
            EXECUTE IMMEDIATE V_SQL;
         END IF;
      END IF;

   END LOOP;

   COMMIT;
END INSERTTABLESBYENTERPRISEID;